Rolling Report for Last 13 months

[csmcvey]

I am trying to create a result set that will tell me how many open orders & the sum of revenue for those orders in inventory I had on the first of the month, for the last 13 months. An open order for inventory purposes are orders that were opened in the last 90 days and not been closed. So for each month, I need to know what the open order inventory count was @ that point in time. I have a table now with open date, close date, order #, and order revenue

For example, if I ran the report today (7/31), I would be returned:

Month #OpenOrders #SumRevenue
June 102 $120,000
May 122 $150,000
and so forth, the key being that the measurements are for that point in time

I may need a pivot table to summarize the info @ the end, but creating the result set with all of the info it what's giving me a fit

 

[RyanEK]

Hi,

Can you provide some example data?

Ryan

 

[RDCushing]

Try this:

select case datepart(m,OpenDate)
when 1 then 'Jan'
when 2 then 'Feb'
when 3 then 'Mar'
when 4 then 'Apr'
when 5 then 'May'
when 6 then 'Jun'
when 7 then 'Jul'
when 8 then 'Aug'
when 9 then 'Sep'
when 10 then 'Oct'
when 11 then 'Nov'
when 12 then 'Dec'
else '*** Error ***'
end 'Month'
, count(OpenDate) 'NumOpenOrders'
, sum(RevAmount) 'SumOpenOrders'
from tsoOrders
where OpenDate >= (getdate() - 90)
and CloseDate is NULL
group by datepart(m,OpenDate)


Richard D. Cushing
Sr. Consultant
SYNERGISTIC SOFTWARE SOLUTION, LLC
A Wholly-owned subsidiary of BDO Seidman, LLP

 

[dGrouse]

First, make a permanent table, which I'll call PIV, which contains integers from, say, -1000 to +1000. Such a table comes in handy in many situations.

I take it that your current data looks something like this:
ORDERS
open_date datetime
close_date datetime
order_num int
order_revenue money

And, if you were to run the query on, say, July 17th, I take it that your result set should look like:

As_Of ct rev
June 1, 2008 14 $4000
May 1, 2008 17 $7500
April 1, 2008 13 $5000

The following query should work. It assumes that you want orders that were open as of the date-in-the-past.

select
d.m as_of
,sum(case when o.order_num is null then 0 else 1 end) ct
,sum(o.order_revenue) rev
from
(
select
dateadd(m,p.i,convert(datetime,convert(char(4),getdate(),12) + '01',12)) m
from
piv p
where
p.i between -13 and -1
) d left outer join orders o
on datediff(d,o.open_date,d.m) between 0 and 90
and IsNull(o.close_date,'12/31/9999') > d.m
group by
d.m
order by
d.m desc

Hope that works! Cheers -



Jeff Prenevost
BI Consultant
Ann Arbor, MI