Link to home
Start Free TrialLog in
Avatar of edrz01
edrz01Flag for United States of America

asked on

Need query to sum data for each day of month

Sounds like a pivot (which I have not done yet).

I need a query which will sum device downtime per day for each day of the month.

Device    Day1    Day2   Day3   Day4 ........
ABCD       0           10        5         27  .......
LBDF        7             3        0          0   .......

Any ideas? I can provide more details if needed.
Avatar of Cluskitt
Cluskitt
Flag of Portugal image

You don't need to pivot that particular query. This would work just as well:

SELECT Device,
    SUM(CASE WHEN DAY(date) IS 1 THEN 1 ELSE 0 END) Day1,
    SUM(CASE WHEN DAY(date) IS 2 THEN 1 ELSE 0 END) Day2,
    SUM(CASE WHEN DAY(date) IS 3 THEN 1 ELSE 0 END) Day3,
etc...

A pivot is probably more efficient, though.
Avatar of Zberteoc
1. Do you have 31 days(columns) in that table?
2. Do you have a row for each month? If yes there should be a month/year column.
Avatar of edrz01

ASKER

Cluskitt, I am trying to adapt your suggestion to my query and see if I can get it to work...

Zberteoc, I would probably create a seperate report for each month so the days per month could vary.
My question was intended to find out more about your data structure. The info you gave is not enough.
If you're using a report per month, you can just add a WHERE date BETWEEN '20110801' AND '20110831'
Or even a variable from a field. If you do plan to use more than one month at a time, just add a column for month and/or year. Like:

SELECT Device, YEAR(date), MONTH(date), SUM.....
...
GROUP BY Device, YEAR(date), MONTH(date)
I would say that the query is well written and you can't really optimize it from the coding perspective. The only thing you want to make sure is that you have indexes on all the columns that are used in JOIN, GROUP BY and WHERE clauses. If 2 or more columns are used then composite indexes are in order. You could also use the database engine tuning advisor to help you with indexes:

Query > Analyze Query in Database Engine Tuning Advisor.
Ignore the above. Was meant for another question.
Avatar of edrz01

ASKER

This is my original query which produces results like:

----- query -----
--ORIGINAL
SELECT
    Nodes.Caption,  
convert(varchar(20),StartTime.EventTime,20) as [Event Time],
    DATEDIFF(Mi, StartTime.EventTime,
    (SELECT TOP 1
        EventTime
        FROM Events AS Endtime
        WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
            AND EndTime.NetObjectType = 'N'
            AND EndTime.NetworkNode = StartTime.NetworkNode
        ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE ((StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N')
)
ORDER BY Base, Nodes.Caption ASC, eventtime asc

---- end query ----

---- Results (partial) ------
DC6      2011-08-02 04:17:02      16
DC6      2011-08-07 21:12:23      1
BC1      2011-07-25 05:37:26      2
BC1      2011-07-25 06:39:48      3
BC1      2011-07-26 12:42:10      2
BC1      2011-07-26 17:22:07      8
BC1      2011-07-26 17:40:37      40
BC1      2011-07-27 05:02:46      4
BC1      2011-07-27 06:58:46      4
BC1      2011-07-27 17:30:45      13
BC1      2011-07-27 20:05:22      0
BC1      2011-07-28 15:51:33      9
BC1      2011-07-29 22:40:12      2
BC1      2011-07-30 11:26:37      18
BC1      2011-08-01 19:21:00      552
BC1      2011-08-03 04:12:12      8
BC1      2011-08-04 04:54:26      6
BC1      2011-08-04 05:04:46      17
BC1      2011-08-05 11:10:57      7
try this


CREATE TABLE table111(Device VARCHAR(25), Day1 int, Day2 int, Day3 int, Day4 int)
GO
-- Inserting Data into Table
INSERT INTO table111(Device, Day1, Day2, Day3, Day4)
VALUES('ABCD','0','10','5','27')
INSERT INTO table111(Device, Day1, Day2, Day3, Day4)
VALUES('AYHD','5','11','56','5')
INSERT INTO table111(Device, Day1, Day2, Day3, Day4)
VALUES('ATGD','0','14','0','7')
INSERT INTO table111(Device, Day1, Day2, Day3, Day4)
VALUES('AFFD','0','7','30','52')

SELECT 
Device, sum(day1) as [Day1], sum(Day2) as [Day2], 
        sum(day3) as [Day3], sum(Day4) as [Day4] 
from table111
group by Device, Day1, Day2, Day3, Day4

DROP TABLE table111
GO

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Cluskitt
Cluskitt
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of edrz01

ASKER

I'm getting an error
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.
I forgot to close the CASE.
THEN 1 ELSE 0 END)
Avatar of edrz01

ASKER

Sweet! Ok this seems to be working but I am either seeing a 1 or 2 and not the number of minutes.
Device         Day1            Day2            Day3             Day4            Day 31
DC6      0      1      0      0      0
BC1      1      0      1      2      0
BC2      1      1      2      1      0
SWT      1      1      1      2      0
EXTDMZ      2      0      1      2      0
INTSWT      1      1      1      2      0
I'm sorry, I misread the question. I didn't realize you wanted the sum of minutes. Easily fixed, though. Just replace the SUMS with:

SUM(CASE WHEN DAY(StartTime.EventTime)=1 THEN DATEDIFF(Mi, StartTime.EventTime,
    (SELECT TOP 1
        EventTime
        FROM Events AS Endtime
        WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
            AND EndTime.NetObjectType = 'N'
            AND EndTime.NetworkNode = StartTime.NetworkNode
        ORDER BY EndTime.EventTime)) ELSE 0 END) Day1,
...

Basically, replace the THEN 1 with your own result. I'm not sure how this would work on EndTimes spanning over 24h though.
Avatar of edrz01

ASKER

Almost there. I am getting this error
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

The beginning of my query (to test) now looks like

SELECT Nodes.Caption,
SUM(CASE WHEN DAY(StartTime.EventTime)=1 THEN
DATEDIFF(Mi, StartTime.EventTime,
    (SELECT TOP 1
        EventTime
        FROM Events AS Endtime
        WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
            AND EndTime.NetObjectType = 'N'
            AND EndTime.NetworkNode = StartTime.NetworkNode
        ORDER BY EndTime.EventTime)) ELSE 0 END) Day1,

SUM(CASE WHEN DAY(StartTime.EventTime)=1 THEN 1 ELSE 0 END) Day2,
SELECT Nodes.Caption,
    SUM(CASE WHEN DAY(StartTime.EventTime)=1 THEN DATEDIFF(Mi, Starttime.EventTime, Endtime.EventTime) ELSE 0) Day1,
...
    SUM(CASE WHEN DAY(StartTime.EventTime)=31 THEN DATEDIFF(Mi, Starttime.EventTime, Endtime.EventTime) ELSE 0) Day31
FROM Events StartTime
INNER JOIN Nodes
ON StartTime.NetworkNode = Nodes.NodeID
LEFT JOIN (SELECT TOP 1
        EventTime
        FROM Events AS Endtime
        WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
            AND EndTime.NetObjectType = 'N'
            AND EndTime.NetworkNode = StartTime.NetworkNode
        ORDER BY EndTime.EventTime) AS Endtime
WHERE StartTime.EventType = 1
    AND StartTime.NetObjectType = 'N'
    AND StartTime.EventTime BETWEEN '20110801' AND '20110831'
GROUP BY Nodes.Caption

I think this might work. I don't have SQL here to test it. You may have to wing it a bit. Basically, turn the subquery into part of the query, so you can reference it at will. You may have to add a check for nulls too, cause of the LEFT JOIN.
Avatar of edrz01

ASKER

Ok, maybe instead of trying to sum the outage minutes maybe just count the number of events per device per day. Would this be easier?
That was the query that returned 0, 1 and 2. I guess you didn't have more than 2 events on a single day for a device in that particular month. The SUM(CASE x THEN 1 ELSE 0) works basically like a COUNT (or maybe more like a COUNTIF). To get the actual SUM, you would have to replace the 1 with whatever you want to add. In this case, it's the product of a subquery, so you have to incorporate it into your query (you can't have an aggregate of a subquery or another aggregate function).
Avatar of edrz01

ASKER

I appreciate your time and effort on this. I can use what we worked on.

Thanks again.
Glad to help. If only I had SQL near me I could probably test and assist you better. But I'm on vacation, so I have to do it by heart. Anyway, I think you might have enough to test a bit and adapt to your uses. Good luck. :)
Avatar of edrz01

ASKER

I am very impressed you could do this from memory! Dang!

Thanks again.