select	TA.[Sales Analysis No],
		TA.[Customer No],
		TA.[Ship-to Code],
		TA.[Location Code],
		cast(round(isnull(TB.[Retail Amount], 0), 2) as decimal(7,2)) [Legacy Retail Amount],
		cast(isnull(TB.[Quantity Transfer In], 0) /* [Legacy Sales Quantity] */ +
		isnull(TC.[Quantity], 0) /* [Transfer Quantity In Previous] */ - 
		isnull(TE.[Quantity], 0) /* [Transfer Quantity Out Previous] */ as int) [Quantity Shipped Previous],
		cast(isnull(TB.[Quantity Sales], 0) /* [Legacy Sales Quantity] */ +
		isnull(TG.[Quantity], 0) /* [Invoiced Quantity Previous] */ as int) [Quantity Sold Previous],
		cast(isnull(TD.[Quantity], 0) /* [Transfer Quantity In Current] */ -
		isnull(TF.[Quantity], 0) /* [Transfer Quantity Out Current] */ as int) [Quantity Shipped Current],
		cast(isnull(TH.[Quantity], 0) /* [Invoiced Quantity Current] */ as int) [Quantity Sold Current]
from   (	select	A.[No_] [Sales Analysis No],
					B.[Customer No_] [Customer No],
					B.[Ship-to Code] [Ship-to Code],
					C.[Location Code] [Location Code],
					D.[Code] [Dimension Code]
			from	[Green Circle Growers$GCG Sales Analysis] A (nolock),
					[Green Circle Growers$GCG Sales Analysis Ship-to] B (nolock),
					[Green Circle Growers$Ship-to Address] C (nolock),
					[Green Circle Growers$Dimension] D (nolock)
			where	A.[No_] = @SalesAnalysisNo
			and		B.[Sales Analysis No_] = A.[No_]
			and		C.[Customer No_] = B.[Customer No_]
			and		C.[Code] = B.[Ship-to Code]
			and		D.[Code] = 'HDANALYSIS') as TA
Left Outer Join
	   (	select	A.[No_] [Sales Analysis No],
					A.[Description],
					B.[Customer No_] [Customer No],
					B.[Ship-to Code],
					C.[Location Code],
					D.[Code] [Dimension Code],
					sum(case H.[Item Ledger Entry Type] when 1 then abs(H.[Quantity]) else 0 end) [Quantity Sales],
					sum(case H.[Item Ledger Entry Type] when 4 then abs(H.[Quantity]) else 0 end) [Quantity Transfer In],
					sum(case cast(abs(H.[Quantity]) as int) when 0 then 0 else H.[Retail Amount (Actual)] end) / sum(case cast(abs(H.[Quantity]) as int) when 0 then 0 else abs(H.[Quantity]) end) [Retail Amount]
			from	[Green Circle Growers$GCG Sales Analysis] A (nolock),
					[Green Circle Growers$GCG Sales Analysis Ship-to] B (nolock),
					[Green Circle Growers$Ship-to Address] C (nolock),
					[Green Circle Growers$Dimension] D (nolock),
					[Green Circle Growers$Dimension Value] E (nolock),
					[Green Circle Growers$Default Dimension] F (nolock),
					[Green Circle Growers$Item] G (nolock),
					[Green Circle Growers$GCG Item Analysis View Entry] H (nolock)
			where	A.[No_] = @SalesAnalysisNo
			and		B.[Sales Analysis No_] = A.[No_]
			and		C.[Customer No_] = B.[Customer No_]
			and		C.[Code] = B.[Ship-to Code]
			and		D.[Code] = 'HDANALYSIS'
			and		E.[Dimension Code] = D.[Code]
			and		E.[Code] = @ItemGroupCode
			and		F.[Table ID] = 27 /**/
			and		F.[Dimension Code] = D.[Code]
			and     F.[Dimension Value Code] = E.[Code]
			and		F.[No_] = G.[No_]
			and		H.[Source No_] = C.[Customer No_]
			and		H.[Location Code] = C.[Location Code]
			and		H.[Analysis Area] = 2 /*OptionString=Inventory*/
			and		H.[Analysis View Code] = 'SELLTHRU'
			and		H.[Item Ledger Entry Type] in (1,4) /*OptionString in (Sales, Transfer)*/
			and		H.[Item No_] = G.[No_]
			and		H.[Posting Date] between @PrevStartDate and @PrevEndDate
			group by	A.[No_],
						A.[Description],
						B.[Customer No_],
						B.[Ship-to Code],
						C.[Location Code],
						D.[Code]) as TB
on		TB.[Sales Analysis No] = TA.[Sales Analysis No]
and		TB.[Customer No] = TA.[Customer No]
and		TB.[Ship-to Code] = TA.[Ship-to Code]
and		TB.[Location Code] = TA.[Location Code]
and		TB.[Dimension Code] = TA.[Dimension Code]
Left Outer Join
		(	select A.[No_] [Sales Analysis No],
					A.[Description],
					B.[Customer No_] [Customer No],
					B.[Ship-to Code],
					C.[Location Code],
					D.[Code] [Dimension Code],
					sum(Quantity) [Quantity]
			from   [Green Circle Growers$GCG Sales Analysis] A (nolock),
					[Green Circle Growers$GCG Sales Analysis Ship-to] B (nolock),
					[Green Circle Growers$Ship-to Address] C (nolock),
					[Green Circle Growers$Dimension] D (nolock),
					[Green Circle Growers$Dimension Value] E (nolock),
					[Green Circle Growers$Default Dimension] F (nolock),
					[Green Circle Growers$Item] G (nolock),
					[Green Circle Growers$Transfer Shipment Header] H (nolock),
					[Green Circle Growers$Transfer Shipment Line] I (nolock)
			where  A.[No_] = @SalesAnalysisNo
			and    B.[Sales Analysis No_] = A.[No_]
			and    C.[Customer No_] = B.[Customer No_]
			and    C.[Code] = B.[Ship-to Code]
			and    D.[Code] = 'HDANALYSIS'
			and    E.[Dimension Code] = D.[Code]
			and    E.[Code] = @ItemGroupCode
			and    F.[Table ID] = 27 /**/
			and    F.[Dimension Code] = D.[Code]
			and    F.[Dimension Value Code] = E.[Code]
			and    F.[No_] = G.[No_]
			and    H.[Transfer-to Code] = C.[Location Code]
			and    H.[Posting Date] between @PrevStartDate and @PrevEndDate
			and    I.[Document No_] = H.[No_]
			and    I.[Transfer-to Code] = H.[Transfer-to Code]
			and    I.[Item No_] = G.[No_]
			group by A.[No_],
					A.[Description],
					B.[Customer No_],
					B.[Ship-to Code],
					C.[Location Code],
					D.[Code]) as TC
on		TC.[Sales Analysis No] = TA.[Sales Analysis No]
and		TC.[Customer No] = TA.[Customer No]
and		TC.[Ship-to Code] = TA.[Ship-to Code]
and		TC.[Location Code] = TA.[Location Code]
and		TC.[Dimension Code] = TA.[Dimension Code]
Left Outer Join
		(	select A.[No_] [Sales Analysis No],
					A.[Description],
					B.[Customer No_] [Customer No],
					B.[Ship-to Code],
					C.[Location Code],
					D.[Code] [Dimension Code],
					sum(Quantity) [Quantity]
			from   [Green Circle Growers$GCG Sales Analysis] A (nolock),
					[Green Circle Growers$GCG Sales Analysis Ship-to] B (nolock),
					[Green Circle Growers$Ship-to Address] C (nolock),
					[Green Circle Growers$Dimension] D (nolock),
					[Green Circle Growers$Dimension Value] E (nolock),
					[Green Circle Growers$Default Dimension] F (nolock),
					[Green Circle Growers$Item] G (nolock),
					[Green Circle Growers$Transfer Shipment Header] H (nolock),
					[Green Circle Growers$Transfer Shipment Line] I (nolock)
			where  A.[No_] = @SalesAnalysisNo
			and    B.[Sales Analysis No_] = A.[No_]
			and    C.[Customer No_] = B.[Customer No_]
			and    C.[Code] = B.[Ship-to Code]
			and    D.[Code] = 'HDANALYSIS'
			and    E.[Dimension Code] = D.[Code]
			and    E.[Code] = @ItemGroupCode
			and    F.[Table ID] = 27 /**/
			and    F.[Dimension Code] = D.[Code]
			and    F.[Dimension Value Code] = E.[Code]
			and    F.[No_] = G.[No_]
			and    H.[Transfer-to Code] = C.[Location Code]
			and    H.[Posting Date] between @CurrStartDate and @CurrEndDate
			and    I.[Document No_] = H.[No_]
			and    I.[Transfer-to Code] = H.[Transfer-to Code]
			and    I.[Item No_] = G.[No_]
			group by A.[No_],
					A.[Description],
					B.[Customer No_],
					B.[Ship-to Code],
					C.[Location Code],
					D.[Code]) as TD
on		TD.[Sales Analysis No] = TA.[Sales Analysis No]
and		TD.[Customer No] = TA.[Customer No]
and		TD.[Ship-to Code] = TA.[Ship-to Code]
and		TD.[Location Code] = TA.[Location Code]
and		TD.[Dimension Code] = TA.[Dimension Code]
Left Outer Join
		(	select A.[No_] [Sales Analysis No],
					A.[Description],
					B.[Customer No_] [Customer No],
					B.[Ship-to Code],
					C.[Location Code],
					D.[Code] [Dimension Code],
					sum(Quantity) [Quantity]
			from   [Green Circle Growers$GCG Sales Analysis] A (nolock),
					[Green Circle Growers$GCG Sales Analysis Ship-to] B (nolock),
					[Green Circle Growers$Ship-to Address] C (nolock),
					[Green Circle Growers$Dimension] D (nolock),
					[Green Circle Growers$Dimension Value] E (nolock),
					[Green Circle Growers$Default Dimension] F (nolock),
					[Green Circle Growers$Item] G (nolock),
					[Green Circle Growers$Transfer Shipment Header] H (nolock),
					[Green Circle Growers$Transfer Shipment Line] I (nolock)
			where  A.[No_] = @SalesAnalysisNo
			and    B.[Sales Analysis No_] = A.[No_]
			and    C.[Customer No_] = B.[Customer No_]
			and    C.[Code] = B.[Ship-to Code]
			and    D.[Code] = 'HDANALYSIS'
			and    E.[Dimension Code] = D.[Code]
			and    E.[Code] = @ItemGroupCode
			and    F.[Table ID] = 27 /**/
			and    F.[Dimension Code] = D.[Code]
			and    F.[Dimension Value Code] = E.[Code]
			and    F.[No_] = G.[No_]
			and    H.[Transfer-from Code] = C.[Location Code]
			and    H.[Posting Date] between @PrevStartDate and @PrevEndDate
			and    I.[Document No_] = H.[No_]
			and    I.[Transfer-from Code] = H.[Transfer-from Code]
			and    I.[Item No_] = G.[No_]
			group by A.[No_],
					A.[Description],
					B.[Customer No_],
					B.[Ship-to Code],
					C.[Location Code],
					D.[Code]) as TE
on		TE.[Sales Analysis No] = TA.[Sales Analysis No]
and		TE.[Customer No] = TA.[Customer No]
and		TE.[Ship-to Code] = TA.[Ship-to Code]
and		TE.[Location Code] = TA.[Location Code]
and		TE.[Dimension Code] = TA.[Dimension Code]
Left Outer Join
		(	select A.[No_] [Sales Analysis No],
					A.[Description],
					B.[Customer No_] [Customer No],
					B.[Ship-to Code],
					C.[Location Code],
					D.[Code] [Dimension Code],
					sum(Quantity) [Quantity]
			from   [Green Circle Growers$GCG Sales Analysis] A (nolock),
					[Green Circle Growers$GCG Sales Analysis Ship-to] B (nolock),
					[Green Circle Growers$Ship-to Address] C (nolock),
					[Green Circle Growers$Dimension] D (nolock),
					[Green Circle Growers$Dimension Value] E (nolock),
					[Green Circle Growers$Default Dimension] F (nolock),
					[Green Circle Growers$Item] G (nolock),
					[Green Circle Growers$Transfer Shipment Header] H (nolock),
					[Green Circle Growers$Transfer Shipment Line] I (nolock)
			where  A.[No_] = @SalesAnalysisNo
			and    B.[Sales Analysis No_] = A.[No_]
			and    C.[Customer No_] = B.[Customer No_]
			and    C.[Code] = B.[Ship-to Code]
			and    D.[Code] = 'HDANALYSIS'
			and    E.[Dimension Code] = D.[Code]
			and    E.[Code] = @ItemGroupCode
			and    F.[Table ID] = 27 /**/
			and    F.[Dimension Code] = D.[Code]
			and    F.[Dimension Value Code] = E.[Code]
			and    F.[No_] = G.[No_]
			and    H.[Transfer-from Code] = C.[Location Code]
			and    H.[Posting Date] between @CurrStartDate and @CurrEndDate
			and    I.[Document No_] = H.[No_]
			and    I.[Transfer-from Code] = H.[Transfer-from Code]
			and    I.[Item No_] = G.[No_]
			group by A.[No_],
					A.[Description],
					B.[Customer No_],
					B.[Ship-to Code],
					C.[Location Code],
					D.[Code]) as TF
