CODEWITHSUNDEEP
sqlsql-server
Patthebug
Patthebug

Reputation: 4797

Select and group past data relative to a certain date

I have data in two tables ORDERS and training. Different kinds of training are provided to various customers. I would like to see what was the affect of these training on revenue for the customers involved. For achieving this, I would like to look at the revenue for the past 90 days and the future 90 days for each customer from the date of receiving the training. In other words, if a customer received a training on March 30 2014, I would like to look at the revenue from Jan 1 2014 till June 30 2014. I have come up with the following query which pretty much does the job:

select 
  o.custno,
  sum(ISNULL(a.revenue,0)) as Revenue,
  o.Date as TrainingDate,
  DATEADD(mm, DATEDIFF(mm, 0, a.created), 0) as RevenueMonth 
from ORDERS a,
  (select distinct custno, max(Date) as Date from Training group by custno) o
where a.custnum = o.custno
  and a.created between DATEADD(day, -90, o.Date) and DATEADD(day, 90, o.Date)
group by o.custno, o.Date, DATEADD(mm, DATEDIFF(mm, 0, a.created), 0)
order by o.custno

The sample output of this query looks something like this:

custno    Revenue    TrainingDate    RevenueMonth
0000100 159.20  2014-06-02 00:00:00.000 2014-03-01 00:00:00.000
0000100 199.00  2014-06-02 00:00:00.000 2014-04-01 00:00:00.000
0000100 79.60   2014-06-02 00:00:00.000 2014-05-01 00:00:00.000
0000100 29.85   2014-06-02 00:00:00.000 2014-06-01 00:00:00.000
0000100 79.60   2014-06-02 00:00:00.000 2014-07-01 00:00:00.000
0000100 99.50   2014-06-02 00:00:00.000 2014-08-01 00:00:00.000
0000250 437.65  2013-02-26 00:00:00.000 2012-11-01 00:00:00.000
0000250 4181.65 2013-02-26 00:00:00.000 2012-12-01 00:00:00.000
0000250 4146.80 2013-02-26 00:00:00.000 2013-01-01 00:00:00.000
0000250 6211.93 2013-02-26 00:00:00.000 2013-02-01 00:00:00.000
0000250 2199.72 2013-02-26 00:00:00.000 2013-03-01 00:00:00.000
0000250 4452.65 2013-02-26 00:00:00.000 2013-04-01 00:00:00.000

Desired output example:

If the training was provided on March 15 2014, for customer number 100, I’d want revenue data in the following format: 

CustNo        Revenue           TrainingDate                      RevenueMonth
100    <Some revenue figure>    March 15 2014        Dec 15 2013 – Jan 14 2014 (Past month 1)
100    <Some revenue figure>    March 15 2014        Jan 15 2014 – Feb 14 2014 (Past month 2)
100    <Some revenue figure>    March 15 2014        Feb 15 2014 – Mar 14 2014 (Past month 3)
100    <Some revenue figure>    March 15 2014        Mar 15 2014 – Apr 14 2014 (Future month 1)
100    <Some revenue figure>    March 15 2014        Apr 15 2014 – May 14 2014 (Future month 2)
100    <Some revenue figure>    March 15 2014        May 15 2014 – Jun 14 2014 (Future month 3)

Here, the RevenueMonth column doesn’t need to be in this format as long as it has the data relative to the training date. The ‘past’ and ‘future’ month references in the braces are only to explain the output, they need not be present in the output.

My query gets the data and groups the data by month. I would like the months to be grouped relative to the training date. For example - If the training was given on March 15, I would like the past month to be from Feb 15 till March 14, my query doesn't do that. I believe a little tweak in this query might just achieve what I'm after.

Any help with the query would be highly appreciated.

Upvotes: 0

Views: 153

Answers (1)

John Bollinger
John Bollinger

Reputation: 181008

Something along these lines may do what you want:

select 
  t.custno,
  sum(ISNULL(o.revenue,0)) as Revenue,
  t.maxDate as TrainingDate,
  ((DATEDIFF(day, TrainingDate, o.created) + 89) / 30) - 3 as DeltaMonth
from ORDERS o
  join (select custno, max([Date]) as maxDate from Training group by custno) t
    on o.custnum = t.custno
where o.created
  between DATEADD(day, -89, t.maxDate) and DATEADD(day, 90, t.maxDate)
group by t.custno, t.maxDate, DeltaMonth
order by t.custno

The general strategy is to compute a difference in months (or 30-day periods, really) from the training date, and group by that. This version uses from 89 days before to 90 days after the training, because if you run from -90 to +90 then you have one more day (the training day itself) than divides evenly into 30-day periods.

The query follows the general structure of the original, but there are several changes:

  • it computes DeltaMonth (from -3 to 2) as an index of 30-day periods relative to the training date, with the training date being the last day of DeltaMonth number -1.
  • it uses the DeltaMonth alias in the GROUP BY clause instead of repeating its formula. That provides better clarity, simplicity, and maintainability.
  • I changed the table aliases. I simply could not handle there being a table named "ORDERS" and an alias "o", with the latter not being associated with the former.
  • the query uses modern join syntax, for better clarity
  • the GROUP BY clause updated appropriately

Upvotes: 1

Related Questions