SELECT
E.Date,
E.Week,
E.Month,
E.[RGM HQ Name] AS RGM_HQ ,
E.[AGM HQ Name] AS AGM_HQ ,
E.[GO HQ Name] AS GO_HQ ,
E.SEC_TGT AS SALES_TGT ,
CAST((E.SECONDARY) AS decimal(12,0)) AS SALES_ACH,
ISNULL(CAST((SUM(E.SECONDARY)/(NULLIF(sum(E.SEC_TGT),0))) AS decimal(12,2)),0)AS GROWTH,
E.TC AS Calls_Made,
E.PC AS Productive_calls,
CAST(DRT.[LPSC Target]AS decimal(12,1)) AS LPSC_TGT,
ISNULL(CAST((SUM(E.TLSD)/(NULLIF(sum(E.PC),0))) AS decimal(12,2)),0)AS TLSD_ACH,
CAST(drt.[TLSD Target] AS decimal(12,0)) AS TLSD_TGT,
E.TLSD AS TLSD_ACH,
CAST(DRT.[Drop Size Target] AS decimal(12,2)) AS DROP_SIZE,
ISNULL(CAST((SUM(E.SECONDARY)/(NULLIF(sum(E.PC),0))) AS decimal(12,2)),0) AS DROP_SIZE_ACH
FROM
(SELECT D.*,CAST((ISNULL(SUM(GTGT.SEC_SAL_TAR_QTY ),0)/7) AS decimal(12,0)) AS SEC_TGT FROM
(SELECT C.*,SC.Week,SC.Week1,SC.Month FROM
(SELECT B.Date,B.[RGM HQ Name],B.[AGM HQ Name],B.[GO HQ Name],B.TC,B.PC,B.TLSD,B.NEW_OUTLET,ISNULL(SUM(ORFMR.Total_Orders_Taken),0) AS ORDER_TAKEN ,ISNULL(SUM(ORFMR.Orders_Fulfilled),0) AS FULFTILED,ISNULL(SUM(ORFMR.Order_Fulfillment),0)AS ORDER_CONV,ISNULL(SUM(ORFMR.Order_Secondary),0) AS SECONDARY,ISNULL(SUM(ORFMR.Orders_Rejected),0) AS REJECTED ,ISNULL(SUM(ORFMR.Pending_Orders),0) AS PENDING FROM
(SELECT A.[RGM HQ Name],A.[AGM HQ Name],A.[GO HQ Name],DGO.Date,ISNULL(SUM(DGO.Total_Calls),0)AS TC,ISNULL(SUM(DGO.Productive_Calls),0) AS PC,ISNULL(SUM(DGO.TLSD),0) AS TLSD,ISNULL(SUM(DGO.New_Outlets_Activated),0) AS NEW_OUTLET FROM
(SELECT GH.[RGM HQ Name],GH.[AGM HQ Name],GH.[GO HQ Name] FROM GO_MASTER GH
WHERE [BH Territory]='TN1') AS A
LEFT JOIN DAILY_GO_REPORT DGO ON (DGO.GO_HQ=A.[GO HQ Name])
GROUP BY A.[RGM HQ Name],A.[AGM HQ Name],A.[GO HQ Name],DGO.Date
) AS B LEFT JOIN ORDER_FULFILLMENT_REPORT ORFMR ON (ORFMR.GO_HQ=B.[GO HQ Name]) and (ORFMR.DATE=B.Date)
GROUP BY B.[RGM HQ Name],B.[AGM HQ Name],B.[GO HQ Name],B.Date,B.TC,B.PC,B.TLSD,B.NEW_OUTLET
) AS C LEFT JOIN SALES_CALENDAR SC ON (SC.DATE=C.DATE)
GROUP BY C.[RGM HQ Name],C.[AGM HQ Name],C.[GO HQ Name],C.Date,C.TC,C.PC,C.TLSD,C.NEW_OUTLET,C.ORDER_TAKEN,C.ORDER_CONV,C.SECONDARY,C.PENDING,C.FULFTILED,C.REJECTED,SC.Week,SC.Week1,SC.Month) AS D
LEFT JOIN GO_TARGET GTGT ON (GTGT.GO_HQ_Name=D.[GO HQ Name]) AND (GTGT.WEEK=D.Week1)
GROUP BY D.[RGM HQ Name],D.[AGM HQ Name],D.[GO HQ Name],D.Date,D.TC,D.PC,D.TLSD,D.NEW_OUTLET,D.ORDER_TAKEN,D.ORDER_CONV,D.SECONDARY,D.PENDING,D.FULFTILED,D.REJECTED,D.Week,D.Week1,D.Month
)AS E LEFT JOIN DROP_SIZE_AND_LPSC_TARGET DRT ON (DRT.[HQ Name]=E.[GO HQ Name] ) and (DRT.Week=e.Week)
GROUP BY E.[RGM HQ Name],E.[AGM HQ Name],E.[GO HQ Name],E.Date,E.TC,E.PC,E.TLSD,E.NEW_OUTLET,E.ORDER_TAKEN,E.ORDER_CONV,E.SECONDARY,E.PENDING,E.FULFTILED,E.REJECTED,E.Week,E.Week1,E.Month,e.SEC_TGT,DRT.[Drop Size Target],DRT.[LPSC Target],drt.[TLSD Target],DRT.[Total call Target]
ORDER BY E.[RGM HQ Name],E.[AGM HQ Name],E.[GO HQ Name],E.Date
my Table names
-
Am getting below result in sql select query
