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;

ALTER PROCEDURE	POD_PIVOT
(
    @date_from DATETIME
)
AS

SET NOCOUNT ON

SELECT	@date_from = DATEADD(day, DATEDIFF(day, 0, @date_From), 0)

-- Create temporary table to load pivot data into
CREATE TABLE	#Pod
		(
		    PrcDescription VARCHAR(100),
		    AccName VARCHAR(40),
		    OrdCustOrdNo VARCHAR(40),
		    Sun INT,
		    Mon INT,
		    Tue INT,
		    Wed INT,
		    Thu INT,
		    Fri INT,
		    Sat INT,
		    Tot AS ISNULL(Sun, 0) + ISNULL(Mon, 0) + ISNULL(Tue, 0) + ISNULL(Wed, 0) + ISNULL(Thu, 0) + ISNULL(Fri, 0) + ISNULL(Sat, 0)
		)

-- This is the pivot select statement
INSERT	    #Pod
	    (
		PrcDescription,
		AccName,
		OrdCustOrdNo,
		Sun,
		Mon,
		Tue,
		Wed,
		Thu,
		Fri,
		Sat
	    )
SELECT	    PrcDescription,
	    AccName,
	    OrdCustOrdNo,
	    MAX(CASE WHEN DATEPART(dw, DlvDelDate) = 1 THEN DelQty end),
	    MAX(CASE WHEN DATEPART(dw, DlvDelDate) = 2 THEN DelQty end),
	    MAX(CASE WHEN DATEPART(dw, DlvDelDate) = 3 THEN DelQty end),
	    MAX(CASE WHEN DATEPART(dw, DlvDelDate) = 4 THEN DelQty end),
	    MAX(CASE WHEN DATEPART(dw, DlvDelDate) = 5 THEN DelQty end),
	    MAX(CASE WHEN DATEPART(dw, DlvDelDate) = 6 THEN DelQty end),
	    MAX(CASE WHEN DATEPART(dw, DlvDelDate) = 7 THEN DelQty end)
FROM	    ewt_orders
WHERE	    DlvDelDate >= @date_from
	    AND DlvDelDate < DATEADD(day, 7, @date_from)
	    AND DelQty IS NOT NULL
GROUP BY    PrcDescription,
	    AccName,
	    OrdCustOrdNo

SELECT	*
FROM	#Pod

DROP TABLE #Pod