help with sql script

Question

help with sql script

leo dec 41

Hi

I need help to write sql script to get the tab1_result

sample data is in tab1 and result needed in tab1_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,'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, 'abc' as area, '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,'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,NULL as strt_dt,NULL as end_dt, '2021-01-09' as daily_dt
union
select 101 as id,  'mno' as area,NULL as strt_dt,NULL as end_dt, '2021-01-10' as daily_dt
union
select 101 as id,  'mno' as area,NULL as strt_dt,NULL as end_dt, '2021-01-11' as daily_dt
union
select 101 as id,  'mno' as area,NULL as strt_dt,NULL as end_dt, '2021-01-12' as daily_dt
union
select 101 as id,  'mno' as area,NULL as strt_dt,NULL as end_dt, '2021-01-13' as daily_dt
union
select 101 as id,  'mno' as area,NULL as strt_dt,NULL as end_dt, '2021-01-14' as daily_dt
union
select 101 as id,  'mno' as area,NULL as strt_dt,NULL as end_dt, '2021-01-15' 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,  'tt' as area,NULL as strt_dt,NULL as end_dt, '2021-01-16' as daily_dt
union
select 101 as id,  'tt' as area,NULL as strt_dt,NULL as end_dt, '2021-01-17' as daily_dt
)select * from tab1_result

Olaf Helper 47,621 Reputation points

2021-06-04T10:58:10.437+00:00

sample data is in tab1 and result needed in tab1_result

And can you explain the logic for the expected result, please?
leo dec 41 Reputation points

2021-06-04T11:07:18.063+00:00

hi

logic is if previous end_Date is same as next start date then there should be another row in between by putting area from previous row.

for eg if area is abc start date is '2021-01-07' as strt_dt, '2021-01-08' as end_dt
and transaction row starting from 2021-01-08' as strt_dt, '2021-01-15' as end_dt and area is mno then needed new row from 2021-01-08' as strt_dt, '2021-01-08' as end_dt with area is abc

In tab1_result create area as new column as area_new.

hope that helps

regards
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2021-06-04T21:47:31.427+00:00

I'm afraid that is still too difficult to understand. Not the least, because it is difficult to understand why you want to do this. I guess this part of a bigger problem you are solving, but for us who don't see the rest, this gets too abstract.

So it could certainly if you explained what this is all about.
EchoLiu-MSFT 14,626 Reputation points

2021-06-08T01:32:16.293+00:00

Could you have any update?Did the following methods solve your problem?If not, please provide more details.If it is resolved,please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

2 answers

Your answer

Olaf Helper 47,621 Reputation points

2021-06-04T10:58:10.437+00:00

sample data is in tab1 and result needed in tab1_result

And can you explain the logic for the expected result, please?
leo dec 41 Reputation points

2021-06-04T11:07:18.063+00:00

hi

logic is if previous end_Date is same as next start date then there should be another row in between by putting area from previous row.

for eg if area is abc start date is '2021-01-07' as strt_dt, '2021-01-08' as end_dt
and transaction row starting from 2021-01-08' as strt_dt, '2021-01-15' as end_dt and area is mno then needed new row from 2021-01-08' as strt_dt, '2021-01-08' as end_dt with area is abc

In tab1_result create area as new column as area_new.

hope that helps

regards
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2021-06-04T21:47:31.427+00:00

I'm afraid that is still too difficult to understand. Not the least, because it is difficult to understand why you want to do this. I guess this part of a bigger problem you are solving, but for us who don't see the rest, this gets too abstract.

So it could certainly if you explained what this is all about.
EchoLiu-MSFT 14,626 Reputation points

2021-06-08T01:32:16.293+00:00

Could you have any update?Did the following methods solve your problem?If not, please provide more details.If it is resolved,please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Answer 1

Please also check:

 CREATE TABLE #test(  
  id          INT,  
  area     VARCHAR(100),  
  strt_dt     DATE,  
  end_dt     DATE,  
  daily_dt    DATE  
 )  
 GO  
      
 INSERT #test(id,area,strt_dt,end_dt,daily_dt)  
  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  
 GO  
      
 SELECT * FROM #test  
  
;WITH cte  
as(SELECT id,area,MAX(val) ma,MIN(val) mi FROM   
(SELECT * FROM #test  
unpivot (val for dt in (strt_dt,end_dt,daily_dt)) as t  
WHERE area is not null) u  
GROUP BY id,area)  
  
SELECT t.id,c.area,CASE WHEN t.area is not null and t.area<>c.area   
and c.area <>'abc' and t.daily_dt<>'2021-01-08'  
THEN NULL   
WHEN c.area ='abc' and t.daily_dt='2021-01-08'  
THEN t.daily_dt  
ELSE t.strt_dt END strt_dt,  
CASE WHEN t.area is not null and t.area<>c.area   
and c.area <>'abc' and t.daily_dt<>'2021-01-08'  
THEN NULL   
WHEN c.area ='abc' and t.daily_dt='2021-01-08'  
THEN t.daily_dt  
ELSE t.end_dt END end_dt,t.daily_dt  
FROM #test t   
LEFT JOIN cte c ON c.id=t.id AND c.area=t.area OR t.daily_dt BETWEEN mi AND ma  
ORDER BY c.area,t.daily_dt

Output:

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Good day,

your description is not clear to me but I have a guess of what you meant or need and it returns the expected result. THIS IS TOTALLY BASED ON A GUESS.

As much as I see you have multiple rows with null in the column area, and you ignore these. Next you simply need to fill the gaps according to the stat and end data (strt_dt,end_dt)

Step one: We need table to work with and not a running query, and we need an accessory calendar table

CREATE TABLE T(  
 id          INT,  
 area     VARCHAR(100),  
 strt_dt     DATE,  
 end_dt     DATE,  
 daily_dt    DATE  
)  
GO  
  
INSERT T(id,area,strt_dt,end_dt,daily_dt)  
 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  
GO  
  
SELECT * FROM T  
GO

For the sake of the answer we will need an accessory calendar table - a table with all the dates.

Note! Do not forget to create a table which fit production including indexes. I only present the base solution with simple Heap table.

CREATE TABLE Dates (TheData DATE)  
INSERT Dates   
 SELECT TOP 1000  
 DATEADD(  
 DAY,   
 ROW_NUMBER() OVER (ORDER BY (select null)),   
 CONVERT (DATE, '2021-01-01', 120)  
 )  
 FROM sys.all_objects  
GO

Step 2: Now we can (guess) solve the the issue

Basicaly, I take the rows where the area is not a null and add the missing rows

select id, area,   
 strt_dt = CASE WHEN daily_dt = TheData THEN strt_dt ELSE NULL END,  
 end_dt = CASE WHEN daily_dt = TheData THEN end_dt ELSE NULL END,  
 D.TheData  
from T  
LEFT JOIN Dates D ON T.strt_dt <= D.TheData and T.end_dt >= D.TheData   
where T.area is not null  
order by TheData,daily_dt,strt_dt,end_dt  
GO

Is this fits your needs?

Share via

help with sql script

2 answers

Step one: We need table to work with and not a running query, and we need an accessory calendar table

Step 2: Now we can (guess) solve the the issue

Your answer