on		TF.[Sales Analysis No] = TA.[Sales Analysis No]
and		TF.[Customer No] = TA.[Customer No]
and		TF.[Ship-to Code] = TA.[Ship-to Code]
and		TF.[Location Code] = TA.[Location Code]
and		TF.[Dimension Code] = TA.[Dimension Code]
Left Outer Join
		(	select A.[No_] [Sales Analysis No],
					A.[Description],
					B.[Customer No_] [Customer No],
					B.[Ship-to Code],
					C.[Location Code],
					D.[Code] [Dimension Code],
					sum(Quantity) [Quantity]
			from   [Green Circle Growers$GCG Sales Analysis] A (nolock),
					[Green Circle Growers$GCG Sales Analysis Ship-to] B (nolock),
					[Green Circle Growers$Ship-to Address] C (nolock),
					[Green Circle Growers$Dimension] D (nolock),
					[Green Circle Growers$Dimension Value] E (nolock),
					[Green Circle Growers$Default Dimension] F (nolock),
					[Green Circle Growers$Item] G (nolock),
					[Green Circle Growers$Sales Invoice Line] H (nolock)
			where  A.[No_] = @SalesAnalysisNo
			and    B.[Sales Analysis No_] = A.[No_]
			and    C.[Customer No_] = B.[Customer No_]
			and    C.[Code] = B.[Ship-to Code]
			and    D.[Code] = 'HDANALYSIS'
			and    E.[Dimension Code] = D.[Code]
			and    E.[Code] = @ItemGroupCode
			and    F.[Table ID] = 27 /**/
			and    F.[Dimension Code] = D.[Code]
			and    F.[Dimension Value Code] = E.[Code]
			and    F.[No_] = G.[No_]
			and    H.[No_] = G.[No_]
			and    H.[Sell-to Customer No_] = C.[Customer No_]
			and    H.[Ship-to Code] = C.[Code]
			and    H.[Posting Date] between @PrevStartDate and @PrevEndDate
			group by A.[No_],
					A.[Description],
					B.[Customer No_],
					B.[Ship-to Code],
					C.[Location Code],
					D.[Code]) as TG
on		TG.[Sales Analysis No] = TA.[Sales Analysis No]
and		TG.[Customer No] = TA.[Customer No]
and		TG.[Ship-to Code] = TA.[Ship-to Code]
and		TG.[Location Code] = TA.[Location Code]
and		TG.[Dimension Code] = TA.[Dimension Code]
Left Outer Join
		(	select A.[No_] [Sales Analysis No],
					A.[Description],
					B.[Customer No_] [Customer No],
					B.[Ship-to Code],
					C.[Location Code],
					D.[Code] [Dimension Code],
					sum(H.[Quantity (Base)]) [Quantity]
			from   [Green Circle Growers$GCG Sales Analysis] A (nolock),
					[Green Circle Growers$GCG Sales Analysis Ship-to] B (nolock),
					[Green Circle Growers$Ship-to Address] C (nolock),
					[Green Circle Growers$Dimension] D (nolock),
					[Green Circle Growers$Dimension Value] E (nolock),
					[Green Circle Growers$Default Dimension] F (nolock),
					[Green Circle Growers$Item] G (nolock),
					[Green Circle Growers$Sales Invoice Line] H (nolock)
			where  A.[No_] = @SalesAnalysisNo
			and    B.[Sales Analysis No_] = A.[No_]
			and    C.[Customer No_] = B.[Customer No_]
			and    C.[Code] = B.[Ship-to Code]
			and    D.[Code] = 'HDANALYSIS'
			and    E.[Dimension Code] = D.[Code]
			and    E.[Code] = @ItemGroupCode
			and    F.[Table ID] = 27 /**/
			and    F.[Dimension Code] = D.[Code]
			and    F.[Dimension Value Code] = E.[Code]
			and    F.[No_] = G.[No_]
			and    H.[No_] = G.[No_]
			and    H.[Sell-to Customer No_] = C.[Customer No_]
			and    H.[Ship-to Code] = C.[Code]
			and    H.[Posting Date] between @CurrStartDate and @CurrEndDate
			group by A.[No_],
					A.[Description],
					B.[Customer No_],
					B.[Ship-to Code],
					C.[Location Code],
					D.[Code]) as TH
on		TH.[Sales Analysis No] = TA.[Sales Analysis No]
and		TH.[Customer No] = TA.[Customer No]
and		TH.[Ship-to Code] = TA.[Ship-to Code]
and		TH.[Location Code] = TA.[Location Code]
and		TH.[Dimension Code] = TA.[Dimension Code]