Difficulty using SELECT SUM in a query USING SQL Server

Question

I have the following query:

WITH rows AS
    (
    SELECT  *, ROW_NUMBER() OVER (ORDER BY TimeStamp) AS rn
    FROM    [AVL_Ignition]
    )
SELECT  mc.[DeviceIMEI], (mp.TimeStamp - mc.TimeStamp) as millisecond, mc.Value, mc.Tag
FROM    rows mc
JOIN    rows mp
ON      mc.rn = mp.rn - 1

this query is working correctly and is returning me the following values

DeviceIMEI| milissecond|value
   123    |     184    |1
   123    |     184    |0
   123    |     184    |1
   123    |     184    |0
   123    |     184    |1
   123    |     184    |0

I am wanting to add the values ​​in the millisecond field where value = 1

I'm trying to use SELECT SUM as follows but I'm not getting a result

SELECT mc.[DeviceIMEI], SUM (WITH rows AS
    (
    SELECT  *, ROW_NUMBER() OVER (ORDER BY TimeStamp) AS rn
    FROM    [AVL_Ignition]
    )
SELECT (mp.TimeStamp - mc.TimeStamp) as millisecond
FROM    rows mc
JOIN    rows mp
ON      mc.rn = mp.rn - 1) where mc.Value = 1;

I know that SELECT SUM is not complicated to use but I'm having trouble doing this with this query

Answer 1

I'd do it without using SUM in this case. If you are on SQL Server 2012+ you can use the IIF function like this (just leave your WITH section where it is):

SELECT
  mc.DeviceIMEI
, (mp.TimeStamp - mc.TimeStamp) + IIF(mc.Value = 1, 1, 0) AS millisecond
, mc.Value
, mc.Tag

Otherwise you have to use a CASE:

SELECT
  mc.DeviceIMEI
, (mp.TimeStamp - mc.TimeStamp) + CASE
                                    WHEN mc.Value = 1 THEN mc.Value
                                    ELSE 0
                                  END AS millisecond
, mc.Value
, mc.Tag