Date ranges between two dates
Hi,
I use PostgreSQL and I have a table, which has the following data (only two rows below, for an example):
I want another table based on this, with the data as following:
Can someone kindly help?
I use PostgreSQL and I have a table, which has the following data (only two rows below, for an example):
ID Start_Dt End_Dt
1 05/10/2017 10/20/2017
2 07/14/2017 NULLI want another table based on this, with the data as following:
ID Start_Dt End_Dt
1 05/10/2017 05/31/2017
1 06/01/2017 06/30/2017
1 07/01/2017 07/31/2017
1 08/01/2017 08/31/2017
1 09/01/2017 09/30/2017
1 10/01/2017 10/20/2017
2 07/14/2017 07/31/2017
2 08/01/2017 08/31/2017
2 09/01/2017 09/30/2017
2 10/01/2017 10/31/2017
2 11/01/2017 11/26/2017Can someone kindly help?
Pawan Kumar
What's the logic to create another table.
ASKER
Hi,
So, the logic is to get all the month begin and end dates between these dates.
In my example, except for 5/10/2017 start date and 10/20/2107, we need to get all the month begin and end dates between them.
If the end_dt is a null, then it is considered as the current date
So, the logic is to get all the month begin and end dates between these dates.
In my example, except for 5/10/2017 start date and 10/20/2107, we need to get all the month begin and end dates between them.
If the end_dt is a null, then it is considered as the current date
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm sure there is a more elegant way to do this, but this seems to work for me. I use a tally table (tbl_Numbers) with a single field (lngNumber) and as many records as I need, with the smallest value = 0, normally about 1000 records (0-999), but for your purposes, you could probably use a lot less.
SELECT SQ.ID
, SQ.Start_Date
, CASE WHEN DateAdd(month, 1, SQ.Start_Date)-1> [End_dt] THEN [End_dt],
ELSE DateAdd(month, 1, SQ.Start_Date) - day(Dateadd(month, 1, SQ.Start_Date))) as End_Date
FROM (
SELECT tbl_Dates2.ID
, CASE WHEN [lngNumber]=0 THEN [Start_Dt]
ELSE DateAdd(month,[lngNumber],[Start_dt])-Day(DateAdd(month,[lngNumber],[Start_dt]))+1) AS Start_Date
, End_dt
FROM tbl_Dates2, tbl_Numbers
WHERE
CASE WHEN [lngNumber]=0 THEN [Start_Dt]
ELSE DateAdd(month,[lngNumber],[Start_dt])-Day(DateAdd(month,[lngNumber],[Start_dt]))+1))<NZ([End_dt],GetDate())))
) as SQ
ORDER BY SQ.ID, SQ.Start_DateSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, both!
I got a unified solution from your suggestions.
Thanks again.
I got a unified solution from your suggestions.
Thanks again.