April 6, 2013 at 5:22 am
Hi,
Am having difficulty writing a pivot query as am pretty new to this. In the attached word document I have given example of the sample table and explained in bried what am trying to acheive.
Could anyone pls help me write a query to get % of rows to acheive the desired output.
thanks
April 8, 2013 at 2:05 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:24 pm
j.miner (4/8/2013)
I would not use a pivot since that is used to turn row values into column aggregations such as sums.
It will also turn row values into column values using MAX on character based values. Helps avoid a lot of the code you're talking about.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply