• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 153
  • Last Modified:

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?
0
pvsbandi
Asked:
pvsbandi
2 Solutions
 
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Dale FyeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now