October 29, 2021 at 8:11 pm
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
October 29, 2021 at 8:58 pm
Use LAG() to look at a previous record
October 29, 2021 at 9:06 pm
Thanks for your quick reply at the moment is on sql server 2008r2, so it doesnt have LAG I think...
October 29, 2021 at 10:37 pm
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
Change is inevitable... Change for the better is not.
October 29, 2021 at 10:38 pm
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
Change is inevitable... Change for the better is not.
October 29, 2021 at 11:33 pm
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););
October 30, 2021 at 1:44 am
Heh... you really should trying running your own code before you post it. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2021 at 1:58 am
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
Change is inevitable... Change for the better is not.
October 30, 2021 at 2:23 am
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;
October 30, 2021 at 11:15 am
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!
October 30, 2021 at 11:19 am
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?
October 30, 2021 at 3:04 pm
Hi Jonathan,
Your query worked a treat, thank you!
November 3, 2021 at 4:22 am
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.
November 3, 2021 at 5:01 am
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
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply