0

I have input table with this structure:

acct_id pvt_data_id pvt_pref_ind start_dttm end_dttm load_dttm pr_load_time
4174878 26  Y   20101126144142  99991231235959  20170527000000  2017052700
4174878 26  Y   20101126144142  99991231235959  20170528000000  2017052800
4174878 26  Y   20101126144142  99991231235959  20170530000000  2017053000
3212472 26  X   20131016144142  99991231235959  20170531000000  2017053100
4174878 26  Y   20101126144142  99991231235959  20170601000000  2017060100
3212472 26  X   20091201142148  99991231235959  20170602000000  2017060200

Im supposed to take this table and create a new one with additional column pr_day, which would have the integer value of one day (e.g 20170814) in the range between start_dttm and end_dttm, so there will be one row for each day within the range.
I started with following query to get the range for each group (consisting of the first 3 columns)

select 
acct_id, 
pvt_data_id,
pvt_pref_ind,
cast(min(substr(cast(start_dttm as string),1,8)) as bigint),
max(case when end_dttm=99991231235959 then cast(from_unixtime(unix_timestamp(now()),'yyyyMMdd') as bigint) when end_dttm is null then cast(from_unixtime(unix_timestamp(now()),'yyyyMMdd') as bigint)  else end_dttm end )  

from table1
group by  acct_id, pvt_data_id,pvt_pref_ind

Note: values 99991231235959 or null means current day should be used as end_date.
Now Im not sure how to continue, searching guided me towards making a cross join to fill the dates, but what should I join the table to?

Desired output would like this: 

acct_id pvt_data_id pvt_pref_ind start_dttm end_dttm load_dttm pr_load_time pr_day
4174878 26  Y   20101126144142  99991231235959  20170527000000  2017052700 20101126
4174878 26  Y   20101126144142  99991231235959  20170528000000  2017052800 20101127
4174878 26  Y   20101126144142  99991231235959  20170529000000  2017052900 20101128
4174878 26  Y   20101126144142  99991231235959  20170530000000  2017053000 20101129
3212472 26  X   20131016144142  99991231235959  20170531000000  2017053100 20091202
4174878 26  Y   20101126144142  99991231235959  20170601000000  2017060100 20101130
3212472 26  X   20091201142148  99991231235959  20170602000000  2017060200 20091201¨

Thanks for tips and help.

k_mishap
  • 451
  • 2
  • 8
  • 17

1 Answers1

0

I think the easiest way is to create a work table that lists all of the dates, and then just join your original table to that work table. Getting a list of dates between two dates is discussed in a Stack overflow article here, and we only have to tweak that slightly to get it to work for you. For the sake of the answer I'll assume you plan on creating a new table so I'll be selecting the results into a temp table which you can change to be your output table:

DECLARE @Date1 DATE, @Date2 DATE
SET @Date1 = (select left(min(start_dttm), 8) from Your_Table) --'20150528'
SET @Date2 = getdate()

SELECT CONVERT(VARCHAR(8), DATEADD(DAY,number,@Date1), 112) + '000000' as [LongDate]
, CONVERT(VARCHAR(8), DATEADD(DAY,number,@Date1), 112) [Shortdate]
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number,@Date1) <= @Date2

Then you just join the new date range table against your original table on a between clause with the long date value using the short date value to populate the column, something like:

#daterange.longdate between YourTable.start_dttm and YourTable.end_dttm

Hope that helps,

Randall
  • 1,441
  • 13
  • 19