March 30, 2015 at 11:26 am
Hello all,
I'm trying to figure out how to show the dates in between a range. Its hard for me to explain so I have ddl with the original results and then ddl of how I would like the desired outcome.
On the side I have a visit ID I need to show each day logged for each ID. Sometime the Start and End are a single day and sometimes they are a range. I need a row for each date.
Thanks so much in advance
Thomas
CREATE TABLE #Results (VisitID INT, DateFrom DATE, DateTo DATE)
INSERT INTO #Results VALUES (361, '2015-03-07', '2015-03-07'), (361, '2015-03-08', '2015-03-10')
,(48, '2015-03-18', '2015-03-18'),(48, '2015-03-19', '2015-03-23')
SELECT *
FROM #Results
DROP TABLE #Results
----Desired Results
CREATE TABLE #Desired (VisitID INT, Desired DATE)
INSERT INTO #Desired VALUES (361, '2015-03-07'),
(361, '2015-03-08'),
(361, '2015-03-09'),
(361, '2015-03-10'),
(48, '2015-03-18'),
(48, '2015-03-19'),
(48, '2015-03-20'),
(48, '2015-03-21'),
(48, '2015-03-22'),
(48, '2015-03-23')
SELECT * FROM #Desired
DROP TABLE #Desired
***SQL born on date Spring 2013:-)
March 30, 2015 at 11:33 am
It would be easier if you have a calendar table.
SELECT DISTINCT r.VisitID, c.cal_Date
FROM #Results r
JOIN Calendar c ON c.cal_Date BETWEEN r.DateFrom AND r.DateTo
You could build one using a tally table.
March 30, 2015 at 11:34 am
I do Luis, I do!! Forgot about my Calendar Table
Thank you sir!
***SQL born on date Spring 2013:-)
March 31, 2015 at 10:10 am
Using an in-line tally table would be more efficient:
/* add in-line tally table (CROSS JOINs of 10 x 10 = 100; 100 x 100 = 10K) here,
be sure the numbers start at zero::
sorry, I'm blocked from posting this part due to software at work, grrr :angry:, */
SELECT r.VisitID, DATEADD(DAY, day_numbers.tally, r.DateFrom) AS Desired
FROM #Results r
INNER JOIN cteTally10K day_numbers ON
day_numbers.tally BETWEEN 0 AND DATEDIFF(DAY, r.DateFrom, r.DateTo)
--ORDER BY VisitID, Desired
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 1, 2015 at 5:35 am
If you don't have a calendar table or if you don't want to use a calendar table. Use CTE instead-
;WITH CTE AS
(
SELECT CAST('2015-03-01' AS DATE) as CDate
UNION ALL
SELECT DATEADD(day,1,CDate)
FROM CTE
WHERE DATEADD(day,1,CDate) <= '2015-03-10'
)
SELECT CDate
FROM CTE
____________________________________________________________
APApril 1, 2015 at 9:34 am
Anshul Parmar (4/1/2015)
If you don't have a calendar table or if you don't want to use a calendar table. Use CTE instead-
;WITH CTE AS
(
SELECT CAST('2015-03-01' AS DATE) as CDate
UNION ALL
SELECT DATEADD(day,1,CDate)
FROM CTE
WHERE DATEADD(day,1,CDate) <= '2015-03-10'
)
SELECT CDate
FROM CTE
Be careful, that's a recursive CTE that counts and is considered Hidden RBAR that could cause major performance problems. This is explained in here: http://www.sqlservercentral.com/articles/T-SQL/74118/
As an alternative (which I'm sure is what Scott intended to post) you can use nested CTEs.
DECLARE @StartDate Date = '2015-03-01',
@EndDate Date = '2015-03-10';
WITH E(n) AS
(
SELECT N FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
)
,E2(n) AS
(
SELECT a.n FROM E a, E b
)
,cteCal(cal_Date) AS
(
SELECT TOP(DATEDIFF(DD, @StartDate, @EndDate) + 1)
DATEADD(DD, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @StartDate) cal_Date
FROM E2 a, E2 b
)
SELECT cal_Date
FROM cteCal;
And certainly, you could convert it to an inline table-values function with zero reads.
CREATE FUNCTION dbo.fnCalendar(
@StartDate Date,
@EndDate Date
)RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH E(n) AS
(
SELECT N FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
)
,E2(n) AS
(
SELECT a.n FROM E a, E b
)
,cteCal(cal_Date) AS
(
SELECT TOP(DATEDIFF(DD, @StartDate, @EndDate) + 1)
DATEADD(DD, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @StartDate) cal_Date
FROM E2 a, E2 b
)
SELECT cal_Date
FROM cteCal
GO
DECLARE @StartDate Date = '2015-03-01',
@EndDate Date = '2015-03-10';
SELECT *
FROM dbo.fnCalendar(@StartDate, @EndDate);
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply