sql-server - SQL 代码更新表，每行具有相同的值

Question

我需要用游标做这个问题的第二部分（https://imgur.com/yyoZxsw），但我的代码正在用相同的值更新每一行。基本上我需要检查时间和超时是否在某个范围内，例如上午 9 点到下午 12 点之间，付款将是 350。另外，如果它从上午 10 点到下午 16 点，我需要在 2 个范围之间进行计算。

我尝试了下面的代码，但它不起作用。它预计将通过timeinandtimeout计算要支付到amtpaid列中的金额。

create table babysitter (
babysitterid char(5) not null primary key,
datein date not null,
timein time not null,
dateout date not null,
timeout time not null, 
noofhrswrk int,
amtpaid int
);

insert into babysitter values('BS001', '18-Jan-2019', '10:00', '18-Jan- 
2019', '16:00', '', '')
insert into babysitter values('BS002', '15-Jan-2019', '13:00', '15-Jan- 
2019', 
'20:00', '', '')
insert into babysitter values('BS003', '21-Jan-2019', '21:00', '21-Jan- 
2019', 
'07:00', '', '')
insert into babysitter values('BS004', '11-Jan-2019', '08:00', '11-Jan- 
2019', '13:00', '', '')

declare @timein time
declare @timeout time
declare @hoursworked datetime

declare Calculate_No_Hrs cursor for 
select timein, timeout, noofhrswrk from babysitter

open Calculate_No_Hrs

fetch next from Calculate_No_Hrs into @timein, @timeout, @hoursworked

while (@@FETCH_STATUS = 0)
begin
update babysitter
set noofhrswrk = abs(datediff(hour, timeout, timein)) 

fetch next from Calculate_No_Hrs into @timein, @timeout, @hoursworked
end

close Calculate_No_Hrs
deallocate Calculate_No_Hrs ---end first question
--------------------------------------------------------------

declare @timein time
declare @timeout time
declare @amount int
declare @hourswrk int 
declare @pay int

set @pay = 0

declare Amt_Paid cursor for 
    select timein, timeout, noofhrswrk, amtpaid 
    from babysitter

open Amt_Paid

fetch next from Amt_Paid into @timein, @timeout, @hourswrk, @amount

while (@@FETCH_STATUS = 0)
begin
    if (@timein >= '09:00' and @timeout <= '12:00')
    begin
        set @amount = 350 * @hourswrk
        set @pay += @amount

        -- update babysitter
        -- set amtpaid = @amount
     end

     if (@timein >= '12:00' and @timeout <= '17:00')
     begin
         set @amount = 400 * @hourswrk
         set @pay += @amount

         -- update babysitter
         -- set amtpaid = @amount
     end

     if (@timein >= '17:00' and @timeout <= '21:00')
     begin
         set @amount = 500 * @hourswrk
         set @pay += @amount

         -- update babysitter
         -- set amtpaid = @amount
     end

     if (@timein >= '21:00' and @timeout <= '00:00')
     begin
         set @amount = 600 * @hourswrk
         set @pay += @amount

         -- update babysitter
         -- set amtpaid = @amount
     end

     if (@timein >= '00:00' and @timeout <= '07:00')
     begin
         set @amount = 800 * @hourswrk

         -- update babysitter
         -- set amtpaid = @amount
     end

     update babysitter
     set amtpaid = @pay

     fetch next from Amt_Paid into @timein, @timeout, @hourswrk, @amount
end

close Amt_Paid
deallocate Amt_Paid

标签： sql-servertsqldatabase-cursor

Answer 1

有一个想法你需要问；从 08:00 起，保姆的报酬是多少，不在任何范围内。就我个人而言，我会重建整个日期时间并添加一小时，直到 datetimeIn < datetimeOut 具有相同的逻辑。

SQL 标准函数不是DATEADDis DATE_ADD，可能你应该改变它。

declare @time time, @timeout time
declare @dateIn date, @dateOut date
declare @BabysiterID NVARCHAR(10)
declare @pay int = 0
declare Amt_Paid cursor for 
select BabysiterID, timein, timeout, DateIn, DateOut from @table

open Amt_Paid

fetch next from Amt_Paid into @BabysiterID, @time, @timeout, @dateIn, @dateOut

while (@@FETCH_STATUS = 0) begin

    while (@time < @timeout) or (@dateIn < @dateOut) BEGIN

    if (@time >= '09:00' and @time < '12:00') begin
        set @pay += 350 end

    if (@time >= '12:00' and @time < '17:00') begin
        set @pay += 400 end

    if (@time >= '17:00' and @time < '21:00') begin
        set @pay += 500 end

    if (@time >= '21:00') begin
        set @pay += 600 end 

    if (@time >= '00:00' and @time < '07:00') begin
        set @pay += 800 end   

    SELECT @time = DATEADD(HOUR,1,@time)    

    IF(@time = '00:00') BEGIN SELECT @dateIn = DATEADD(DAY,1,@dateIn) END

    END -- while

    update @table
     set amtpaid = @pay
    where BabysiterID = @BabysiterID

    set @pay = 0

    fetch next from Amt_Paid into @BabysiterID, @time, @timeout, @dateIn, @dateOut
end --cursor

close Amt_Paid
deallocate Amt_Paid