January 28, 2019 at 9:10 am
Hi,
I need to to something similar for TOwners like I have for WorkDone in the code below . I am looking for prev_Twoners column to see where the change occurred based on TaskID and ProjectID for TOwners .
CREATE TABLE #Test
(
TaskID INT,
ProjectID INT,
TOwners VARCHAR(10),
WorkDone INT,
UpdateDate DATETIME,
Counts INT
)
INSERT INTO #Test ( TaskID,ProjectID,TOwners,WorkDone,UpdateDate,Counts )
SELECT 1,1,'Name1',100,'2019-01-22 00:00:00.000',0 UNION
SELECT 1,1,'Name1',100,'2019-01-23 00:00:00.000',0 UNION
SELECT 1,1,'Name1',100,'2019-01-24 00:00:00.000',0 UNION
SELECT 1,1,'Name1',110,'2019-01-25 00:00:00.000',0 UNION
SELECT 1,1,'Name2',100,'2019-01-18 00:00:00.000',0 UNION
SELECT 1,1,'Name2',100,'2019-01-19 00:00:00.000',0 UNION
SELECT 1,1,'Name2',100,'2019-01-20 00:00:00.000',0 UNION
SELECT 1,1,'Name3',100,'2019-01-21 00:00:00.000',0
SELECT * FROM #Test
--Code used to detect change for work done
SELECT
t1.TaskID,t1.ProjectID ,Updatedate,
max(WorkDone) over (partition by TaskID order by UpdateDate rows between 1 preceding and 1 preceding) as prev_Completed,
WorkDone,
TOwners
FROM #Test t1
DROP TABLE #Test
Thanks,
PSB
January 28, 2019 at 4:48 pm
I'll present two methods that I found to get the data. Thanks for posting the DDL; it was very helpful to have something to use for testing. If my solutions are not what you need, please explain further and I'll try again.
First, I'll use the "LAG" function, which gets data from prior rows in the set. There is also a corresponding "LEAD" function, which gets data from subsequent rows. You tell SQL how to sort the data, then how many rows back (for LAG) or how many rows forward (for LEAD) to get data. In the query below, "LAG(WorkDone, 1)" tells SQL to get the "WorkDone" column from "1" row prior (which is determined by the OVER clause).SELECT TaskID,
ProjectID,
UpdateDate,
LAG(WorkDone, 1) OVER (PARTITION BY TaskID, ProjectID ORDER BY UpdateDate) AS Prev_WorkDone,
WorkDone,
LAG(TOwners, 1) OVER (PARTITION BY TaskID, ProjectID ORDER BY UpdateDate) AS Prev_TOwners,
TOwners
FROM #Test
ORDER BY TaskID,
ProjectID,
UpdateDate
Second, I'll use a Common Table Expression to sort the records in order and create a "SortSeq" column, then join the data to itself using the sequence number but offset by one, which gets the prior records. Then you can use anything you want from the prior record. This is the manual way of doing what LEAD and LAG do.WITH BaseData
AS (
SELECT TaskID, ProjectID, TOwners, WorkDone, UpdateDate, Counts,
ROW_NUMBER() OVER (PARTITION BY TaskID, ProjectID ORDER BY UpdateDate) AS SortSeq
FROM #Test
)
SELECT b.TaskID,
b.ProjectID,
b.UpdateDate,
p.WorkDone AS Prev_WorkDone,
b.WorkDone,
p.TOwners AS Prev_TOwners,
b.TOwners
FROM BaseData b /* Base data */
LEFT JOIN BaseData p /* Previous record */
ON p.SortSeq = b.SortSeq - 1
ORDER BY b.TaskID,
b.ProjectID,
b.UpdateDate
January 29, 2019 at 8:15 am
fahey.jonathan - Monday, January 28, 2019 4:48 PMI'll present two methods that I found to get the data. Thanks for posting the DDL; it was very helpful to have something to use for testing. If my solutions are not what you need, please explain further and I'll try again.First, I'll use the "LAG" function, which gets data from prior rows in the set. There is also a corresponding "LEAD" function, which gets data from subsequent rows. You tell SQL how to sort the data, then how many rows back (for LAG) or how many rows forward (for LEAD) to get data. In the query below, "LAG(WorkDone, 1)" tells SQL to get the "WorkDone" column from "1" row prior (which is determined by the OVER clause).
SELECT TaskID,
ProjectID,
UpdateDate,
LAG(WorkDone, 1) OVER (PARTITION BY TaskID, ProjectID ORDER BY UpdateDate) AS Prev_WorkDone,
WorkDone,
LAG(TOwners, 1) OVER (PARTITION BY TaskID, ProjectID ORDER BY UpdateDate) AS Prev_TOwners,
TOwners
FROM #Test
ORDER BY TaskID,
ProjectID,
UpdateDateSecond, I'll use a Common Table Expression to sort the records in order and create a "SortSeq" column, then join the data to itself using the sequence number but offset by one, which gets the prior records. Then you can use anything you want from the prior record. This is the manual way of doing what LEAD and LAG do.
WITH BaseData
AS (
SELECT TaskID, ProjectID, TOwners, WorkDone, UpdateDate, Counts,
ROW_NUMBER() OVER (PARTITION BY TaskID, ProjectID ORDER BY UpdateDate) AS SortSeq
FROM #Test
)
SELECT b.TaskID,
b.ProjectID,
b.UpdateDate,
p.WorkDone AS Prev_WorkDone,
b.WorkDone,
p.TOwners AS Prev_TOwners,
b.TOwners
FROM BaseData b /* Base data */
LEFT JOIN BaseData p /* Previous record */
ON p.SortSeq = b.SortSeq - 1
ORDER BY b.TaskID,
b.ProjectID,
b.UpdateDate
If you're going to present two options, you should discuss the relative merits of each option. In this case, there is simply no comparison. LAG will always outperform the second method. The only reason to even consider the second method is if you are working on SQL 2008 or earlier where LAG is not available.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 29, 2019 at 11:42 am
Works perfectly . Tried the first solution . Thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply