Solved

Daily Total sql query

Posted on 2009-04-28
209 Views
Now I have my meters in place now I need a total for each day. here is the query I have.

SELECT     MdbDeviceID, Volume, Energy, DailyFlowDateTime
FROM         FlowDaily
WHERE     (MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244)) and (DailyFlowDateTime >= @StartDate) AND (DailyFlowDateTime <= @EndDate)
ORDER BY DailyFlowDateTime DESC

this is query is giving me the value for each meter. I need a total off all meters for each day.
by the guys thanks for the help.
0
Question by:SamRunyon

LVL 41

Expert Comment

SELECT     MdbDeviceID, SUM(Volume) VolumeTotal, SUM(Energy) EnergyTotal,
CONVERT(char(10), DailyFlowDateTime, 101) DateUS
FROM         FlowDaily
WHERE     (MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244)) and (DailyFlowDateTime >= @StartDate) AND (DailyFlowDateTime <= @EndDate)
GROUP BY MdbDeviceID, CONVERT(char(10), DailyFlowDateTime, 101), , CONVERT(char(10), DailyFlowDateTime, 112)
ORDER BY MdbDeviceID, CONVERT(char(10), DailyFlowDateTime, 112) DESC

0

LVL 11

Expert Comment

Try This:
``````SELECT     MdbDeviceID, sum(Volume), sum(Energy), DailyFlowDateTime

FROM         FlowDaily

WHERE     (MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244)) and (DailyFlowDateTime >= @StartDate) AND (DailyFlowDateTime <= @EndDate)

GROUP BY MdbDeviceID, DailyFlowDateTime

ORDER BY DailyFlowDateTime DESC
``````
0

LVL 92

Expert Comment

Assuming DailyFlowDateTime is stored always as "midnight"...

SELECT     DailyFlowDateTime, SUM(Volume) AS Volume, SUM(Energy) AS Energy
FROM         FlowDaily
WHERE     (MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244)) and (DailyFlowDateTime >= @StartDate) AND (DailyFlowDateTime <= @EndDate)
GROUP BY DailyFlowDateTime
ORDER BY DailyFlowDateTime DESC
0

LVL 41

Expert Comment

The @EndDate is interpreted as Datetime. If it contains just date part then the time pat is interpreted as 00:00:00 and such date is not included in results.
The two commas in GROUP BY part should be replaced by one comma only...
0

LVL 41

Expert Comment

Of course, if results should be summed over all devices then all occureneces of MdbDeviceID should be removed from the above SELECT.
0

LVL 92

Expert Comment

pcelba said:
>>Of course, if results should be summed over all devices then all occureneces of MdbDeviceID should be
>>removed from the above SELECT.

That's where I thought this was going, but I could well be mistaken :)
0

LVL 41

Expert Comment

That I am not sure also.  But I am almost 100% sure about Datetime column containing time part because the example seems to be some measurement collection from the field. Maybe I am mistaken :-)
0

LVL 92

Accepted Solution

ANd if there is a time portion...

SELECT     CONVERT(varchar, DailyFlowDateTime, 111) AS TheDate, SUM(Volume) AS Volume, SUM(Energy) AS Energy
FROM         FlowDaily
WHERE     (MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244)) and (DailyFlowDateTime >= @StartDate) AND (DailyFlowDateTime <= @EndDate)
GROUP BY CONVERT(varchar, DailyFlowDateTime, 111)
ORDER BY CONVERT(varchar, DailyFlowDateTime, 111) DESC
0

Author Comment

Matthew, thank fix it.

Sam
0

LVL 41

Expert Comment

It seems the problem was the Japanese date format which I've missed ... :-)
0

Featured Post

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.