on 12-16-2015 1:00 PM
SELECT
GT.TOWN_NAME_C
,IFNULL(SUM(CompStats.NUM1),0) AS NUM1
,IFNULL(SUM(CompStats.NUM2),0) AS NUM2
,IFNULL(SUM(CompStats.NUM1),0) + IFNULL(SUM(CompStats.NUM2),0) AS NUM3
,IFNULL(SUM(CompStats.NUM4),0) AS NUM4
,IFNULL(SUM(CompStats.NUM1),0) + IFNULL(SUM(CompStats.NUM2),0) -IFNULL(SUM(CompStats.NUM4),0) AS NUM5
,IFNULL(SUM(CompStats.NUM4),0)- IFNULL(SUM(CompStats.NUM7),0) AS NUM6
,IFNULL(SUM(CompStats.NUM7),0) AS NUM7
,CASE WHEN IFNULL(SUM(CompStats.NUM4),0) != 0
THEN CAST((CAST(IFNULL(SUM(CompStats.NUM4),0)- IFNULL(SUM(CompStats.NUM7),0) AS DECIMAL(18, 2)) /
(CAST(IFNULL(SUM(CompStats.NUM4), 0) AS DECIMAL(18, 2))+ 0.0001) * 100) AS DECIMAL(18,0))
ELSE 0
END AS NUM8
FROM
(
SELECT CSR.TOWN_CD_C, CSR.CREATED_DT AS CREATED_DT,
COUNT(DISTINCT CSR.SERVICE_REQUEST_ID_N) AS NUM1,
0 As NUM2,
0 AS TotPendComp,
0 AS ComplPendingPeriod,
0 AS NUM4,
0 AS NUM6,
0 As NUM7
From "XXXXX"."CR_SERVICE_REQUEST" as CSR
WHERE CSR.CREATED_DT < :FROMDATE
AND CSR.SERVICE_REQUEST_ID_N NOT IN (
SELECT DISTINCT CSRL.SERVICE_REQUEST_ID_N
FROM "XXXXX"."CR_SERVICE_REQUEST_LOG" as CSRL
WHERE CSRL.REQUEST_STATUS_C IN ('Closed')
AND CREATED_DT < :FROMDATE)
Group By CSR.TOWN_CD_C,CSR.CREATED_DT
UNION ALL
SELECT CSR.TOWN_CD_C, CSR.CREATED_DT AS CREATED_DT,
0 As NUM1,
COUNT(DISTINCT SERVICE_REQUEST_ID_N) As NUM2,
0 AS TotPendComp,
0 AS ComplPendingPeriod,
0 AS NUM4,
0 AS NUM6,
0 As NUM7
FROM "XXXXX"."CR_SERVICE_REQUEST" as CSR
WHERE CREATED_DT >= :FROMDATE AND CREATED_DT <= :TODATE
GROUP BY CSR.TOWN_CD_C,CSR.CREATED_DT
UNION ALL
SELECT CSR.TOWN_CD_C, CSR.CREATED_DT AS CREATED_DT,
0 AS NUM1,
0 As NUM2,
0 AS TotPendComp,
0 AS ComplPendingPeriod,
COUNT(DISTINCT CSR.SERVICE_REQUEST_ID_N) As NUM4,
0 AS NUM6,
0 As NUM7
FROM "XXXXX"."CR_SERVICE_REQUEST" as CSR
INNER JOIN "HANAPRD"."CR_SERVICE_REQUEST_LOG" as CSRL ON
CSR.REQUEST_CURRENT_LOG_ID_N = CSRL.SERVICE_REQUEST_LOG_ID_N
WHERE CSRL.CREATED_DT >= :FROMDATE AND CSRL.CREATED_DT <= :TODATE
AND CSRL.REQUEST_STATUS_C IN ('Closed')
GROUP BY CSR.TOWN_CD_C,CSR.CREATED_DT
UNION ALL
SELECT CSR.TOWN_CD_C,CSR.CREATED_DT AS CREATED_DT,
0 AS NUM1,
0 As NUM2,
0 AS TotPendComp,
0 AS ComplPendingPeriod,
0 AS NUM4,
0 AS NUM6,
0 As NUM7
FROM "XXXXX"."CR_SERVICE_REQUEST" as CSR
INNER JOIN "HANAPRD"."CR_SERVICE_REQUEST_LOG" as CSRL ON
CSR.REQUEST_CURRENT_LOG_ID_N = CSRL.SERVICE_REQUEST_LOG_ID_N
AND CSRL.REQUEST_STATUS_C NOT IN ('Closed')
WHERE CSR.CREATED_DT >= :FROMDATE AND CSR.CREATED_DT <= :TODATE
GROUP BY CSR.TOWN_CD_C,CSR.CREATED_DT
UNION ALL
SELECT CSR.TOWN_CD_C,CSR.CREATED_DT AS CREATED_DT,
0 AS NUM1,
0 As NUM2,
0 As TotPendComp,
0 AS ComplPendingPeriod,
0 AS NUM4,
0 AS NUM6,
COUNT(DISTINCT CE.SERVICE_REQUEST_ID_N) As NUM7
FROM "XXXXX"."CR_ESCALATION" as CE INNER JOIN "XXXXX"."CR_SERVICE_REQUEST" as CSR
ON CE.SERVICE_REQUEST_ID_N = CSR.SERVICE_REQUEST_ID_N
WHERE CSR.CREATED_DT >= :FROMDATE AND CSR.CREATED_DT <= :TODATE
AND CE.SERVICE_REQUEST_ID_N IN
( SELECT CSRL.SERVICE_REQUEST_ID_N FROM "XXXXX"."CR_SERVICE_REQUEST" as CSR
INNER JOIN "XXXXX"."CR_SERVICE_REQUEST_LOG" as CSRL
ON CSR.REQUEST_CURRENT_LOG_ID_N = CSRL.SERVICE_REQUEST_LOG_ID_N
WHERE CSRL.CREATED_DT >= :FROMDATE AND CSRL.CREATED_DT <=:TODATE
AND CSRL.REQUEST_STATUS_C IN ('Closed'))
Group By CSR.TOWN_CD_C,CSR.CREATED_DT
) As CompStats
RIGHT OUTER JOIN "HANAPRD"."GIS_TOWN" GT ON CompStats.TOWN_CD_C = GT.TOWN_CD_C
WHERE GT.TOWN_NAME_C NOT IN ('FRANCHISE NAGPUR','AURANGABAD','JALGAON','NON RAPDRP')
Group By CompStats.TOWN_CD_C, GT.TOWN_NAME_C
Order By GT.TOWN_NAME_C;
What are you looking for ?
Regards,
Krishna Tangudu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
71 | |
26 | |
10 | |
9 | |
7 | |
6 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.