CODEWITHSUNDEEP
sqlsql-serversql-server-2005
user2020598
user2020598

Reputation: 37

Not Select Bottom Row

I have this LINK ,

Why it not counting until last row?

I want output that count until last row.
It count the interval of STATUS = 0 

    CREATE TABLE [Alerts]
(
  [Timestamp] datetime,
  [Status] int
)

INSERT INTO [Alerts] ([Timestamp], [Status])
  VALUES
    ('2013-1-1 00:00:00', 1),
    ('2013-1-1 00:00:05', 1),
    ('2013-1-1 00:00:10', 2),
    ('2013-1-1 00:00:15', 2),
    ('2013-1-1 00:00:20', 0),
    ('2013-1-1 00:00:25', 1),
    ('2013-1-1 00:00:30', 1),
    ('2013-1-1 00:00:32', 2),
    ('2013-1-1 00:00:35', 2),
    ('2013-1-1 00:00:40', 0),
    ('2013-1-1 00:00:45', 0),
    ('2013-1-1 00:00:50', 0)



I've tried this:

SELECT
  MIN ([main].[Start]) AS [STOP_Begin],
  [main].[End] AS [STOP_End],
  DATEDIFF(s, MIN([main].[Start]), [main].[End]) AS [Interval_Second]
FROM
(
  SELECT
    [sub].[Start],
    MIN([sub].[End]) AS [End]
  FROM
  (
    SELECT
      [start].[Timestamp] AS [Start],
      [start].[Status] AS [StartingStatus],
      [end].[Timestamp] AS [End],
      [end].[Status] AS [EndingStatus]
    FROM [Alerts] [start],  [Alerts] [end]
    WHERE [start].[Status] = 0
      AND [start].[Timestamp] < [end].[Timestamp]
      AND [start].[Status] <> [end].[Status]
  ) AS [sub]
  GROUP BY
    [sub].[Start],
    [sub].[StartingStatus]
) AS [main]
GROUP BY
  [main].[End]



It give output:
OUTPUT

I want output that count until last row, if 0 is the last row it gives me Interval=NULL ,
My problem it doesn't count until last row where 0 there



My desired result:

 ---------STOP_BEGIN-----         --------STOP_END--------- ----INTERVAL_SECOND-----<br />
January, 01 2013 00:00:20+0000  January, 01 2013 00:00:25+0000      5
January, 01 2013 00:00:40+0000           NULL                       NULL

Upvotes: 1

Views: 73

Answers (1)

cha
cha

Reputation: 10411

here is your query (I have added it to the SQL Fiddle to the bottom of yours)

SELECT
  MIN ([main].[Start]) AS [STOP_Begin],
  [main].[End] AS [STOP_End],
  DATEDIFF(s, MIN([main].[Start]), [main].[End]) AS [Interval_Second]
FROM
(
  SELECT
    [starts].[Start],
    MIN([ends].[Timestamp]) AS [End]
  FROM
  (
    SELECT
      [Timestamp] AS [Start]
    FROM [Alerts]
    WHERE [Status] = 0
  ) AS [starts] LEFT JOIN [Alerts] AS [ends]
  ON  [starts].[Start] < [ends].[Timestamp]
  AND [ends].[Status] <> 0
  GROUP BY
    [starts].[Start]
) AS [main]
GROUP BY
  [main].[End]
ORDER BY 1

Upvotes: 1

Related Questions