OK, got this procedure I am trying to get to work, and I am trying to get the procedure to pivot data, but only when the day columns have a value for the product.. I thought about putting it all into a temp table, which I can do for everything except the Total... Ideally I want it in a temp table as I still have some jiggery pokery to do with the data after it has been pivoted. SO I ask the SQL guru's that reside in the dark here to come forth and assist me in getting the Totals column into the temp table...
here is the code so far.
here is the code so far.
Code:
ALTER procedure POD_PIVOT
@date_from char (10)
as
--create temporary table to load pivot data into
create table #pod (
TMPprcdescription char (100),
TMPaccname char (40),
TMPordcustordno char (40),
TMPsun int,
TMPmon int,
TMPtue int,
TMPwed int,
TMPthu int,
TMPfri int,
TMPsat int,
TMPtot int)
set dateformat dmy
--set declarations
DECLARE @date_sun smalldatetime,
@date_mon smalldatetime,
@date_tue smalldatetime,
@date_wed smalldatetime,
@date_thu smalldatetime,
@date_fri smalldatetime,
@date_sat smalldatetime,
@date_total smalldatetime
-- set days of the week according to date entered
select @date_sun = @date_from
select @date_mon = dateadd (day,1,@date_from)
Select @date_tue = dateadd (day,2,@date_from)
Select @date_wed = dateadd (day,3,@date_from)
Select @date_thu = dateadd (day,4,@date_from)
Select @date_fri = dateadd (day,5,@date_from)
Select @date_sat = dateadd (day,6,@date_from)
begin transaction
--this is the pivot select statement
Insert #pod
select distinct P1.*,(P1.sun + P1.mon + p1.tue + p1.wed + p1.thu + p1.fri + p1.sat) as tmptot
FROM (select prcdescription,Accname,ordcustordno,
max(CASE dlvdeldate when @date_sun then delqty else null end) as 'Sun',
max(CASE dlvdeldate when @date_mon then delqty else null end) as 'Mon',
max(CASE dlvdeldate when @date_tue then delqty else null end) as 'Tue',
max(CASE dlvdeldate when @date_wed then delqty else null end) as 'Wed',
max(CASE dlvdeldate when @date_thu then delqty else null end) as 'Thu',
max(CASE dlvdeldate when @date_fri then delqty else null end) as 'Fri',
max(CASE dlvdeldate when @date_sat then delqty else null end) as 'Sat',
from ewt_orders as P
WHERE delqty IS NOT NULL
group by p.Prcdescription,accname,ordcustordno ) as P1
select *
from #pod
commit;