CODEWITHSUNDEEP
sqlsql-servert-sql
codingsnake99
codingsnake99

Reputation: 146

problem of calculating month to date revenue for each department in sql

I queried one data frame like this:

Select 
Date, 
Department, 
cumu_revenue_yr, 
LAG(cumu_revenue_yr,1) OVER (
                PARTITION BY Department
                ORDER BY Date) as previous_cumu_revenue_yr,
(cumu_revenue_yr - previous_cumu_revenue_yr) as 1day_revenue
from dmf_dbo.revenue

enter image description here

cumu_revenue_yr is the cumulative revenues from first day of year to the current day.

However, if I want to have one additional column to show how much I earn from beginning of each month until now (current cumu_revenue_yr - previous month-end cumu_revenue_yr), how can I do that?

I try to do something like:

Select 
Date, 
Department, 
cumu_revenue_yr, 
LAG(cumu_revenue_yr, 1) OVER (
                PARTITION BY Department
                ORDER BY Date) as previous_cumu_revenue_yr,
(cumu_revenue_yr - previous_cumu_revenue_yr) as 1day_revenue,
LAG(cumu_revenue_yr, Day(Date)) OVER (
                PARTITION BY Department
                ORDER BY Date) as previous_monthend_cumu_revenue,
(cumu_revenue_yr - previous_monthend_cumu_revenue) as whole_month_revenue,,
from dmf_dbo.revenue

But there are two problems:

  1. LAG() only accept positive int in second argument.
  2. The time series is business day. In other words, there will be no data records in weekend or holiday. So, using LAG(cumu_revenue_yr, Day(Date)) cannot help me extract the correct data.

Anyone has any suggestion? The final data frame should look like

enter image description here

Upvotes: 0

Views: 495

Answers (2)

SteveC
SteveC

Reputation: 6015

Here are 2 queries. The first query is how I think the first set of data was returned. The second query adds the monthly running cumulative total of 1day_revenue

/* Query */
Select [Date], Department, cumu_revenue_yr, 
       (cumu_revenue_yr - LAG(cumu_revenue_yr, 1) OVER (
                              PARTITION BY Department
                              ORDER BY Date)) as [1day_revenue]
from dmf_dbo.revenue;

[EDIT] The LAG function runs in a CTE. The CTE is then accessed to calculate the SUM OVER the year, month partitioning.

/* Query with SUM() OVER */
;with lag_cte([Date], Department, cumu_revenue_yr, [1day_revenue]) as (
    Select [Date], Department, cumu_revenue_yr, 
           (cumu_revenue_yr - LAG(cumu_revenue_yr, 1) OVER (
                                  PARTITION BY Department
                                  ORDER BY Date))
    from dmf_dbo.revenue)
Select *, sum(cumu_revenue_yr-[1day_revenue]) over 
             (partition by year([Date]), month([Date])
              order by [Date]) as cumu_revenue_month
from lag_cte;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You can use apply for this:

select r2.*, r.cumu_revenue_yr - coalesce(r2.cumu_revenue_yr, 0) as mtd
from dmf_dbo.revenue r outer apply
     (select top (1) r2.*
      from dmf_dbo.revenue r2
      where r2.department = r.department and
            year(r2.date) = year(r.date) and
            r2.date < datefromparts(year(r.date), month(r.date), 1)
      order by r2.date desc
     ) r2

Upvotes: 0

Related Questions