April 5, 2013 at 8:05 am
Hi,
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_CountFWEEKprod_DESCWarrantyStatusOrders
1201402XPS Desktops IW1
1201402OptiPlex Desktops IWNULL
1201402Other Electronics IWNULL
3201402Personal Notebooks IW3
6201402XPS Desktops OOWNULL
1201402Imaging IWNULL
5201402Tablets IWNULL
18201402XPS Desktops UnknownNULL
10201402Personal Desktops OOWNULL
221201402XPS Desktops IWNULL
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:
FWEEKprod_DESCOrdersIWOOW
201402Personal DesktopsNULL35810
201402Imaging NULL1NULL
201402XPS Desktops11NULL
201402OptiPlex DesktopsNULL1NULL
201402Other ElectronicsNULL1NULL
201402Personal NotebooksNULL132445
201402Tablets NULL5NULL
201402Personal Notebooks 33NULL
201402Personal Notebooks 1NULLNULL
201402XPS NotebooksNULL23115
201402XPS DesktopsNULL2216
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 for 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_descIWOOW
Personal Notebooks61.76%57.69%
Personal Desktops16.67%12.82%
XPS Notebooks10.78%19.23%
XPS Desktops10.36%7.69%
Tablets 0.23%0.00%
Other Electronics0.05%0.00%
OptiPlex Desktops0.05%0.00%
Imaging 0.05%0.00%
Grand Total100.00%100.00%
Please help.
thanks
April 5, 2013 at 9:28 am
please read this post (http://www.sqlservercentral.com/articles/SQLServerCentral/66909/) and then reply to this thread with a create table statement, insert statements with your sample data and then formatted output of the desired output. Then we can help you get what you want for this request.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 6, 2013 at 2:11 am
Hello,
Thanks for sharing the URL which helps understand how to post the question.
I have rewrriten the question in a word document and has been attached as an attachment name pivotquery.docx
Please review and hope you help find me a solution.
Regards
April 6, 2013 at 5:52 am
Please put the stuff up as a TEXT document. I don't download files that can run code. :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 6, 2013 at 10:00 am
geetha.jasmine (4/6/2013)
Hello,Thanks for sharing the URL which helps understand how to post the question.
I have rewrriten the question in a word document and has been attached as an attachment name pivotquery.docx
Please review and hope you help find me a solution.
Regards
a word document is not what is required...please reread the URL posted.
that said...does the following code give you some ideas...
SELECT FWEEK, prod_DESC,
cast (100 * SUM(CASE WHEN WarrantyStatus = 'IW' THEN orders ELSE 0 END) / SUM(Orders) as decimal(9,2)) as IWpercent,
cast (100 * SUM(CASE WHEN WarrantyStatus = 'OOW' THEN orders ELSE 0 END) / SUM(Orders) as decimal(9,2)) as OOWpercent,
cast (SUM(CASE WHEN WarrantyStatus = 'IW' THEN orders ELSE 0 END) as INT) AS IW,
cast (SUM(CASE WHEN WarrantyStatus = 'OOW' THEN orders ELSE 0 END) as INT) AS OOW
FROM gm_table
GROUP BY FWEEK, prod_DESC
HAVING (FWEEK = 201404)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 7, 2013 at 9:02 am
TheSQLGuru (4/6/2013)
Please put the stuff up as a TEXT document. I don't download files that can run code. :hehe:
Word's .docx files cannot run code, as far as I know - that's what .docm files are for.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 7, 2013 at 4:15 pm
Phil Parkin (4/7/2013)
TheSQLGuru (4/6/2013)
Please put the stuff up as a TEXT document. I don't download files that can run code. :hehe:Word's .docx files cannot run code, as far as I know - that's what .docm files are for.
Learn all kinds of things on SSC.com!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 8, 2013 at 2:26 pm
Hi geetha jasmine,
I would not use a pivot since that is used to turn row values into column aggregations such as sums.
Use two common table expressions, a full join, and a bunch of calculations and conversions.
Solution is below creates the following output.
I assume you have a existing database named [test] to play with the solution in.
Sincerely
John
--
-- Results
--
my_fweekmy_prod_desciw_totaliw_pctoow_totaloow_pctgrand_total
201404Imaging0011001
201404Latitude1100001
201404Personal Desktops0021002
201404Personal Notebooks571.4285714285714228.57142857142867
201405Imaging0011001
201405Personal Notebooks266.6666666666667133.33333333333333
--
-- Solution
--
-- Use the test database
use test;
go
-- Create the table
Create table gm_table
(
SR_Num INT
, FWEEK INT
, prod_DESC VARCHAR(255)
, WarrantyStatus VARCHAR(255)
, Orders INT
);
go
-- Add data to the table
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);
go
-- Peek at the data
Select * from GM_TABLE
order by FWEEK, prod_DESC, WarrantyStatus
go
--
-- Solve business problem, two cte & full join
--
;
with cte_iw_data
as
(
select fweek, prod_desc, warrantystatus, sum(orders) as iw_total
from gm_table where warrantystatus = 'IW'
group by fweek, prod_desc, warrantystatus
),
cte_oow_data
as
(
select fweek, prod_desc, warrantystatus, sum(orders) as oow_total
from gm_table where warrantystatus = 'OOW'
group by fweek, prod_desc, warrantystatus
)
select
-- pick first non-null week
case
when i.FWEEK is null then o.FWEEK
else i.FWEEK
end as my_fweek,
-- pick first non-null prod desc
case
when i.prod_DESC is null then o.prod_DESC
else i.prod_DESC
end as my_prod_desc,
-- iw total
isnull(iw_total, 0) as iw_total,
-- make iw pct
case
when isnull(iw_total, 0) + isnull(oow_total, 0) = 0 then convert(float, 0)
else
convert(float, isnull(iw_total, 0)) / convert(float, ( isnull(iw_total, 0) + isnull(oow_total, 0) )) * convert(float, 100)
end as iw_pct,
-- oow total
isnull(oow_total, 0) as oow_total,
-- make oow pct
case
when isnull(iw_total, 0) + isnull(oow_total, 0) = 0 then convert(float, 0)
else
convert(float, isnull(oow_total, 0)) / convert(float, ( isnull(iw_total, 0) + isnull(oow_total, 0) )) * convert(float, 100)
end as oow_pct,
-- grand total
isnull(iw_total, 0) + isnull(oow_total, 0) as grand_total
from cte_iw_data i full join cte_oow_data o on i.FWEEK = o.FWEEK and i.prod_DESC = o.prod_DESC
John Miner
Crafty DBA
www.craftydba.com
April 8, 2013 at 4:35 pm
Are you serious j.miner? Take a look at J Livingston code, it's way simpler.
I just would change the having clause for a where clause (to filter the information before grouping it).
April 9, 2013 at 12:33 pm
So, you do not like my first solution? I totally agree it was the quickest thing that came off the press.
It is like life, if you spend a couple of minutes more looking at it, a simpler solution can be found.
How about a filtered roll-up? most of the code is for formatting the results.
--
-- Solve business problem with a roll-up
--
; with cte_rollup as (
select
fweek,
prod_desc,
cast(sum(case when warrantystatus = 'IW' then orders else 0 end) as decimal(9,2)) as iw_total,
cast(sum(case when warrantystatus = 'OOW' then orders else 0 end) as decimal(9,2)) as oow_total,
cast(sum(orders) as decimal(9,2)) as grand_total
from gm_table
group by rollup (fweek, prod_desc)
)
select
fweek, prod_desc, iw_total,
cast((iw_total/ grand_total * 100) as decimal(9,2)) as iw_pct,
oow_total,
cast((oow_total/ grand_total * 100) as decimal(9,2)) as oow_pct
from cte_rollup where prod_desc is not null
John Miner
Crafty DBA
www.craftydba.com
April 9, 2013 at 2:58 pm
Is there any reason to use ROLLUP when a simple GROUP BY is enough?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply