Calendar Tables II - The Anchor

  • SalvageDog

    SSC Veteran

    Points: 205

    I too am not a fan of the NextDay column. In this age of the DATE data type, why not convert SaleDate to DATE and equate it to the calendar table date? The query plans are roughly the same, and it makes the join a bit simpler.

    SELECT

    CD.DayDate, CD.NameOfDay

    , COUNT(S.SaleID) AS NumberOfSales

    , SUM(ISNULL(S.SaleAmt, 0)) AS DaySales

    FROM CalDay CD

    LEFT JOIN Sales S ON

    CONVERT(DATE, S.SaleDate) = CD.DayDate

    AND S.SlsRepID IN (0, 3)

    WHERE CD.DayID BETWEEN @FirstDay And @FirstDay + 6

    GROUP BY CD.DayDate, CD.NameOfDay

    ORDER BY CD.DayDate;

  • Ryan C. Price

    Ten Centuries

    Points: 1182

    Jeff, challenge accepted.

    something like this?:

    create function [dbo].[fn_callist](@startdate date , @enddate date)

    returns table as

    -- CTE Tally table from http://www.sqlservercentral.com/blogs/never_say_never/2010/03/19/tally_2D00_table_2D00_cte/

    return

    WITH

    t1 AS (SELECT 1 N UNION ALL SELECT 1 N),

    t2 AS (SELECT 1 N FROM t1 x, t1 y),

    t3 AS (SELECT 1 N FROM t2 x, t2 y),

    t4 AS (SELECT 1 N FROM t3 x, t3 y),

    -- t5 AS (SELECT 1 N FROM t4 x, t4 y), -- if 64K days aren't enough, you could expand to include t5.

    cteTally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t4 x, t4 y)

    --- from Jeff's code as above...

    , cteDays AS

    (

    SELECT DayDate = DATEADD(dd,t.N-1,@startdate)

    FROM cteTally t --works for zero and unit based Tally tables in this case

    WHERE t.N BETWEEN 1 AND DATEDIFF(dd,@startdate,@enddate)

    )

    SELECT DayDate,

    NextDay = DATEADD(dd,1,DayDate),

    DayNum = DATEPART(dw,dateadd(day, @@DATEFIRST-1, DayDate)), --Doesn't care what DATEFIRST is set to.

    NameOfDay = DATENAME(dw,DayDate)

    FROM cteDays

    ;

    go

    select * from [dbo].[fn_callist]('2011-01-01', '2011-10-31')

    using both a dynamic tally table and a dynamic calendar table...

  • Jeff Moden

    SSC Guru

    Points: 997212

    SalvageDog (2/21/2014)


    I too am not a fan of the NextDay column. In this age of the DATE data type, why not convert SaleDate to DATE and equate it to the calendar table date? The query plans are roughly the same, and it makes the join a bit simpler.

    SELECT

    CD.DayDate, CD.NameOfDay

    , COUNT(S.SaleID) AS NumberOfSales

    , SUM(ISNULL(S.SaleAmt, 0)) AS DaySales

    FROM CalDay CD

    LEFT JOIN Sales S ON

    CONVERT(DATE, S.SaleDate) = CD.DayDate

    AND S.SlsRepID IN (0, 3)

    WHERE CD.DayID BETWEEN @FirstDay And @FirstDay + 6

    GROUP BY CD.DayDate, CD.NameOfDay

    ORDER BY CD.DayDate;

    Unless that turns out to somehow be SARGable code (I don't believe it will but haven't tested it), that would be the reason why.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • SalvageDog

    SSC Veteran

    Points: 205

    I agree, that is always something to watch for, as using a function on a column in the where or join clause will usually cause a scan plan. In my experience, one exception to this rule is converting the longer date types to DATE, which has always resulted in SARGable code for me. I still test every time though.

Viewing 4 posts - 16 through 19 (of 19 total)

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