• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

Daily Total sql query

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
SamRunyon
Asked:
SamRunyon
1 Solution
 
pcelbaCommented:
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
 
Nathan RileyFounder/CTOCommented:
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

Open in new window

0
 
Patrick MatthewsCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
pcelbaCommented:
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
 
pcelbaCommented:
Of course, if results should be summed over all devices then all occureneces of MdbDeviceID should be removed from the above SELECT.
0
 
Patrick MatthewsCommented:
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
 
pcelbaCommented:
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
 
Patrick MatthewsCommented:
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
 
SamRunyonAuthor Commented:
Matthew, thank fix it.

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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now