Merging SQL Statements (mssql)

Question

I would like to merge these 2 statements to get 7 lines (one per day) and telling how many events and alarms I've got for each of those days. If there's nothing, I would like to get a "NULL" value for the each row (day).

The current statement works but "events" and "alarms" are on different lines and I don't get anything if there's no events...

I guess I may use the "WITH" clause but I'm a bit lost :S

SELECT 'events' as Type, 
CAST(extended_timestamp AS DATE) as DateField, 
count(*) as SumField 
FROM [dbauditor_repo].[dbo].[dbauditor_repo_events] 
WHERE extended_timestamp > (select DATEADD(day, DATEDIFF(day, 0, GETDATE())-7, 0)) 
GROUP BY CAST(extended_timestamp AS DATE) 
UNION 
SELECT 'alarms' as Type, 
CAST(extended_timestamp AS DATE) as DateField, count(*) as SumField 
FROM [dbauditor_repo].[dbo].[dbauditor_repo_events] 
WHERE extended_timestamp > (select DATEADD(day, DATEDIFF(day, 0, GETDATE())-7, 0)) 
AND returncode = 1 
GROUP BY CAST(extended_timestamp AS DATE) 
ORDER BY DateField

Thanks for your help !

you are asking put thi statement in CTE ???? – mohan111 Aug 06 '14 at 09:32 — mohan111, Aug 06 '14 at 09:32

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

Without knowing the schema behind the tables, and making a big assumption that returncode = 1 is what indicates an alarm:

SELECT IIF(returncode = 1, 'alarms', 'events') as [Type]
     , CAST(extended_timestamp AS DATE) as DateField
     , count(*) as SumField 
FROM [dbauditor_repo].[dbo].[dbauditor_repo_events] 
WHERE extended_timestamp > DATEADD(day, DATEDIFF(day, 0, GETDATE())-7, 0)
GROUP BY IIF(returncode = 1, 'alarms', 'events'), CAST(extended_timestamp AS DATE) 
ORDER BY DateField

Update

Apologies, it seems I didn't read your question properly. What you need is a combination of my original answer above with a PIVOT to flatten the resultset and get you your 3 columns.

The CTE is pretty much the same - its taking the dataset and calculating its Type. After this, we use PIVOT to place the count-per-day for each Type into its own column.

declare @events table (ID int, extended_timestamp datetime, returncode int)
insert into @events values (1, dateadd(day, -6, GETDATE()), 0), (2, dateadd(day, -6, GETDATE()), 1), (3, dateadd(day, -6, GETDATE()), 1), (4, dateadd(day, -5, GETDATE()), 1);

WITH CTE AS(
    SELECT IIF(returncode = 1, 'alarms', 'events') as [Type]
         , DATEDIFF(day, 0, extended_timestamp) as DayNumber
         , count(*) as SumField 
    FROM @events
    WHERE extended_timestamp >= DATEADD(day, DATEDIFF(day, 0, GETDATE())-6, 0)
    GROUP BY IIF(returncode = 1, 'alarms', 'events'), DATEDIFF(day, 0, extended_timestamp)
)
, CTE2 AS(
    select DayNumber, [events] + [alarms] as [events], [alarms]
    from
    (select SumField, DayNumber, [Type] from CTE) as _S
    PIVOT (
        SUM(SumField)
        FOR [Type] IN ([events], [alarms])
    ) as _P
)
select cast(DATEADD(day, n.N, 0) as Date) as DateField, [events], [alarms]
from dbo.Numbers n
     left outer join CTE2 on n.N = DayNumber
where n.N between DATEDIFF(day, 0, GETDATE()) - 6 and DATEDIFF(day, 0, GETDATE())
order by 1

This returns:

DateField  events      alarms
---------- ----------- -----------
2014-07-31 3           2
2014-08-01 1           1
2014-08-02 NULL        NULL
2014-08-03 NULL        NULL
2014-08-04 NULL        NULL
2014-08-05 NULL        NULL
2014-08-06 NULL        NULL

This also uses a Numbers table to get the '1 row per day regardless' requirement. Note that the query changed to use DayNumber, so that this join could be as clean as possible.

edited May 23 '17 at 12:05

Community

1
1

answered Aug 06 '14 at 09:36

chrisb

2,200
1
20
23

First, many thanks for your precious help ! All your assumption are correct. I'm indeed getting something identical to the first statement in a more optimized way, that's a very good point :-) However, the idea behind is to get 3 fields per row (day, events, alarms) with a NULL value possible for column events, alarms. Currently, I'm still getting 2 lines per day or even none if there are no entries. Any idea to get this ? – Phil Aug 06 '14 at 10:58
by the way, events includes every returncode, alarms, only those with returncode = 1 – Phil Aug 06 '14 at 11:02
Wahoo, that seems to be much better :-) But, where am I supposed to set the table name (dbauditor_repo_events) ? – Phil Aug 06 '14 at 12:00
Just substitute it for @events. I included the table variable so that it could be easily reproduced. – chrisb Aug 06 '14 at 12:10
Sorry Dude, I'm back again. I'm using the code you delivered before but I do notice that the events figure is not correct. An event for me includes everything (including alarms): Therefore, for the 2014-07-31, I should get 3 events and not 1... – Phil Aug 06 '14 at 12:55
Haha "delivered" :-) I've updated with a simple change to combine [events] and [alarms] in the PIVOT select. – chrisb Aug 06 '14 at 13:02
Made a drastic simplification to the SQL - see my other answer. – chrisb Aug 06 '14 at 13:13
Cool, thanks. A last thing he he The role of the dbo.Numbers table is to deliver a list of the latest 7 days (column DayNumber), right ? – Phil Aug 06 '14 at 13:32
I tried this DECLARE @numbers table (N datetime) DECLARE @i INT; SELECT @i = 0; SET NOCOUNT ON WHILE @i <= 7 BEGIN INSERT INTO @numbers(N) VALUES (DATEADD(day, DATEDIFF(day, 0, GETDATE())-(@i), 0)); SELECT @i = @i + 1; END; SELECT * from @numbers; But I'm getting Msg 8116, Level 16, State 1, Line 15 Argument data type datetime is invalid for argument 2 of dateadd function. – Phil Aug 06 '14 at 13:33
Re: numbers table. Use [this](http://stackoverflow.com/a/1394093/8262). Its a multi-purpose utility - doesn't take up a lot of storage but makes queries like this dead easy. – chrisb Aug 06 '14 at 13:36
Hum, the syntax is good for the Number table but I'm still getting a Msg 8116, Level 16, State 1, Line 4 Argument data type datetime is invalid for argument 2 of dateadd function. – Phil Aug 06 '14 at 13:41
Check you're taking the query across correctly - so long as my assumptions on datatypes in the @events table are correct theres no reason for you to see an error. – chrisb Aug 06 '14 at 13:52
In fact, the extended_timestamp field is a datetime2(7) – Phil Aug 06 '14 at 14:02
Just tried my second query (below) with datetime2(7) and still not seeing an error. – chrisb Aug 06 '14 at 14:06
You're right (I just screwed up). It works like a charm ! Thanks for your patience Chris :-) – Phil Aug 06 '14 at 14:15

score 0 · Answer 2 · answered Aug 06 '14 at 13:11

After the many revisions we've made following my misunderstanding the requirement, the thought occurred that the SQL in my answer above was really dumb. It gets the job done, but once its properly understood there is a much clearer version:

declare @events table (ID int, extended_timestamp datetime, returncode int)
insert into @events values (1, dateadd(day, -6, GETDATE()), 0), (2, dateadd(day, -6, GETDATE()), 1), (3, dateadd(day, -6, GETDATE()), 1), (4, dateadd(day, -5, GETDATE()), 1);

select cast(DATEADD(day, n.N, 0) as Date) as DateField
     , NULLIF(SUM(CASE WHEN returncode IS NOT NULL THEN 1 ELSE 0 END),0) as [events]
     , NULLIF(SUM(CASE WHEN returncode = 1 THEN 1 ELSE 0 END),0) as [alarms]
from dbo.Numbers n
     left outer join @events on n.N = DATEDIFF(day, 0, extended_timestamp)
where n.N between DATEDIFF(day, 0, GETDATE()) - 6 and DATEDIFF(day, 0, GETDATE())
group by n.N
order by 1

The results are identical:

DateField  events      alarms
---------- ----------- -----------
2014-07-31 3           2
2014-08-01 1           1
2014-08-02 NULL        NULL
2014-08-03 NULL        NULL
2014-08-04 NULL        NULL
2014-08-05 NULL        NULL
2014-08-06 NULL        NULL

Chris Werner · Answer 3 · 2014-08-06T14:08:31.577

Your original question was how to merge to statements or different sets of data.

The answer is that you need to find something to join them together by. UNION will merge the results as seperate rows as you found out.

I think this will do what you're looking for, this groups each seperate query by the DATE and then joins on the same DATE to display the result you're looking for.

select a.DateField, a.events, b.alarms FROM 
(select CAST(extended_timestamp AS DATE) as DateField, count(*) as [events]
   FROM [dbauditor_repo].[dbo].[dbauditor_repo_events] 
   WHERE extended_timestamp > DATEADD(day,-7, getdate()) 
   GROUP BY CAST(extended_timestamp AS DATE)
) a
,(select CAST(extended_timestamp AS DATE) as DateField, count(*) as [alarms]
   FROM [dbauditor_repo].[dbo].[dbauditor_repo_events] 
   WHERE extended_timestamp > DATEADD(day, -7, getdate()) AND returncode = 1 
   GROUP BY CAST(extended_timestamp AS DATE) 
) b
where a.DateField = b.DateField
ORDER BY a.DateField

But then again, after reviewing your statements, it looks like 'alarm' is just a type of event, so I think you can do this with one selection in your case:

SELECT DateField, max([events]) as [events], max([alarms]) as [alarms] FROM
(select CAST(extended_timestamp AS DATE) as DateField, count(*) as [events], 
       SUM(case when returncode = 1 as 1 else 0 end) as [alarms]
  FROM [dbauditor_repo].[dbo].[dbauditor_repo_events] 
  WHERE extended_timestamp > DATEADD(day, -7, getdate()) 
  GROUP BY CAST(extended_timestamp AS DATE)
UNION
select CAST(getdate() as DATE), NULL, NULL
UNION
select CAST(DATEADD(day, -1, getdate()) as DATE), NULL, NULL
UNION
select CAST(DATEADD(day, -2, getdate()) as DATE), NULL, NULL
UNION
select CAST(DATEADD(day, -3, getdate()) as DATE), NULL, NULL
UNION
select CAST(DATEADD(day, -4, getdate()) as DATE), NULL, NULL
UNION
select CAST(DATEADD(day, -5, getdate()) as DATE), NULL, NULL
UNION
select CAST(DATEADD(day, -6, getdate()) as DATE), NULL, NULL
) a
GROUP BY DateField
ORDER BY DateField

What you proposed before was perfect for me (Date, Event_number, Alarms_number) for the latest 7 days The question was just which exact statement you used for dbo.Numbers :-) — Phil, Aug 06 '14 at 13:53
You must not have any data in the last 7 days then. These are the same queries you provided in your question. — Chris Werner, Aug 06 '14 at 14:09

