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