October 31, 2013 at 1:21 am
Hi
I need to display all the dates data between two dates.
For ex: the data currently is in this format
Voyage date rangesEarnings
01/01/201315/01/201340000
15/01/201305/02/201325000
05/02/201316/03/201335000
But I need to display this as
DateVoyage earnings
01/01/201340000
02/01/201340000
03/01/201340000
04/01/201340000
05/01/201340000
06/01/201340000
07/01/201340000
08/01/201340000
09/01/201340000
10/01/201340000
11/01/201340000
12/01/201340000
13/01/201340000
14/01/201340000
15/01/201340000
16/01/201325000
17/01/201325000
18/01/201325000
19/01/201325000
20/01/201325000
21/01/201325000
22/01/201325000
23/01/201325000
24/01/201325000
25/01/201325000
26/01/201325000
27/01/201325000
28/01/201325000
29/01/201325000
30/01/201325000
31/01/201325000
01/02/201325000
02/02/201325000
03/02/201325000
04/02/201325000
05/02/201325000
06/02/201335000
07/02/201335000
08/02/201335000
09/02/201335000
10/02/201335000
11/02/201335000
12/02/201335000
13/02/201335000
14/02/201335000
15/02/201335000
16/02/201335000
17/02/201335000
18/02/201335000
19/02/201335000
20/02/201335000
21/02/201335000
22/02/201335000
23/02/201335000
24/02/201335000
25/02/201335000
26/02/201335000
27/02/201335000
28/02/201335000
01/03/201335000
02/03/201335000
03/03/201335000
04/03/201335000
05/03/201335000
06/03/201335000
07/03/201335000
08/03/201335000
09/03/201335000
10/03/201335000
11/03/201335000
12/03/201335000
13/03/201335000
14/03/201335000
15/03/201335000
16/03/201335000
Can any one pls let me know how to do this
Regards
Naveen
October 31, 2013 at 3:14 am
Here you go:
-- Create Table
CREATE TABLE #TestDates
(VoyageDate DATE, Ranges DATE, Earnings INT);
-- Insert Sample Data
INSERT INTO #TestDates
VALUES ('2013-01-01','2013-01-15',40000),('2013-01-15','2013-02-05',25000),('2013-02-05','2013-03-16',35000);
-- Tally Table of 6561 rows
WITH T1(F) AS
(
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
),
T2(F) AS
(SELECT 1 FROM T1 A, T1 B),
T3(F) AS
(SELECT 1 FROM T2 A, T2 B),
T4(F) AS
(SELECT 1 FROM T3 A, T3 B),
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM T4
)
SELECT VoyageDate = TallyDates, Earnings
FROM #TestDates td
INNER JOIN
(
SELECT TallyDates = DATEADD(dd,N-1,CONVERT(DATE,'2013-01-01')) FROM cteTally
) tmp ON td.VoyageDate <= TallyDates AND Ranges >= TallyDates;
-- Clean-up
DROP TABLE #TestDates;
Since your date ranges overlap, you might want to filter a few rows out.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply