SQLTeam.com | Weblogs | Forums

Time difference between date field by record, grouped by different columns


#1

Hi, I'm trying to work out how to write time difference between records based on a date field, then grouping time difference by other fields.
Needs to be sorted by date field - in time order.

SQL:
SELECT LEFT(dbo.Tags.TagName, 10) AS RetortName_ID, dbo.Tags.TagName, dbo.NumericSamples.TagID, dbo.ToDate(dbo.NumericSamples.SampleDateTime) AS PhaseDateTime,
dbo.NumericSamples.SampleDateTime, dbo.NumericSamples.SampleValue, dbo.NumericSamples.QualityID, LEFT(dbo.NumericSamples.SampleValue, 1) AS Phase, RIGHT(dbo.NumericSamples.SampleValue, 2)
AS Cycle
FROM dbo.NumericSamples INNER JOIN
dbo.Tags ON dbo.NumericSamples.TagID = dbo.Tags.ID
WHERE (dbo.NumericSamples.TagID IN
(SELECT ID
FROM dbo.Tags AS Tags_1
WHERE (TagName LIKE '%PRT%PhzCyc%'))) AND (dbo.Tags.TagName LIKE 'WGIPS.PRT1%') AND (dbo.ToDate(dbo.NumericSamples.SampleDateTime) >= '2018-05-01')


Hope to get this ouput - any help is much appreciated!:

Tag Name PhaseDateTime| Phase | Cycle | PhaseDiff | CycleDiff
RT1PhzCyc 2018-05-01 00:05:50.000 | 1 | 1 | 0 | 0
RT1PhzCyc 2018-05-01 00:25:15.000 | 5 | 1 | 20 | 0
RT1PhzCyc 2018-05-01 00:43:15.000 | 2 | 1 | 18 | 0
RT1PhzCyc 2018-05-01 01:06:25.000 | 3 | 1 | 23 | 0
RT1PhzCyc 2018-05-01 01:12:40.000 | 1 | 1 | 6 | 0
RT1PhzCyc 2018-05-01 01:28:45.000 | 1 | 2 | 0 | 67
RT1PhzCyc 2018-05-01 01:48:10.000 | 5 | 2 | 20 | 0
RT1PhzCyc 2018-05-01 02:06:15.000 | 2 | 2 | 18 | 0
RT1PhzCyc 2018-05-01 02:28:50.000 | 3 | 2 | 22 | 0
RT1PhzCyc 2018-05-01 02:35:10.000 | 1 | 2 | 7 | 0
RT1PhzCyc 2018-05-01 02:51:15.000 | 1 | 3 | 0 | 67


#2

Please provide:

  • table definitions in the form of create statements
  • sample data in the form of insert statements