Date ranges between two dates


  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                      NULL

Open in new window

I 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/2017

Open in new window

Can someone kindly help?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
What's the logic to create another table.
pvsbandiAuthor Commented:
 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
ste5anSenior DeveloperCommented:
Please explain your use-case. Cause it looks like a redundant table.

Here you would normally use a calendar tally table to create these kind of sets as intermediates, but not materialized.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Dale FyeOwner, Dev-Soln LLCCommented:
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.

, 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
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

Open in new window

earth man2Commented:
The problem with tally tables - you need to catch missing data errors.
Another approach is to use built in GENERATE_SERIES function and some complicated nesting.
You could simplify this a bit using WITH syntax yourself .....

SELECT (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day')::date;

select id,
cast( case when start_dt > first_dom then start_dt else first_dom end as date) as start_dt,
case when end_dt   < last_day(first_dom::date) then end_dt else last_day(first_dom::date) end as end_dt from
(select id, start_dt, end_dt::date, date_trunc('month',generate_series(y.start_dt::timestamp,  y.end_dt::timestamp,'1 month')) as first_dom from
(select id, start_dt, coalesce(end_dt,current_date) as end_dt from x) y) w order by id,start_dt;

 id |  start_dt  |   end_dt
  1 | 2017-05-10 | 2017-05-31
  1 | 2017-06-01 | 2017-06-30
  1 | 2017-07-01 | 2017-07-31
  1 | 2017-08-01 | 2017-08-31
  1 | 2017-09-01 | 2017-09-30
  1 | 2017-10-01 | 2017-10-20
  2 | 2017-07-14 | 2017-07-31
  2 | 2017-08-01 | 2017-08-31
  2 | 2017-09-01 | 2017-09-30
  2 | 2017-10-01 | 2017-10-31
  2 | 2017-11-01 | 2017-11-27
(11 rows)

Open in new window

pvsbandiAuthor Commented:
Thank you, both!
  I got a unified solution from your suggestions.
Thanks again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.