CODEWITHSUNDEEP
sql-server-2008t-sql
sekarmaeu
sekarmaeu

Reputation: 105

Calculate difference (time) between two records, keep the last record in the result

I have a table with process data containing TagName, TimeStamp and machine status, 1=Running, 0=Not running. The raw data could look something like this:

Bss105_AUTO 2011-11-03 17:00:00.0000000 1
Bss105_AUTO 2011-11-03 22:32:49.5520000 1
Bss105_AUTO 2011-11-03 22:36:52.3200000 1
Bss105_AUTO 2011-11-04 08:15:28.6460000 0
Bss105_AUTO 2011-11-04 08:17:42.6520000 1
Bss105_AUTO 2011-11-04 11:49:42.3780000 1

What I want to achieve is to calculate the total runtime for the machine by using this query:

 WITH    rows AS
        (
        SELECT *, ROW_NUMBER() OVER (ORDER BY DateTime) AS RowNumber
        FROM   History where TagName='Bss105_AUTO' 
                AND DateTime >= '2011-11-03 17:00:00.000' 
                AND DateTime <= '2011-11-04 17:00:00.000'
                AND wwRetrievalMode = 'Delta'
                AND wwVersion = 'Original'
                AND OPCQuality = 192
        ) 
    SELECT  mc.RowNumber, mc.DateTime, mc.TagName, mc.Value as MachineStatus, DATEDIFF(second, mc.DateTime, mp.DateTime) GrindingMachineRuntimeInSeconds
    FROM    rows mc 
    JOIN    rows mp 
    ON      mc.RowNumber = mp.RowNumber - 1 
    where mc.Value <> 0

I do not want to include the calculated downtime where the machine isn't running (status 0=Not running). No problem, I can achive this by excluding these records from the result which would look like this (the last column contains number of seconds between the records).

1   2011-11-03 17:00:00.0000000 Bss105_AUTO 1   19969
2   2011-11-03 22:32:49.5520000 Bss105_AUTO 1   243
3   2011-11-03 22:36:52.3200000 Bss105_AUTO 1   34716
5   2011-11-04 08:17:42.6520000 Bss105_AUTO 1   12720

My problem here is that I also need the last record stored with this timestamp: 2011-11-04 11:49:42.3780000

This is the case only if the machine status at this point is equal to 1 (=running), because I need to know if it is running or not to be able to calculate runtime from the last stored record to the end of the query interval!! The last column for that record must then be empty!

Are there any gurus out there who can give me a hand on this?

Kind regards!

Upvotes: 1

Views: 848

Answers (1)

Tom Hunter
Tom Hunter

Reputation: 5918

CREATE TABLE [dbo].[History]
(
    [TagName]           CHAR(11)    NOT NULL,
    [DateTime]          DATETIME2   NOT NULL,
    [MachineStatus]     BIT         NOT NULL
)
GO

INSERT  [dbo].[History]
VALUES  ('Bss105_AUTO', '2011-11-03 17:00:00.0000000', 1),
        ('Bss105_AUTO', '2011-11-03 22:32:49.5520000', 1),
        ('Bss105_AUTO', '2011-11-03 22:36:52.3200000', 1),
        ('Bss105_AUTO', '2011-11-04 08:15:28.6460000', 0),
        ('Bss105_AUTO', '2011-11-04 08:17:42.6520000', 1),
        ('Bss105_AUTO', '2011-11-04 11:49:42.3780000', 1)
GO

;WITH [cteRows] AS
(
    SELECT  [TagName],
            [DateTime],
            [MachineStatus],
            ROW_NUMBER() OVER (ORDER BY [DateTime]) AS [RowNumber]
    FROM [dbo].[History]
    WHERE [TagName] = 'Bss105_AUTO' 
    AND [DateTime] >= '2011-11-03 17:00:00.000' 
    AND [DateTime] <= '2011-11-04 17:00:00.000'
) 
SELECT  mc.[RowNumber],
        mc.[DateTime],
        mc.[TagName],
        mc.[MachineStatus],
        DATEDIFF(SECOND, mc.[DateTime], ISNULL(mp.[DateTime], mc.[DateTime])) AS [GrindingMachineRuntimeInSeconds]
FROM [cteRows] mc 
LEFT JOIN [cteRows] mp 
    ON mc.[RowNumber] = mp.[RowNumber] - 1 
WHERE mc.[MachineStatus] <> 0

Upvotes: 1

Related Questions