Calendar Tables II - The Anchor

Question

Calendar Tables II - The Anchor

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

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

SalvageDog SSC Veteran Points: 205 More actions · Answer 1

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: 1184 More actions · Answer 2

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: 1003863 More actions · Answer 3

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.

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

SalvageDog SSC Veteran Points: 205 More actions · Answer 4

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.