Availability calculation - a nice T-SQL problem
-
Hi all
I'm looking forward to some innovative solutions to this one.
I have a table of hotel booking data - it includes Hotel Id, Room Type Id, date (in YYYYMMDD int format) and number of rooms free.
I want to build a query which includes these columns, plus one new one, called 'Availability'. This will calculate the number of consecutive days, looking forward, that there is at least one room of this type, at this hotel, free.
You can assume that a row already exists for every date, for every Hotel Id and Room Type Id.
Here is some sample source data:
declare @Booking table (Id int IDENTITY(1, 1) not null primary key clustered
,HotelId int not null
,RoomTypeId int not null
,DateKey int not null
,FreeCount int not null
)
insert @Booking(HotelId,RoomTypeId ,DateKey ,FreeCount)
SELECT 1, 1, 20120101, 2 UNION ALL
SELECT 1, 1, 20120102, 5 UNION ALL
SELECT 1, 1, 20120103, 9 UNION ALL
SELECT 1, 1, 20120104, 1 UNION ALL
SELECT 1, 1, 20120105, 2 UNION ALL
SELECT 1, 1, 20120106, 0 UNION ALL
SELECT 1, 1, 20120107, 2 UNION ALL
SELECT 1, 2, 20120101, 1 UNION ALL
SELECT 1, 2, 20120102, 0 UNION ALL
SELECT 1, 2, 20120103, 5 UNION ALL
SELECT 1, 2, 20120104, 6 UNION ALL
SELECT 1, 2, 20120105, 7 UNION ALL
SELECT 1, 2, 20120106, 8 UNION ALL
SELECT 1, 2, 20120107, 3
select * from @Booking
And, based on the above sample data, here is the output I would like to see:
SELECT 1 HotelId, 1 RoomTypeId, 20120101 DateKey, 2 FreeCount, 5 Availability UNION ALLSELECT 1, 1, 20120102, 5, 4 UNION ALL
SELECT 1, 1, 20120103, 9, 3 UNION ALL
SELECT 1, 1, 20120104, 1, 2 UNION ALL
SELECT 1, 1, 20120105, 2, 1 UNION ALL
SELECT 1, 1, 20120106, 0, 0 UNION ALL
SELECT 1, 1, 20120107, 2, 1 UNION ALL
SELECT 1, 2, 20120101, 1, 1 UNION ALL
SELECT 1, 2, 20120102, 0, 0 UNION ALL
SELECT 1, 2, 20120103, 5, 5 UNION ALL
SELECT 1, 2, 20120104, 6, 4 UNION ALL
SELECT 1, 2, 20120105, 7, 3 UNION ALL
SELECT 1, 2, 20120106, 8, 2 UNION ALL
SELECT 1, 2, 20120107, 3, 1
Anyone got any tidy solutions?
Thanks in advance.
-
Try this.
WITH CTE AS (
SELECT HotelId,RoomTypeId ,DateKey ,FreeCount,
ROW_NUMBER() OVER(PARTITION BY HotelId,RoomTypeId
ORDER BY DateKey DESC) AS rn1,
ROW_NUMBER() OVER(PARTITION BY HotelId,RoomTypeId,CASE WHEN FreeCount=0 THEN 0 ELSE 1 END
ORDER BY DateKey DESC) AS rn2
FROM @Booking)
SELECT HotelId,RoomTypeId ,DateKey ,FreeCount,
CASE WHEN FreeCount=0
THEN 0
ELSE ROW_NUMBER() OVER(PARTITION BY HotelId,RoomTypeId,CASE WHEN FreeCount=0 THEN 0 ELSE 1 END,rn1-rn2
ORDER BY rn1)
END AS Availability
FROM CTE
ORDER BY HotelId,RoomTypeId ,DateKey;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
There is another way with using quirky update instead of cte with two window functions.
select *, CAST(FreeCount AS BIT) avail, cast(null as int) grp
into #ba
from @Booking b
order by Id
create unique clustered index ixc_#ba on #ba(HotelId, RoomTypeId, DateKey)
declare @hotelId int, @RoomTypeId int, @avail bit, @grp int
update #ba
set @grp = CASE WHEN HotelId = @hotelId and RoomTypeId = @RoomTypeId and avail = @avail then @grp
WHEN HotelId = @hotelId and RoomTypeId = @RoomTypeId and avail != @avail then @grp + 1
ELSE 1
END
,grp = isnull(@grp,1)
,@hotelId = HotelId
,@RoomTypeId = RoomTypeId
,@avail = avail
from #ba with (tablockx)
option (maxdop 1)
select *,
ROW_NUMBER() OVER (PARTITION BY HotelId, RoomTypeId, grp ORDER BY DateKey DESC)
- CAST(~avail AS INT) As Availability
from #ba
ORDER BY Id
You can check which method performs better for you...
-
Excellent, thanks guys. Both ways look good to me - as I expand the problem into the real world, I'll assess which fits best.
-
Here is one more way:
;with MyCTE as (
select HotelId, RoomTypeId, cast (DateKey as char(8)) as DateKey, FreeCount,
row_number() over (partition by RoomTypeId, case when FreeCount = 0 then 0 else 1 end order by DateKey) as Num
from @Booking)
select HotelId,RoomTypeId ,DateKey ,FreeCount,
case when FreeCount = 0 then 0 else row_number() over (partition by RoomTypeId, datediff(dd,'20120101',DateKey) - Num order by DateKey desc) end as Booking from MyCTE
order by HotelId, RoomTypeId, DateKey
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Adi Cohn-120898 (6/25/2012)
Here is one more way:;with MyCTE as (
select HotelId, RoomTypeId, cast (DateKey as char(8)) as DateKey, FreeCount,
row_number() over (partition by RoomTypeId, case when FreeCount = 0 then 0 else 1 end order by DateKey) as Num
from @Booking)
select HotelId,RoomTypeId ,DateKey ,FreeCount,
case when FreeCount = 0 then 0 else row_number() over (partition by RoomTypeId, datediff(dd,'20120101',DateKey) - Num order by DateKey desc) end as Booking from MyCTE
order by HotelId, RoomTypeId, DateKey
Nice. My favourite so far, I think.
-
Don't forget the rCTE version:
;WITH OrderedData AS (SELECT *, rn = ROW_NUMBER() OVER (ORDER BY HotelId, RoomTypeId, DateKey DESC) FROM @Booking),Calculator AS (
SELECT rn, Id, HotelId, RoomTypeId, DateKey, FreeCount,
Availability = CASE WHEN FreeCount = 0 THEN 0 ELSE 1 END
FROM OrderedData
WHERE rn = 1
UNION ALL
SELECT tr.rn, tr.Id, tr.HotelId, tr.RoomTypeId, tr.DateKey, tr.FreeCount,
Availability = CASE
WHEN tr.FreeCount = 0 THEN 0
WHEN tr.HotelId <> lr.HotelId OR tr.RoomTypeId <> lr.RoomTypeId THEN 1
ELSE Availability + 1 END
FROM Calculator lr
INNER JOIN OrderedData tr ON tr.rn = lr.rn+1
)
SELECT HotelId, RoomTypeId, DateKey, FreeCount, Availability
FROM Calculator
ORDER BY HotelId, RoomTypeId, DateKey
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Edit: Removed the solution as it was incorrect for some cases on a larger data set
Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
just as a side dish ... why is column datekey not a date column ?
May avoid conversions to take advantage of date functions.
( maybe that's all been covered in your dates table which holds, month, weekno, weekInMonth, ...)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I like this problem and wanted to contribute. Alas my query contribution (as follows) barks like the dog that it is.
;WITH Booking AS (
SELECT ID, HotelID, RoomTypeID, Datekey
,Datekey1=CAST(CAST(DateKey AS VARCHAR(8)) AS DATETIME), FreeCount
,rn=ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY Datekey DESC)
FROM @Booking)
SELECT HotelID, RoomTypeID, DateKey, FreeCount
,Availability=ISNULL((
SELECT TOP 1 CAST(b2.Datekey1-b1.Datekey1 AS INT)
FROM Booking b2
WHERE b1.HotelID = b2.HotelID and b1.RoomTypeID = b2.RoomTypeID
AND b2.Datekey >= b1.Datekey and FreeCount = 0
ORDER BY Datekey), b1.rn)
FROM Booking b1
ORDER BY HotelID, RoomTypeID, Datekey
However I can contribute by performance testing the profferred solutions. You can use any Calendar table to replace GenerateCalendar in the test harness.
declare @Booking table (
Id int IDENTITY(1, 1) not null primary key clustered
,HotelId int not null
,RoomTypeId int not null
,DateKey int not null
,FreeCount int not null
)
insert @Booking(HotelId,RoomTypeId ,DateKey ,FreeCount)
SELECT HotelID, RoomTypeID, YYYYMM*100+Day
,ABS(CHECKSUM(NEWID())) % 5
FROM (SELECT 1, 1 UNION ALL SELECT 1, 2) x(HotelId,RoomTypeId)
CROSS APPLY dbo.GenerateCalendar('2012-01-01', 2000) c
SET STATISTICS TIME ON
PRINT 'DWAIN'
;WITH Booking AS (
SELECT ID, HotelID, RoomTypeID, Datekey
,Datekey1=CAST(CAST(DateKey AS VARCHAR(8)) AS DATETIME), FreeCount
,rn=ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY Datekey DESC)
FROM @Booking)
SELECT HotelID, RoomTypeID, DateKey, FreeCount
,Availability=ISNULL((
SELECT TOP 1 CAST(b2.Datekey1-b1.Datekey1 AS INT)
FROM Booking b2
WHERE b1.HotelID = b2.HotelID and b1.RoomTypeID = b2.RoomTypeID
AND b2.Datekey >= b1.Datekey and FreeCount = 0
ORDER BY Datekey), b1.rn)
FROM Booking b1
ORDER BY HotelID, RoomTypeID, Datekey
PRINT 'MARK'
;WITH CTE AS (
SELECT HotelId,RoomTypeId ,DateKey ,FreeCount,
ROW_NUMBER() OVER(PARTITION BY HotelId,RoomTypeId
ORDER BY DateKey DESC) AS rn1,
ROW_NUMBER() OVER(PARTITION BY HotelId,RoomTypeId,CASE WHEN FreeCount=0 THEN 0 ELSE 1 END
ORDER BY DateKey DESC) AS rn2
FROM @Booking)
SELECT HotelId,RoomTypeId ,DateKey ,FreeCount,
CASE WHEN FreeCount=0
THEN 0
ELSE ROW_NUMBER() OVER(PARTITION BY HotelId,RoomTypeId,CASE WHEN FreeCount=0 THEN 0 ELSE 1 END,rn1-rn2
ORDER BY rn1)
END AS Availability
FROM CTE
ORDER BY HotelID, RoomTypeID, Datekey
PRINT 'ADI COHN'
;with MyCTE as (
select HotelId, RoomTypeId, cast (DateKey as char(8)) as DateKey, FreeCount,
row_number() over (partition by RoomTypeId, case when FreeCount = 0 then 0 else 1 end order by DateKey) as Num
from @Booking)
select HotelId,RoomTypeId ,DateKey ,FreeCount,
case when FreeCount = 0 then 0 else row_number() over (partition by RoomTypeId, datediff(dd,'20120101',DateKey) - Num order by DateKey desc) end as Booking from MyCTE
order by HotelId, RoomTypeId, DateKey
PRINT 'CHRISM'
;WITH OrderedData AS (SELECT *, rn = ROW_NUMBER() OVER (ORDER BY HotelId, RoomTypeId, DateKey DESC) FROM @Booking),
Calculator AS (
SELECT rn, Id, HotelId, RoomTypeId, DateKey, FreeCount,
Availability = CASE WHEN FreeCount = 0 THEN 0 ELSE 1 END
FROM OrderedData
WHERE rn = 1
UNION ALL
SELECT tr.rn, tr.Id, tr.HotelId, tr.RoomTypeId, tr.DateKey, tr.FreeCount,
Availability = CASE
WHEN tr.FreeCount = 0 THEN 0
WHEN tr.HotelId <> lr.HotelId OR tr.RoomTypeId <> lr.RoomTypeId THEN 1
ELSE Availability + 1 END
FROM Calculator lr
INNER JOIN OrderedData tr ON tr.rn = lr.rn+1
)
SELECT HotelId, RoomTypeId, DateKey, FreeCount, Availability
FROM Calculator
OPTION (MAXRECURSION 0)
SET STATISTICS TIME OFF
Giving these CPU/Elapsed time results:
DWAIN
SQL Server Execution Times:
CPU time = 1607 ms, elapsed time = 1752 ms.
MARK
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 234 ms.
ADI COHN
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 153 ms.
CHRISM
SQL Server Execution Times:
CPU time = 20732 ms, elapsed time = 20962 ms.
I've got to get a better handle on what you're doing with ROW_NUMBER() so I can bark with you big dogs but least I beat one of you old-timers!
Apologies to Eugene and Kingston for omitting yours from the harness.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
First of all, thanks Dwain for doing the testing & suggesting your own solution.
ChrisM - think you've got some tuning to do 🙂
just as a side dish ... why is column datekey not a date column ?
May avoid conversions to take advantage of date functions.
It's because this data is coming from a data warehouse, not a transactional system.
-
dwain.c (6/27/2012)
I like this problem and wanted to contribute.Let other people in on the fun. 🙂 What does dbo.GenerateCalendar() look like?
--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) -
Phil Parkin (6/25/2012)
Hi allI'm looking forward to some innovative solutions to this one.
Although I very much appreciate the solutions given so far, the non-DATETIME datatype throws a pretty big monkey-wrench into things. My question would be could the column be converted to a DATETIME? If not, can a persisted calculated column be added to the table?
--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 (6/28/2012)
dwain.c (6/27/2012)
I like this problem and wanted to contribute.Let other people in on the fun. 🙂 What does dbo.GenerateCalendar() look like?
Please don't laugh! This was a very early attempt by me to get the value of a Calendar table without actually creating the table. It is slower than reading from a table.
For what it's worth.
USE [SQL08-SANDBOX]
GO
/****** Object: UserDefinedFunction [dbo].[GenerateCalendar] Script Date: 06/28/2012 21:12:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:D. Camps
-- Create date: 29-Dec-2011
-- Description:Generates a calendar table with sequential day numbering (@FromDate = SeqNo 1).
--See RETURNS table (comments) for meaning of each column.
-- Performance Notes:
--1) Max for NoDays is 65536, which runs in just over 2 seconds.
--
-- Example calls to generate the calendar:
-- 1) Forward for 365 days starting today:
--DECLARE @Date DATETIME
--SELECT @Date = GETDATE()
--SELECT * FROM dbo.GenerateCalendar(@Date, 365) ORDER BY SeqNo
-- 2) Backwards for 365 days back starting today:
--DECLARE @Date DATETIME
--SELECT @Date = GETDATE()
--SELECT * FROM dbo.GenerateCalendar(@Date, -365) ORDER BY SeqNo
-- 3) For only the FromDate:
--DECLARE @Date DATETIME
--SELECT @Date = GETDATE()
--SELECT * FROM dbo.GenerateCalendar(@Date, 1)
-- 4) Including only the last week days of each month:
--Note: Seq no in this case are as if all dates were generated
--DECLARE @Date DATETIME
--SELECT @Date = GETDATE()
--SELECT * FROM dbo.GenerateCalendar(@Date, 365) WHERE Last = 1 ORDER BY SeqNo
-- =============================================
CREATE FUNCTION [dbo].[GenerateCalendar]
(
@FromDateDATETIME
,@NoDaysINT
)
RETURNS @DatesTABLE
(
SeqNoINT-- Sequential day number (@FromDate always = 1) forward or back
,DateDATETIME-- Date (with 00:00:00 for time component)
,[Year]INT-- Four digit year
,YrINT-- Two digit year
,YYYYMMINT-- Integer YYYYMM (year * 100 + month)
,BuddhaYrINT-- Buddha year
,[Month]TINYINT-- Month
,[Day]TINYINT-- Day (of month)
,WkDNoINT-- Week day number (based on @@DATEFIRST)
,WkDNameNVARCHAR(9)-- Full name of the week day (e.g., Monday, Tuesday, etc.)
,WkDName2NVARCHAR(2)-- Name of the week day (e.g., Mo, Tu, etc.)
,WkDName3NVARCHAR(3)-- Name of the week day (e.g., Mon, Tue, etc.)
,JulDayINT-- Julian day (day number of the year)
,JulWkINT-- Week number of the year
,WkNoTINYINT-- Week number
,QtrNoTINYINT-- Quarter number
,LastTINYINT-- Numbers the weeks for the month in reverse
,LDofMoTINYINT-- Last Day of Month
,LDTofMoDATETIME-- Last Day of Month (as date)
)
AS
BEGIN;
-- Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen)
WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 )
,Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 )
,Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 )
,Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 )
,Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
INSERT INTO @Dates
SELECT SeqNo, Date
,DATEPART(year, Date)As [Year]
,DATEPART(year, Date) % 100As [YrNN]
,DATEPART(year, Date) * 100 + DATEPART(month, Date)As [YYYYMM]
,DATEPART(year, Date) + 543As [BuddhaYr]
,DATEPART(month, Date)As [Month]
,DATEPART(day, Date)As [Day]
,DATEPART(weekday, Date)As WkDNo
,CAST(DATENAME(weekday, Date) AS NVARCHAR(10))As WkDName
,SUBSTRING(CAST(DATENAME(weekday, Date) AS NVARCHAR(10)),1,2)As WkDName2
,SUBSTRING(CAST(DATENAME(weekday, Date) AS NVARCHAR(10)),1,3)As WkDName3
,DATEPART(dy, Date)As JulDay
,1+(DATEPART(dy, Date) - 1)/7As JulWk
,CASE WHEN DATEPART(day, Date) BETWEEN 1 AND 7 THEN 1
WHEN DATEPART(day, Date) BETWEEN 8 AND 14 THEN 2
WHEN DATEPART(day, Date) BETWEEN 15 AND 21 THEN 3
WHEN DATEPART(day, Date) BETWEEN 22 AND 28 THEN 4
ELSE 5 ENDAs WkNo
,CASE WHEN 401 > 100*DATEPART(month, Date)+DATEPART(day,Date) THEN 1
WHEN 701 > 100*DATEPART(month, Date)+DATEPART(day,Date) THEN 2
WHEN 1001 > 100*DATEPART(month, Date)+DATEPART(day,Date) THEN 3
ELSE 4 ENDAs Qtr
,CASE WHEN DATEPART(day, Date) BETWEEN LDofMo - 6 AND LDofMo THEN 1
WHEN DATEPART(day, Date) BETWEEN LDofMo - 13 AND LDofMo - 7 THEN 2
WHEN DATEPART(day, Date) BETWEEN LDofMo - 20 AND LDofMo - 14 THEN 3
WHEN DATEPART(day, Date) BETWEEN LDofMo - 27 AND LDofMo - 21 THEN 4
ELSE 5 ENDAs Last
,LDofMoAs LDofMo
,LDTofMoAs LDTofMo
FROM (
-- Generate all base dates moving forward or backward from the FromDate
SELECT DATEADD(day, CASE WHEN @NoDays > 0 THEN n - 1 ELSE -(n - 1) END
,FromDate)As Date
,nAS SeqNo
,DATEPART(day, DATEADD(day, -1, DATEADD(month, 1+DATEDIFF(month, 0
,DATEADD(day, CASE WHEN @NoDays > 0 THEN n - 1 ELSE -(n - 1) END
,FromDate)), 0)))AS LDofMo
,DATEADD(day, -1, DATEADD(month, 1+DATEDIFF(month, 0
,DATEADD(day, CASE WHEN @NoDays > 0 THEN n - 1 ELSE -(n - 1) END
,FromDate)), 0))AS LDTofMo
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY n) As n
FROM Nbrs) D
CROSS APPLY (SELECT FromDate = DATEADD(day, DATEDIFF(day, 0, @FromDate), 0)) z
WHERE n <= ABS(@NoDays)
) Dates
RETURN
END
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
dwain.c (6/28/2012)
Please don't laugh! This was a very early attempt by me to get the value of a Calendar table without actually creating the table. It is slower than reading from a table.For what it's worth.
It's worth a lot and certainly nothing to laugh at especially since you used it to generate test data for other folks. Thanks for your efforts on all the testing you've been doing lately. In fact, here's a gift for you... same thing as yours... just a little shorter and a little bit faster thanks to a trick that ChrisM showed me. No need for me to mark the trick. You'll see it immediately. It made for some wonderfully easy to read code IMHO.
CREATE FUNCTION [dbo].[GenerateCalendar1]
(
@FromDate DATETIME,
@NoDays INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen)
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows
E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows
cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
SELECT [SeqNo] = t.N,
[Date] = dt.DT,
[Year] = dp.YY,
[YrNN] = dp.YY % 100,
[YYYYMM] = dp.YY * 100 + dp.MM,
[BuddhaYr] = dp.YY + 543,
[Month] = dp.MM,
[Day] = dp.DD,
[WkDNo] = DATEPART(dw,dt.DT),
[WkDName] = CONVERT(NCHAR(9),dp.DW),
[WkDName2] = CONVERT(NCHAR(2),dp.DW),
[WkDName3] = CONVERT(NCHAR(3),dp.DW),
[JulDay] = dp.DY,
[JulWk] = dp.DY/7+1,
[WkNo] = dp.DD/7+1,
[Qtr] = DATEPART(qq,dt.Dt),
[Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,
[LdOfMo] = DATEPART(dd,dp.LDtOfMo),
[LDtOfMo] = dp.LDtOfMo
FROM cteTally t
CROSS APPLY ( --=== Create the date
SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)
) dt
CROSS APPLY ( --=== Create the other parts from the date above using a "cCA"
-- (Cascading CROSS APPLY, Acourtesy of ChrisM)
SELECT YY = DATEPART(yy,dt.DT),
MM = DATEPART(mm,dt.DT),
DD = DATEPART(dd,dt.DT),
DW = DATENAME(dw,dt.DT),
Dy = DATEPART(dy,dt.DT),
LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)
) dp
;
Test code:
DECLARE @Date DATETIME;
SELECT @Date = '2000';
SET STATISTICS TIME ON;
SELECT * FROM dbo.GenerateCalendar(@Date, 65535);
SELECT * FROM dbo.GenerateCalendar1(@Date, 65535);
SET STATISTICS TIME OFF;
Results from my 10 year old, 1.8GHz Single CPU desktop on 2k5.
(65535 row(s) affected)
SQL Server Execution Times:
CPU time = 3875 ms, elapsed time = 9007 ms.
(65535 row(s) affected)
SQL Server Execution Times:
CPU time = 953 ms, elapsed time = 6153 ms.
I agree though... a real Calendar Table would still be faster especially since I just don't know of too many folks that would count negative days or days over 65536 (179+ years) from now.
--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 - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply