CREATE PROCEDURE [dbo].[sp_Get_DynSalesAndBudgetFigures]
(
	@StartDate AS VarChar(10), 
	@EndDate AS VarChar(10)
)
AS 

/*
Testing variables
DECLARE @StartDate AS VarChar(10)
DECLARE @EndDate AS VarChar(10)
*/
DECLARE @StartMonth AS VARCHAR(50)
DECLARE @EndMonth AS VARCHAR(50)
DECLARE @StartYear AS VARCHAR(50)
DECLARE @EndYear AS VARCHAR(50)
DECLARE @FinYear AS VARCHAR(4)
DECLARE @SDate AS VARCHAR(50)
DECLARE @EDate AS VARCHAR(50)

-- DECLARE @QueryString AS VARCHAR(8000)
-- DECLARE @ConditionString AS VARCHAR(1000) 
/*
Testing variables
SET @StartDate = '01/08/2003'
SET @EndDate = '31/08/2003'
*/
SET @SDate = CONVERT(DATETIME, @StartDate, 103)

IF LEN(LTRIM(RTRIM(@EndDate))) = 0
SET @EDate = CONVERT(DATETIME, @StartDate, 103) + 6 
ELSE
SET @EDate = CONVERT(DATETIME, @EndDate, 103)

SET @StartMonth = MONTH(CONVERT(DATETIME, @StartDate, 103))
SET @EndMonth = MONTH(CONVERT(DATETIME, @EndDate, 103))
SET @StartYear = YEAR(CONVERT(DATETIME, @StartDate, 103))
SET @EndYear = YEAR(CONVERT(DATETIME, @EndDate, 103))

-- Calculate the Financial year
IF YEAR(CONVERT(DATETIME, @StartDate, 103)) = YEAR(CONVERT(DATETIME, @EndDate, 103))
BEGIN

-- SET @ConditionString = 'WHERE db..DynBudget.Month >= ' + @StartMonth + ' AND 
-- db..DynBudget.Month <= ' + @EndMonth 

IF MONTH(CONVERT(DATETIME, @StartDate, 103)) < 7
SET @FinYear = YEAR(CONVERT(DATETIME, @StartDate, 103))
ELSE
SET @FinYear = YEAR(CONVERT(DATETIME, @StartDate, 103)) + 1
END
ELSE
BEGIN

-- SET @ConditionString = 'WHERE ((db..DynBudget.Month >= ' + @StartMonth + ') 
-- AND (db..DynBudget.Year = ' + @StartYear + '))
-- OR ((db..DynBudget.Month <= ' + @EndMonth + ') AND (db..DynBudget.Year = ' + @EndYear + '))' 

IF MONTH(CONVERT(DATETIME, @EndDate, 103)) < 7
SET @FinYear = YEAR(CONVERT(DATETIME, @EndDate, 103))
END

-- SET @QueryString = 
SELECT ClientID, DeptID, SUM(Amount) AS Amount INTO #Bdgt 
FROM db..DynBudget 

WHERE ((db..DynBudget.Month >= @StartMonth ) AND (db..DynBudget.Year = @StartYear ))
OR ((db..DynBudget.Month <= @EndMonth ) AND (db..DynBudget.Year = @EndYear ))

	AND db..DynBudget.FinYear = @FinYear 

GROUP BY ClientID, DeptID

CREATE TABLE #GL
(
	... Column Definitions here ...
	... With PK definition ...
)
INSERT INTO #GL

SELECT *  ... column list here ...
-- INTO #GL
FROM	CIMEL04.CDSYD.DBO.GL20000 GL20000

WHERE	    GL20000.trxdate < ( @EDate + 1) 
	AND GL20000.trxdate >= @SDate 

ORDER BY GL20000.ORMSTRID


CREATE TABLE #GLCL
(
	... Column Definitions here ...
	... With PK definition ...
	Client	datatype NOT NULL PRIMARY KEY,
	ClientName datatype
)
INSERT INTO #GLCL

SELECT DISTINCT 
	RM00101.Custnmbr AS Client, 
	RM00101.CustName AS ClientName 
-- INTO #GLCL 
FROM	CIMEL04.CDSYD.DBO.RM00101 RM00101 
ORDER BY RM00101.Custnmbr


CREATE TABLE #BdgLaser
(
	... Column Definitions here ...
	... With PK definition ...
	ClientID	datatype NOT NULL PRIMARY KEY,
	Amount datatype
)
INSERT INTO #BdgLaser

SELECT	ClientID, 
	SUM(Amount) AS Amount
-- INTO #BdgLaser
FROM #Bdgt 
WHERE	(  #Bdgt.DeptID = '14' 
	OR #Bdgt.DeptID = '09')
GROUP BY ClientID


CREATE TABLE #GL00100
(
	... Column Definitions here ...
	... With PK definition ...
	... Make sure that Actnumbr_2 and Actnumbr_3 are stored as INT types ...
	... and remove all the TRIM and CAST on these columns below ...
	... Make sure Actnumbr_2 and Actnumbr_3 are indexed ...
)
INSERT INTO #GL00100

SELECT *  ... column list here ...
-- INTO #GL00100
FROM	CIMEL04.CDSYD.DBO.GL00100 
-- TRIM here is bad for performance
WHERE 	LTRIM(Actnumbr_2) in ('12', '14', '03', '04', '07', '15', '17', '19', '11') 
-- CAST/TRIM here is bad for performance
	and (CAST(RTRIM(LTRIM(actnumbr_3)) as integer) >= 100) 
-- CAST/TRIM here is bad for performance
	and (cast(rtrim(ltrim(actnumbr_3)) as Integer) <= 199)


CREATE TABLE #LASER
(
	... Column Definitions here ...
	... With PK definition ...
	ORMSTRID	datatype NOT NULL PRIMARY KEY,
	LaserCredit datatype,
	LaserDebit datatype,
	BudgtdLaserAmt datatype
)
INSERT INTO #LASER

SELECT	Client AS ORMSTRID, 
	SUM(Crdtamnt)AS LaserCredit, 
	SUM(debitamt) AS LaserDebit, 
	#BdgLaser.Amount AS BudgtdLaserAmt
-- INTO #LASER
FROM	#GLCL 
-- FULL JOIN expensive, is it required?
	FULL JOIN #GL 
		 ON #GL.ORMSTRID = #GLCL.Client 
	LEFT JOIN #BdgLaser 
		 ON #GLCL.Client = #BdgLaser.ClientID
-- IN very bad for performance here, use JOIN isntead (optimiser MAY be doing this anyway)
WHERE #GL.actindx IN 
(
	SELECT	actindx 
	FROM	#GL00100 
-- TRIM here is bad for performance
	WHERE	    LTRIM(Actnumbr_2) = '14' 
-- CAST/TRIM here is bad for performance
		and (CAST(RTRIM(LTRIM(actnumbr_3)) as integer) >= 100) 
-- CAST/TRIM here is bad for performance
		and (cast(rtrim(ltrim(actnumbr_3)) as Integer) <= 199)
)
GROUP BY ALL #GLCL.Client, #BdgLaser.Amount


CREATE TABLE #MAIL
(
	... Column Definitions here ...
	... With PK definition ...
	ORMSTRID	datatype NOT NULL PRIMARY KEY,
	MailCredit datatype,
	MailDebit datatype,
	BudgtdMailAmt datatype
)
INSERT INTO #MAIL

SELECT	Client AS ORMSTRID, 
	SUM(Crdtamnt)AS MailCredit, 
	SUM(debitamt) AS MailDebit, 
	#Bdgt.Amount AS BudgtdMailAmt
-- INTO #MAIL
FROM #GLCL 
-- FULL JOIN expensive, is it required?
	FULL JOIN #GL 
		 ON #GL.ORMSTRID = #GLCL.Client 
	LEFT JOIN #Bdgt 
		 ON #GLCL.Client = #Bdgt.ClientID 
		AND #Bdgt.DeptID = '15'
-- IN very bad for performance here, use JOIN isntead (optimiser MAY be doing this anyway)
WHERE #GL.actindx IN 
(
	SELECT	actindx 
	FROM	#GL00100 
-- TRIM here is bad for performance
	WHERE	    LTRIM(Actnumbr_2) = '15' 
-- CAST/TRIM here is bad for performance
		and (CAST(RTRIM(LTRIM(actnumbr_3)) as integer) >= 100) 
-- CAST/TRIM here is bad for performance
		and (cast(rtrim(ltrim(actnumbr_3)) as Integer) <= 199)
)
GROUP BY ALL #GLCL.Client, #Bdgt.Amount


CREATE TABLE #MATERIAL
(
	... Column Definitions here ...
	... With PK definition ...
	ORMSTRID	datatype NOT NULL PRIMARY KEY,
	MaterialCredit datatype,
	MaterialDebit datatype,
	BudgtdMaterialAmt datatype
)
INSERT INTO #MATERIAL

SELECT	Client AS ORMSTRID, 
	SUM(Crdtamnt)AS MaterialCredit, 
	SUM(debitamt) AS MaterialDebit, 
	#Bdgt.Amount AS BudgtdMaterialAmt
FROM	#GLCL 
-- FULL JOIN expensive, is it required?
	FULL JOIN #GL 
		 ON #GL.ORMSTRID = #GLCL.Client 
	LEFT JOIN #Bdgt 
		 ON #GLCL.Client = #Bdgt.ClientID 
		AND #Bdgt.DeptID = '17'
-- IN very bad for performance here, use JOIN isntead (optimiser MAY be doing this anyway)
WHERE	#GL.actindx IN 
(
	SELECT	actindx 
	FROM	#GL00100 
-- TRIM here is bad for performance
	WHERE	    LTRIM(Actnumbr_2) = '17' 
-- CAST/TRIM here is bad for performance
		and (CAST(RTRIM(LTRIM(actnumbr_3)) as integer) >= 100) 
-- CAST/TRIM here is bad for performance
		and (cast(rtrim(ltrim(actnumbr_3)) as Integer) <= 199)
)
GROUP BY ALL #GLCL.Client, #Bdgt.Amount


CREATE TABLE #PROGRAM
(
	... Column Definitions here ...
	... With PK definition ...
	ORMSTRID	datatype NOT NULL PRIMARY KEY,
	MaterialCredit datatype,
	MaterialDebit datatype,
	BudgtdMaterialAmt datatype
)
INSERT INTO #PROGRAM

SELECT	Client AS ORMSTRID, 
	SUM(Crdtamnt)AS ProgramCredit, 
	SUM(debitamt) AS ProgramDebit, 
	#Bdgt.Amount AS BudgtdProgramAmt
FROM #GLCL 
-- FULL JOIN expensive, is it required?
	FULL JOIN #GL 
		 ON #GL.ORMSTRID = #GLCL.Client 
	LEFT JOIN #Bdgt 
		 ON #GLCL.Client = #Bdgt.ClientID 
		AND #Bdgt.DeptID = '12'
-- IN very bad for performance here, use JOIN isntead (optimiser MAY be doing this anyway)
WHERE #GL.actindx IN 
(
	SELECT actindx 
	FROM #GL00100 
-- TRIM here is bad for performance
	WHERE LTRIM(Actnumbr_2) = '12' 
-- CAST/TRIM here is bad for performance
		and (CAST(RTRIM(LTRIM(actnumbr_3)) as integer) >= 100) 
-- CAST/TRIM here is bad for performance
		and (cast(rtrim(ltrim(actnumbr_3)) as Integer) <= 199)
)
GROUP BY ALL #GLCL.Client, #Bdgt.Amount


CREATE TABLE #EBIZ
(
	... Column Definitions here ...
	... With PK definition ...
	ORMSTRID	datatype NOT NULL PRIMARY KEY,
	EBCredit datatype,
	EBDebit datatype,
	BudgtdEBAmt datatype
)
INSERT INTO #EBIZ

SELECT	Client AS ORMSTRID, 
	SUM(Crdtamnt)AS EBCredit, 
	SUM(debitamt) AS EBDebit, 
	#Bdgt.Amount AS BudgtdEBAmt
-- INTO #EBIZ
FROM	#GLCL 
	FULLJOIN #GL 
		 ON #GL.ORMSTRID = #GLCL.Client 
	LEFT JOIN #Bdgt 
		 ON #GLCL.Client = #Bdgt.ClientID 
		AND #Bdgt.DeptID = '19'
WHERE #GL.actindx IN 
(
	SELECT	actindx 
	FROM	#GL00100 
	WHERE	    LTRIM(Actnumbr_2) = '19' 
		and (CAST(RTRIM(LTRIM(actnumbr_3)) as integer) >= 100) 
		and (cast(rtrim(ltrim(actnumbr_3)) as Integer) <= 199)
)
GROUP BY ALL #GLCL.Client, #Bdgt.Amount


CREATE TABLE #EBIZ
(
	... Column Definitions here ...
	... With PK definition ...
	ORMSTRID	datatype NOT NULL PRIMARY KEY,
	DesignCredit datatype,
	DesignDebit datatype,
	BudgtdDesignAmt datatype
)
INSERT INTO #EBIZ

SELECT	Client AS ORMSTRID, 
	SUM(Crdtamnt)AS DesignCredit, 
	SUM(debitamt) AS DesignDebit, 
	#Bdgt.Amount AS BudgtdDesignAmt
-- INTO #DESIGN
FROM #GLCL 
	FULL JOIN #GL 
		 ON #GL.ORMSTRID = #GLCL.Client 
	LEFT JOIN #Bdgt 
		 ON #GLCL.Client = #Bdgt.ClientID 
		AND #Bdgt.DeptID = '11'
WHERE #GL.actindx IN 
(
	SELECT	actindx 
	FROM	#GL00100 
	WHERE	    LTRIM(Actnumbr_2) = '11' 
		and (CAST(RTRIM(LTRIM(actnumbr_3)) as integer) >= 100) 
		and (cast(rtrim(ltrim(actnumbr_3)) as Integer) <= 199)
)
GROUP BY ALL #GLCL.Client, #Bdgt.Amount


CREATE TABLE #LODGEMENT
(
	... Column Definitions here ...
	... With PK definition ...
	ORMSTRID	datatype NOT NULL PRIMARY KEY,
	DesignCredit datatype,
	DesignDebit datatype,
	BudgtdDesignAmt datatype
)
INSERT INTO #LODGEMENT

SELECT	Client AS ORMSTRID, 
	SUM(Crdtamnt)AS LodgementCredit, 
	SUM(debitamt) AS LodgementDebit, 
	#Bdgt.Amount AS BudgtdLodgementAmt
-- INTO #LODGEMENT
FROM #GLCL
	FULL JOIN #GL 
		 ON #GL.ORMSTRID = #GLCL.Client 
	LEFT JOIN #Bdgt 
		 ON #GLCL.Client = #Bdgt.ClientID 
		AND #Bdgt.DeptID = '08'
WHERE #GL.actindx IN 
(
	SELECT	actindx 
	FROM	#GL00100 
	WHERE	    LTRIM(Actnumbr_2) = '04' 
		and (CAST(RTRIM(LTRIM(actnumbr_3)) as integer) = 102))
GROUP BY ALL #GLCL.Client, #Bdgt.Amount


CREATE TABLE #POSTAGE
(
	... Column Definitions here ...
	... With PK definition ...
	ORMSTRID	datatype NOT NULL PRIMARY KEY,
	PostageCredit datatype,
	PostageDebit datatype,
	BudgtdPostageAmt datatype
)
INSERT INTO #POSTAGE

SELECT	Client AS ORMSTRID, 
	SUM(Crdtamnt)AS PostageCredit, 
	SUM(debitamt) AS PostageDebit, 
	#Bdgt.Amount AS BudgtdPostageAmt
-- INTO #POSTAGE
FROM #GLCL 
	FULL JOIN #GL 
		 ON #GL.ORMSTRID = #GLCL.Client 
	LEFT JOIN #Bdgt 
		 ON #GLCL.Client = #Bdgt.ClientID 
		AND #Bdgt.DeptID = '04'
WHERE #GL.actindx IN
(
	SELECT	actindx 
	FROM	#GL00100 
	WHERE	    LTRIM(Actnumbr_2) = '04' 
		and (CAST(RTRIM(LTRIM(actnumbr_3)) as integer) IN (180, 181)))
GROUP BY ALL #GLCL.Client, #Bdgt.Amount


CREATE TABLE #FREIGHT
(
	... Column Definitions here ...
	... With PK definition ...
	ORMSTRID	datatype NOT NULL PRIMARY KEY,
	FreightCredit datatype,
	FreightDebit datatype,
	BudgtdFreightAmt datatype
)
INSERT INTO #FREIGHT

SELECT	Client AS ORMSTRID, 
	SUM(Crdtamnt)AS FreightCredit, 
	SUM(debitamt) AS FreightDebit, 
	#Bdgt.Amount AS BudgtdFreightAmt
-- INTO #FREIGHT
FROM #GLCL 
	FULL JOIN #GL 
		 ON #GL.ORMSTRID = #GLCL.Client 
	LEFT JOIN #Bdgt 
		 ON #GLCL.Client = #Bdgt.ClientID 
		AND #Bdgt.DeptID = '03'
WHERE #GL.actindx IN 
(
	SELECT	actindx 
	FROM	#GL00100 
	WHERE	    LTRIM(Actnumbr_2) = '03' 
		and (CAST(RTRIM(LTRIM(actnumbr_3)) as integer) >= 100) 
		and (cast(rtrim(ltrim(actnumbr_3)) as Integer) <= 199)
)
GROUP BY ALL #GLCL.Client, #Bdgt.Amount

SELECT	Client, 
	ClientName, 
	SUM(Laser) as Laser, 
	SUM(BudgtdLaser) AS BudgtdLaser, 
	SUM(Mail) AS Mail, 
	SUM(BudgtdMail) AS BudgtdMail, 
	SUM(Material) AS Material, 
	SUM(BudgtdMaterial) AS BudgtdMaterial, 
	SUM(Program) AS Program, 
	SUM(BudgtdProgram) AS BudgtdProgram, 
	SUM(EBiz) AS EBiz, 
	SUM(BudgtdEBiz) AS BudgtdEBiz, 
	SUM(Design) AS Design, 
	SUM(BudgtdDesign) AS BudgtdDesign, 
	SUM(Lodgement) AS Lodgement, 
	SUM(BudgtdLodgement) AS BudgtdLodgement, 
	SUM(Postage) AS Postage, 
	SUM(BudgtdPostage) AS BudgtdPostage, 
	SUM(Freight) AS Freight, 
	SUM(BudgtdFreight) AS BudgtdFreight
FROM
(
	SELECT #GLCL.Client, #GLCL.ClientName, 
		ISNULL((#LASER.LaserCredit - #LASER.LaserDebit),0) AS Laser, 
		ISNULL(#LASER.BudgtdLaserAmt, 0) AS BudgtdLaser,
		ISNULL((#MAIL.MailCredit - #MAIL.MailDebit),0) AS Mail, 
		ISNULL(#MAIL.BudgtdMailAmt, 0) AS BudgtdMail,
		ISNULL((#MATERIAL.MaterialCredit - #MATERIAL.MaterialDebit),0) AS Material, 
		ISNULL(#MATERIAL.BudgtdMaterialAmt, 0) AS BudgtdMaterial,
		ISNULL((#PROGRAM.ProgramCredit - #PROGRAM.ProgramDebit),0) AS Program, 
		ISNULL(#PROGRAM.BudgtdProgramAmt, 0) AS BudgtdProgram,
		ISNULL((#EBIZ.EBCredit - #EBIZ.EBDebit),0) AS EBiz, 
		ISNULL(#EBIZ.BudgtdEBAmt, 0) AS BudgtdEBiz,
		ISNULL((#DESIGN.DesignCredit - #DESIGN.DesignDebit),0) AS Design, 
		ISNULL(#DESIGN.BudgtdDesignAmt, 0) AS BudgtdDesign,
		ISNULL((#LODGEMENT.LodgementCredit - #LODGEMENT.LodgementDebit),0) AS Lodgement, 
		ISNULL(#LODGEMENT.BudgtdLodgementAmt, 0) AS BudgtdLodgement,
		ISNULL((#POSTAGE.PostageCredit - #POSTAGE.PostageDebit),0) AS Postage, 
		ISNULL(#POSTAGE.BudgtdPostageAmt, 0) AS BudgtdPostage,
		ISNULL((#FREIGHT.FreightCredit - #FREIGHT.FreightDebit),0) AS Freight, 
		ISNULL(#FREIGHT.BudgtdFreightAmt, 0) AS BudgtdFreight
-- 	INTO #FINAL
	FROM	#GLCL 
		FULL JOIN #LASER 
			 ON #GLCL.Client = #LASER.ORMSTRID 
		FULL JOIN #MAIL 
			 ON #GLCL.Client = #MAIL.ORMSTRID
		FULL JOIN #MATERIAL 
			 ON #GLCL.Client = #MATERIAL.ORMSTRID 
		FULL JOIN #PROGRAM 
			 ON #GLCL.Client = #PROGRAM.ORMSTRID
		FULL JOIN #EBIZ 
			 ON #GLCL.Client = #EBIZ.ORMSTRID 
		FULL JOIN #DESIGN 
			 ON #GLCL.Client = #DESIGN.ORMSTRID
		FULL JOIN #LODGEMENT 
			 ON #GLCL.Client = #LODGEMENT.ORMSTRID 
		FULL JOIN #POSTAGE 
			 ON #GLCL.Client = #POSTAGE.ORMSTRID
		FULL JOIN #FREIGHT 
			 ON #GLCL.Client = #FREIGHT.ORMSTRID 
-- 	ORDER BY #GLCL.Client
) X
-- FROM #FINAL
GROUP BY Client, ClientName 
ORDER BY Client

SELECT	actindx 
FROM	#GL00100 
WHERE	    LTRIM(Actnumbr_2) = '19' 
	and (CAST(RTRIM(LTRIM(actnumbr_3)) as integer) >= 100) 
	and (cast(rtrim(ltrim(actnumbr_3)) as Integer) <= 199)

CREATE TABLE #GL00100
(
	... Column Definitions here ...
	... With PK definition ...
	... Make sure that Actnumbr_2 and Actnumbr_3 are stored as INT types ...
	... and remove all the TRIM and CAST on these columns below ...
	... Make sure Actnumbr_2 and Actnumbr_3 are indexed ...
	Actnumbr_2	int NOT NULL,	-- I'm guessing that this should be INT
	actnumbr_3	int NOT NULL
)

INSERT INTO #GL00100 (... other columns listed by name ..., Actnumbr_2, actnumbr_3)
SELECT ... other columns listed by name ...
	LTRIM(Actnumbr_2) AS Actnumbr_2,
	(CAST(RTRIM(LTRIM(actnumbr_3)) as integer) AS actnumbr_3
FROM	CIMEL04.CDSYD.DBO.GL00100 
WHERE 	LTRIM(Actnumbr_2) in ('12', '14', '03', '04', '07', '15', '17', '19', '11') 
	and (CAST(RTRIM(LTRIM(actnumbr_3)) as integer) >= 100) 
	and (cast(rtrim(ltrim(actnumbr_3)) as Integer) <= 199)