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):
     
   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?
pvsbandiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.
0
pvsbandiAuthor Commented:
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
0
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.
0

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
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Dale FyeOwner, Developing Solutions 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.

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_Date

Open in new window

0
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 .....

CREATE OR REPLACE FUNCTION last_day(date) RETURNS date AS $$
SELECT (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day')::date;
$$ LANGUAGE 'sql' IMMUTABLE STRICT;

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

0
pvsbandiAuthor Commented:
Thank you, both!
  I got a unified solution from your suggestions.
Thanks again.
0
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
PostgreSQL

From novice to tech pro — start learning today.