SQL code updating table with same values for each rows

Question

I need to do the 2nd part of this question(https://i.sstatic.net/iubZR.jpg) with a cursor but my code is updating every rows with the same value. Basically i need to check if the timein and timeout is in a certain range like between 9am and 12pm the payment will be 350. Also if its from, lets say 10am to 16pm, i need to calculate between 2 ranges.

I tried the code below but its not working. Its expected to go through the timein and timeout and calculate the amount to be paid into amtpaid column.

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

Why would you want to use a cursor to do this? I would suggest you could do this without one. Like I mentioned in your other (now deleted) question, put the context of your question in your question; not on an off-site resource. Also, don't forget, we don't have access to your data (or homework) so we can't run the above without DDL and DML. — Thom A
– Thom A ♦, Commented Feb 17, 2019 at 17:30
@Larnu I think is just a question practice using a cursor is one of the requirements. @Cat_img.jpeg I've not checked if your code works but it would be pointless if you don't specify the babysiterid in the update statement. — SNR
– SNR, Commented Feb 17, 2019 at 18:04
You need to determine the hours worked within each individual range and apply the appropriate rate to those hours, e.g. someone working from 10:00 to 14:00 has worked 2 hours at Rs350/hour and 2 hours at Rs400/hour, right? — HABO
– HABO, Commented Feb 17, 2019 at 18:56
@HABO yes ive done that in the first part and stored it in a column named noofhrswrk — Cat_img.jpeg
– Cat_img.jpeg, Commented Feb 18, 2019 at 11:13
@Larnu i've updated the post with my data, but for the 3rd row im getting 14hours instead of 10, from 21:00 to 07:00 — Cat_img.jpeg
– Cat_img.jpeg, Commented Feb 18, 2019 at 11:30

SNR · Accepted Answer · 2019-02-17 18:44:54Z

1

There is one think you need to ask; How much a babysiter is paid from 08:00 since, is not in any range. Personally I would reconstruct the whole datetime and add one hour until datetimeIn < datetimeOut with silghtly the same logic.

SQL standard function is not DATEADD is DATE_ADD, probably you should change that.

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

edited Feb 17, 2019 at 18:44

answered Feb 17, 2019 at 18:34

SNR

7921 gold badge9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

HABO Over a year ago

You appear to be summing the various hourly rates that might apply, but never multiplying by the number of hours worked at any of the rates.

SNR Over a year ago

@HABO no need to multiply by hours worked if we go hour by hour through a while loop.

SNR Over a year ago

@HABO recommendation compute the results through excel (for instance) and then run the script. Test if the script have returned the correct results.

Cat_img.jpeg Over a year ago

Ok this one works, but 1 row the amount is 0 and the numberofhours is showing 14 instead of 10, its for the 21:00 to 07:00

SNR Over a year ago

@Cat_img.jpeg third row is returning 0 because your input data is incorrect. insert into babysitter values('BS003', '21-Jan-2019', '21:00', '21-Jan- 2019', '07:00', '', '') dateIn and dateOut are equal with timeIn > timeOut.

|

Nisfan · Accepted Answer · 2019-02-17 17:13:39Z

0

Check this code

DECLARE @timein TIME, @timeout TIME
DECLARE @amount INT, @hourswrk INT

SET @timein = '13:00'
SET @timeout = '20:00';

SET @hourswrk = 7

if (@timein > '7:00' AND @timeout <= '12:00:00')
BEGIN
set @amount = 350 * @hourswrk
END
else if (@timeout <= '17:00:00')
BEGIN
set @amount = 400 * @hourswrk
END
else if (@timeout <= '21:00:00')
BEGIN
set @amount = 500 * @hourswrk
END
else if (@timeout <= '00:00')
BEGIN
set @amount = 600 * @hourswrk
END
else if (@timeout <= '07:00')
BEGIN
set @amount = 800 * @hourswrk
END

answered Feb 17, 2019 at 17:13

Nisfan

7466 silver badges10 bronze badges

Comments

HABO · Accepted Answer · 2019-02-18 04:14:11Z

The various select statements at the end of the code may be used to view the intermediate results from the CTE and puzzle out what is being done step-by-step. (It's also a generally useful way to break a problem into simpler pieces and debug them one at a time.)

Worth noting is that DateDiff returns a count of boundary crossings. Please refer to the documentation for more information.

Left as an exercise for the reader is tearing apart the CTE to use cursors and loops as required by the OP.

declare @Rates as Table ( RateId Int Identity, Shift Int, StartTime Time, EndTime Time, Rate Decimal(6,2) );
-- A rate is applicable from the   StartTime   up to, but not including, the   EndTime .
-- StartTime < EndTime   unless the   EndTime   is 00:00 indicating a rate that applies until midnight.
-- A rate cannot span across midnight, but two entries for a single shift may be used to continue a rate past midnight.
insert into @Rates ( Shift, StartTime, EndTime, Rate ) values
  ( 5, '00:00', '07:00', 800.00 ),
  ( 0, '07:00', '09:00', 0.00 ), -- No rate supplied in homework assignment.
  ( 1, '09:00', '12:00', 350.00 ),
  ( 2, '12:00', '17:00', 400.00 ),
  ( 3, '17:00', '21:00', 500.00 ),
  ( 4, '21:00', '00:00', 600.00 );

select * from @Rates order by Shift;

declare @Work as Table ( WorkId Int Identity, WorkerId Int, Started DateTime, Ended DateTime );
insert into @Work ( WorkerId, Started, Ended ) values
  ( 1, '2000-01-01T11:00:00', '2000-01-01T11:15:00' ), -- Single rate.
  ( 1, '2000-01-01T09:00:00', '2000-01-01T12:00:00' ), -- Single rate.
  ( 1, '2000-01-01T10:00:00', '2000-01-01T16:00:00' ), -- Multiple rates.
  ( 5, '2000-01-01T00:00:00', '2000-01-01T04:00:00' ), -- Single rate starting at midnight.
  ( 6, '2000-01-01T10:00:00', '2000-01-02T00:00:00' ), -- Multiple rates ending at midnight.
  ( 7, '2000-01-01T10:00:00', '2000-01-02T16:00:00' ), -- Multiple dates and rates.
  ( 8, '2000-01-01T10:00:00', '2000-01-03T16:00:00' ), -- Multiple dates and rates.
  ( 9, '2000-01-01T10:00:00', '2000-01-04T00:00:00' ); -- Multiple dates and rates.

select * from @Work order by Started, WorkerId;

declare @Midnight as Time = '00:00'; -- For easier reading.

with
  Ten ( Number ) as ( select * from ( values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) as Digits( Number ) ),
  TenUp2 ( Number ) as ( select 42 from Ten as L cross join Ten as R ),
  Numbers ( Number ) as ( select Row_Number() over ( order by ( select NULL ) ) from TenUp2 ),
  Work as ( -- Split out the date/times into separate date and time columns.
    select WorkId, WorkerId,
      Cast( Started as Date ) as StartedDate, Cast( Started as Time ) as StartedTime,
      Cast( Ended as Date ) as EndedDate, Cast( Ended as Time ) as EndedTime
      from @Work ),
  WorkOverDates as ( -- Split work across dates into separate rows for each date.
    -- Work completed in a single day.
    select WorkId, WorkerId, StartedDate, StartedTime, EndedDate, EndedTime
      from Work
      where StartedDate = EndedDate
    union
    -- First day of work that spans dates.
    select WorkId, WorkerId, StartedDate, StartedTime, StartedDate, @Midnight
      from Work
      where StartedDate <> EndedDate
    union
    -- Last day of work that spans dates.
    select WorkId, WorkerId, EndedDate, @Midnight, EndedDate, EndedTime
      from Work
      where StartedDate <> EndedDate and EndedTime <> @Midnight
    union
    -- Add any intermediate days, just in case someone worked a really long time.
    select WorkId, WorkerId, DateAdd( day, N.Number, StartedDate ), @Midnight, DateAdd( day, N.Number, StartedDate ), @Midnight
      from Work as W inner join
        Numbers as N on N.Number < DateDiff( day, StartedDate, EndedDate )
      where DateDiff( day, StartedDate, EndedDate ) > 1 ),
  WorkOverRates as ( -- For each work row generate rows for all of the applicable rates (for each date).
    select WOD.WorkId, WOD.WorkerId, WOD.StartedDate, WOD.StartedTime, WOD.EndedDate, WOD.EndedTime,
      R.RateId, R.Shift, R.StartTime, R.EndTime, R.Rate
      from WorkOverDates as WOD inner join
        -- The general test for overlapping ranges is: Start1 <= End2 and Start2 <= End1.
        @Rates as R on ( WOD.StartedTime < R.EndTime or R.EndTime = @Midnight ) and
          ( R.StartTime < WOD.EndedTime or WOD.EndedTime = @Midnight ) ),
  PaidIntervals as ( -- Determine the hours worked from at each rate (for each date).
    select WorkId, WorkerId, StartedDate, StartedTime, EndedDate, EndedTime,
      RateId, Shift, StartTime, EndTime, Rate,
      DateDiff( millisecond,
        -- From the later of the work or rate start time to ...
        case when StartedTime < StartTime then StartTime else StartedTime end,
        -- ... the earlier of the work or rate end time allowing for midnight.
        case
          when EndedTime = @Midnight and EndTime = @Midnight then DateAdd( day, 1, 0 )
          when EndedTime = @Midnight then EndTime
          when EndTime = @Midnight then EndedTime
          when EndedTime < EndTime then EndedTime
          else EndTime end ) / 3600000.0 as HoursWorked
      from WorkOverRates ),
    PaySummary as ( -- Summarize all of the rate periods for each   WorkId .
      select WorkId, Sum( HoursWorked ) as TotalHours, Count( 42 ) as RatePeriods,
        Sum( Rate * HoursWorked ) as TotalPay
        from PaidIntervals
        group by WorkId )
  -- To see the intermediate results in the CTE use one of the following   select   statements instead of the final   select :
  --select * from Numbers;
  --select * from Work order by WorkId;
  --select * from WorkOverDates order by WorkId, StartedDate;
  --select * from WorkOverRates order by WorkId, StartedDate, StartedTime;
  --select * from PaidIntervals order by WorkId, StartedDate, StartedTime;
  --select * from PaySummary order by WorkId;
  -- Put the summary together with the original work data.
  select W.WorkId, W.WorkerId, W.Started, W.Ended, PS.TotalHours, PS.RatePeriods, PS.TotalPay
    from @Work as W inner join
      PaySummary as PS on PS.WorkId = W.WorkId
    order by PS.WorkId;

well this one is a little bit advanced for me but i'll study it, thnks

Collectives™ on Stack Overflow

SQL code updating table with same values for each rows

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related