question

leodec-4354 avatar image
0 Votes"
leodec-4354 asked EchoLiu-msft commented

help with sql script

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



sql-server-transact-sql
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

sample data is in tab1 and result needed in tab1_result

And can you explain the logic for the expected result, please?



0 Votes 0 ·

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

0 Votes 0 ·

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.

0 Votes 0 ·

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.

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered pituach edited

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

102643-image.png

Is this fits your needs?



image.png (25.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

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:
102925-image.png

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.



image.png (16.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.