Remove bias from the monthly totals

Question

In order to remove the bias introduced by the differences in the number of days in the months and years (in case of leap years), from monthly total comparisons of arbitrary quantities and assuming, for example, a table named My_Table with a datetime column named order_date, and an integer one named revenue, I use the following query to get unbiased monthly revenue totals:

-- sum revenue per month, divided by the number of days of that month 
-- and then multiplied by the average days of the that year's months 

SELECT datepart(mm,order_date) as my_month, sum(
round(convert(decimal, revenue) /
    CASE WHEN MONTH(order_date) = 2 THEN
CASE WHEN (YEAR(order_date) % 4 = 0 AND YEAR(order_date) % 100 != 0) OR 
          (YEAR(order_date) % 400  = 0) THEN 29 ELSE 28 END
     WHEN (MONTH(order_date)%8 + floor(MONTH(order_date)/8)) % 2 = 0 THEN 30 ELSE 31 END
    * CASE WHEN (YEAR(order_date) % 4 = 0 AND YEAR(order_date) % 100 != 0) OR 
                (YEAR(order_date) % 400  = 0) THEN 366 ELSE 365 END / 12 , 3)
) as monthly_unb_revenue 
FROM My_Table
group by datepart(mm,order_date)

I would like your opinions or other practices

Answer 1

Rolling your own calendar calculations can be hard. It's probably more reliable to use built-in functions.

For example, you can find the first day of any month like:

DATEADD(mm, DATEDIFF(m,0,DateColumn),0)

Doing a datediff between the two would get you the number of days for that month. You could do the same for years:

select 
    datediff(d,
        DATEADD(mm, DATEDIFF(mm,0,getdate()),0),
        DATEADD(mm, DATEDIFF(mm,0,getdate())+1,0)) 
        as NumOfDaysThisMonth,
    datediff(d,
        DATEADD(yy, DATEDIFF(yy,0,getdate()),0),
        DATEADD(yy, DATEDIFF(yy,0,getdate())+1,0)) 
        as NumOfDaysThisYear

This example works on SQL Server, hopefully there's an equivalent on Sybase :)

Answer 2

I am assuming you are using SQL Server since that seems to be the only system I know of with datepart().

In oracle and MySQL you have a LAST_DAY() function. I found a user defined function equivalent for SQL Server here:

CREATE FUNCTION [dbo].[ufn_GetLastDayOfMonth] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

    DECLARE @vOutputDate        DATETIME

    SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' + 
                       CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01'
    SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))

    RETURN @vOutputDate

END 
GO

You could add that function and then use it to simplify your query.