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