Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Daily Sum, Month To Date, MTD Avg, Prev Month Avg

Author  Topic 

proxxycon
Starting Member

2 Posts

Posted - 2014-10-15 : 21:33:01
Hi,
Please help me in a problem related to SUM, AVG, MTD SUM about finding Oil Volumes flow through meters in Facilities.

Say, there are two Facilities
1. NORTH - having meters N1, N2
2. SOUTH - having meters S1, S2

There is a daily flow through the meters. Table FACILITY_OIL looks like following -

TimeStamp-----Facility-----Meter-----OilVolume
10-SEP-2014 | NORTH | N1 | 100
10-SEP-2014 | NORTH | N2 | 200
10-SEP-2014 | SOUTH | S1 | 300
10-SEP-2014 | SOUTH | S2 | 400

10-OCT-2014 | NORTH | N1 | 100
10-OCT-2014 | NORTH | N2 | 200
10-OCT-2014 | SOUTH | S1 | 300
10-OCT-2014 | SOUTH | S2 | 400

11-OCT-2014 | NORTH | N1 | 400
11-OCT-2014 | NORTH | N2 | 300
11-OCT-2014 | SOUTH | S1 | 200
11-OCT-2014 | SOUTH | S2 | 100

12-OCT-2014 | NORTH | N1 | 100
12-OCT-2014 | NORTH | N2 | 200
12-OCT-2014 | SOUTH | S1 | 300
12-OCT-2014 | SOUTH | S2 | 400
---
---
---
Daily Transactions

I have to find following grouped by facility:
1. Sum of Oil Volume through all Meters in a Facility on a given day.
2. Month to Date sum of Oil Volume through meters.
3. Average of MTD Oil Volume
4. Previous Month Average of Oil Volume

Output should look like following on 12-OCT :

Facility--DailySum--MTD_Sum--MTD_Avg--Prev_Month_Avg
NORTH | 300 | 1300 | 108.33 | 10
SOUTH | 700 | 1700 | 141.66 | 23.33

Here for NORTH on 12-OCT,
DailySum = (N1+N2)
MTD SUM = (N1 + N2) from 01-Oct to 12-Oct
MTD_Avg = MTD_SUM/12days
Pre_Month_Avg = (100+200)/30

Is it possible to output in the same format. Output is needed on a daily basis for reporting. My limitations are I cant use SP. It has to be from a view.

I tried using group by but was not able to achieve it. Any help is much appreciated. Thanks a ton!

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-10-16 : 12:10:13
Try this:
select Facility
,sum(case when [TimeStamp]>=dateadd(dd,datediff(dd,0,getdate()),0) then OilVolume else 0 end) as DailySum
,sum(case when [TimeStamp]>=dateadd(mm,datediff(mm,0,getdate()),0) then OilVolume else 0 end) as MTD_Sum
,sum(case when [TimeStamp]>=dateadd(mm,datediff(mm,0,getdate()),0) then OilVolume else 0 end)/datepart(dd,getdate()) as MTD_Avg
,sum(case when [TimeStamp]>=dateadd(mm,datediff(mm,0,getdate()),0) then 0 else OilVolume end)/datepart(dd,dateadd(dd,datepart(dd,getdate())*-1,getdate())) as Prev_Month_Avg
from yourtable
where [TimeStamp]>=dateadd(mm,datediff(mm,0,getdate())-1,0)
group by Facility
I don't have access to my db at the moment, so haven't syntax checked it = there might be syntax bugs
Go to Top of Page

proxxycon
Starting Member

2 Posts

Posted - 2014-10-21 : 12:24:14
Thanks for your help!

It seems to work perfect for one day. I was able to use your query.

I added 'with rollup' to get the sum at the end. However, the requirement now changed to get the dataset for each of the day. So it should look like following: (values are not correct, but i hope you get the idea)

Timestamp--Facility--DailySum--MTD_Sum--MTD_Avg--Prev_Month_Avg
10-Oct | NORTH | 300 | 1300 | 108.33 | 10
10-Oct | SOUTH | 700 | 1700 | 141.66 | 23.33
10-Oct | TOTAL | 1000| 3000 | 249.99 | 33.33

11-Oct | NORTH | 700 | 1300 | 108.33 | 10
11-Oct | SOUTH | 300 | 1700 | 141.66 | 23.33
11-Oct | TOTAL | 1000| 3000 | 249.99 | 33.33

12-Oct | NORTH | 300 | 1300 | 108.33 | 10
12-Oct | SOUTH | 700 | 1700 | 141.66 | 23.33
12-Oct | TOTAL | 1000| 3000 | 249.99 | 33.33

I tried modified query from yours-

select
,Timestamp
,case when null then 'TOTAL' else Facility end as 'Facility' -- To put 'TOTAL' as value for rolled up row
,sum(case when [TimeStamp]>=dateadd(dd,datediff(dd,0,getdate()),0) then OilVolume else 0 end) as DailySum
,sum(case when [TimeStamp]>=dateadd(mm,datediff(mm,0,getdate()),0) then OilVolume else 0 end) as MTD_Sum
,sum(case when [TimeStamp]>=dateadd(mm,datediff(mm,0,getdate()),0) then OilVolume else 0 end)/datepart(dd,getdate()) as MTD_Avg
,sum(case when [TimeStamp]>=dateadd(mm,datediff(mm,0,getdate()),0) then 0 else OilVolume end)/datepart(dd,dateadd(dd,datepart(dd,getdate())*-1,getdate())) as Prev_Month_Avg
from FACILITY_OIL
where [TimeStamp]>=dateadd(mm,datediff(mm,0,getdate())-1,0)
group by Timestamp, Facility with rollup

It doesn't seem to work with simple group by on Timestamp. I am not sure how to get the repeating result for one day. I cannot use CURSOR or loop in a view. Please help with this. Thanks!
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-10-22 : 13:57:23
Try this:
select case
when grouping(cast([TimeStamp] as date))=1 then 'Total'
else substring(replace(convert(char(11),[TimeStamp],106),' ','-'),1,6)
end as [TimeStamp]
,case
when grouping(Facility)=1 then 'Total'
else Facility
end as Facility
,sum(case when [TimeStamp]>=dateadd(dd,datediff(dd,0,getdate()),0) then OilVolume else 0 end) as DailySum
,sum(case when [TimeStamp]>=dateadd(mm,datediff(mm,0,getdate()),0) then OilVolume else 0 end) as MTD_Sum
,sum(case when [TimeStamp]>=dateadd(mm,datediff(mm,0,getdate()),0) then OilVolume else 0 end)/datepart(dd,getdate()) as MTD_Avg
,sum(case when [TimeStamp]>=dateadd(mm,datediff(mm,0,getdate()),0) then 0 else OilVolume end)/datepart(dd,dateadd(dd,datepart(dd,getdate())*-1,getdate())) as Prev_Month_Avg
from FACILITY_OIL
where [TimeStamp]>=dateadd(mm,datediff(mm,0,getdate())-1,0)
group by cast([TimeStamp] as date)
,Facility with rollup
Go to Top of Page
   

- Advertisement -