Windows function : difference b/w current row and prev row of different column
-
Hi,
I am trying to perform a Windows lag function across two different columns. My table has names with columns for start dates and end dates. I am trying to find the number of days between the start date of the current row and the end date of the previous row for the same employee. Is there a way to do this ? I had a look online and all I could find was windows lead and lag functions done on the same column
Hopefully the image below will make it more clear what I am trying to achieve. Any help will be much appreciated
- This topic was modified 2 years, 9 months ago by masterelaichi.
- This topic was modified 2 years, 9 months ago by masterelaichi.
-
In order to help us to help you, please provide data in readily consumable format (like this)
IF OBJECT_ID( 'TempDB..#Data', 'U' ) IS NOT NULL
DROP TABLE #Data;
CREATE TABLE #Data (
[Name] char(1)
, [Start] date
, [End] date
);
INSERT INTO #Data ( [Name], [Start], [End] )
VALUES ( 'A', '2021-01-01', '2021-01-31' )
, ( 'A', '2021-02-01', '2021-02-13' )
, ( 'A', '2021-03-15', '2021-03-31' )
---------------------------------------
, ( 'B', '2021-02-15', '2021-02-28' )
, ( 'B', '2021-03-13', '2021-03-31' )
, ( 'B', '2021-04-01', '2021-04-25' )
, ( 'B', '2021-04-26', '2021-05-10' )
---------------------------------------
, ( 'C', '2021-02-03', '2021-02-10' )
, ( 'C', '2021-03-15', '3031-03-30' );Now, based on your data, the following SQL query should do the trick
SELECT d.[Name]
, d.[Start]
, d.[End]
--, Prev_End = LAG(d.[End]) OVER (PARTITION BY d.[Name] ORDER BY d.[Start]) -- Uncomment to see the result of the LAG
, Numdays = ISNULL(DATEDIFF(dd, LAG(d.[End]) OVER (PARTITION BY d.[Name] ORDER BY d.[Start]), d.[Start]), 0)
FROM #Data AS d
ORDER BY d.[Name], d.[Start]; -
Thank you! this one work perfectly. I will run a couple of checks on the report I require
I am newish to SQl. Is there a difference in using an alias using the AS keyword as opposed to what you have done here for the Prev_end and Numday lines?
- masterelaichi wrote:
Is there a difference in using an alias using the AS keyword as opposed to what you have done here for the Prev_end and Numday lines?
Functionally, there is no difference. Consider the following
- <li style="list-style-type: none;">
- [Alias] = "Sometime you get a very long calculations for the source, like the LAG in this case"
- "Sometime you get a very long calculations for the source, like the LAG in this case" AS [Alias]
I prefer the 1st format. The reason for this is that when I look at a list of columns, I see all of the column alias/name aligned down the left hand side. Then, if I want to see where the column value comes from, I move over to the right and dig deeper.
In the 2nd format, my eyes are constantly having to move left and right looking for the alias/name. I find that irritating to do.
-
The LAG function can be simplified - no need to use ISNULL if you use the following:
, Numdays = DATEDIFF(dd, LAG(d.[End], 1, d.Start) OVER (PARTITION BY d.[Name] ORDER BY d.[Start]), d.[Start])
The second parameter is the offset value and determines how far back LAG will look for a value. The third parameter is the default value - which will return the value when the offset is beyond the scope of the partition. In other words, when there isn't a previous row - return the d.Start value.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I am trying to filter the results on the Numdays column. Is it possible to filter the results on an aliased column ? I want to look for anomalies on the numdays column but I get an incorrect syntax error. I cam do it using temp tables but trying to avoid it where I can
Thanks
-
Use s CTE or derived table
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - masterelaichi wrote:
I am newish to SQl. Is there a difference in using an alias using the AS keyword as opposed to what you have done here for the Prev_end and Numday lines?
IMHO, there's a huge difference... Using the equality method rather than AS makes it a whole lot easier to find such column aliases. If you go the extra mile and vertically align the code to the right of the "=" sign, it makes it much easier to read and troubleshoot, which is basically what Des Norton also said.
--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) -
That makes sense. Thanks for all your responses!
-
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply