Sub Query for datediff

Question

Hi i am trying to create a sub query for the function of DateDiff so i can use that data to calculate recurring dates. However i am strugling to create the sub query, could anyone help?

I have the function Datediff as DateDiff([PeriodTypeID],[EventStart],[EventEnd]) but i am strugling to write a working subquery.

Thanks

Answer 1

First, create a query that will return 10 numbers - from 0 to 9:

SELECT DISTINCT

    Abs([id] Mod 10) AS N

FROM

    MSysObjects;

Save it by the name Ten.

Now you can run this stellar query to create a series of dates with any date count with any number of any interval starting from any date within the entire range of Date values:

PARAMETERS

    [Interval] Text ( 255 ),

    [Number] IEEEDouble,

    [Date] DateTime,

    [Count] IEEEDouble;

SELECT

    [Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000+[Ten_7].[N]*10000000 AS Id,

    DateAdd([Interval],Fix([Number])*([Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000+[Ten_7].[N]*10000000),[Date]) AS [Date]

FROM

    Ten AS Ten_0,

    Ten AS Ten_1,

    Ten AS Ten_2,

    Ten AS Ten_3,

    Ten AS Ten_4,

    Ten AS Ten_5,

    Ten AS Ten_6,

    Ten AS Ten_7

WHERE

    ((([Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000+[Ten_7].[N]*10000000)<[Count])

    AND

    (([Interval]) In ("s","n","h","d","w","ww","m","q","yyyy"))

    AND

    ((Fix([Number])*([Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000+[Ten_7].[N]*10000000) Mod IIf(Fix([Number])=0,1,Fix([Number])))=0)

    AND

    ((Ten_0.N)<=[Count]\1)

    AND

    ((Ten_1.N)<=[Count]\10)

    AND

    ((Ten_2.N)<=[Count]\100)

    AND

    ((Ten_3.N)<=[Count]\1000)

    AND

    ((Ten_4.N)<=[Count]\10000)

    AND

    ((Ten_5.N)<=[Count]\100000)

    AND

    ((Ten_6.N)<=[Count]\1000000)

    AND

    ((Ten_7.N)<=[Count]\10000000));

Answer 2

Thankyou very much, that seems a better way.

Thankyou