Returning sequential rows from non-sequential data.
-
Good morning!
I have a table that has a date value and a number, something like this:
1/1/2006 12
1/3/2006 33
1/4/2006 23
1/7/2006 18
And I want to write a query that returns the dates sequentially with a zero for the number if no data exists. Something like this:
1/1/2006 12
1/2/2006 0
1/3/2006 33
1/4/2006 23
1/5/2006 0
1/6/2006 0
1/7/2006 18
My plan is to insert my date range into a temp table and join them together on the date value. Unless, of course, there is a better way to accomplish the objective!
My queries may be for any date range and there are numerous gaps in the data. Thanks in advance!
-
If you don't already have one... now is the time to make a "Tally" table... Here's how...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
Then, this problem get's real easy... (on my way to work so haven't tested this actual code but have done similar many times)
--===== Declare local variables
DECLARE @MinDate DATETIME
DECLARE @MaxDate DATETIME
--===== Find the date "limits"
SELECT @MinDate = MIN(yourdatecol),
@MaxDate = MAX(yourdatecol)
--===== Solve the problem (assumes your date column is DATETIME and "has no time")
SELECT @MinDate+t.n-1 AS TheDate,
ISNULL(y.yournumbercol,0)
FROM dbo.Tally t WITH (NOLOCK)
LEFT OUTER JOIN
yourtable y
ON @MinDate+t.n-1 = y.yourdatecol
WHERE t.n <= CAST(@Maxdate-@MinDate+1 AS INT)
--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) -
Excellent! That is exactly what I was looking for.
Thank you!
Ian
-
You're welcome and thank you for the feedback...
--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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply