asked on

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.

Pavel Celba

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

Nathan Riley

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

Patrick Matthews

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

Pavel Celba

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

Pavel Celba

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

Patrick Matthews

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

Pavel Celba

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

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SamRunyon

ASKER

Matthew, thank fix it.

Sam

Pavel Celba

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