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