June 30, 2008 at 6:26 pm
SQL 2005...
Need SQL sample for a View that will retrieve a START_DATE field and an END_DATE field which is already stored in each record of a table. The View needs to dynamically recreate (display) a duplicate row of that record for each date in the range specified in the original record.
Example Source Table:
ID: START_DATE: END_DATE: TEXT_FIELD:
123 06/17/2008 06/20/2008 BLAHBLAHBLAH
The view would create:
NEW_ID: OLD_ID: DATE: TEXT_FIELD:
1 123 06/17/2008 BLAHBLAHBLAH
2 123 06/18/2008 BLAHBLAHBLAH
3 123 06/19/2008 BLAHBLAHBLAH
4 123 06/20/2008 BLAHBLAHBLAH
The View need only be read only which will be used to populate cells in a calendar control of a web page.
July 1, 2008 at 5:48 am
Jeff will probably post with a solution using a tally table - which will be faster, but I did not have a tally table on my test box so I used a recursive CTE. So, this is not the most efficient solution, but it certainly works. If your data set is small, this would be fine.
[font="Courier New"]CREATE TABLE #tmp
(ID INT IDENTITY(1,1), StartDate DATETIME, EndDate DATETIME, DataField VARCHAR(20))
INSERT #tmp (StartDate, EndDate, DataField) VALUES ('6/17/2008','6/20/2008','MyData1')
INSERT #tmp (StartDate, EndDate, DataField) VALUES ('5/1/2008','5/3/2008','MyData2')
; WITH DateList (Id, Date, StartDate, EndDate, DataField)
AS (
SELECT Id, StartDate, StartDate, EndDate, DataField FROM #tmp
UNION ALL
SELECT Id, Date+1, StartDate, EndDate, DataField FROM DateList
WHERE Date+1 <= EndDate
)
SELECT
*
FROM
DateList
ORDER BY
Id
OPTION
(MAXRECURSION 10000)[/font]
July 1, 2008 at 8:24 am
I would go a bit further and use a "calendar" table. Pretty much like the tally table but with the dates already stored 😉
* Noel
July 1, 2008 at 2:43 pm
Excellent! I so appreciate the help guys. Minor mods and it worked like a charm.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply