Avatar of edrz01
edrz01
Flag 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.
Microsoft SQL Server

Avatar of undefined
Last Comment
edrz01

8/22/2022 - Mon
Cluskitt

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.
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.
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Zberteoc

My question was intended to find out more about your data structure. The info you gave is not enough.
Cluskitt

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)
Zberteoc

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Zberteoc

Ignore the above. Was meant for another question.
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
barlet

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Cluskitt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
edrz01

ASKER
I'm getting an error
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.
Cluskitt

I forgot to close the CASE.
THEN 1 ELSE 0 END)
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Cluskitt

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.
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,
Cluskitt

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
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?
Cluskitt

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).
edrz01

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

Thanks again.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Cluskitt

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. :)
edrz01

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

Thanks again.