sql join morethen 5 tables

Question

 

 

 

 Dear frnds,

 

Am create sql view joins morethan 5 tables

 

any simple methoed is there !!!

 

any ideas ?? 

 

for reference here with below my sql query  

 

CREATE VIEW SIS_YEAR

AS

SELECT G4.GO_HQ,G4.AGM_HQ,G4.RGM_HQ,G4.DATE,G4.Week,G4.Week1,G4.Month,ISNULL(ROUND((SUM(G4.SALES_TGT)/6),0 ),0) AS SALES_TGT,ISNULL(CAST(( G4.Order_Fulfillment) as decimal (12,2)),0) AS SALES_ACH,ISNULL(CAST((SUM(G4.Order_Fulfillment)/sum(G4.SALES_TGT)) AS decimal(12,2)),0)AS GROWTH,ISNULL(G4.Calls_Made,0) as Calls_Made,ISNULL(G4.Productive_calls,0) AS Productive_calls,ISNULL(DROP_SIZE,0) as DROP_SIZE,ISNULL(CAST(SUM(G4.Order_Fulfillment)/SUM(G4.Productive_calls) AS decimal(12,2)),0) AS DROP_SIZE_ACH,ISNULL(G4.LPSC_TGT,0) as LPSC_TGT,ISNULL(CAST(SUM(G4.TLSD)/SUM(G4.Productive_calls) AS decimal(12,2)),0) AS LPSC_ACH,ISNULL(G4.TLSD_TGT,0) as TLSD_TGT,ISNULL(G4.TLSD,0) AS TLSD_ACH FROM

(SELECT GSSS.GO_HQ,GSSS.AGM_HQ,GSSS.RGM_HQ,GSSS.DATE,GSSS.Week,GSSS.Week1,GSSS.Month,SUM(GSSS.TGT) AS SALES_TGT,GSSS.Calls_Made,GSSS.Productive_calls,GSSS.TLSD,DR.DROP_SIZE,DR.LPSC AS LPSC_TGT,DR.TLSD_TARGET as TLSD_TGT,GSSS.New_Outlets,GSSS.[Total_Orders_Taken ],GSSS.Order_Fulfillment,GSSS.[Pending_Orders ],GSSS.[Orders_Rejected ] FROM

(SELECT GO_HQ,AGM_HQ,RGM_HQ,DATE,GSS.Week,GSS.Week1,GSS.Month,Calls_Made,Productive_calls,TLSD,New_Outlets,[Total_Orders_Taken ],Order_Fulfillment,[Pending_Orders ],[Orders_Rejected ],SUM(GTGT.SEC_SAL_TAR_QTY) AS TGT FROM

(SELECT GO_HQ,AGM_HQ,RGM_HQ,GS.DATE,Week,Week1,SC.Month,Calls_Made,Productive_calls,TLSD,New_Outlets,[Total_Orders_Taken ],Order_Fulfillment,[Pending_Orders ],[Orders_Rejected ] FROM

(SELECT B.GO_HQ,B.AGM_HQ,B.RGM_HQ,B.DATE,B.Calls_Made,B.Productive_calls,B.TLSD,b.New_Outlets,SUM(ORFMR.[Order_Fulfillment ]) AS [Order_Fulfillment],SUM(ORFMR.[Total_Orders_Taken ]) AS [Total_Orders_Taken ],ORFMR.[Orders_Rejected ],ORFMR.[Pending_Orders ] FROM

(SELECT A.GO_HQ,A.AGM_HQ,A.RGM_HQ,SUM(DGO.Total_Calls)AS [Calls_Made], SUM (DGO.Productive_Calls)AS [Productive_calls],SUM(DGO.TLSD) AS TLSD,SUM(DGO.New_Outlets_Activated) AS [New_Outlets] ,Date AS DATE FROM

(SELECT CP.GO_HQ,CP.AGM_HQ,CP.RGM_HQ FROM CP_MASTER CP

GROUP BY CP.GO_HQ,CP.AGM_HQ,CP.RGM_HQ

)AS A

LEFT JOIN DAILY_GO_REPORT DGO ON (A.GO_HQ=DGO.GO_HQ)

GROUP BY A.RGM_HQ,A.AGM_HQ,A.GO_HQ,DGO.Date) AS B

LEFT JOIN ORDER_FULFILLMENT_REPORT ORFMR ON (B.GO_HQ=ORFMR.[GO_HQ ]) and (b.DATE=ORFMR.DATE)

GROUP BY B.RGM_HQ,B.AGM_HQ,B.GO_HQ,B.DATE,B.Calls_Made,B.Productive_calls,B.TLSD,B.New_Outlets,[Order_Fulfillment ],[Total_Orders_Taken ],[Pending_Orders ],[Orders_Rejected ]

) AS GS

LEFT JOIN SALES_CALENDAR SC ON (GS.DATE=SC.DATE)

GROUP BY GS.RGM_HQ,GS.AGM_HQ,GS.GO_HQ,GS.DATE,SC.DATE,SC.Week1,SC.Week,SC.Month,GS.Calls_Made,GS.Productive_calls,GS.TLSD,GS.New_Outlets,GS.Order_Fulfillment,GS.[Total_Orders_Taken ],gs.[Pending_Orders ],gs.[Orders_Rejected ]

) AS GSS

LEFT JOIN GO_TARGET GTGT ON (GSS.GO_HQ=GTGT.GO_HQ_Name) AND (GSS.Week1=GTGT.WEEK)

GROUP BY GSS.RGM_HQ,GSS.AGM_HQ,GSS.GO_HQ,GSS.DATE,GSS.Week1,GSS.Week,GSS.Month,GSS.Calls_Made,GSS.Productive_calls,GSS.TLSD,GSS.New_Outlets,GSS.Order_Fulfillment,GSS.[Total_Orders_Taken ],GSS.[Pending_Orders ],GSS.[Orders_Rejected ]

) AS GSSS LEFT JOIN

DROP_SIZE_TARGET DR ON (GSSS.GO_HQ=DR.GO_HQ)

GROUP BY GSSS.RGM_HQ,GSSS.AGM_HQ,GSSS.GO_HQ,GSSS.DATE,GSSS.Week1,GSSS.Week,GSSS.Month,GSSS.Calls_Made,GSSS.Productive_calls,GSSS.New_Outlets,GSSS.Order_Fulfillment,GSSS.[Total_Orders_Taken ],GSSS.[Orders_Rejected ],GSSS.[Pending_Orders ],GSSS.TLSD,DR.DROP_SIZE,DR.LPSC ,DR.TLSD_TARGET

) AS G4

GROUP BY G4.GO_HQ,G4.AGM_HQ,G4.RGM_HQ,G4.DATE,G4.Week,G4.Week1,G4.Month,G4.Calls_Made,G4.Productive_calls,G4.TLSD,G4.New_Outlets,G4.[Total_Orders_Taken ],G4.Order_Fulfillment,G4.[Pending_Orders ],G4.[Orders_Rejected ],G4.DROP_SIZE,G4.LPSC_TGT,G4.TLSD_TGT