Calculate duration based on a condition

Tinashe Chinyati 156 Reputation points
2022-05-22T07:29:59.747+00:00

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][1] 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][2] [1]: /api/attachments/204340-source.png?platform=QnA [2]: /api/attachments/204278-output.png?platform=QnA

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,697 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments
Accepted answer
  1. Erland Sommarskog 100.9K Reputation points MVP
    2022-05-22T16:06:43.363+00:00

    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.

    2 people found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,096 Reputation points
    2022-05-22T13:55:24.447+00:00

    Good day anonymous user 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://learn.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

    0 comments