You could test if the modulo .1 of HRS equals zero

;WITH cte_data AS (
    SELECT CAST(50.10 AS numeric(5, 2)) AS HRS
   ...

Also there's EOMONTH which has an optional second parameter 'offset'.  Regardless of which date/datetime datatype is passed to the EOMONTH function it returns DATE.  Ha, some SSC members were wondering...

All perfect parents have the same thing in common.  No kids

Also my code assumes the RequestNo is UNIQUE NOT NULL and has appropriate sequential values.  The DDL provided says the RequestNo is NULL-able

drop TABLE  if exists #F6000059;
go
CREATE TABLE #F6000059
(
    [RequestNo] [int] NULL,
    [REAN82] [int] NULL,
    [REAN83] [int] NULL,
 ...

• This reply was modified 2 years, 9 months ago by Steve Collins.

It seems like you're looking for an UPDATE statement

/* select using OUTER APPLY and TOP(1) */
select t.id, t.code, t.result, prev.result prev_result, 
     ...

...

In source table each row is allways for one date, so if the work shift continues over midnight it's broken into two parts. But for expected output the time...

Thanks, Drew for your reply. Did you bother to read my latest post? and did you bother to run the query that I provided?

IF yes, then my question was...

Using the preceding...

Afaik the set up is not clear.  This seems to produce the correct result

with
gaps_cte as (
    select *, case when crit<>lag(crit) over (order by...

This took a while, but was fun! There are a couple of characters in this forum who like to come along and make my code run 17 times faster,...

One way to generalize the...

Ok the set up and explanation are appreciated.  Right off the bat tho there's an issue (which is hopefully a typo) with the sample data.  In the #tbl_transection table in...

Keep at it you're on the right track.  Your query doesn't seem to return any error so what's the question?  What's Wrong Output and Correct Output seems...

It appears the math in your example calculation is off (by 300.00) beginning on line 3: instead of 21.37 should it not be 321.37?

select 21.37 + (11033.21)...

• This reply was modified 2 years, 9 months ago by Steve Collins.