CREATE PROCEDURE Sales (@StartDate AS VarChar(10), @EndDate AS VarChar(10))
AS 

CREATE TABLE #JobLaserMail
(
	JobNo VARCHAR(10) PRIMARY KEY,
	TotLaserImages INT,
	TotalMailpacks INT
)

CREATE TABLE #JobAmount
(
	JobNo VARCHAR(10) PRIMARY KEY,
	JobAmount INT
)

CREATE TABLE #AM
(
	JobNo VARCHAR(10), 
	CompanyID INT,
	ResID INT,
	Name VARCHAR(50),
	RelationTypeID VARCHAR(4)
)

CREATE TABLE #AE
(
	JobNo VARCHAR(10), 
	CompanyID INT,
	ResID INT,
	Name VARCHAR(50),
	RelationTypeID VARCHAR(4)
)

--Getting CDS Relation AM for each Job
INSERT INTO #AM
SELECT ContractID AS JobNo,CompanyID,Relation.ResID,LTRIM(RTRIM(Res.Name)),RelationTypeID
FROM Relation,Res 
WHERE Relation.ResID=Res.ResID
AND ContractID IN
(SELECT DISTINCT JobNo FROM JobInvoice 
WHERE  InvoiceDate >= CONVERT(DATETIME, @StartDate, 103)AND 
InvoiceDate <= CONVERT(DATETIME, @EndDate, 103)+1) AND RelationTypeID='CR4'

--Getting CDS Relation AE for each Job
INSERT INTO #AE
SELECT ContractID AS JobNo,CompanyID,Relation.ResID,LTRIM(RTRIM(Res.Name)),RelationTypeID
FROM Relation,Res 
WHERE Relation.ResID=Res.ResID
AND ContractID IN
(SELECT DISTINCT JobNo FROM JobInvoice 
WHERE  InvoiceDate >= CONVERT(DATETIME, @StartDate, 103)AND 
InvoiceDate <= CONVERT(DATETIME, @EndDate, 103)+1) AND RelationTypeID='CR5'

--Getting Total Laser imasges and mailpacks  for each Job
INSERT INTO #JobLaserMail
SELECT 	JobInvoice.JobNo,sum(ReconcileJobProductComponent.ActLaserTotalA4Images) AS 'TotLaserImages',
	sum(ReconcileJobProductComponent.ActMailMachineMailPacks) AS 'TotalMailpacks' 
FROM JobInvoice LEFT OUTER JOIN ReconcileJobProductComponent on JobInvoice.JobNo=ReconcileJobProductComponent.JobNo
WHERE  JobInvoice.InvoiceDate >= CONVERT(DATETIME, @StartDate, 103)AND JobInvoice.InvoiceDate <= CONVERT(DATETIME, @EndDate, 103)+1
GROUP BY JobInvoice.JobNo

--Getting Total value of each Job
INSERT INTO #JobAmount
SELECT 	JobInvoice.JobNo, 	sum(SOP.DOCAMNT) AS 'JobAmount' 
FROM JobInvoice LEFT JOIN SOP302 AS SOP on JobInvoice.InvoiceNo=SOP.SOPNUMBE		
WHERE  JobInvoice.InvoiceDate >= CONVERT(DATETIME, @StartDate, 103)AND JobInvoice.InvoiceDate <= CONVERT(DATETIME, @EndDate, 103)+1
GROUP BY JobInvoice.JobNo
ORDER BY JobInvoice.JobNo


CREATE TABLE #WorkingTable (
JobNo VARCHAR(10),
CompanyID INT,
Name VARCHAR(50),
list VARCHAR(1000))

INSERT INTO #WorkingTable (JobNo,CompanyID,Name)
SELECT JobNo,CompanyID,Name
FROM #AE
ORDER BY JobNo,Name

DECLARE
@list VARCHAR(1000),
@lastJobNo VARCHAR(10),
@lastCompanyID INT

SELECT
@list = '',
@lastJobNo = '',
@lastCompanyID = 0

UPDATE
#WorkingTable
SET
@list = list = CASE
WHEN @lastJobNo <> JobNo OR @lastCompanyID<>CompanyID THEN Name
else @list + ',' + Name
end,
@lastJobNo = JobNo,@lastCompanyID=CompanyID

CREATE TABLE #final (JobNo VARCHAR(10), CompanyID int,AM VARCHAR(1000), AE VARCHAR(1000))
INSERT INTO #final
SELECT #WorkingTable.JobNo, #WorkingTable.CompanyID,max(#AM.Name), max(list) as list
FROM #WorkingTable LEFT JOIN #AM ON #WorkingTable.JobNo=#AM.JobNo 
GROUP BY #WorkingTable.JobNo,#WorkingTable.CompanyID
ORDER BY #WorkingTable.JobNo,#WorkingTable.CompanyID

UPDATE #final
Set 
	AM = CASE WHEN CHARINDEX(',',AE)>0 THEN LEFT(AE,CHARINDEX(',',AE)-1) else AE end,
	AE = CASE WHEN CHARINDEX(',',AE)>0 THEN RIGHT(AE,LEN(AE) - CHARINDEX(',',AE)) else NULL end
FROM #final
Where AM is NULL

SELECT 	DISTINCT	JobInvoice.JobNo,
			Job.JobName,
			Company.CompanyName,
			#JobAmount.JobAmount,
			#JobLaserMail.TotLaserImages,#JobLaserMail.TotalMailpacks,
			#final.AM,#final.AE
FROM JobInvoice 
	LEFT JOIN Job ON JobInvoice.JobNo=Job.JobNo
	LEFT JOIN #JobAmount ON JobInvoice.JobNo=#JobAmount.JobNo
	LEFT JOIN #JobLaserMail ON JobInvoice.JobNo=#JobLaserMail.JobNo
	RIGHT JOIN #final ON JobInvoice.JobNo=#final.JobNo
	INNER JOIN Company on #final.CompanyID=Company.CompanyID	
WHERE  JobInvoice.InvoiceDate >= CONVERT(DATETIME, @StartDate, 103)AND JobInvoice.InvoiceDate <= CONVERT(DATETIME, @EndDate, 103)+1
ORDER BY  #Final.AM, Company.CompanyName, JobInvoice.JobNo


DROP TABLE #AM 
DROP TABLE  #AE
DROP TABLE #JobLaserMail
DROP TABLE #JobAmount
DROP TABLE #final
DROP TABLE #WorkingTable

quote:

---

I put SET NOCOUNT ON as the very first statement in all my SProcs. And SET ARITHABORT ON and SET XACT_ABORT ON

---

quote:

---

InvoiceDate <= CONVERT(DATETIME, @EndDate, 103)+1

---

quote:

---

Why have both #AE and #WorkingTable?

---

quote:

---

#JobLaserMail (and probably some others) is only used in the final SELECT statement - is it worth making a temporary table, rather than just referencing the data in that final SELECT?

---