ORDERING CROSS TAB QUERY BASED ON MONTH AND YEAR

Question

Here is my table schema

+---------------+----------+-------------------+-----------------------------+----------------------------------+-----------+-------------+------------+-------------+--------+----------+----------+
|   VERTICAL    |   LOB    |      PROCESS      |         SUB PROCESS         |             ACTIVITY             |   MNTH    | OPPORTUNITY | DEFECTS(F) | DEFECTS(NF) |  CTQ   |   TYPE   | CATEGORY |
+---------------+----------+-------------------+-----------------------------+----------------------------------+-----------+-------------+------------+-------------+--------+----------+----------+
| Bill Delivery | Mobility | Service Complaint | Original eBill not Received | Original eBill not received      | 31-May-13 |         895 |          0 |          20 | Repeat | Customer | D        |
| Bill Delivery | Mobility | Service Complaint | MNP- Bill Delivery          | Bill Delivery Issue              | 30-Apr-13 |          12 |          0 |           0 | CAR    | Customer | C        |
| Bill Delivery | Mobility | Service Complaint | MNP- Bill Delivery          | Bill Delivery Issue              | 30-Apr-13 |         179 |          0 |           0 | CAR    | Customer | C        |
| Bill Delivery | Mobility | Service Complaint | Original Bill not Received  | Original Paper Bill not received | 30-Apr-13 |        1157 |          0 |           0 | CAR    | Customer | D        |
| Bill Delivery | Mobility | Service Complaint | Original Bill not Received  | Original Paper Bill not received | 30-Apr-13 |       16381 |          0 |          38 | CAR    | Customer | D        |
| Bill Delivery | Mobility | Service Complaint | Original eBill not Received | Original eBill not received      | 30-Apr-13 |          75 |          0 |           1 | CAR    | Customer | D        |
| Bill Delivery | Mobility | Service Complaint | Original eBill not Received | Original eBill not received      | 30-Apr-13 |         913 |          0 |          10 | CAR    | Customer | D        |
| Bill Delivery | Mobility | Service Request   | Duplicate Bill to be sent   | Duplicate Paper Bill to be sent  | 30-Apr-13 |        1022 |          0 |           1 | CAR    | Customer | D        |
| Bill Delivery | Mobility | Service Request   | Duplicate Bill to be sent   | Duplicate Paper Bill to be sent  | 30-Apr-13 |       14278 |          0 |          25 | CAR    | Customer | D        |
| Bill Delivery | Mobility | Service Request   | Other Delivery related      | Statement of Account to be sent  | 30-Apr-13 |          16 |          0 |           0 | CAR    | Customer | D        |
| Bill Delivery | Mobility | Service Request   | Other Delivery related      | Statement of Account to be sent  | 30-Apr-13 |         627 |          0 |          17 | CAR    | Customer | D        |
| Billing       | Mobility | Service Complaint | Billing Discrepancy         | 2G -  Usage & Rental Issue       | 30-Apr-13 |         849 |          0 |          40 | CAR    | Customer | C        |
| Billing       | Mobility | Service Complaint | Billing Discrepancy         | 2G -  Usage & Rental Issue       | 30-Apr-13 |        7834 |          0 |         569 | CAR    | Customer | C        |
| Billing       | Mobility | Service Complaint | Billing Discrepancy         | 3G -  Usage & Rental Issue       | 30-Apr-13 |         529 |          0 |          31 | CAR    | Customer | C        |
| Billing       | Mobility | Service Complaint | Billing Discrepancy         | 3G -  Usage & Rental Issue       | 30-Apr-13 |        3456 |          0 |         233 | CAR    | Customer | C        |
| Billing       | Mobility | Service Complaint | Billing Discrepancy         | 4G -  Usage & Rental Issue       | 30-Apr-13 |          17 |          0 |           0 | CAR    | Customer | C        |
+---------------+----------+-------------------+-----------------------------+----------------------------------+-----------+-------------+------------+-------------+--------+----------+----------+

I have used the following Cross Tab query to get the output in the desired format

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

    SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(DATENAME(MONTH, MNTH)+''+DATENAME(YEAR, MNTH)) 
                    FROM CAPABILITY
                  WHERE DATENAME(YEAR,MNTH)>2012
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
SET @query = 'SELECT VERTICAL, PROCESS, [SUB PROCESS], ACTIVITY, CTQ, TYPE,' 

