A DateRange Table Valued function

  • IMHO, the closest thing to a CROSS APPLY is a "Correlated Sub-Query" with an implicit join, which can return more than one row per input row if you're not careful or... if that's what you want, as in "Relational Multiplication".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    IMHO, the closest thing to a CROSS APPLY is a "Correlated Sub-Query" with an implicit join, which can return more than one row per input row if you're not careful or... if that's what you want, as in "Relational Multiplication".

    Not that I doubt you Jeff (I've learned never to do that! 🙂 ) but I don't follow you in reference to the term "Correlated". Table A (with 100 rows) cross joined with Table B (with 15 rows) will produce a result set with 1500 rows without any join criteria or limitation of values from one table to the other. Doesn't correlation mean a reference or relationship between the two tables? Could you let me in on your rationale?

  • Just for giggles and grins today, I wrote a version of Jonathan's function that is similar but does not use the CROSS JOIN nor the IIF/CASE statements:

    CREATE FUNCTION dbo.fnDateTimeRange(@StartDate datetime2, @EndDate datetime2, @DatePart varchar(3)='dd', @Interval int=1)
    RETURNS TABLE WITH SCHEMABINDING
    AS RETURN
    WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(n)) -- 16 records
    ,e4(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b CROSS JOIN e1 c CROSS JOIN e1 d) -- 16^4 or 65,536 records (256*256)
    ,cteTally(seqnbr, direction) AS (
    SELECT TOP(ABS(CASE @DatePart WHEN 'ns' THEN DATEDIFF(ns, @EndDate, @StartDate)/@Interval
    WHEN 'mcs' THEN DATEDIFF(mcs,@EndDate, @StartDate)/@Interval
    WHEN 'ms' THEN DATEDIFF(ms, @EndDate, @StartDate)/@Interval
    WHEN 'ss' THEN DATEDIFF(ss, @EndDate, @StartDate)/@Interval
    WHEN 'mi' THEN DATEDIFF(mi, @EndDate, @StartDate)/@Interval
    WHEN 'hh' THEN DATEDIFF(hh, @EndDate, @StartDate)/@Interval
    WHEN 'dd' THEN DATEDIFF(dd, @EndDate, @StartDate)/@Interval
    WHEN 'ww' THEN DATEDIFF(ww, @EndDate, @StartDate)/@Interval
    WHEN 'mm' THEN DATEDIFF(mm, @EndDate, @StartDate)/@Interval
    WHEN 'qq' THEN DATEDIFF(qq, @EndDate, @StartDate)/@Interval
    WHEN 'yy' THEN DATEDIFF(yy, @EndDate, @StartDate)/@Interval
    ELSE ABS(DATEDIFF(dd, @EndDate, @StartDate))/@Interval
    END) + 1)
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1,
    CASE WHEN @StartDate < @EndDate THEN 1 ELSE -1 END
    FROM e4 a CROSS JOIN e4 b-- 16^8 or 4,294,967,296 records (65,536*65,536)
    )
    SELECT CASE @DatePart WHEN 'ns' THEN DATEADD(ns, direction * seqnbr * @interval, @StartDate)
    WHEN 'mcs' THEN DATEADD(mcs,direction * seqnbr * @interval, @StartDate)
    WHEN 'ms' THEN DATEADD(ms, direction * seqnbr * @interval, @StartDate)
    WHEN 'ss' THEN DATEADD(ss, direction * seqnbr * @interval, @StartDate)
    WHEN 'mi' THEN DATEADD(mi, direction * seqnbr * @interval, @StartDate)
    WHEN 'hh' THEN DATEADD(hh, direction * seqnbr * @interval, @StartDate)
    WHEN 'dd' THEN DATEADD(dd, direction * seqnbr * @interval, @StartDate)
    WHEN 'ww' THEN DATEADD(ww, direction * seqnbr * @interval, @StartDate)
    WHEN 'mm' THEN DATEADD(mm, direction * seqnbr * @interval, @StartDate)
    WHEN 'qq' THEN DATEADD(qq, direction * seqnbr * @interval, @StartDate)
    WHEN 'yy' THEN DATEADD(yy, direction * seqnbr * @interval, @StartDate)
    ELSE DATEADD(dd, direction * seqnbr * @interval, @StartDate)
    END AS DateTimeValue
    FROM cteTally;
    GO

    I'd appreciate any feedback on it.

  • Hi Aaron, Yes, good idea to remove the cross apply and put the logic in the CTE. Also, changing the IIF to CASE means it should work on older versions of SQL Server. I've amended my code a bit with your ideas, it's made the code a little bit shorter and processing very slightly faster.

    ALTER FUNCTION [dbo].[DateRange] 
    (
    @StartDate datetime2,
    @EndDate datetime2,
    @DatePart nvarchar(3)='dd',
    @Interval int=1
    )
    RETURNS TABLE WITH SCHEMABINDING AS RETURN
    WITH A(A) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) A(A)),
    T(AddAmount) AS (SELECT TOP(ABS(CASE @DatePart
    WHEN 'ns' THEN DATEDIFF(ns, @EndDate, @StartDate)/@Interval
    WHEN 'mcs' THEN DATEDIFF(mcs,@EndDate, @StartDate)/@Interval
    WHEN 'ms' THEN DATEDIFF(ms, @EndDate, @StartDate)/@Interval
    WHEN 'ss' THEN DATEDIFF(ss, @EndDate, @StartDate)/@Interval
    WHEN 'mi' THEN DATEDIFF(mi, @EndDate, @StartDate)/@Interval
    WHEN 'hh' THEN DATEDIFF(hh, @EndDate, @StartDate)/@Interval
    WHEN 'ww' THEN DATEDIFF(ww, @EndDate, @StartDate)/@Interval
    WHEN 'mm' THEN DATEDIFF(mm, @EndDate, @StartDate)/@Interval
    WHEN 'qq' THEN DATEDIFF(qq, @EndDate, @StartDate)/@Interval
    WHEN 'yy' THEN DATEDIFF(yy, @EndDate, @StartDate)/@Interval
    ELSE DATEDIFF(dd, @EndDate, @StartDate)/@Interval
    END) + 1)
    (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) * CASE WHEN @StartDate < @EndDate THEN 1 ELSE -1 END * @Interval
    FROM A A, A B, A C, A D, A E, A F, A G, A H) -- A maximum of 16^8 (or 2^32) rows could be returned from this inline tally
    SELECT CASE @DatePart
    WHEN 'ns' THEN DATEADD(ns, T.AddAmount, @StartDate)
    WHEN 'mcs' THEN DATEADD(mcs,T.AddAmount, @StartDate)
    WHEN 'ms' THEN DATEADD(ms, T.AddAmount, @StartDate)
    WHEN 'ss' THEN DATEADD(ss, T.AddAmount, @StartDate)
    WHEN 'mi' THEN DATEADD(mi, T.AddAmount, @StartDate)
    WHEN 'hh' THEN DATEADD(hh, T.AddAmount, @StartDate)
    WHEN 'dd' THEN DATEADD(dd, T.AddAmount, @StartDate)
    WHEN 'ww' THEN DATEADD(ww, T.AddAmount, @StartDate)
    WHEN 'mm' THEN DATEADD(mm, T.AddAmount, @StartDate)
    WHEN 'qq' THEN DATEADD(qq, T.AddAmount, @StartDate)
    WHEN 'yy' THEN DATEADD(yy, T.AddAmount, @StartDate)
    ELSE DATEADD(dd, T.AddAmount, @StartDate)
    END [Value]
    FROM T

     

  • Aaron N. Cutshall wrote:

    Jeff Moden wrote:

    IMHO, the closest thing to a CROSS APPLY is a "Correlated Sub-Query" with an implicit join, which can return more than one row per input row if you're not careful or... if that's what you want, as in "Relational Multiplication".

    Not that I doubt you Jeff (I've learned never to do that! 🙂 ) but I don't follow you in reference to the term "Correlated". Table A (with 100 rows) cross joined with Table B (with 15 rows) will produce a result set with 1500 rows without any join criteria or limitation of values from one table to the other. Doesn't correlation mean a reference or relationship between the two tables? Could you let me in on your rationale?

    The "correlation" is that for every row in Table A, something (Action A) will be done with it (more like an OUTER APPLY because of the "every row" comment).  You can easily accomplish the same thing through the use of a Sub-Query in a SELECT list, which frequently has a lookup in another table and correlates that lookup between the current row and the table being used for the lookup.  If it's not a table that you're looking up for every row, but a formula instead, it's still very much the same as CROSS APPLY with the only real difference being that a correlated sub-query in a SELECT list must be constrained to return a single value rather than multiple values as you can do in an APPLY (CROSS or OUTER).

    An example is that you can actually use an iTVF in the SELECT list of a query using a correlated sub-query if the iTVF returns only one value (implied iSF). The iTVF can contain a formula or a reference to another table.  It could just be a SELECT though.

    Another example is that (as you're seriously aware), that we used to join Tally Tables in the from clause and it had a correlation between some value in Table A (such as the length of the column) and which range of values would be returned from the Tally Table to get the necessary Relational Muliplication.

    I actually don't like the term CROSS APPLY because too many people related it to cross JOINs.  They should have named it INNER APPLY because that's what it really boils down to.  And, yes, you can get an accidental (or intentional) CROSS JOIN out of it just like you can with a correlated sub-query or even a simple bit of bad criteria in an INNER JOIN.

    As a bit of a sidebar, when I first found out what CROSS APPLY did, my first thought was "COOL!  Now I don't have to write correlated sub-queries in the SELECT list anymore".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    The "correlation" is that for every row in Table A, something (Action A) will be done with it (more like an OUTER APPLY because of the "every row" comment).  You can easily accomplish the same thing through the use of a Sub-Query in a SELECT list, which frequently has a lookup in another table and correlates that lookup between the current row and the table being used for the lookup.  If it's not a table that you're looking up for every row, but a formula instead, it's still very much the same as CROSS APPLY with the only real difference being that a correlated sub-query in a SELECT list must be constrained to return a single value rather than multiple values as you can do in an APPLY (CROSS or OUTER).

    That's where I don't follow you. A correlated sub-query in a SELECT list, as you pointed out, must return a single value so therefore has no effect on the number of rows returned.

    Jeff Moden wrote:

    Another example is that (as you're seriously aware), that we used to join Tally Tables in the from clause and it had a correlation between some value in Table A (such as the length of the column) and which range of values would be returned from the Tally Table to get the necessary Relational Muliplication.

    This is the part that I'm much more familiar with and even count upon. In fact, I've used Tally Tables more often as an INNER JOIN so that I can apply constraints to the Tally Table (such as string length). However, I've also used them as unbounded to produce a row where otherwise there would not be (such as finding missing dates) and that's where the CROSS APPLY becomes handy.

    Jeff Moden wrote:

    I actually don't like the term CROSS APPLY because too many people related it to cross JOINs.  They should have named it INNER APPLY because that's what it really boils down to.  And, yes, you can get an accidental (or intentional) CROSS JOIN out of it just like you can with a correlated sub-query or even a simple bit of bad criteria in an INNER JOIN.

    I look at CROSS APPLY as to mean "apply every row of this table to every row of the other table(s)". In that sense, it seems to me that an OUTER APPLY would be more applicable than INNER APPLY since INNER implies some sort of restriction. Therefore, what I said earlier about a CROSS APPLY being like an INNER JOIN ON 1=1 is not correct.

    As always Jeff, I appreciate your insight!!

  • is this compatible with SQL2000?

  • chenks wrote:

    is this compatible with SQL2000?

    No, as SQL 2000 did not have CROSS APPLY or selecting from VALUES or the datetime2 data type or ROW_NUMBER() or IIF or TOP.

     

  • damn, that scuppers that idea then.

    as i have a problem that this might have been a solution for.

  • chenks wrote:

    damn, that scuppers that idea then.

    as i have a problem that this might have been a solution for.

    I believe that there is a solution. I'll need a bit of time to test my theory, so have a bit of patience.

  • many thanks!

    just for reference - this is thread explaining what i am trying to achieve

    https://www.sqlservercentral.com/forums/topic/count-number-of-days-between-two-dates-excluding-weekends-for-a-particular-month

  • It's been a while since I've worked on SQL 2000 so I'm a bit rusty on what's allowable and what's not. I also realized that I couldn't set my 2017 database to 2000 compatibility mode!

    I'm thinking that perhaps the best thing to do is to create a DATE_DIM table like what's used in data warehouses. We may have to use some ugly T-SQL that works in SQL 2000, but once created then it's available. According to the post you reference time was not an issue, correct?

  • yes time is not an issue as we are only counting full days, so the two time fields can be ignored, only the date start and date end are the important ones.

    I'll drop you a PM as i may be able to give you access to an SQL 2000 database if you need it.

  • chenks wrote:

    yes time is not an issue as we are only counting full days, so the two time fields can be ignored, only the date start and date end are the important ones.

    I'll drop you a PM as i may be able to give you access to an SQL 2000 database if you need it.

    Did you ever solve your problem for 2000?  If not, post the problem in the "2000" forum on this site and PM me with the link to the post.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jonathan AC Roberts wrote:

    Jeff Moden wrote:

    Nice function, Jonathan.  Well done!

    Thank you Jeff, that's a great compliment coming from someone as experienced as you.

    I just used the function to hammer out a solution to a forum question.  It made stuff wicked easy.  Again, nicely done, Mr. Roberts!  You should have done this as a lightning -round for PASS Summit this year!  Maybe next year?

    Here's the link for the forum question...

    https://www.sqlservercentral.com/forums/topic/tsql-create-dynamic-partition-datarange#post-3944333

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply