locked
join two PIVOT table RRS feed

  • Question


  • with NoOfOrder 
    as (
    SELECT  Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    FROM (
        select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
        from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    ) P
    PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV ),
    OntimeDelivery 
    as (
    SELECT  Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    FROM (
        select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
        from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    	and  CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date)
    ) P
    PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV )
    
    select * from NoOfOrder
    union all
    select * from OntimeDelivery 

    It giving resulte

    Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
    18	35	39	52	32	47	47	22	14	0	0	0
    9	10	16	22	6	11	19	10	5	0	0	0

    Expected Resulte

    			Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Total
    NoOfOrder		18	35	39	52	32	47	47	22	14	0	0	0	306
    OnTimeDelivered		9	10	16	22	6	11	19	10	5	0	0	0	108
    DeliverPerformance%	50.00	28.57	41.03	42.31	18.75	23.40	40.43	45.45	35.71	0.00	0.00	0.00	35.29

    DeliverPerformance% formula = (OnTimeDelivered/NoOfOrder) * 100 e.g ( (9/18)*100) = 50

    thanks in advance



    i am using sql server 2008 i have quer

    i am using sql server 2008 i have quer

    • Edited by naweez Monday, February 10, 2014 9:11 PM mistsk
    Monday, February 10, 2014 8:57 PM

Answers

  • you may need to do cast your calculation....

    Try the below

    select   CAST(1 as int) as  Id,
    		(cast(b.Jan as decimal(10,2)) / cast(a.Jan as decimal(10,2)) )* 100.00 as Jan,...

    May be the below(Not tested):

    with NoOfOrder 
    as (
    	SELECT CAST(1as int) as  Id, Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    	FROM (
    		select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
    		from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
    		where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    		and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    	) P
    	PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV ),
    OntimeDelivery 
    as (
    	SELECT CAST(1 as int) as  Id, Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    	FROM (
    		select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
    		from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
    		where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    		and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    		and  CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date)
    		) P
    		PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV ),
    cteFinal AS (
    select   CAST(1 as int) as  Id,
    		(CAST(b.Jan as decimal(10,2)) / CAST(a.Jan  as decimal(10,2)))* 100.00 as Jan,
    		(CAST(b.Feb  as decimal(10,2))/ CAST(a.Feb  as decimal(10,2)))* 100.00 as Feb,
    		--(b.Mar / a.Mar )* 100 as Mar,
    				0 as Mar,
    		(CAST(b.Apr as decimal(10,2))/ CAST(a.Apr  as decimal(10,2)))* 100.00 as Apr,
    		(CAST(b.May  as decimal(10,2))/ CAST(a.May  as decimal(10,2)))* 100.00 as May,
    		(CAST(b.Jun  as decimal(10,2))/ CAST(a.Jun  as decimal(10,2)))* 100.00 as Jun,
    		(CAST(b.Jul  as decimal(10,2))/ CAST(a.Jul  as decimal(10,2)))* 100.00 as Jul,
    		(CAST(b.Aug  as decimal(10,2))/ CAST(a.Aug  as decimal(10,2)))* 100.00 as Aug,
    		(CAST(b.Sep  as decimal(10,2))/ CAST(a.Sep  as decimal(10,2)))* 100.00 as Sep,
    		(CAST(b.Oct  as decimal(10,2))/ CAST(a.Oct  as decimal(10,2)))* 100.00 as Oct,
    		(CAST(b.Nov  as decimal(10,2))/ CAST(a.Nov  as decimal(10,2)))* 100.00 as Nov,
    		(CAST(b.Dec  as decimal(10,2))/ CAST(a.Dec  as decimal(10,2)))* 100.00 as Dec
    		 
    		From NoOfOrder a inner join OntimeDelivery b  on   a.Id = b.Id  )
    		 
    select 'NoOfOrder' as mType,* from NoOfOrder
    UNION ALL
    select 'OntimeDelivery' as mType,* from OntimeDelivery
    UNION ALL
    select 'DeliveryPercentage' as mType, * from cteFinal

    • Edited by SQLZealots Wednesday, February 12, 2014 11:28 AM
    • Marked as answer by naweez Monday, February 17, 2014 5:39 AM
    Wednesday, February 12, 2014 11:22 AM
  • sure you can 

    here you go

    ;With CTE
    AS
    (
    SELECT Cat,
    COUNT(CASE WHEN mon ='Jan' THEN InvoiceNo END) AS Jan,
    COUNT(CASE WHEN mon ='Feb' THEN InvoiceNo END) AS Feb,
    COUNT(CASE WHEN mon ='Mar' THEN InvoiceNo END) AS Mar,
    ...
    COUNT(CASE WHEN mon ='Dec' THEN InvoiceNo END) AS Dec,
    COUNT(InvoiceNo) AS Total
    FROM
    (
    select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo,CASE WHEN CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date) THEN 1 ELSE 0 END AS OnTime
    from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    )t
    INNER JOIN (VALUES ('OnTime',1),('NoOfOrder',2))t1(Cat)
    ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')
    OR t.OnTime =1)
    GROUP BY Cat
    )
    SELECT Cat,Jan,Feb,....,Dec,Total
    FROM CTE
    UNION ALL
    SELECT 'Deliver Performance %',
    MAX(CASE WHEN Cat = 'OnTime' THEN Jan END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jan END) ,0) AS Jan,
    MAX(CASE WHEN Cat = 'OnTime' THEN Feb END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Feb END) ,0) AS Feb,
    ...,
    MAX(CASE WHEN Cat = 'OnTime' THEN Dec END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Dec END) ,0) AS Dec,
    MAX(CASE WHEN Cat = 'OnTime' THEN Total END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Total END) ,0) AS Total
    FROM CTE


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by naweez Thursday, February 13, 2014 1:26 PM
    Wednesday, February 12, 2014 5:50 PM
  • sure you can 

    here you go

    ;With CTE
    AS
    (
    SELECT Cat,
    COUNT(CASE WHEN mon ='Jan' THEN InvoiceNo END) AS Jan,
    COUNT(CASE WHEN mon ='Feb' THEN InvoiceNo END) AS Feb,
    COUNT(CASE WHEN mon ='Mar' THEN InvoiceNo END) AS Mar,
    ...
    COUNT(CASE WHEN mon ='Dec' THEN InvoiceNo END) AS Dec,
    COUNT(InvoiceNo) AS Total
    FROM
    (
    select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo,CASE WHEN CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date) THEN 1 ELSE 0 END AS OnTime
    from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    )t
    INNER JOIN (VALUES ('OnTime',1),('NoOfOrder',2))t1(Cat)
    ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')
    OR t.OnTime =1)
    GROUP BY Cat
    )
    SELECT Cat,Jan,Feb,....,Dec,Total
    FROM CTE
    UNION ALL
    SELECT 'Deliver Performance %',
    MAX(CASE WHEN Cat = 'OnTime' THEN Jan END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jan END) ,0) AS Jan,
    MAX(CASE WHEN Cat = 'OnTime' THEN Feb END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Feb END) ,0) AS Feb,
    ...,
    MAX(CASE WHEN Cat = 'OnTime' THEN Dec END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Dec END) ,0) AS Dec,
    MAX(CASE WHEN Cat = 'OnTime' THEN Total END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Total END) ,0) AS Total
    FROM CTE


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    no it giving wrong total

    ;With CTE
    AS
    (
    SELECT Cat,
    COUNT(CASE WHEN mon ='Jan' THEN InvoiceNo END) AS Jan,
    COUNT(CASE WHEN mon ='Feb' THEN InvoiceNo END) AS Feb,
    COUNT(InvoiceNo) AS Total
    
    FROM
    (
    select LEFT(datename(month,InvoiceDate),3) mon,
    		InvoiceNo as InvoiceNo,
           CASE WHEN CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date) THEN 1 ELSE 0 END AS OnTime
    from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    )t
    INNER JOIN (VALUES ('OnTime'),('NoOfOrder'))t1(Cat)ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')OR t.OnTime =1)
    GROUP BY Cat
    )
    SELECT Cat,Jan,Feb,Total
    FROM CTE
    UNION ALL
    SELECT 'Deliver Performance %',
    MAX(CASE WHEN Cat = 'OnTime' THEN Jan END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jan END) ,0) AS Jan,
    MAX(CASE WHEN Cat = 'OnTime' THEN Feb END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Feb END) ,0) AS Feb,
    MAX(CASE WHEN Cat = 'OnTime' THEN Total END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Total END) ,0) AS Total
    FROM CTE


    result

    Cat			Jan		Feb		Total
    NoOfOrder		32.000000000000	11.000000000000	378.000000000000
    OnTime			15.000000000000	7.000000000000	140.000000000000
    Deliver Performance %	0.468750000000	0.636363636363	140.000000000000

    if used your inner join

    INNER JOIN (VALUES ('OnTime',1),('NoOfOrder',2))t1(Cat)
    ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')
    OR t.OnTime =1)
    GROUP BY Cat

     it give error

    Msg 8158, Level 16, State 1, Line 19
    't1' has more columns than were specified in the column list.


    Thanks Best Regard Naweez


    thanks you very much it's working fine.

    the code is 

    ;With CTE
    AS
    (
    SELECT Cat,
    
    COUNT(CASE WHEN mon ='Apr' THEN InvoiceNo END) AS Apr,
    COUNT(CASE WHEN mon ='May' THEN InvoiceNo END) AS May,
    COUNT(CASE WHEN mon ='Jun' THEN InvoiceNo END) AS Jun,
    
    COUNT(CASE WHEN mon ='Jul' THEN InvoiceNo END) AS Jul,
    COUNT(CASE WHEN mon ='Aug' THEN InvoiceNo END) AS Aug,
    COUNT(CASE WHEN mon ='Sep' THEN InvoiceNo END) AS Sep,
    
    COUNT(CASE WHEN mon ='Oct' THEN InvoiceNo END) AS Oct,
    COUNT(CASE WHEN mon ='Nov' THEN InvoiceNo END) AS Nov,
    COUNT(CASE WHEN mon ='Dec' THEN InvoiceNo END) AS Dec,
    
    COUNT(CASE WHEN mon ='Jan' THEN InvoiceNo END) AS Jan,
    COUNT(CASE WHEN mon ='Feb' THEN InvoiceNo END) AS Feb,
    COUNT(CASE WHEN mon ='Mar' THEN InvoiceNo END) AS Mar,
    
    COUNT(InvoiceNo) AS Total
    FROM
    (
    select LEFT(datename(month,InvoiceDate),3) mon,
    InvoiceNo,
    CASE WHEN CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date) THEN 1 ELSE 0 END AS OnTime
    from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    )t
    INNER JOIN (VALUES ('OnTime'),('NoOfOrder'))t1(Cat)
    ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')
    OR t.OnTime =1)
    GROUP BY Cat
    )
    SELECT Cat,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,[Dec],Jan,Feb,Mar, Total 
    FROM CTE
    UNION ALL
    SELECT 'Deliver Performance %',
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Apr END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Apr END) ,0) AS Apr,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN May END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN May END) ,0) AS May,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Jun END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jun END) ,0) AS Jun,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Jul END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jul END) ,0) AS Jul,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Aug END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Aug END) ,0) AS Aug,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Sep END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Sep END) ,0) AS Sep,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Oct END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Oct END) ,0) AS Oct,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Nov END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Nov END) ,0) AS Nov,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN [Dec] END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN [Dec] END) ,0) AS [Dec],
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Jan END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jan END) ,0) AS Jan,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Feb END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Feb END) ,0) AS Feb,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Mar END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Mar END) ,0) AS Mar,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Total END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Total END) ,0) AS Total
    FROM CTE

    the result is 

    Cat			Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Total
    NoOfOrder		18	35	39	52	32	47	47	22	43	32	11	0	378
    OnTime			9	10	16	22	6	11	19	10	15	15	7	0	140
    Deliver Performance %	50	28	41	42	18	23	40	45	34	46	63	NULL	37
    can you explain me how MAX function working in above code

    Thanks Best Regard Naweez

    • Marked as answer by naweez Thursday, February 13, 2014 1:26 PM
    Thursday, February 13, 2014 1:26 PM
  • Sure.

    the MAX function is used here to get the Ontime and Ttal OrderNo on the same record. this method is called classical crosstabbing. Here you apply an aggregate function to merge mutiple rows data. In your case because you've only one record per category (OnTime & NoOfOrder) you can use MIN,MAX or SUM. Then I apply your division logic to get you % values


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by naweez Thursday, February 13, 2014 2:11 PM
    Thursday, February 13, 2014 1:40 PM

All replies

  • I don't see anything meaningful.

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, February 10, 2014 9:10 PM
  • I don't see anything meaningful.

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    what does it mean?

    Thanks Best Regard Naweez

    Monday, February 10, 2014 9:19 PM
  • Try:

    with NoOfOrder 
    as (
    SELECT  Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    FROM (
        select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
        from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    ) P
    PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV ),
    OntimeDelivery 
    as (
    SELECT  Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    FROM (
        select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
        from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    	and  CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date)
    ) P
    PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV ),
    cteFinal AS (
    select *, CAST('NoOfOrder' as varchar(40)) as mType from NoOfOrder
    union all
    select *, 'OnTimeDelivered' as mType from OntimeDelivery)
    
    select * from cte
    UNION ALL
    select max(case when mType = 'OnTimeDelivered' then [Jan]*1.0 end) /max(case when mType = 'NoOfOrder' then [Jan] end) + 100.0 as [Jan], etc. for all other months  


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Kalman Toth Saturday, February 15, 2014 11:12 PM
    Monday, February 10, 2014 9:25 PM
  • Try:

    with NoOfOrder 
    as (
    SELECT  Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    FROM (
        select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
        from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    ) P
    PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV ),
    OntimeDelivery 
    as (
    SELECT  Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    FROM (
        select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
        from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    	and  CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date)
    ) P
    PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV ),
    cteFinal AS (
    select *, CAST('NoOfOrder' as varchar(40)) as mType from NoOfOrder
    union all
    select *, 'OnTimeDelivered' as mType from OntimeDelivery)
    
    select * from cte
    UNION ALL
    select max(case when mType = 'OnTimeDelivered' then [Jan]*1.0 end) /max(case when mType = 'NoOfOrder' then [Jan] end) + 100.0 as [Jan], etc. for all other months  


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    select max(case when mType = 'OnTimeDelivered' then [Jan]*1.0 end) /max(case when mType = 'NoOfOrder' then [Jan] end) + 100.0 as [Jan], etc. for all other months

    it geveniving error , even i have not understood.

    can you guide how to join NoOfOrder  and OntimeDelivery .




    Thanks Best Regard Naweez

    Wednesday, February 12, 2014 6:20 AM

  • with NoOfOrder 
    as (
    SELECT  Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    FROM (
        select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
        from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    ) P
    PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV ),
    OntimeDelivery 
    as (
    SELECT  Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    FROM (
        select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
        from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    	and  CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date)
    ) P
    PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV )
    
    select * from NoOfOrder
    union all
    select * from OntimeDelivery 

    It giving resulte

    Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
    18	35	39	52	32	47	47	22	14	0	0	0
    9	10	16	22	6	11	19	10	5	0	0	0

    Expected Resulte

    			Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Total
    NoOfOrder		18	35	39	52	32	47	47	22	14	0	0	0	306
    OnTimeDelivered		9	10	16	22	6	11	19	10	5	0	0	0	108
    DeliverPerformance%	50.00	28.57	41.03	42.31	18.75	23.40	40.43	45.45	35.71	0.00	0.00	0.00	35.29

    DeliverPerformance% formula = (OnTimeDelivered/NoOfOrder) * 100 e.g ( (9/18)*100) = 50

    thanks in advance



    i am using sql server 2008 i have quer

    i am using sql server 2008 i have quer

    i did but it giving zero result why i don't know 

    with NoOfOrder 
    as (
    	SELECT CAST(1as int) as  Id, Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    	FROM (
    		select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
    		from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
    		where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    		and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    	) P
    	PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV ),
    OntimeDelivery 
    as (
    	SELECT CAST(1 as int) as  Id, Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    	FROM (
    		select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
    		from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
    		where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    		and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    		and  CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date)
    		) P
    		PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV ),
    cteFinal AS (
    select   CAST(1 as int) as  Id,
    		(b.Jan / a.Jan )* 100 as Jan,
    		(b.Feb / a.Feb )* 100 as Feb,
    		--(b.Mar / a.Mar )* 100 as Mar,
    				0 as Mar,
    		(b.Apr/ a.Apr )* 100 as Apr,
    		(b.May / a.May )* 100 as May,
    		(b.Jun / a.Jun )* 100 as Jun,
    		(b.Jul / a.Jul )* 100 as Jul,
    		(b.Aug / a.Aug )* 100 as Aug,
    		(b.Sep / a.Sep )* 100 as Sep,
    		(b.Oct / a.Oct )* 100 as Oct,
    		(b.Nov / a.Nov )* 100 as Nov,
    		(b.Dec / a.Dec )* 100 as Dec
    		 from NoOfOrder a inner join OntimeDelivery b  on   a.Id = b.Id  )
    select 'NoOfOrder' as mType,* from NoOfOrder
    UNION ALL
    select 'OntimeDelivery' as mType,* from OntimeDelivery
    UNION ALL
    select 'DeliveryPercentage' as mType, * from cteFinal

    it giving result

    mType			Id	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
    NoOfOrder		1	18	35	39	52	32	47	47	22	43	32	10	0
    OntimeDelivery		1	9	10	16	22	6	11	19	10	15	15	7	0
    DeliveryPercentage	1	0	0	0	0	0	0	0	0	0	0	0	0
    


    why   zero  value coming

    DeliveryPercentage	1	0	0	0	0	0	0	0	0	0	0	0	0

    physicaly i created two table but it giving right result

    SELECT    *
    FROM         a 
    union 
    SELECT    *
    FROM         b 
    union 
    SELECT   1 as id,  (b.Apr / a.Apr) * 100
    FROM         a INNER JOIN
                          b ON a.id = b.id

    it give right result

    id	Apr
    1	9
    1	18
    1	50

    any body can me guide where i am doing wrong?


    Thanks Best Regard Naweez

    Wednesday, February 12, 2014 11:17 AM
  • you may need to do cast your calculation....

    Try the below

    select   CAST(1 as int) as  Id,
    		(cast(b.Jan as decimal(10,2)) / cast(a.Jan as decimal(10,2)) )* 100.00 as Jan,...

    May be the below(Not tested):

    with NoOfOrder 
    as (
    	SELECT CAST(1as int) as  Id, Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    	FROM (
    		select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
    		from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
    		where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    		and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    	) P
    	PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV ),
    OntimeDelivery 
    as (
    	SELECT CAST(1 as int) as  Id, Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    	FROM (
    		select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
    		from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
    		where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    		and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    		and  CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date)
    		) P
    		PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV ),
    cteFinal AS (
    select   CAST(1 as int) as  Id,
    		(CAST(b.Jan as decimal(10,2)) / CAST(a.Jan  as decimal(10,2)))* 100.00 as Jan,
    		(CAST(b.Feb  as decimal(10,2))/ CAST(a.Feb  as decimal(10,2)))* 100.00 as Feb,
    		--(b.Mar / a.Mar )* 100 as Mar,
    				0 as Mar,
    		(CAST(b.Apr as decimal(10,2))/ CAST(a.Apr  as decimal(10,2)))* 100.00 as Apr,
    		(CAST(b.May  as decimal(10,2))/ CAST(a.May  as decimal(10,2)))* 100.00 as May,
    		(CAST(b.Jun  as decimal(10,2))/ CAST(a.Jun  as decimal(10,2)))* 100.00 as Jun,
    		(CAST(b.Jul  as decimal(10,2))/ CAST(a.Jul  as decimal(10,2)))* 100.00 as Jul,
    		(CAST(b.Aug  as decimal(10,2))/ CAST(a.Aug  as decimal(10,2)))* 100.00 as Aug,
    		(CAST(b.Sep  as decimal(10,2))/ CAST(a.Sep  as decimal(10,2)))* 100.00 as Sep,
    		(CAST(b.Oct  as decimal(10,2))/ CAST(a.Oct  as decimal(10,2)))* 100.00 as Oct,
    		(CAST(b.Nov  as decimal(10,2))/ CAST(a.Nov  as decimal(10,2)))* 100.00 as Nov,
    		(CAST(b.Dec  as decimal(10,2))/ CAST(a.Dec  as decimal(10,2)))* 100.00 as Dec
    		 
    		From NoOfOrder a inner join OntimeDelivery b  on   a.Id = b.Id  )
    		 
    select 'NoOfOrder' as mType,* from NoOfOrder
    UNION ALL
    select 'OntimeDelivery' as mType,* from OntimeDelivery
    UNION ALL
    select 'DeliveryPercentage' as mType, * from cteFinal

    • Edited by SQLZealots Wednesday, February 12, 2014 11:28 AM
    • Marked as answer by naweez Monday, February 17, 2014 5:39 AM
    Wednesday, February 12, 2014 11:22 AM
  • you may need to do cast your calculation....

    Try the below

    select   CAST(1 as int) as  Id,
    		(cast(b.Jan as decimal(10,2)) / cast(a.Jan as decimal(10,2)) )* 100.00 as Jan,...

    May be the below(Not tested):

    with NoOfOrder 
    as (
    	SELECT CAST(1as int) as  Id, Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    	FROM (
    		select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
    		from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
    		where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    		and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    	) P
    	PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV ),
    OntimeDelivery 
    as (
    	SELECT CAST(1 as int) as  Id, Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
    	FROM (
    		select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo
    		from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
    		where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    
    		and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    		and  CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date)
    		) P
    		PIVOT (count(InvoiceNo)for mon in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)) PV ),
    cteFinal AS (
    select   CAST(1 as int) as  Id,
    		(CAST(b.Jan as decimal(10,2)) / CAST(a.Jan  as decimal(10,2)))* 100.00 as Jan,
    		(CAST(b.Feb  as decimal(10,2))/ CAST(a.Feb  as decimal(10,2)))* 100.00 as Feb,
    		--(b.Mar / a.Mar )* 100 as Mar,
    				0 as Mar,
    		(CAST(b.Apr as decimal(10,2))/ CAST(a.Apr  as decimal(10,2)))* 100.00 as Apr,
    		(CAST(b.May  as decimal(10,2))/ CAST(a.May  as decimal(10,2)))* 100.00 as May,
    		(CAST(b.Jun  as decimal(10,2))/ CAST(a.Jun  as decimal(10,2)))* 100.00 as Jun,
    		(CAST(b.Jul  as decimal(10,2))/ CAST(a.Jul  as decimal(10,2)))* 100.00 as Jul,
    		(CAST(b.Aug  as decimal(10,2))/ CAST(a.Aug  as decimal(10,2)))* 100.00 as Aug,
    		(CAST(b.Sep  as decimal(10,2))/ CAST(a.Sep  as decimal(10,2)))* 100.00 as Sep,
    		(CAST(b.Oct  as decimal(10,2))/ CAST(a.Oct  as decimal(10,2)))* 100.00 as Oct,
    		(CAST(b.Nov  as decimal(10,2))/ CAST(a.Nov  as decimal(10,2)))* 100.00 as Nov,
    		(CAST(b.Dec  as decimal(10,2))/ CAST(a.Dec  as decimal(10,2)))* 100.00 as Dec
    		 
    		From NoOfOrder a inner join OntimeDelivery b  on   a.Id = b.Id  )
    		 
    select 'NoOfOrder' as mType,* from NoOfOrder
    UNION ALL
    select 'OntimeDelivery' as mType,* from OntimeDelivery
    UNION ALL
    select 'DeliveryPercentage' as mType, * from cteFinal

    thanks  it giving result right

    mType	Id	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
    NoOfOrder	1	18.000000000000000	35.000000000000000	39.000000000000000	52.000000000000000	32.000000000000000	47.000000000000000	47.000000000000000	22.000000000000000	43.000000000000000	32.000000000000000	10.000000000000000	0
    OntimeDelivery	1	9.000000000000000	10.000000000000000	16.000000000000000	22.000000000000000	6.000000000000000	11.000000000000000	19.000000000000000	10.000000000000000	15.000000000000000	15.000000000000000	7.000000000000000	0
    DeliveryPercentage	1	50.000000000000000	28.571428571420000	41.025641025640000	42.307692307690000	18.750000000000000	23.404255319140000	40.425531914890000	45.454545454540000	34.883720930230000	46.875000000000000	70.000000000000000	0

    but if NoofOrder and OntimeDelivery in some month zero value then it give  error

    Msg 8134, Level 16, State 1, Line 1
    Divide by zero error encountered. like march month there is zero value

    how to avoid that please help me.


    Thanks Best Regard Naweez

    Wednesday, February 12, 2014 12:13 PM
  • This is simple arithmetic issue...

    Use CASE ...WHEN apporpriately as below in you actual code.

    --if a.jan is 0 then,

    Select 1/a.jan --this will give you error Select 1/cast(Case when a.jan=0 then NULL Else 1 end as decimal(10,2)) --this will work

    --you need to apply for all month calculation


    Wednesday, February 12, 2014 12:36 PM
  • you dont even need two CTE and PIVOTs the below would be enough

    ;With CTE AS ( SELECT Cat, COUNT(CASE WHEN mon ='Jan' THEN InvoiceNo END) AS Jan, COUNT(CASE WHEN mon ='Feb' THEN InvoiceNo END) AS Feb, COUNT(CASE WHEN mon ='Mar' THEN InvoiceNo END) AS Mar, ... COUNT(CASE WHEN mon ='Dec' THEN InvoiceNo END) AS Dec, FROM ( select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo,CASE WHEN CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date) THEN 1 ELSE 0 END AS OnTime from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart AND tbl_CompanyMaster.YearEnd) )t INNER JOIN (VALUES ('OnTime',1),('NoOfOrder',2))t1(Cat)
    ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')
    OR t.OnTime =1) GROUP BY Cat ) SELECT Cat,Jan,Feb,....,Dec FROM CTE UNION ALL SELECT 'Deliver Performance %', MAX(CASE WHEN Cat = 'OnTime' THEN Jan END) * 1.0/ NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jan END) ,0) AS Jan, MAX(CASE WHEN Cat = 'OnTime' THEN Feb END) * 1.0/ NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Feb END) ,0) AS Feb, ..., MAX(CASE WHEN Cat = 'OnTime' THEN Dec END) * 1.0/ NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Dec END) ,0) AS Dec FROM CTE



    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Wednesday, February 12, 2014 1:17 PM
    Wednesday, February 12, 2014 1:13 PM
  • it giving error INNER JOIN (VALUES ('OnTime',1),('NoOfOrder',2))t1(Cat) Msg 8158, Level 16, State 1, Line 15 't1' has more columns than were specified in the column list.

    Thanks Best Regard Naweez

    Wednesday, February 12, 2014 1:43 PM
  • sorry that was a typo

    see modified suggestion below

    ;With CTE
    AS
    (
    SELECT Cat,
    COUNT(CASE WHEN mon ='Jan' THEN InvoiceNo END) AS Jan,
    COUNT(CASE WHEN mon ='Feb' THEN InvoiceNo END) AS Feb,
    COUNT(CASE WHEN mon ='Mar' THEN InvoiceNo END) AS Mar,
    ...
    COUNT(CASE WHEN mon ='Dec' THEN InvoiceNo END) AS Dec,
    FROM
    (
    select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo,CASE WHEN CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date) THEN 1 ELSE 0 END AS OnTime
    from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    )t
    INNER JOIN (VALUES ('OnTime'),('NoOfOrder'))t1(Cat)ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')OR t.OnTime =1)
    GROUP BY Cat
    )
    SELECT Cat,Jan,Feb,....,Dec
    FROM CTE
    UNION ALL
    SELECT 'Deliver Performance %',
    MAX(CASE WHEN Cat = 'OnTime' THEN Jan END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jan END) ,0) AS Jan,
    MAX(CASE WHEN Cat = 'OnTime' THEN Feb END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Feb END) ,0) AS Feb,
    ...,
    MAX(CASE WHEN Cat = 'OnTime' THEN Dec END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Dec END) ,0) AS Dec
    FROM CTE


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, February 12, 2014 1:46 PM
  • you dont even need two CTE and PIVOTs the below would be enough

    ;With CTE AS ( SELECT Cat, COUNT(CASE WHEN mon ='Jan' THEN InvoiceNo END) AS Jan, COUNT(CASE WHEN mon ='Feb' THEN InvoiceNo END) AS Feb, COUNT(CASE WHEN mon ='Mar' THEN InvoiceNo END) AS Mar, ... COUNT(CASE WHEN mon ='Dec' THEN InvoiceNo END) AS Dec, FROM ( select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo,CASE WHEN CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date) THEN 1 ELSE 0 END AS OnTime from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart AND tbl_CompanyMaster.YearEnd) )t INNER JOIN (VALUES ('OnTime',1),('NoOfOrder',2))t1(Cat)
    ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')
    OR t.OnTime =1) GROUP BY Cat ) SELECT Cat,Jan,Feb,....,Dec FROM CTE UNION ALL SELECT 'Deliver Performance %', MAX(CASE WHEN Cat = 'OnTime' THEN Jan END) * 1.0/ NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jan END) ,0) AS Jan, MAX(CASE WHEN Cat = 'OnTime' THEN Feb END) * 1.0/ NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Feb END) ,0) AS Feb, ..., MAX(CASE WHEN Cat = 'OnTime' THEN Dec END) * 1.0/ NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Dec END) ,0) AS Dec FROM CTE



    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    thanks it giving right result

    i just add  replace 0.1 to 100

    Cat			Jan	Feb
    NoOfOrder		32	10
    OnTime			15	7
    Deliver Performance %	46	70

    can i get result like this

    Cat			Jan	Feb	Total
    NoOfOrder		32	10	42
    OnTime			15	7	22
    Deliver Performance %	46	70	52


    Thanks Best Regard Naweez

    Wednesday, February 12, 2014 1:55 PM
  • sorry that was a typo

    see modified suggestion below

    ;With CTE
    AS
    (
    SELECT Cat,
    COUNT(CASE WHEN mon ='Jan' THEN InvoiceNo END) AS Jan,
    COUNT(CASE WHEN mon ='Feb' THEN InvoiceNo END) AS Feb,
    COUNT(CASE WHEN mon ='Mar' THEN InvoiceNo END) AS Mar,
    ...
    COUNT(CASE WHEN mon ='Dec' THEN InvoiceNo END) AS Dec,
    FROM
    (
    select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo,CASE WHEN CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date) THEN 1 ELSE 0 END AS OnTime
    from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    )t
    INNER JOIN (VALUES ('OnTime'),('NoOfOrder'))t1(Cat)ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')OR t.OnTime =1)
    GROUP BY Cat
    )
    SELECT Cat,Jan,Feb,....,Dec
    FROM CTE
    UNION ALL
    SELECT 'Deliver Performance %',
    MAX(CASE WHEN Cat = 'OnTime' THEN Jan END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jan END) ,0) AS Jan,
    MAX(CASE WHEN Cat = 'OnTime' THEN Feb END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Feb END) ,0) AS Feb,
    ...,
    MAX(CASE WHEN Cat = 'OnTime' THEN Dec END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Dec END) ,0) AS Dec
    FROM CTE


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    thanks it giving right result
    
    i just modified 0.1 to 100
    
    Cat			Jan	Feb
    NoOfOrder		32	10
    OnTime			15	7
    Deliver Performance %	46	70
    
    
    can i get result like this
    
    Cat			Jan	Feb	Total
    NoOfOrder		32	10	42
    OnTime			15	7	22
    Deliver Performance %	46	70	52



    Thanks Best Regard Naweez

    Wednesday, February 12, 2014 1:58 PM
  • Can you post the exact error, please?

    Also, 

    I think it should be

    select mType, cast([Jan] as Decimal(10,2)) as [Jan], ... from cte UNION ALL select 'Total' as mType,

    max(case when mType = 'OnTimeDelivered'

    then [Jan]*1.0 end)/NULLIF(max(case when mType = 'NoOfOrder' then [Jan] end),0) * 100.0 as [Jan], etc.

    NULLIF is used to prevent division by 0. And I used *1.0 to implicitly convert values to decimal.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    • Edited by Naomi N Wednesday, February 12, 2014 5:33 PM
    Wednesday, February 12, 2014 5:29 PM
  • sure you can 

    here you go

    ;With CTE
    AS
    (
    SELECT Cat,
    COUNT(CASE WHEN mon ='Jan' THEN InvoiceNo END) AS Jan,
    COUNT(CASE WHEN mon ='Feb' THEN InvoiceNo END) AS Feb,
    COUNT(CASE WHEN mon ='Mar' THEN InvoiceNo END) AS Mar,
    ...
    COUNT(CASE WHEN mon ='Dec' THEN InvoiceNo END) AS Dec,
    COUNT(InvoiceNo) AS Total
    FROM
    (
    select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo,CASE WHEN CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date) THEN 1 ELSE 0 END AS OnTime
    from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    )t
    INNER JOIN (VALUES ('OnTime',1),('NoOfOrder',2))t1(Cat)
    ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')
    OR t.OnTime =1)
    GROUP BY Cat
    )
    SELECT Cat,Jan,Feb,....,Dec,Total
    FROM CTE
    UNION ALL
    SELECT 'Deliver Performance %',
    MAX(CASE WHEN Cat = 'OnTime' THEN Jan END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jan END) ,0) AS Jan,
    MAX(CASE WHEN Cat = 'OnTime' THEN Feb END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Feb END) ,0) AS Feb,
    ...,
    MAX(CASE WHEN Cat = 'OnTime' THEN Dec END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Dec END) ,0) AS Dec,
    MAX(CASE WHEN Cat = 'OnTime' THEN Total END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Total END) ,0) AS Total
    FROM CTE


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by naweez Thursday, February 13, 2014 1:26 PM
    Wednesday, February 12, 2014 5:50 PM
  • sure you can 

    here you go

    ;With CTE
    AS
    (
    SELECT Cat,
    COUNT(CASE WHEN mon ='Jan' THEN InvoiceNo END) AS Jan,
    COUNT(CASE WHEN mon ='Feb' THEN InvoiceNo END) AS Feb,
    COUNT(CASE WHEN mon ='Mar' THEN InvoiceNo END) AS Mar,
    ...
    COUNT(CASE WHEN mon ='Dec' THEN InvoiceNo END) AS Dec,
    COUNT(InvoiceNo) AS Total
    FROM
    (
    select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo,CASE WHEN CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date) THEN 1 ELSE 0 END AS OnTime
    from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    )t
    INNER JOIN (VALUES ('OnTime',1),('NoOfOrder',2))t1(Cat)
    ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')
    OR t.OnTime =1)
    GROUP BY Cat
    )
    SELECT Cat,Jan,Feb,....,Dec,Total
    FROM CTE
    UNION ALL
    SELECT 'Deliver Performance %',
    MAX(CASE WHEN Cat = 'OnTime' THEN Jan END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jan END) ,0) AS Jan,
    MAX(CASE WHEN Cat = 'OnTime' THEN Feb END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Feb END) ,0) AS Feb,
    ...,
    MAX(CASE WHEN Cat = 'OnTime' THEN Dec END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Dec END) ,0) AS Dec,
    MAX(CASE WHEN Cat = 'OnTime' THEN Total END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Total END) ,0) AS Total
    FROM CTE


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    no it giving wrong total

    ;With CTE
    AS
    (
    SELECT Cat,
    COUNT(CASE WHEN mon ='Jan' THEN InvoiceNo END) AS Jan,
    COUNT(CASE WHEN mon ='Feb' THEN InvoiceNo END) AS Feb,
    COUNT(InvoiceNo) AS Total
    
    FROM
    (
    select LEFT(datename(month,InvoiceDate),3) mon,
    		InvoiceNo as InvoiceNo,
           CASE WHEN CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date) THEN 1 ELSE 0 END AS OnTime
    from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    )t
    INNER JOIN (VALUES ('OnTime'),('NoOfOrder'))t1(Cat)ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')OR t.OnTime =1)
    GROUP BY Cat
    )
    SELECT Cat,Jan,Feb,Total
    FROM CTE
    UNION ALL
    SELECT 'Deliver Performance %',
    MAX(CASE WHEN Cat = 'OnTime' THEN Jan END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jan END) ,0) AS Jan,
    MAX(CASE WHEN Cat = 'OnTime' THEN Feb END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Feb END) ,0) AS Feb,
    MAX(CASE WHEN Cat = 'OnTime' THEN Total END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Total END) ,0) AS Total
    FROM CTE


    result

    Cat			Jan		Feb		Total
    NoOfOrder		32.000000000000	11.000000000000	378.000000000000
    OnTime			15.000000000000	7.000000000000	140.000000000000
    Deliver Performance %	0.468750000000	0.636363636363	140.000000000000

    if used your inner join

    INNER JOIN (VALUES ('OnTime',1),('NoOfOrder',2))t1(Cat)
    ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')
    OR t.OnTime =1)
    GROUP BY Cat

     it give error

    Msg 8158, Level 16, State 1, Line 19
    't1' has more columns than were specified in the column list.


    Thanks Best Regard Naweez


    • Edited by naweez Thursday, February 13, 2014 10:53 AM added error
    Thursday, February 13, 2014 10:30 AM
  • sure you can 

    here you go

    ;With CTE
    AS
    (
    SELECT Cat,
    COUNT(CASE WHEN mon ='Jan' THEN InvoiceNo END) AS Jan,
    COUNT(CASE WHEN mon ='Feb' THEN InvoiceNo END) AS Feb,
    COUNT(CASE WHEN mon ='Mar' THEN InvoiceNo END) AS Mar,
    ...
    COUNT(CASE WHEN mon ='Dec' THEN InvoiceNo END) AS Dec,
    COUNT(InvoiceNo) AS Total
    FROM
    (
    select LEFT(datename(month,InvoiceDate),3) mon,InvoiceNo as InvoiceNo,CASE WHEN CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date) THEN 1 ELSE 0 END AS OnTime
    from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    )t
    INNER JOIN (VALUES ('OnTime',1),('NoOfOrder',2))t1(Cat)
    ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')
    OR t.OnTime =1)
    GROUP BY Cat
    )
    SELECT Cat,Jan,Feb,....,Dec,Total
    FROM CTE
    UNION ALL
    SELECT 'Deliver Performance %',
    MAX(CASE WHEN Cat = 'OnTime' THEN Jan END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jan END) ,0) AS Jan,
    MAX(CASE WHEN Cat = 'OnTime' THEN Feb END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Feb END) ,0) AS Feb,
    ...,
    MAX(CASE WHEN Cat = 'OnTime' THEN Dec END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Dec END) ,0) AS Dec,
    MAX(CASE WHEN Cat = 'OnTime' THEN Total END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Total END) ,0) AS Total
    FROM CTE


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    no it giving wrong total

    ;With CTE
    AS
    (
    SELECT Cat,
    COUNT(CASE WHEN mon ='Jan' THEN InvoiceNo END) AS Jan,
    COUNT(CASE WHEN mon ='Feb' THEN InvoiceNo END) AS Feb,
    COUNT(InvoiceNo) AS Total
    
    FROM
    (
    select LEFT(datename(month,InvoiceDate),3) mon,
    		InvoiceNo as InvoiceNo,
           CASE WHEN CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date) THEN 1 ELSE 0 END AS OnTime
    from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    )t
    INNER JOIN (VALUES ('OnTime'),('NoOfOrder'))t1(Cat)ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')OR t.OnTime =1)
    GROUP BY Cat
    )
    SELECT Cat,Jan,Feb,Total
    FROM CTE
    UNION ALL
    SELECT 'Deliver Performance %',
    MAX(CASE WHEN Cat = 'OnTime' THEN Jan END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jan END) ,0) AS Jan,
    MAX(CASE WHEN Cat = 'OnTime' THEN Feb END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Feb END) ,0) AS Feb,
    MAX(CASE WHEN Cat = 'OnTime' THEN Total END) * 1.0/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Total END) ,0) AS Total
    FROM CTE


    result

    Cat			Jan		Feb		Total
    NoOfOrder		32.000000000000	11.000000000000	378.000000000000
    OnTime			15.000000000000	7.000000000000	140.000000000000
    Deliver Performance %	0.468750000000	0.636363636363	140.000000000000

    if used your inner join

    INNER JOIN (VALUES ('OnTime',1),('NoOfOrder',2))t1(Cat)
    ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')
    OR t.OnTime =1)
    GROUP BY Cat

     it give error

    Msg 8158, Level 16, State 1, Line 19
    't1' has more columns than were specified in the column list.


    Thanks Best Regard Naweez


    thanks you very much it's working fine.

    the code is 

    ;With CTE
    AS
    (
    SELECT Cat,
    
    COUNT(CASE WHEN mon ='Apr' THEN InvoiceNo END) AS Apr,
    COUNT(CASE WHEN mon ='May' THEN InvoiceNo END) AS May,
    COUNT(CASE WHEN mon ='Jun' THEN InvoiceNo END) AS Jun,
    
    COUNT(CASE WHEN mon ='Jul' THEN InvoiceNo END) AS Jul,
    COUNT(CASE WHEN mon ='Aug' THEN InvoiceNo END) AS Aug,
    COUNT(CASE WHEN mon ='Sep' THEN InvoiceNo END) AS Sep,
    
    COUNT(CASE WHEN mon ='Oct' THEN InvoiceNo END) AS Oct,
    COUNT(CASE WHEN mon ='Nov' THEN InvoiceNo END) AS Nov,
    COUNT(CASE WHEN mon ='Dec' THEN InvoiceNo END) AS Dec,
    
    COUNT(CASE WHEN mon ='Jan' THEN InvoiceNo END) AS Jan,
    COUNT(CASE WHEN mon ='Feb' THEN InvoiceNo END) AS Feb,
    COUNT(CASE WHEN mon ='Mar' THEN InvoiceNo END) AS Mar,
    
    COUNT(InvoiceNo) AS Total
    FROM
    (
    select LEFT(datename(month,InvoiceDate),3) mon,
    InvoiceNo,
    CASE WHEN CAST(tbl_InvoiceMain.InvoiceDate AS date) <= CAST(tbl_OrderMain.ScheduledDispatchDate AS date) THEN 1 ELSE 0 END AS OnTime
    from tbl_InvoiceMain ,tbl_OrderMain,tbl_CompanyMaster    
        where tbl_InvoiceMain.OrderID = tbl_OrderMain.OrderID
    	and (CAST(tbl_InvoiceMain.InvoiceDate AS date) BETWEEN tbl_CompanyMaster.YearStart  AND tbl_CompanyMaster.YearEnd)
    )t
    INNER JOIN (VALUES ('OnTime'),('NoOfOrder'))t1(Cat)
    ON ((t.OnTime = 0 AND t1.Cat ='NoOfOrder')
    OR t.OnTime =1)
    GROUP BY Cat
    )
    SELECT Cat,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,[Dec],Jan,Feb,Mar, Total 
    FROM CTE
    UNION ALL
    SELECT 'Deliver Performance %',
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Apr END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Apr END) ,0) AS Apr,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN May END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN May END) ,0) AS May,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Jun END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jun END) ,0) AS Jun,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Jul END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jul END) ,0) AS Jul,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Aug END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Aug END) ,0) AS Aug,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Sep END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Sep END) ,0) AS Sep,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Oct END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Oct END) ,0) AS Oct,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Nov END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Nov END) ,0) AS Nov,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN [Dec] END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN [Dec] END) ,0) AS [Dec],
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Jan END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Jan END) ,0) AS Jan,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Feb END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Feb END) ,0) AS Feb,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Mar END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Mar END) ,0) AS Mar,
    
    MAX(CASE WHEN Cat = 'OnTime' THEN Total END) * 100/
    NULLIF(MAX(CASE WHEN Cat = 'NoOfOrder' THEN Total END) ,0) AS Total
    FROM CTE

    the result is 

    Cat			Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Total
    NoOfOrder		18	35	39	52	32	47	47	22	43	32	11	0	378
    OnTime			9	10	16	22	6	11	19	10	15	15	7	0	140
    Deliver Performance %	50	28	41	42	18	23	40	45	34	46	63	NULL	37
    can you explain me how MAX function working in above code

    Thanks Best Regard Naweez

    • Marked as answer by naweez Thursday, February 13, 2014 1:26 PM
    Thursday, February 13, 2014 1:26 PM
  • Sure.

    the MAX function is used here to get the Ontime and Ttal OrderNo on the same record. this method is called classical crosstabbing. Here you apply an aggregate function to merge mutiple rows data. In your case because you've only one record per category (OnTime & NoOfOrder) you can use MIN,MAX or SUM. Then I apply your division logic to get you % values


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by naweez Thursday, February 13, 2014 2:11 PM
    Thursday, February 13, 2014 1:40 PM