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: 2300
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: 63198 More actions · Answer 1

pietlinden

SSC Guru

Points: 63198

October 29, 2021 at 8:58 pm

#3946105

Use LAG() to look at a previous record

Tallboy SSCrazy Points: 2300 More actions · Answer 2

Tallboy

SSCrazy

Points: 2300

October 29, 2021 at 9:06 pm

#3946108

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: 1000539 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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 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)
Intro to Tally Tables and Functions

Tallboy SSCrazy Points: 2300 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 2 years, 11 months ago by Tallboy.

Jeff Moden SSC Guru Points: 1000539 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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 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)
Intro to Tally Tables and Functions

Jonathan AC Roberts SSCoach Points: 17601 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: 2300 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: 2300 More actions · Answer 10

Tallboy

SSCrazy

Points: 2300

October 30, 2021 at 11:19 am

#3946235

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: 2300 More actions · Answer 11

Tallboy

SSCrazy

Points: 2300

October 30, 2021 at 3:04 pm

#3946272

Hi Jonathan,

Your query worked a treat, thank you!

pietlinden SSC Guru Points: 63198 More actions · Answer 12

pietlinden

SSC Guru

Points: 63198

November 3, 2021 at 4:22 am

#3947350

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: 1000539 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)
Intro to Tally Tables and Functions