Introduction
This post continues on from a post earlier today where I was asked to display useage figures by month / item, see here for full details http://www.jordanfey.co.uk/?p=391 …… This time they wanted to see all sales, but also be able to see it for all customers or just one.
I wont go into the details too much, as I explained it in my previous post, but I am using the syteline tables co and co_item and dividing the sales up by due_date – I could of done it via the packing tables, but for business reasons chose not to do this!
The Code
declare @startdate asdate=‘2010-01-01’
declare @enddate asdate=getdate()
declare @custnum as custnumtype =‘%’
declare @periods asnvarchar(max)=(selectdistinct
‘[‘+cast(DATEPART(year,due_date)asnvarchar(4))+‘/’+right(’00’+cast(DATEPART(mm,due_date)asnvarchar(2)),2)+‘],’
from coitem as coi
where
coi.due_date between @startdate and @enddate
orderby‘[‘+cast(DATEPART(year,due_date)asnvarchar(4))+‘/’+right(’00’+cast(DATEPART(mm,due_date)asnvarchar(2)),2)+‘],’
FORXMLPATH (”))
declare @periodstrimed asnvarchar(max)=left(@periods,len(@periods)-1)
select @periodstrimed;
declare @select1 asnvarchar(4000)=‘
with pivotdata as
(select
cast(DATEPART(year,due_date) as nvarchar(4)) + ”/” + right(”00” + cast(DATEPART(mm,due_date) as nvarchar(2)),2) DatePeriod
, item as item
, qty_ordered as qty
from coitem as coi
left join co on coi.co_num = co.co_num
where co.cust_num like ”’
declare @select1a asnvarchar(4000)=”’)
select
item,’
declare @select2 asnvarchar(4000)=
‘from pivotdata
pivot (sum(qty)
for dateperiod in (‘
declare @select3 asnvarchar(4000)=
‘))as p order by item asc ‘
select (@select1 + @custnum + @select1a + @periodstrimed + @select2 + @periodstrimed + @select3)
exec (@select1 + @custnum + @select1a + @periodstrimed + @select2 + @periodstrimed + @select3)