CODEWITHSUNDEEP
sqlsql-server
user1463242
user1463242

Reputation: 137

Update table for each date and add remaining sites from another table

I have a table 'test' like this-

ID  Site    Start Time  End Time    
1   A   30-12-2014 16:06:54 30-12-2014 16:39:52    
2   B   30-12-2014 12:12:50 30-12-2014 12:13:52    
3   C   31-12-2014 12:14:23 31-12-2014 12:15:22    
4   A   01-01-2015 12:20:29 01-01-2015 12:23:32    
5   B   01-01-2015 12:28:49 01-01-2015 12:29:47

I have another table 'list' with a listing of sites-

Site    
A    
B    
C

I need an output table where for each date, all the sites from 'list' is included like this-

ID  Site    Start Time  End Time    
1   A   30-12-2014 16:06:54 30-12-2014 16:39:52    
2   B   30-12-2014 12:12:50 30-12-2014 12:13:52    
NULL    C   30-12-2014 00:00:00 30-12-2014 00:00:00    
NULL    A   31-12-2014 00:00:00 31-12-2014 00:00:00     
NULL    B   31-12-2014 00:00:00 31-12-2014 00:00:00    
3   C   31-12-2014 12:14:23 31-12-2014 12:15:22    
4   A   01-01-2015 12:20:29 01-01-2015 12:23:32    
5   B   01-01-2015 12:28:49 01-01-2015 12:29:47    
NULL    C   01-01-2015 00:00:00 01-01-2015 00:00:00

Till now I have been table to separate the 'test' table on each date into intermediate tables and select the non matching sites from 'list' table. I am stuck with the loop. Please help.

Here is my code-

ALTER TABLE [test] ADD [DATE] date;

update [test]
set [DATE] = CAST(Start Time] as Date)

select t1.[Site]
from list t1
left join test t2 on t1.[site]=t2.[site] where t2.site is null;

select distinct [DATE] into #Temp1 from [test]
order by [DATE];

select [DATE], row_number()over(order by ([Date])asc) as [Row] into #Temp2 from #Temp1;
drop table #Temp1;

GO
declare @row int
select @row = 0
while ( @row <= (select COUNT(*) from #Temp2))
begin
select @row = 1 + @row
select c.* into #temp3
from( 
select a.* , b.[DATE] as b_date, b.[row]
from test a
inner join #Temp2 b
on a.[Date] = b.[Date] where b.[row] = @row
) c
End;

Upvotes: 0

Views: 48

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5088

Try this,

Declare @t table(ID int, Site varchar(50),StartTime datetime,EndTime datetime)
insert into @t    values
(1,   'A',   '12-30-2014 16:06:54','12-30-2014 16:39:52'),
(2 ,  'B',   '12-30-2014 12:12:50','12-30-2014 12:13:52'),    
(3  , 'C',   '12-31-2014 12:14:23','12-31-2014 12:15:22'),  
(4 ,  'A',   '01-01-2015 12:20:29','01-01-2015 12:23:32'),
(5,   'B',   '01-01-2015 12:28:49','01-01-2015 12:29:47')   
dECLARE @lIST TABLE(Site varchar(50))
insert into @lIST values('A'),('B'),('C')


;WITH CTE AS
(
SELECT min(cast(StartTime as date)) st FROM @t 
union all
SELECT dateadd(day,1, st) FROM CTE where 
st<casT('01-01-2015 12:28:49' as date)--max date(can be dynamic)
)
,CTE1 as
(
  select * from @lIST a 
  cross apply (select * from cte)b
)
,CTE2 as
(
  select y.ID,x.Site
  ,ISNULL(y.StartTime,x.st)StartTime,ISNULL(y.EndTime,x.st)EndTime
   from CTE1 x
  left join @t y on x.site=y.site and 
  cast(x.st as date)=cast(y.StartTime as date)

)
SELECT * FROM CTE2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You can get the output you want using a select:

select t.id, l.site, coalesce(t.starttime, d.d) as starttime, coalesce(t.endtime, d.d) as endtime
from list l cross join
     (select distinct cast(starttime as date) as d from test) d left join
     test t
     on t.site = l.site and cast(t.starttime as date) = d.d;

You can insert non-matching rows into the table with similar logic:

insert into test(id, site, starttime, endtime)
    select t.id, l.site, d.d, d.d
    from list l cross join
         (select distinct cast(starttime as date) as d from test) d left join
         test t
         on t.site = l.site and cast(t.starttime as date) = d.d
    where t.site is null;

Upvotes: 2

Related Questions