Duplicate rows based on "from" and "to" dates

Question

I've got a table like (code in SAS SQL but I can convert reply from SQLServer engine as I have a bit of experience in it):

proc sql;
   create table work.temp1
       (date_from num informat=date7. format=date7.,
        date_to num informat=date7.   format=date7.,
        some_number num);

insert into work.temp1
    values('15MAY2018'd,'26JUL18'd, 10);
QUIT;

result:

and I want convert it to (with some clever join and probably some temp table with dates and months) to:

proc sql;
   create table work.temp2
       (date_from num informat=date7. format=date7.,
        date_to num informat=date7.   format=date7.,
        some_number num);

insert into work.temp2
    values('15MAY2018'd,'31MAY18'd, 10)
    values('1JUN2018'd,'30JUN18'd, 10)
    values('1JUL2018'd,'26JUL18'd, 10);
QUIT;

result:

All of other columns should be duplicated. Dates from and to are always in one calendar date but each row can be in different year (2016-2020).

[EDIT]:

Tom solution looks good but before I use, I am trying to develop SQL solution.

I've added a "calendar" table to my db and it looks like: name: work.calendar

Now the join I am thinking about would be something like:

SELECT t1.* 
FROM work.temp1 t1 INNER JOIN 
     work.calendar t2 ON t1.date_from >= t2.month_FROM AND t1.date_to <= month_TO

But it doesn't work obviously.

The basic technique for this would be to establish an artificial list of dates that represent the first of the month, and join to it your actual data where the artificial date is between the actual data dates, this way your artificial JUN and JUL are crossed with your actual data. Artificial dates can be formed in a variety of ways, usually involving an incrementing counter and a DATEADD style operation, for example if you need 1000 dates, pick on a table with at least 1000 rows and select a row number from it.. — Caius Jard, Sep 12 '18 at 11:59
ALternatively if you have a numbers/dates table (many DBAs create one for things like this) then just join to that in a way that causes your data rows to repeat (`artificialdates JOIN actualdates ON artificildates.date BETWEEN actual.from and actual.to` and then choose the date to represent with a case when (`when actualenddate < artificialdate+1month, actualdate, else artificialdate+1month` ) — Caius Jard, Sep 12 '18 at 12:02
Not something that is easy to do with SQL, but not hard to do using normal SAS code. — Tom, Sep 12 '18 at 12:27

Tom · Accepted Answer · 2018-09-12T12:29:55.490

Basically you want to convert your periods into monthly records. Pretty easy to do using SAS code, but it would be much harder using SQL since it is set based instead sequential processing.

So let's start by creating your test data.

data temp1;
  date_from='15MAY2018'd;
  date_to='26JUL18'd;
  some_number= 10;
  format date_: date9. ;
run;

The intck() function can be used to determine the number of intervals. And the intnx() function can by used to find the beginning/ending of the month. You could also add lines of code to rename the new variables and drop the old variables and the loop counter.

data want ;
  set temp1 ;
  do i=0 to intck('month',date_from,date_to);
    from = max(intnx('month',date_from,i,'b'),date_from);
    to = min(intnx('month',date_from,i,'e'),date_to);
    output;
  end;
  format from to date9.;
  rename from=date_from to=date_to;
  drop date_from date_to i ;
run;

Duplicate rows based on "from" and "to" dates

1 Answers1