CODEWITHSUNDEEP
sqlsql-servert-sqlaggregate-functions
Clifford Pabs-Garnon
Clifford Pabs-Garnon

Reputation: 97

Weekly Average (per day) for the previous month

Hello again good stack people. I am faced with the dilemma of getting the weekly average per day (for example, Monday_Avg, Tuesday_Avg…etc) for the previous month I have succeeded in getting the monthly average for the previous month with the following code. However, I do not have enough know how of date/time functions in sql to complete this task.

This works for me for getting monthly average. 

ALTER procedure [dbo].[usp_consumation]
@strMonth varchar(2),
@strYear varchar(4),
@strPrevMonth varchar(2),
@strPrevYear varchar(4)
as
--set @strMonth = '05'
--set @strYear = '2013'
--set @strPrevMonth = '04'
--set @strPrevYear = '2013'

declare @tbl_PrevMonthAverage table(cell varchar(25),average_d numeric(18,4))
drop table ##tempCIUnits
drop table ##tempCIUnitsTotal

declare @FieldName varchar(150)
declare @FieldAbv varchar(50)
declare @strSql varchar(8000)
declare @strFields varchar(5000)
declare @strFieldsSum varchar(5000)
declare @intCounter integer
\

--Get avarage past month
set @strFields = ' '
set @intCounter = 0
declare curStrSQLUnits cursor for 
            select name, right(name,4) name_abv  
            from sys.columns 
            where system_type_id = 108
            and object_id = isnull((select top 1 id from sites.dbo.sysobjects where name = 'tbl_Revenue_'+@strPrevYear+@strPrevMonth),0)
            and name like 'count_d_%'
            order by name asc
open curStrSQLUnits
fetch next from curStrSQLUnits into @FieldName,@FieldAbv
while @@fetch_status = 0
begin 
      set @intCounter = @intCounter + 1   
      set @strFields = @strFields + @FieldName + '+'
      fetch next from curStrSQLUnits into  @FieldName,@FieldAbv
end
close curStrSQLUnits
deallocate curStrSQLUnits
set @strFields = left(@strFields,len(@strFields)-1)
set @strSql = 'select [Cell], (' + @strFields + ')/'+CONVERT(varchar,@intCounter)+' as avg_previous_month '
set @strSql = @strSql + ' from sites.dbo.tbl_Revenue_'+@strPrevYear+@strPrevMonth + ' order by Cell asc'
insert into @tbl_PrevMonthAverage(cell,average_d) exec (@strSql)

Another method of solving the problem.

USE [Sites]
GO

declare @strPrevMonth varchar(2)
declare @strPrevYear varchar(4)

set @strPrevMonth = '07'
set @strPrevYear = '2013'

declare @FieldName varchar(150)
declare @FieldAbv varchar(50)
declare @strSql varchar(8000)
declare @strFieldsSum varchar(5000)

declare @intCounter integer
declare @strFields varchar(5000)
declare @intCounter_1 integer
declare @strFields_1 varchar(5000)
declare @intCounter_2 integer
declare @strFields_2 varchar(5000)
declare @intCounter_3 integer
declare @strFields_3 varchar(5000)
declare @intCounter_4 integer
declare @strFields_4 varchar(5000)
declare @intCounter_5 integer
declare @strFields_5 varchar(5000)
declare @intCounter_6 integer
declare @strFields_6 varchar(5000)
declare @intCounter_7 integer
declare @strFields_7 varchar(5000)

--Get avarage past month
set @strFields = ' '
set @intCounter = 0
set @strFields_1 = ' '
set @intCounter_1 = 0
set @strFields_2 = ' '
set @intCounter_2 = 0
set @strFields_3 = ' '
set @intCounter_3 = 0
set @strFields_4 = ' '
set @intCounter_4 = 0
set @strFields_5 = ' '
set @intCounter_5 = 0
set @strFields_6 = ' '
set @intCounter_6 = 0
set @strFields_7 = ' '
set @intCounter_7 = 0

declare curStrSQLUnits cursor for 
            select name, right(name,4) name_abv  
            from sys.columns 
            where system_type_id = 108
            and object_id = isnull((select top 1 id from sites.dbo.sysobjects where name = 'tbl_CellId_Revenue_'+@strPrevYear+@strPrevMonth),0)
            and name like 'minutes_d_%'
            order by name asc
open curStrSQLUnits

fetch next from curStrSQLUnits into @FieldName,@FieldAbv

while @@FETCH_STATUS = 0
begin 

      set @intCounter = @intCounter + 1   
      set @strFields = @strFields + @FieldName + '+'

        if (datepart(weekday,@strPrevMonth+'/'+RIGHT(@FieldName,2)+'/'+@strPrevYear)) = 1
        begin
            set @intCounter_1 = @intCounter_1 + 1   
            set @strFields_1 = @strFields_1 + @FieldName + '+'    
        end

        if (datepart(weekday,@strPrevMonth+'/'+RIGHT(@FieldName,2)+'/'+@strPrevYear)) = 2
        begin
            set @intCounter_2 = @intCounter_2 + 1   
            set @strFields_2 = @strFields_2 + @FieldName + '+'    
        end

        if (datepart(weekday,@strPrevMonth+'/'+RIGHT(@FieldName,2)+'/'+@strPrevYear)) = 3
        begin
            set @intCounter_3 = @intCounter_3 + 1   
            set @strFields_3 = @strFields_3 + @FieldName + '+'    
        end

        if (datepart(weekday,@strPrevMonth+'/'+RIGHT(@FieldName,2)+'/'+@strPrevYear)) = 4
        begin
            set @intCounter_4 = @intCounter_4 + 1   
            set @strFields_4 = @strFields_4 + @FieldName + '+'    
        end

        if (datepart(weekday,@strPrevMonth+'/'+RIGHT(@FieldName,2)+'/'+@strPrevYear)) = 5
        begin
            set @intCounter_5 = @intCounter_5 + 1   
            set @strFields_5 = @strFields_5 + @FieldName + '+'    
        end

        if (datepart(weekday,@strPrevMonth+'/'+RIGHT(@FieldName,2)+'/'+@strPrevYear)) = 6
        begin
            set @intCounter_6 = @intCounter_6 + 1   
            set @strFields_6 = @strFields_6 + @FieldName + '+'    
        end

        if (datepart(weekday,@strPrevMonth+'/'+RIGHT(@FieldName,2)+'/'+@strPrevYear)) = 7
        begin
            set @intCounter_7 = @intCounter_7 + 1   
            set @strFields_7 = @strFields_7 + @FieldName + '+'    
        end

      fetch next from curStrSQLUnits into @FieldName,@FieldAbv

end
close curStrSQLUnits
deallocate curStrSQLUnits
set @strFields = left(@strFields,len(@strFields)-1)
set @strFields_1 = left(@strFields_1,len(@strFields_1)-1)
set @strFields_2 = left(@strFields_2,len(@strFields_2)-1)
set @strFields_3 = left(@strFields_3,len(@strFields_3)-1)
set @strFields_4 = left(@strFields_4,len(@strFields_4)-1)
set @strFields_5 = left(@strFields_5,len(@strFields_5)-1)
set @strFields_6 = left(@strFields_6,len(@strFields_6)-1)
set @strFields_7 = left(@strFields_7,len(@strFields_7)-1)


