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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy