sql - Add column with filled days between start_date and end_date -
i have input table 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 take table , create new 1 additional column pr_day
, have integer value of 1 day (e.g 20170814
) in range between start_dttm
, end_dttm
, there 1 row each day within range.
started following query range each group (consisting of first 3 columns)
select acct_id, pvt_data_id, pvt_pref_ind, cast(min(substr(cast(start_dttm string),1,8)) bigint), max(case when end_dttm=99991231235959 cast(from_unixtime(unix_timestamp(now()),'yyyymmdd') bigint) when end_dttm null cast(from_unixtime(unix_timestamp(now()),'yyyymmdd') bigint) else end_dttm end ) table1 group acct_id, pvt_data_id,pvt_pref_ind
note: values 99991231235959 or null means current day should used end_date.
im not sure how continue, searching guided me towards making cross join fill dates, should join table to?
desired output 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 tips , help.
i think easiest way create work table lists of dates, , join original table work table. getting list of dates between 2 dates discussed in stack overflow article here, , have tweak work you. sake of answer i'll assume plan on creating new table i'll selecting results temp table can change output table:
declare @date1 date, @date2 date set @date1 = (select left(min(start_dttm), 8) your_table) --'20150528' set @date2 = getdate() select convert(varchar(8), dateadd(day,number,@date1), 112) + '000000' [longdate] , convert(varchar(8), dateadd(day,number,@date1), 112) [shortdate] master..spt_values type = 'p' , dateadd(day,number,@date1) <= @date2
then join new date range table against original table on between clause long date value using short date value populate column, like:
#daterange.longdate between yourtable.start_dttm , yourtable.end_dttm
hope helps,
Comments
Post a Comment