Kumaran P

Kumaran P

  • NA
  • 102
  • 9.5k

SQL unable to convert asp dot net

Apr 12 2017 3:37 AM
dear frdns ,
 
any one help me for below
 
am wrinting sql qeury for below but am unable to connect asp dot net
 

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

   
 
 i want below result in asp dot net web page
 
 
 
 
 
 
 

Answers (4)