question

Tinashe-5786 avatar image
0 Votes"
Tinashe-5786 asked SeeyaXi-msft edited

Calculate duration based on a condition

Hi

I am still new in using sql server and would like some assistance. I have a table with 3 columns SerialNumber, EventDate and LaunchExitEventKey
204340-source.png


I want to check if the LaunchExitEventKey from previous row is not equal to the next row i.e. 2 and 52 then calculate the duration by subtracting EventDate. In case if the LaunchExitEventKey is repeated eg 2,2,2,52 I need the last row for 2 and next 52 to calculate the duration.
204278-output.png


sql-server-generalsql-server-transact-sql
source.png (22.2 KiB)
output.png (35.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
2 Votes"
ErlandSommarskog answered

Here is a query that meets your needs as I understand them:

WITH CTE AS (
   SELECT SerialNumber, EventDate, LauchExitEventKey,
            prevkey = LAG(LauchExitEventKey) OVER(PARTITION BY SerialiNumber ORDER BY EventDate), 
            prevdate = LAG(EventDate) OVER(PARTITION BY SerialiNumber ORDER BY EventDate) 
   FROM  tbl
)
SELECT SerialNumber, prevdate, EventDate,  datediff(DAY, prevdate, EventDate)
FROM   CTE
WHERE  LauchExitEventKey = 52
   AND prevkey = 2


For reasons that Ronen (a.k.a. "Pituach") points out, this is an untested query. If the query does not work for you, please review Ronen's post in detail.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered pituach edited

Good day @Tinashe-5786 and welcome to the QnA forums

I have a table

Unfortunately we do not have your table and most of us cannot read minds or execute queries on your table. What you provided is images and NOT table.

If the following short explanation do not solve your needs then please provide

1) Queries to CREATE your table including indexes (this called DDL)
2) Queries to INSERT sample data (this called DML).
3) The desired result given the sample, as text or image of excel for example.
4) A short description of the business rules, and how you got 1-2 of the results
5) Which version of SQL Server you are using (this will help to fit the query to your version).

I want to check if the LaunchExitEventKey from previous row is not equal to the next row i.e. 2 and 52 then calculate the duration by subtracting EventDate.

In first glance it seems like you can solve your needs using the function LAG

https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15&WT.mc_id=DP-MVP-5001699

You can use LAG inside a CTE in your query and compare the values of the previews row. Use the clue OVER in order to set the grouping and the order of the rows

If you need specific solution the please provide the missing information (DDL+DML) as explained above, but please try to solve it using the explanation here first

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.