# question

## 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

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.

source.png (22.2 KiB)
output.png (35.4 KiB)

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

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.

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

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

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