Recursive Query to get get diff between two rows for same client

Question

Post reply

Recursive Query to get get diff between two rows for same client

Tallboy

SSCrazy

Points: 2455
More actions
October 29, 2021 at 8:11 pm

#3946098

Hi I have a client table the stores Client services with multiple rows for each client. StartDate, ServiceType, Status and Weekly Hours.
Status is 1 Active and 0 is Inactive
CREATE TABLE [dbo].[tblClientService](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NULL,
[StartDate] [date] NULL,
[Status] [bit] NULL,
[ServiceType] [int] NULL,
[WklyHours] [numeric(16,2)] NULL)
I need to somehow link the first row with the second, second with the third etc etc and show the difference between WklyHours!
I was gonna use the OVER and PARTITION BY but havent got it working yet
WITH DataSet AS (
SELECT *, ROW_NUMBER() OVER ( ORDER BY ClientID) rownum
FROM tblClientService
)
SELECT D2.ClientID, D2. ServiceType,D2.StartDate, (D2.WeeklyHrs - D1.WeeklyHrs) As WkDiff
FROM DataSet d1
LEFT JOIN DataSet d2
ON d1.ClientID = d2.ClientID AND d1.rownum+1 = d2.rownum

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63487 More actions · Answer 1

pietlinden

SSC Guru

Points: 63487

October 29, 2021 at 8:58 pm

#3946105

Use LAG() to look at a previous record

Tallboy SSCrazy Points: 2455 More actions · Answer 2

Thanks for your quick reply at the moment is on sql server 2008r2, so it doesnt have LAG I think...

Jeff Moden SSC Guru Points: 1004300 More actions · Answer 3

Tallboy wrote:

Thanks for your quick reply at the moment is on sql server 2008r2, so it doesnt have LAG I think...

You posted in a 2019 forum. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004300 More actions · Answer 4

If you'd post some readily consumable data, that would help us help you with an answer. See the first link in my signature line below for one of many ways to do such a thing and why.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tallboy SSCrazy Points: 2455 More actions · Answer 5

SURE, I mean to do and forgot, here it is now...

tblClientService (ID, ClientID, StartDate, Status (1/0), ServiceType(1-100), WklyHours)

INSERT INTO tblClientServiceVALUES (100, '2021-01-01', 1, 21, 5.0);

INSERT INTO tblClientService VALUES (100, '2021-02-22', 1, 22, 9.0);

INSERT INTO tblClientService VALUES (100, '2021-03-13', 1, 25, "11.0);

INSERT INTO tblClientService VALUES (101, '2021-01-11', 1, 26, 2.0);

INSERT INTO tblClientService VALUES (101, '2021-02-12', 1, 22, 4.0);

INSERT INTO tblClientService VALUES (101, '2021-03-23', 1, 25, "3.0););

This reply was modified 3 years ago by Tallboy.

Jeff Moden SSC Guru Points: 1004300 More actions · Answer 6

Heh... you really should trying running your own code before you post it. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004300 More actions · Answer 7

Ah.... sorry.... What do you want the output to look like for the test data you provided? I ask because you have multiple clients and you've not identified what you want to do for the first row of each client because it has no previous row by client. Also, you have no rows in the test data with an inactive status of "0". What do you want done with those if they show up in the data?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18339 More actions · Answer 8

CREATE TABLE [dbo].[tblClientService]
(
    [ID]          [int] IDENTITY(1,1) NOT NULL,
    [ClientID]    [int]               NULL,
    [StartDate]   [date]              NULL,
    [Status]      [bit]               NULL,
    [ServiceType] [int]               NULL,
    [WklyHours]   [numeric](16,2)     NULL
);


;WITH DataSet AS 
(
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY StartDate) rownum
      FROM tblClientService
)
SELECT D2.ClientID, 
       D2.ServiceType,
       D2.StartDate, 
       (D2.[WklyHours] - D1.[WklyHours]) As WkDiff
  FROM DataSet d1
  LEFT JOIN DataSet d2
         ON d1.ClientID = d2.ClientID 
        AND d1.rownum + 1 = d2.rownum;

Tallboy SSCrazy Points: 2455 More actions · Answer 9

Hi Jeff,

Good questions Jeff, its in development so not sure exactly what to do with results yet?

If Client has only one row then leave it out of the results set, so client must have 2 or more rows in the table!

Status doesn't really affect the results so no need to use it.

The real life table has many more fields than the example shown, but are not necessary for the results, which is the difference in WklyHours between one service and the next for each client, it may be +ve or -ve.

But thanks for your interest again any help appreciated!

Tallboy SSCrazy Points: 2455 More actions · Answer 10

Hi JOnathon,

I see my mistake I should have used Partion by StartDate!

Must have been sleeping!

Is there any simpler way of doing it tho or is that the best way (again using SLQ Server 2008R2, must upgrade sto Slq 2019 at some stage!). pietlingen suggested LAG but I have not used before?

Tallboy SSCrazy Points: 2455 More actions · Answer 11

Hi Jonathan,

Your query worked a treat, thank you!

pietlinden SSC Guru Points: 63487 More actions · Answer 12

Don't think you can use LAG(). I think it was introduced in SQL Server 2012.

I only suggested it because you posted in a 2019 forum.

Jeff Moden SSC Guru Points: 1004300 More actions · Answer 13

Tallboy wrote:

Hi Jeff,
Good questions Jeff, its in development so not sure exactly what to do with results yet?
If Client has only one row then leave it out of the results set, so client must have 2 or more rows in the table!
Status doesn't really affect the results so no need to use it.
The real life table has many more fields than the example shown, but are not necessary for the results, which is the difference in WklyHours between one service and the next for each client, it may be +ve or -ve.
But thanks for your interest again any help appreciated!

What I was getting at is the code for your test data won't run without error.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)