score 0 · Answer 4 · answered Aug 09 '14 at 11:19

Based on your current query, an event can also be an alarm when its returncode is 1, and so it would contribute to both counts.

This query would return both counts on the same line:

SELECT
  x.DateField,
  Events = COUNT(*),
  Alarms = COUNT(CASE e.returncode WHEN 1 THEN 1 END)
FROM
  [dbauditor_repo].[dbo].[dbauditor_repo_events] AS e
CROSS APPLY
  (SELECT CAST(e.extended_timestamp AS DATE)) AS x (DateField)
WHERE
  e.extended_timestamp > DATEADD(day, DATEDIFF(day, 0, GETDATE())-7, 0)
GROUP BY
  x.DateField
;

The Alarms expression uses conditional aggregation: it only counts rows when e.returncode matches 1, while Events is just "count all rows".

The only issue with the above query is that it would only return data for days represented in the table. To return a result set for an entire interval, as you want, you could use a calendar table like this:

SELECT
  DateField = c.Date,
  Events    = COUNT(*),
  Alarms    = COUNT(CASE returncode WHEN 1 THEN 1 END)
FROM
  [dbauditor_repo].[dbo].[dbauditor_repo_events] AS e
CROSS APPLY
  (SELECT CAST(e.extended_timestamp AS DATE)) AS x (DateField)
RIGHT JOIN
  dbo.Calendar AS c ON c.Date = x.DateField
                   AND e.extended_timestamp > DATEADD(day, DATEDIFF(day, 0, GETDATE())-7, 0)
GROUP BY
  c.Date
;

If you do not have a calendar table, it is easy to create it "on the fly" for a small period like 7 days, for instance using this method:

SELECT
  Date = DATEADD(DAY, DayNumber, StartingDate)
FROM
  (VALUES (0), (1), (2), (3), (4), (5), (6)) AS v (DayNumber)
CROSS APPLY
  (SELECT DATEADD(DAY, -7, CAST(GETDATE() AS date))) AS x (StartingDate)
;

So, combining that with the previous query, you would get something like this:

WITH Calendar AS (
  SELECT
    Date = DATEADD(DAY, DayNumber, StartingDate)
  FROM
    (VALUES (0), (1), (2), (3), (4), (5), (6)) AS v (DayNumber)
  CROSS APPLY
    (SELECT DATEADD(DAY, -7, CAST(GETDATE() AS date))) AS x (StartingDate)
)
SELECT
  DateField = c.Date,
  Events    = COUNT(*),
  Alarms    = COUNT(CASE returncode WHEN 1 THEN 1 END)
FROM
  [dbauditor_repo].[dbo].[dbauditor_repo_events] AS e
CROSS APPLY
  (SELECT CAST(e.extended_timestamp AS DATE)) AS x (DateField)
RIGHT JOIN
  Calendar AS c ON c.Date = x.DateField
               AND e.extended_timestamp > DATEADD(day, DATEDIFF(day, 0, GETDATE())-7, 0)
GROUP BY
  c.Date
;

Merging SQL Statements (mssql)

4 Answers4