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

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
1 Solution

Commented:
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

Founder/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
``````
0

Commented:
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

Commented:
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

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

Commented:
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

Commented:
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

Commented:
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 Commented:
Matthew, thank fix it.

Sam
0

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

Featured Post

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