I am really new to SQL so I am asking for some assistance.

I would like to have a query that would calculate the difference between two timestamps.

The timestamp data is unsorted and stored in two columns as shown in TABLE RandomData.
Given the following data in TABLE RandomData it should produce the results shown.

The timestamp is calculated as (Weeks * 604800) + Seconds (probably promote to a BIGINT)
to account for seconds rolling over at the beginning of a new week. So I will need to sort
by the timestamp then calculate the TimeDiff as next row timestamp minus current row timestamp,
the last row timestamp ends up null.

This should be doable without producing any temporary tables but I don't know enough yet
how to structure the query. It would also be nice to have an all-in-one query that identifies
only the differences that exceed a threshold value.

Any assistance is greatly appreciated.

CREATE TABLE RandomData
(
ID INT -- ID
Weeks INT, -- Weeks since beginning of epoch
Seconds INT -- Time since start of week
);

Can you explain how did you calculate the TimeDiff?

0

Mr_DifficultAuthor Commented:

...then calculate the TimeDiff as next row timestamp minus current row timestamp,
the last row timestamp ends up null.

TimeDiff is calculated by subtracting the timestamp in one row from the timestamp in the next row and so on until the end of the table is reached. (The table needs to be sorted for any of the TimeDiff values to make sense.)

So the sorted table (based on the calculated timestamp) for the first two rows is ID 1 and ID 3.
Take the calculated timestamp of the second row in the sorted table (ID 3) and subtract the calculated timestamp of the first row in the sorted table (ID 1) to get the TimeDiff.

so we have ((161*604800)+161985) - ((161*604800)+161983) = 2 which is the TimeDiff for the first row (ID = 1)

timestamp is not a column in the table, it is a calculated value based on Week and Time per the formula

timestamp = (Week*604800)+Time

ID Week Time TimeDiff
1 161 161983 2
3 161 161985 2

Hopefully this is a little more clear now, if not I will try to rephrase it.

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

simply put WHERE condition inside the "Sorting" query

for ex

;WITH Sorting AS (SELECT ID, Weeks, Seconds, ROW_NUMBER() OVER (ORDER BY Weeks, Seconds) AS RNFROM RandomDataWHERE Weeks = 161-- or WHERE Weeks BETWEEN 153 AND 154 etc.) SELECT ....

Then should put WHERE condition at the select query

;WITH Sorting AS (
SELECT ID, Weeks, Seconds, ROW_NUMBER() OVER (ORDER BY Weeks, Seconds) AS RN
FROM RandomData
--ORDER BY Weeks, Seconds
)
SELECT A.*, ((B.Weeks - A.Weeks) * 604800) + B.Seconds - A.Seconds
FROM Sorting A
LEFT JOIN Sorting B ON A.RN = B.RN - 1
WHERE ((B.Weeks - A.Weeks) * 604800) + B.Seconds - A.Seconds > 200 -- or >= @Threshold which must declare/set earlier.
--ORDER BY A.RN

0

Mr_DifficultAuthor Commented:

Last Question: The ORDER BY A.RN is commented out, is that by design or an accident?

It's depend, since to compare by Threshold, order might not important.
However, you can remain the ORDER BY to have the resultset sorting by Weeks, Seconds

0

Mr_DifficultAuthor Commented:

Cool, I will retain it then. Awesome job by the way. The code exactly what I want with no goofing around with temporary tables.

Thanks.

0

Mr_DifficultAuthor Commented:

Awesome job, quick, short and to the point solution.

0

Featured Post

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!