June 4, 2021 at 10:01 am
hi
needed help to write the sql script where need to create a new column as area_new in which it should fill area if daily_dt is between or equal to end_dt and create a new row where end date of previous row is same as start date of next row.
rest required in tab_result.
with tab1 as
(
select 101 as id, 'abc' as area,'2021-01-07' as strt_dt,'2021-01-07' as end_dt, '2021-01-07' as daily_dt
union
select 101 as id, 'abc' as area, '2021-01-07' as strt_dt, '2021-01-08' as end_dt, '2021-01-07' as daily_dt
union
select 101 as id, 'mno' as area,'2021-01-08' as strt_dt,'2021-01-15' as end_dt, '2021-01-08' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-09' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-10' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-11' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-12' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-13' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-14' as daily_dt
union
select 101 as id, 'tt' as area,'2021-01-15' as strt_dt, '2021-01-17' as end_dt, '2021-01-15' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-16' as daily_dt
union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-17' as daily_dt
)select * from tab1
with tab1_result as
(
select 101 as id, 'abc' as area_new,'2021-01-07' as strt_dt,'2021-01-07' as end_dt, '2021-01-07' as daily_dt
union
select 101 as id, 'abc' as area_new, '2021-01-07' as strt_dt, '2021-01-08' as end_dt, '2021-01-07' as daily_dt
union
select 101 as id, 'abc' as area_new, '2021-01-08' as strt_dt, '2021-01-08' as end_dt, '2021-01-08' as daily_dt
union
select 101 as id, 'mno' as area_new,'2021-01-08' as strt_dt,'2021-01-15' as end_dt, '2021-01-08' as daily_dt
union
select 101 as id, 'mno' as area_new,NULL as strt_dt,NULL as end_dt, '2021-01-09' as daily_dt
union
select 101 as id, 'mno' as area_new,NULL as strt_dt,NULL as end_dt, '2021-01-10' as daily_dt
union
select 101 as id, 'mno' as area_new,NULL as strt_dt,NULL as end_dt, '2021-01-11' as daily_dt
union
select 101 as id, 'mno' as area_new,NULL as strt_dt,NULL as end_dt, '2021-01-12' as daily_dt
union
select 101 as id, 'mno' as area_new,NULL as strt_dt,NULL as end_dt, '2021-01-13' as daily_dt
union
select 101 as id, 'mno' as area_new,NULL as strt_dt,NULL as end_dt, '2021-01-14' as daily_dt
union
select 101 as id, 'mno' as area_new,NULL as strt_dt,NULL as end_dt, '2021-01-15' as daily_dt
union
select 101 as id, 'tt' as area_new,'2021-01-15' as strt_dt, '2021-01-17' as end_dt, '2021-01-15' as daily_dt
union
select 101 as id, 'tt' as area_new,NULL as strt_dt,NULL as end_dt, '2021-01-16' as daily_dt
union
select 101 as id, 'tt' as area_new,NULL as strt_dt,NULL as end_dt, '2021-01-17' as daily_dt
)select * from tab1_result
June 4, 2021 at 12:57 pm
This query is really close but maybe there's an inconsistency issue with the 'tab1_result' query?
with tab1 as
(
select 101 as id, 'abc' as area,'2021-01-07' as strt_dt,'2021-01-07' as end_dt, '2021-01-07' as daily_dt union
select 101 as id, 'abc' as area, '2021-01-07' as strt_dt, '2021-01-08' as end_dt, '2021-01-07' as daily_dt union
select 101 as id, 'mno' as area,'2021-01-08' as strt_dt,'2021-01-15' as end_dt, '2021-01-08' as daily_dt union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-09' as daily_dt union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-10' as daily_dt union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-11' as daily_dt union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-12' as daily_dt union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-13' as daily_dt union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-14' as daily_dt union
select 101 as id, 'tt' as area,'2021-01-15' as strt_dt, '2021-01-17' as end_dt, '2021-01-15' as daily_dt union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-16' as daily_dt union
select 101 as id, NULL as area,NULL as strt_dt,NULL as end_dt, '2021-01-17' as daily_dt
)
select t.id, t.area as new_area, tb.strt_dt, tb.end_dt, v.calc_dt
from tab1 t
cross apply dbo.fnTally(0, datediff(day, strt_dt, end_dt)) fn
cross apply (values (cast(dateadd(day, fn.n, strt_dt) as date))) v(calc_dt)
left join tab1 tb on t.strt_dt=tb.strt_dt
and t.end_dt=tb.end_dt
and t.daily_dt=v.calc_dt
where t.area is not null
order by t.id, t.area, v.calc_dt;
idnew_areastrt_dtend_dtcalc_dt
101abc2021-01-072021-01-072021-01-07
101abc2021-01-072021-01-082021-01-07
101abcNULLNULL2021-01-08
101mno2021-01-082021-01-152021-01-08
101mnoNULLNULL2021-01-09
101mnoNULLNULL2021-01-10
101mnoNULLNULL2021-01-11
101mnoNULLNULL2021-01-12
101mnoNULLNULL2021-01-13
101mnoNULLNULL2021-01-14
101mnoNULLNULL2021-01-15
101tt2021-01-152021-01-172021-01-15
101ttNULLNULL2021-01-16
101ttNULLNULL2021-01-17
versus 'tab1_result'
idarea_newstrt_dtend_dtdaily_dt
101abc2021-01-072021-01-072021-01-07
101abc2021-01-072021-01-082021-01-07
101abc2021-01-082021-01-082021-01-08
101mno2021-01-082021-01-152021-01-08
101mnoNULLNULL2021-01-09
101mnoNULLNULL2021-01-10
101mnoNULLNULL2021-01-11
101mnoNULLNULL2021-01-12
101mnoNULLNULL2021-01-13
101mnoNULLNULL2021-01-14
101mnoNULLNULL2021-01-15
101tt2021-01-152021-01-172021-01-15
101ttNULLNULL2021-01-16
101ttNULLNULL2021-01-17
In area 'abc' the generated row should contain NULL values in the strt_dt and end_dt columns, no?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
June 4, 2021 at 1:06 pm
hi Steve
could you please share you fnTally function
regards
June 4, 2021 at 1:27 pm
Sure, the function is described in this really nice article. This is the code I use
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
H2(N) AS ( SELECT 1
FROM (VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
)V(N)) --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
SELECT TOP(@MaxN)
N = ROW_NUMBER() OVER (ORDER BY N)
FROM H8
;
It comes in handy in a lot of places. The query uses fnTally to generate the range of dates (calc_dt) between 'strt_dt' and 'end_dt'. Then it uses LEFT JOIN to the 'tab1' CTE.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply