SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Pivot data into Temp Table help!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

boogybaz
Starting Member

8 Posts

Posted - 02/01/2007 :  05:20:42  Show Profile  Reply with Quote
Hi again, I have a problem, in the following SQL I am trying to get the pivoted data into a temporary table so that I can work the data some more to achieve the desired results. I can get all the columns to populate except the Totals(TMPtot) column, which just ends up blank.

Any suggestions?


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;






Thanks in advance :)

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 02/01/2007 :  06:04:54  Show Profile  Reply with Quote
it may be one of the day value is null. NULL + whatever = NULL

try use isnull()

isnull(P1.sun, 0) + isnull(P1.mon, 0) + isnull(p1.tue, 0) + ...


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30113 Posts

Posted - 02/01/2007 :  06:11:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Because the MAX for at least one day is NULL (ie, there is no order for that day.

Try this for instead!
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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

boogybaz
Starting Member

8 Posts

Posted - 02/01/2007 :  07:51:04  Show Profile  Reply with Quote
Thanks guys, I think i sorted it out before checking for replies.. I changed the NULL to '0' in the Max(case...) statement and it is working ok... Just need to sort out the asp.net 2 side :p
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30113 Posts

Posted - 02/01/2007 :  08:13:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
1) What do you think happens with the code you use in the first posting, if the @date_from is not a Sunday?
2) You only check @date_from and the next six days, but you select the entire table. No optimization there...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

boogybaz
Starting Member

8 Posts

Posted - 02/01/2007 :  10:23:06  Show Profile  Reply with Quote
Hi Peso..

I have had a play with your code and it does what I need, I was originally trying to do a +1,+2 day etc instead of using days of the week, might be just as useful. The report specification was to have the data start from a sunday,

1) No choice, it's taken from a drop down list. Probably not the best way to do it, but it works
2) Not sure what you mean....


Another thing Peso, how would you get totals at the bottom of recordset?


Edited by - boogybaz on 02/01/2007 10:34:09
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30113 Posts

Posted - 02/01/2007 :  11:14:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
1)
If for some reason a developer changes the routine for filling the drop down list with sundays, YOUR code will fail miserably too!
You have to do what you can to minimize the impact of other developer's mistakes.

2)
You only aggregate with cases for the current date and the next six days. But the table may have thousands of other days in it. Both before and after the @from_date.
Why would you like the select them also, if you do not want them aggregated?

With the suggestion I gave to you, I take care of both these scenarios;
1) Even if the date provided to the stored procedure is not a sunday, it aggregates correctly anyway! Sunday values are stored in column SUN, wednesdays values are stored in WED and so on, no matter what the "start date" (@from_date) is. With your original code, it migth happen that fridays values are stored in the TUE column if the start date is not a sunday.

2) If only seven consecutive days are wanted in the aggregation, there is no need to select from the entire table.
Add a WHERE filter to only select to date range you want! Don't select the entire table if not necessary.


3) The TOTAL is calculated with a CALCULATED COLUMN in the temporary table. Very easy and no additional code in the query is needed to get this sum.

I suggest you take a closer look at my suggestion and think hard what the code does.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 02/01/2007 11:19:19
Go to Top of Page

boogybaz
Starting Member

8 Posts

Posted - 02/01/2007 :  12:25:20  Show Profile  Reply with Quote
Thanks Peso, it's really appreciated.

Just looking into the day of the week scenario, It is unlikely that their requirement will change, but it is possible.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000