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)