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 2012 Forums
 Transact-SQL (2012)
 Need Help on SQL Pivot Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

geethachetan
Starting Member

India
5 Posts

Posted - 04/05/2013 :  10:00:48  Show Profile  Reply with Quote
Hello,
I have a SQL Table in the below structure and am trying to write a PIVOT Query to get the %contribution of Service Request Numbers by Warranty Type. Could anyone pls suggest how do i obtain this?

SR_Count FWEEK prod_DESC WarrantyStatus Orders
1 201402 XPS Desktops IW 1
1 201402 OptiPlex Desktops IW NULL
1 201402 Other Electronics IW NULL
3 201402 Personal Notebooks IW 3
6 201402 XPS Desktops OOW NULL
1 201402 Imaging IW NULL
5 201402 Tablets IW NULL
18 201402 XPS Desktops Unknown NULL
10 201402 Personal Desktops OOW NULL
221 201402 XPS Desktops IW NULL

Pivot Query that i have written is as below:

SELECT * FROM
( SELECT COUNT( [SERVICE REQUEST NUMBER]) SR_Count, FWEEK, prod_DESC,
[WarrantyStatus], SUM([ordercount]) AS Orders
FROM [dbo].[GM_Logs_Wk05] where fweek = '201402'
GROUP BY FWEEK, prod_DESC, [WarrantyStatus],[ordercount]
) AS A
PIVOT
(
sum([SR_Count]) FOR [WarrantyStatus] in
([IW], [OOW]
)
)as b

Output of the above pivot query is as below:

FWEEK prod_DESC Orders IW OOW
201402 Personal Desktops NULL 358 10
201402 Imaging NULL 1 NULL
201402 XPS Desktops 1 1 NULL
201402 OptiPlex Desktops NULL 1 NULL
201402 Other Electronics NULL 1 NULL
201402 Personal Notebooks NULL 1324 45
201402 Tablets NULL 5 NULL
201402 Personal Notebooks 3 3 NULL
201402 Personal Notebooks 1 NULL NULL
201402 XPS Notebooks NULL 231 15
201402 XPS Desktops NULL 221 6

Help I need:
1. How do I get just one row for one product (as for ex: Personal Notebooks is appearing 3times in the above output)
2. Instead of getting the count – is it possible to get % of the column total....as in if I draw a pivot on excel below is the output – just wondering how do I convert the value of count to % of total of the respective column

Below is the excel output..(which I do in the excel pivot value field settings – show value as % of column total) which is the similar output i want thru a SQL Pivot query or any other means to acheive this in PIVOT...this query output is what am finally intending to use as a dataset in my SSRS Report.

Prod_desc IW OOW
Personal Notebooks 61.76% 57.69%
Personal Desktops 16.67% 12.82%
XPS Notebooks 10.78% 19.23%
XPS Desktops 10.36% 7.69%
Tablets 0.23% 0.00%
Other Electronics 0.05% 0.00%
OptiPlex Desktops 0.05% 0.00%
Imaging 0.05% 0.00%

Please help.

Thanks

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 04/05/2013 :  15:10:17  Show Profile  Reply with Quote
Can you try this - if it does not work, can you post the data in consumable format (i.e., something that someone can copy and paste to their SSMS window and run. See here for how to do that http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx )
SELECT FWEEK,prod_DESC,
	SUM(IW) over(partition by prod_DESC)*100.0/SUM(IW) as IW,
	SUM(OOW) over(partition by prod_DESC)*100.0/SUM(OOW) as OOW FROM
( SELECT COUNT( [SERVICE REQUEST NUMBER]) SR_Count, FWEEK, prod_DESC,
[WarrantyStatus], SUM([ordercount]) AS Orders
FROM [dbo].[GM_Logs_Wk05] where fweek = '201402'
GROUP BY FWEEK, prod_DESC, [WarrantyStatus]--,[ordercount]
) AS A
PIVOT
(
sum([SR_Count]) FOR [WarrantyStatus] in
([IW], [OOW]
)
)as b
Go to Top of Page

geethachetan
Starting Member

India
5 Posts

