cancel
Showing results for 
Search instead for 
Did you mean: 

HANA Script based calculation view with input para meter

Former Member
0 Kudos

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;

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member182302
Active Contributor
0 Kudos

What are you looking for ?

Regards,

Krishna Tangudu