Daily Sum, Month To Date, MTD Avg, Prev Month Avg

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

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!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources