ChrisM@Work (3/22/2013)
It's difficult to tell with all of those funky joins and no sample data, but you might get a win with this too;
SELECT CAST(ID AS NUMERIC(9))INTO #Accounts
FROM dbo.SplitIDs(@vcAccountId, ',')
SELECT
h.numDomainId,
coa.numAccountId,
CompanyName = ci.vcCompanyName,
dm.numDivisionID,
0 AS Opening,
CONVERT(VARCHAR(20), SUM(ISNULL(d.numDebitAmt, 0))) TotalDebit,
CONVERT(VARCHAR(20), SUM(ISNULL(d.numCreditAmt, 0))) TotalCredit,
(SUM(ISNULL(d.numDebitAmt, 0)) - SUM(ISNULL(d.numCreditAmt, 0))) AS Closing,
COA.numParntAcntTypeId
FROM dbo.General_Journal_Header h
INNER JOIN dbo.General_Journal_Details d
ON h.numJournal_Id = d.numJournalId
LEFT JOIN dbo.Chart_Of_Accounts coa
ON d.numChartAcntId = coa.numAccountId
LEFT JOIN dbo.DivisionMaster dm
LEFT JOIN dbo.CompanyInfo ci
ON dm.numCompanyID = ci.numCompanyId
ON d.numCustomerId = dm.numDivisionID
WHERE
h.numDomainId = @numDomainID
AND dm.numDivisionID IS NOT null -- converts dm to IJ
AND coa.numAccountId IN (SELECT ID FROM #Accounts)
Chris,
Thank you very very much for giving your precious time to write the code, due to time zone differences, I could not thank you earlier so forgive me for that.
Let me tell you that the above code that you have given as a direct substitute for the procedure is giving me correct results after I tested it for 3 different set of parameters. In another post, the way you changed the function to an inline function returning the data as a table rather than a cursor looks great too.
I do a question at this juncture:
The above code looks very neat as it does not use the big view which has a lot of unnecessary tables and uses tables for most of the tasks. However as suggested by Grant earlier, I looked at the execution plan of original query to see what tables the optimizer is actually using but in your query it does not uses all of them and skips few which are in execution plan.
So how did you determine the table relationships to reach the actual joins between least sufficient tables. Is there a way I can learn this as it will help me think like an efficient developer?
Please guide me at this.
Regards
Chandan Jha