set @strSql = 'select isnull((' + @strFields_1 + '),0)/'+CONVERT(varchar,@intCounter_1)+' as Sun_Avg_PrevMnth '
set @strSql = @strSql + ', isnull((' + @strFields_2 + '),0)/'+CONVERT(varchar,@intCounter_2)+' as Mon_Avg_PrevMnth ' 
set @strSql = @strSql + ', isnull((' + @strFields_3 + '),0)/'+CONVERT(varchar,@intCounter_3)+' as Tue_Avg_PrevMnth '
set @strSql = @strSql + ', isnull((' + @strFields_4 + '),0)/'+CONVERT(varchar,@intCounter_4)+' as Wed_Avg_PrevMnth '
set @strSql = @strSql + ', isnull((' + @strFields_5 + '),0)/'+CONVERT(varchar,@intCounter_5)+' as Thu_Avg_PrevMnth '
set @strSql = @strSql + ', isnull((' + @strFields_6 + '),0)/'+CONVERT(varchar,@intCounter_6)+' as Fri_Avg_PrevMnth '
set @strSql = @strSql + ', isnull((' + @strFields_7 + '),0)/'+CONVERT(varchar,@intCounter_7)+' as Sat_Avg_PrevMnth '
set @strSql = @strSql + ' from sites.dbo.tbl_Revenue_'+@strPrevYear+@strPrevMonth + ' order by CellId asc'
exec (@strSql)

Upvotes: 0

Views: 173

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280423

I'm going to answer what I think you mean by "Weekly Average (per day)" with a very simple example, and then let you work out how you need to incorporate it into this massive stored procedure with all sorts of code that seems irrelevant to the actual problem you're trying to solve. I could go on and on about how you're incorrectly using global temp tables or questioning why you need a table variable or doubting the sanity of tables named tbl_Revenue_201307, but again, they don't seem like they have anything to do with the problem you have:

getting the weekly average per day (for example, Monday_Avg, Tuesday_Avg…etc) for the previous month ...
I do not have enough know how of date/time functions in sql to complete this task.

Let's say you have a table with four columns, one datetime and three decimal(18,2):

CREATE TABLE dbo.tbl_Revenue_201307
(
  d DATETIME,
  val1 DECIMAL(18,2),
  val2 DECIMAL(18,2),
  val3 DECIMAL(18,2)
);

Some sample data:

INSERT dbo.tbl_Revenue_201307 VALUES
('20130701',12,15,18),('20130701',13,14,15),('20130702',5,1,5), 
('20130703',12,15,18),('20130703',13,9,13),('20130703',5,1,5),  
('20130704',12,15,18),('20130704',13,1,12),('20130705',5,1,5),  
('20130705',12,15,18),('20130706',13,11,16),('20130714',5,1,5);

You want to look at all of the data for that month, and average "it" ("it" meaning the sum of val1+val2+val3) per weekday. So, one value that represents the average over all Sundays in the month, one value that represents the average over all Mondays, etc. for a total of 7 individual values. Two queries that can do that (depending on whether you want the data in rows or columns):

SELECT
  wd = DATEPART(WEEKDAY, date_col),
  dn = LEFT(DATENAME(WEEKDAY, date_col), 3) + '_Avg', 
  [avg] = AVG(val1 + val2 + val3)
FROM dbo.tbl_Revenue_201307
GROUP BY DATENAME(WEEKDAY, date_col), DATEPART(WEEKDAY, date_col)
ORDER BY wd;

SELECT * FROM 
(
  SELECT 
    col = LEFT(DATENAME(WEEKDAY, date_col),3) + '_Avg',
    val = AVG(val1 + val2 + val3)
  FROM dbo.tbl_Revenue_201307
  GROUP BY LEFT(DATENAME(WEEKDAY, date_col),3)
) AS p
PIVOT (MAX(val) FOR col IN 
  ([Sun_Avg],[Mon_Avg],[Tue_Avg],[Wed_Avg],[Thu_Avg],[Fri_Avg],[Sat_Avg])) AS d;

Results:

wd   dn        avg
--   -------   ---------
1    Sun_Avg   11.000000
2    Mon_Avg   43.500000
3    Tue_Avg   11.000000
4    Wed_Avg   30.333333
5    Thu_Avg   35.500000
6    Fri_Avg   28.000000
7    Sat_Avg   40.000000

Sun_Avg     Mon_Avg     Tue_Avg     Wed_Avg     Thu_Avg     Fri_Avg     Sat_Avg
---------   ---------   ---------   ---------   ---------   ---------   ---------
11.000000   43.500000   11.000000   30.333333   35.500000   28.000000   40.000000

Upvotes: 1

Related Questions