+@cols+ 
'FROM  

(
SELECT VERTICAL, PROCESS,TYPE,CTQ,[SUB PROCESS], ACTIVITY,
    CASE
WHEN
(SUM([DEFECTS(F)])+SUM([DEFECTS(NF)]))=0
THEN
6
WHEN
(SUM([DEFECTS(F)])+SUM([DEFECTS(NF)]))= SUM(OPPORTUNITY)
THEN
0
WHEN
(SUM([DEFECTS(F)])+SUM([DEFECTS(NF)]))> SUM(OPPORTUNITY)
THEN 
''ERROR'' 
ELSE
DBO.NORMSINV((1-(SUM([DEFECTS(F)])+SUM([DEFECTS(NF)]))/SUM(OPPORTUNITY)))+1.5
END AS Sigma    

    ,DATENAME(MONTH, MNTH)+''''+DATENAME(YEAR, MNTH)
 AS MONTHS
    FROM CAPABILITY

        GROUP BY VERTICAL, PROCESS, DATENAME(MONTH, MNTH)+''''+DATENAME(YEAR, MNTH),TYPE,CTQ,[SUB PROCESS],ACTIVITY





)X

PIVOT 
            (
                MIN(SIGMA)
                FOR MONTHS in (' + @cols + ')
            ) X '

            --//PRINT (@QUERY)
EXECUTE (@QUERY)

This gives following output

+--------------+--------------+-------------------------------------+-------------------------------------+-----+---------------+--------+--------+--------+--------+--------+
|   VERTICAL   |   PROCESS    |             SUB PROCESS             |              ACTIVITY               | CTQ |     TYPE      | Feb-13 | Apr-13 | May-13 | Mar-13 | Jan-13 |
+--------------+--------------+-------------------------------------+-------------------------------------+-----+---------------+--------+--------+--------+--------+--------+
| Airtel Money | Airtel Money | 10% cashback                        | 10% cashback                        | SQ  | Transactional | NULL   | 6      | NULL   | NULL   | NULL   |
| Airtel Money | Airtel Money | 5 % Cashback                        | 5 % Cashback                        | SQ  | Transactional | NULL   | 6      | NULL   | NULL   | NULL   |
| Airtel Money | Airtel Money | 5 % Cashback Post paid Self payment | 5 % Cashback Post paid Self payment | SQ  | Transactional | NULL   | NULL   | 6      | NULL   | NULL   |
| Airtel Money | Airtel money | APEF                                | APEF                                | SQ  | Transactional | NULL   | NULL   | NULL   | NULL   | NULL   |
| Airtel Money | Airtel money | Bank File                           | BANK FILE                           | SQ  | Transactional | NULL   | NULL   | NULL   | NULL   | NULL   |
+--------------+--------------+-------------------------------------+-------------------------------------+-----+---------------+--------+--------+--------+--------+--------+

The only constrain I am facing is I am not able to order the details in descending (or ascending) order based on the Month and Year. Hence instead of "FEB -13","APRIL - 13", "MAY -13","MARCH - 13", "JAN -13" I would want it in the correct order (Jan 13 to May 13). Can someone please help me here.

I didn't try, but what if you add ORDER BY MNTH to the SELECT for XML and to the PIVOT itself? — Luis LL

Igor Borisenko Igor Borisenko · Accepted Answer · 2013-07-06T09:00:40

Try this

   SELECT @cols = ISNULL(@cols+',','') + QUOTENAME(DATENAME(MONTH, MNTH)+''+DATENAME(YEAR, MNTH)) 
    FROM CAPABILITY
    WHERE MNTH>='20120101'
    GROUP BY DATENAME(MONTH, MNTH), DATENAME(YEAR, MNTH), DATEPART(MONTH,mnth)
    ORDER BY DATENAME(YEAR, MNTH), DATEPART(MONTH,mnth)

I also replaced

WHERE DATENAME(YEAR,MNTH)>2012

with

WHERE MNTH>='20120101'

because the first one guarantee Index Scan which is not what one wants.

ORDERING CROSS TAB QUERY BASED ON MONTH AND YEAR

1 Answers