Posted - 04/06/2013 :  03:56:25  Show Profile  Reply with Quote
Thanks for your response. The output is not yet close to what i am wanting. hence as you suggested, re-posting the question..as below :

Create table gm_table
(
SR_Num INT
, FWEEK INT
, prod_DESC VARCHAR(255)
, WarrantyStatus VARCHAR(255)
, Orders INT
)

INSERT INTO GM_TABLE (SR_Num, FWEEK, prod_DESC, WarrantyStatus, Orders) VALUES
(869977621,201404,'Personal Notebooks','IW',1),
(870120681,201404,'Personal Notebooks','IW',1),
(870903077,201405,'Personal Notebooks','IW',1),
(871100687,201405,'Personal Notebooks','IW',1),
(871117976,201404,'Personal Notebooks','IW',1),
(871117976,201404,'Personal Notebooks','IW',1),
(871328699,201404,'Personal Notebooks','IW',1),
(871494818,201404,'Latitude','IW',1),
(872106684,201404,'Personal Desktops','OOW',1),
(872246996,201405,'Imaging','OOW',1),
(872292272,201404,'Imaging','OOW',1),
(872303637,201405,'Personal Notebooks','OOW',1),
(872367703,201404,'Personal Notebooks','OOW',1),
(872430971,201404,'Personal Notebooks','OOW',1),
(872500235,201404,'Personal Desktops','OOW',1)

From the above table (gm_table) that i have created what i need is the count of SR_Num (%of row) by WarrantyType & SumofOrders

To acheive this i wrote a Pivot query which is not giving the desired result:

SELECT * FROM
( SELECT COUNT( [SR_NUM]) SR_Count, FWEEK, prod_DESC,
[WarrantyStatus], SUM([orders]) AS TotalOrders
FROM GM_TABLE where fweek = '201404'
GROUP BY FWEEK, prod_DESC, [WarrantyStatus],[orders]
) AS A
PIVOT
(
sum([SR_Count]) FOR [WarrantyStatus] in
([IW], [OOW]
)
)as b

Later, on the same dataset i tried the query suggested by you:

SELECT FWEEK,prod_DESC,
SUM(IW) over(partition by prod_DESC)*100.0/SUM(IW) as IW,
SUM(OOW) over(partition by prod_DESC)*100.0/SUM(OOW) as OOW,
Orders FROM
( SELECT COUNT( [SR_NUM]) SR_Count, FWEEK, prod_DESC,
[WarrantyStatus], SUM([orders]) AS Orders
FROM gm_table where fweek = '201404'
GROUP BY FWEEK, prod_DESC, [WarrantyStatus],[orders]
) AS A
PIVOT
(
sum([SR_Count]) FOR [WarrantyStatus] in
([IW], [OOW]
)
)as b
group by FWEEK,prod_DESC, b.iw, b.oow, b.orders

Output is still not what am looking at.

I need help in building a query which can show output as below (This is the desired output which i got from excel):

Fweek Prod_Desc IW - Count of SR_NUM OOW - Count of SR_NUM IW - Sum of Orders OOW - Sum of Orders
201404 Imaging 0.00% 100.00% 1
201404 Latitude 100.00% 0.00% 1
201404 Personal Desktops 0.00% 100.00% 2
201404 Personal Notebooks 71.43% 28.57% 5 2

sorry dont know how to post a table (output) in the correct table format..

Any help will be much appreciated. Thanks

Geetha
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/09/2013 :  01:32:14  Show Profile  Reply with Quote
sounds like this to me


SELECT FWEEK,prod_DESC,
SUM(CASE WHEN WarrantyStatus = 'OOW' THEN 1 ELSE 0 END)*1.0/SUM(1) AS CountOOW,
SUM(CASE WHEN WarrantyStatus <> 'OOW' THEN 1 ELSE 0 END)*1.0/SUM(1) AS CountOrders,
SUM(CASE WHEN WarrantyStatus = 'OOW' THEN 1 ELSE 0 END) AS SumOOW,
SUM(CASE WHEN WarrantyStatus <> 'OOW' THEN 1 ELSE 0 END) AS SumOrders
FROM gm_table 
GROUP BY FWEEK,prod_DESC


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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