Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL return record for every day of year


T-SQL return record for every day of year

Author
Message
hughesj23
hughesj23
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 167
I'm having a hard time explaining my intentions here. I will do my best. Hopefully someone can read my mind. LOL

I have a table with one record per month. For example:

Month Price
1/1/09 50
2/1/09 58
3/1/09 65

I want to write a query that will duplicate each month's record and return a record for every day of the year. So, using above example, bring back this:

1/1/09 50
1/2/09 50
1/3/09 50
duplicated all the way thru 1/31/09
2/1/09 58
2/2/09 58
2/3/09 58
duplicated all the way thru 2/28/09

I could join my table by month/year to another static table that just had all the days of the year in it. But I was hoping to avoid creating and remembering to maintain such a table every year.

Thanks.
Joel



Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38939
Joel you are on the right track; you'll need to join against another table, but it doesn't necessarily need to be a real calendar table. you can use a Tally or Numbers table;


here is an example of an inline Tally table tat is being used to generate days with some data function.

then the code is generating balances for all the days that were not featured in the data...similar to what you are planning;

example results:
TranDate   TransactionAmount   Balance
2008-12-01 00:00:00.000   100.00   100.00
2008-12-02 00:00:00.000   0.00   100.00
2008-12-03 00:00:00.000   -60.00   40.00
2008-12-04 00:00:00.000   10.00   50.00
2008-12-05 00:00:00.000   0.00   50.00
2008-12-06 00:00:00.000   0.00   50.00


the code example:

SET DATEFORMAT MDY
DROP TABLE #myBalances
CREATE TABLE #myBalances (
[TranDate] DATETIME,
[TransactionAmount] money,
[Balance] money)

INSERT INTO #myBalances VALUES ('12/1/2008',100,100)
INSERT INTO #myBalances VALUES ('12/3/2008',-60,40)
INSERT INTO #myBalances VALUES ('12/4/2008',10,50)



DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '12/1/2008'
SET @EndDate = '12/6/2008'

;with TallyCalendar as (
--5 years prior to today plus 5 years after
SELECT dateadd( dd, -3650 + RW ,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) As N
FROM (
SELECT TOP 7300 -- ~10 years of days
row_number() OVER (ORDER BY sc1.id) AS RW
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2
) X
)

SELECT DATEADD(DD, 0, Tally.n) AS TranDate, ISNULL(b.TransactionAmount, 0) AS TransactionAmount, x.Balance
FROM TallyCalendar Tally
LEFT JOIN #myBalances b ON b.TranDate = DATEADD(DD, 0, Tally.n)
LEFT JOIN #myBalances x ON x.TranDate = (SELECT MAX(TranDate) AS TranDate FROM #myBalances WHERE TranDate <= DATEADD(DD, 0, Tally.n))
WHERE n BETWEEN DATEDIFF(DD, 0, @StartDate) AND DATEDIFF(DD, 0, @EndDate)



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Alvin Ramard
Alvin Ramard
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2676 Visits: 11589
hughesj23 (2/1/2010)

I could join my table by month/year to another static table that just had all the days of the year in it. But I was hoping to avoid creating and remembering to maintain such a table every year.

Thanks.
Joel


Create a scheduled job to run at the end of each month or year to add the values for the upcoming month or year.



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350

USE tempdb;

DECLARE @Data
TABLE (
input_month DATETIME NOT NULL,
price MONEY
);

INSERT @Data
VALUES ('20090101', $50.00),
('20090201', $58.00),
('20090301', $65.00);

SELECT Days.output_date,
D.price
FROM @Data D
CROSS
JOIN (
VALUES (00), (01), (02), (03), (04), (05), (06), (07), (08), (09),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19),
(20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30)
) DayOffset (n)
CROSS
APPLY (SELECT DATEADD(DAY, DayOffset.n, D.input_month)) Days (output_date)
WHERE MONTH(Days.output_date) = MONTH(D.input_month);



edit: Fixed!



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Peter Brinkhaus
Peter Brinkhaus
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1676 Visits: 7065
Paul, nice solution! However, the first day of the month is missing. Either subtract 1 from DayOffset.n as the parameter for the dateadd function or use a zero-based DayOffset (0-30) will fix it.

Peter
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Peter Brinkhaus (2/2/2010)
Paul, nice solution! However, the first day of the month is missing. Either subtract 1 from DayOffset.n as the parameter for the dateadd function or use a zero-based DayOffset (0-30) will fix it.

Darn it!!! w00t
Fixed - thanks for the great catch!



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
hughesj23
hughesj23
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 167
Good stuff. This has gotten the wheels turning. Thank you all.



RyanDuclos
RyanDuclos
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 38
With the introduction of CTEs is SQL 2005 it made it a lot easier to create a record for every date in a range.


WITH Dates AS (
   SELECT
    [Date] = CONVERT(DATETIME,'01/01/2010')
   UNION ALL SELECT
    [Date] = DATEADD(DAY, 1, [Date])
   FROM
    Dates
   WHERE
    Date < '12/31/2010'
) SELECT
[Date]
FROM
Dates
OPTION (MAXRECURSION 400)

]
nigel.
nigel.
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1333 Visits: 2823
Beware, recursive CTEs can perform poorly. See here

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help    
The "Numbers" or "Tally" Table - Jeff Moden

Nice Marmot
Nice Marmot
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 767
Hi,

IMHO it is worthwhile having a calendar table (often called a time dimension in data warehousing lingo), particularly to support reporting with external tools. (Tools beyond T-SQL, I mean.)

This http://www.sqlservercentral.com/scripts/Date/68389/ is a good recent article which would get you up and running. We have a large shared calendar table which is always being extended with new clients' week, month and year periods, etc. (Good practice for date arithmetic.;-)) HTH
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search