Update problem

Transact-SQL
, ,
1

hi friends

i have two table created scripts that

create table #t1
(
gr char(10),
muh int,
mes int,
less_id int
)

insert #t1 values('a1',4,2,2)
insert #t1 values('a1',2,2,1)
insert #t1 values('a1',2,1,6)
insert #t1 values('a1',3,1,5)
insert #t1 values('a1',3,2,3)
insert #t1 values('a1',2,2,4)

create table #t2
(gr char(10),d_id int,start_time time,end_time time,less_id int,last_week_less int)

insert into #t2 (gr,d_id,start_time,end_time) values('a1',1,'12:30','14:05')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',1,'14:15','15:50')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',1,'16:00','17:35')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',1,'17:45','19:20')

insert into #t2 (gr,d_id,start_time,end_time) values('a1',2,'12:30','14:05')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',2,'14:15','15:50')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',2,'16:00','17:35')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',2,'17:45','19:20')

insert into #t2 (gr,d_id,start_time,end_time) values('a1',3,'12:30','14:05')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',3,'14:15','15:50')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',3,'16:00','17:35')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',3,'17:45','19:20')

insert into #t2 (gr,d_id,start_time,end_time) values('a1',4,'12:30','14:05')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',4,'14:15','15:50')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',4,'16:00','17:35')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',4,'17:45','19:20')

insert into #t2 (gr,d_id,start_time,end_time) values('a1',5,'12:30','14:05')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',5,'14:15','15:50')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',5,'16:00','17:35')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',5,'17:45','19:20')

select *from #t1

gr muh mes less_id
a1 4 2 2
a1 2 2 1
a1 2 1 6
a1 3 1 5
a1 3 2 3
a1 2 2 4

select* from #t2

gr d_id start_time end_time less_id last_week_less

a1 1 12:30:00.0000000 14:05:00.0000000 NULL NULL
a1 1 14:15:00.0000000 15:50:00.0000000 NULL NULL
a1 1 16:00:00.0000000 17:35:00.0000000 NULL NULL
a1 1 17:45:00.0000000 19:20:00.0000000 NULL NULL
a1 2 12:30:00.0000000 14:05:00.0000000 NULL NULL
a1 2 14:15:00.0000000 15:50:00.0000000 NULL NULL
a1 2 16:00:00.0000000 17:35:00.0000000 NULL NULL
a1 2 17:45:00.0000000 19:20:00.0000000 NULL NULL
a1 3 12:30:00.0000000 14:05:00.0000000 NULL NULL
a1 3 14:15:00.0000000 15:50:00.0000000 NULL NULL
a1 3 16:00:00.0000000 17:35:00.0000000 NULL NULL
a1 3 17:45:00.0000000 19:20:00.0000000 NULL NULL
a1 4 12:30:00.0000000 14:05:00.0000000 NULL NULL
a1 4 14:15:00.0000000 15:50:00.0000000 NULL NULL
a1 4 16:00:00.0000000 17:35:00.0000000 NULL NULL
a1 4 17:45:00.0000000 19:20:00.0000000 NULL NULL
a1 5 12:30:00.0000000 14:05:00.0000000 NULL NULL
a1 5 14:15:00.0000000 15:50:00.0000000 NULL NULL
a1 5 16:00:00.0000000 17:35:00.0000000 NULL NULL
a1 5 17:45:00.0000000 19:20:00.0000000 NULL NULL

and i need update table #t2 columns less_id and last_week_less

for table#t1 where #t2 gr= #t1 gr

but hier need interesting rules

  1. when muh colum from #t1 = 4

    then we need update 2 column in #table

    and need different in the #t1 table d_id column

    for example after output result

    need give

gr d_id start_time end_time less_id last_week_less
a1 1 12:30:00.0000000 14:05:00.0000000 2 NULL

a1 2 12:30:00.0000000 14:05:00.0000000 2 NULL

hier our count update=2 (4/2)

and d_id need diffrent

and if will muh column =3 (3/2)

then we need update 1 column and 1 in the last_week_less update

after ouput need give

gr d_id start_time end_time less_id last_week_less
a1 1 14:15:00.0000000 15:50:00.0000000 5 5

because 3/2 we give 1 and 1

and after this update we need update for column mes column too

when the column is null

and simple fragment for result we need give that results

for example

and in 1 section d_id dont more 3 less_id
for example this is update for muh column #t1 table after output

gr d_id start_time end_time less_id last_week_less

a1 1 12:30:00.0000000 14:05:00.0000000 2 NULL
a1 1 14:15:00.0000000 15:50:00.0000000 1 null
a1 1 16:00:00.0000000 17:35:00.0000000 null NULL
a1 1 17:45:00.0000000 19:20:00.0000000 null null
a1 2 12:30:00.0000000 14:05:00.0000000 2 NULL
a1 2 14:15:00.0000000 15:50:00.0000000 null null
a1 2 16:00:00.0000000 17:35:00.0000000 null NULL
a1 2 17:45:00.0000000 19:20:00.0000000 NULL NULL
a1 3 12:30:00.0000000 14:05:00.0000000 5 5
a1 3 14:15:00.0000000 15:50:00.0000000 6 NULL
a1 3 16:00:00.0000000 17:35:00.0000000 NULL NULL
a1 3 17:45:00.0000000 19:20:00.0000000 NULL NULL
a1 4 12:30:00.0000000 14:05:00.0000000 3 3
a1 4 14:15:00.0000000 15:50:00.0000000 NULL NULL
a1 4 16:00:00.0000000 17:35:00.0000000 NULL NULL
a1 4 17:45:00.0000000 19:20:00.0000000 NULL NULL
a1 5 12:30:00.0000000 14:05:00.0000000 4 NULL
a1 5 14:15:00.0000000 15:50:00.0000000 NULL NULL
a1 5 16:00:00.0000000 17:35:00.0000000 NULL NULL
a1 5 17:45:00.0000000 19:20:00.0000000 NULL NULL

thanks for helping