Update the table for each date and add the other sites from another table

advertisements

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;


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;