Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T-SQL return record for every day of year Expand / Collapse
Author
Message
Posted Monday, February 1, 2010 2:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 11:52 AM
Points: 41, Visits: 152
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



Post #857563
Posted Monday, February 1, 2010 2:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 12,864, Visits: 31,693


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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #857575
Posted Monday, February 1, 2010 2:45 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:28 AM
Points: 3,099, Visits: 7,785
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.
Post #857578
Posted Monday, February 1, 2010 9:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:24 AM
Points: 11,185, Visits: 11,068
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #857711
Posted Tuesday, February 2, 2010 1:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:53 PM
Points: 1,592, Visits: 6,578
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
Post #857768
Posted Tuesday, February 2, 2010 3:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:24 AM
Points: 11,185, Visits: 11,068
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!!!
Fixed - thanks for the great catch!




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #857802
Posted Tuesday, February 2, 2010 7:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 11:52 AM
Points: 41, Visits: 152
Good stuff. This has gotten the wheels turning. Thank you all.


Post #857942
Posted Monday, February 8, 2010 10:16 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 25, 2013 1:15 PM
Points: 19, Visits: 36
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)

]
Post #862203
Posted Wednesday, February 10, 2010 4:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 2:33 AM
Points: 1,178, Visits: 2,644
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

Post #863093
Posted Thursday, February 11, 2010 3:51 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, June 30, 2014 9:41 AM
Points: 459, Visits: 698
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
Post #863970
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse