Hi There,
Please help me make this into a script without using a #Temp File:
IF OBJECT_ID('tempdb..#TempALLPS') IS NOT NULL DROP TABLE #TempALLPS;
Select
xm.ID As 'Reference'
, IsNull(xd.D_Surname,'') As 'Debtor Surname'
, CONVERT(varchar, xm.M_IntructionReceived, 103) 'Instructed'
, Isnull(XDB.DebtorBalance,0) As 'Debtors Balance'
--, Isnull(Format(XDBR.DB_Date,'yyyy/MM/dd'),'') As 'Last Receipt'
--, IsNull(XDBR.DB_Credit,0) As 'L/R Amount'
, IsNull(xdt.DP_Name,'') as 'Department'
, IsNull(xb.Name,'') as 'Book'
, IsNull(xub.UB_BranchName,'') As 'Branch'
, IsNull(xfs.St_Status,'') As 'Status'
, CONVERT(varchar, CAST(ba_m.DateInstructed-36163 as DateTime), 111) As 'BA Instructed'
-- , CONVERT(Varchar, DATEADD(YEAR, -1, GETDATE()), 111) As 'Today Less 1 Year'
, xm.M_IDX As 'M_IDX'
Into #TempALLPS
From Matter xm With (Nolock)
Inner Join MatterFileStatus xmfs With (Nolock)on xmfs.IDX = xm.M_IDX
Inner join Department xdt with (Nolock) on xmfs.M_DepartmentID = xdt.ID
Inner Join BookListView xb with (Nolock) on xb.ID = xm.M_ClientID
Inner Join LegalSuite.dbo.Matter ba_m with (Nolock) on ba_m.TheirRef = Replace(xm.ID, '-','/')
Left join FileStatus xfs with (nolock) on xfs.ID = xmfs.M_FileStatus
Left join UserBranch xub with (Nolock) on xub.ID = xm.M_Branch
Left Join Debtor xd With (Nolock) on xd.D_IDX = xm.M_IDX
Left Join (Select DB_IDX, SUM(IsNull(DB_Debit,0)) - SUM(IsNull(DB_Credit,0)) As DebtorBalance From Debtor_Balance with (nolock) Group By DB_IDX) As xdb On xdb.DB_IDX = xm.M_IDX
Where xb.Name Not Like '%TEST%' -- Test Matters -- EXCLUDE
And xb.Name Not Like '%ACME%' -- Test Matters -- EXCLUDE
And xb.Name Not Like '%Ithala%' -- Ithala Matters -- EXCLUDE
And xb.Name Not Like '%First National%' -- First National Matters -- EXCLUDE
And xm.ID Not Like '%TEST%' -- Test Matters -- EXCLUDE
And xm.ID Not Like '%ACME%' -- ACME Test Matters -- EXCLUDE
And xb.ID Not In (901305, 901499, 901507) -- Outsourced Matters -- EXCLUDE
And xmfs.M_FileStatus Not In (2,5,10,11,16,23,24,25,27,29,40,50,51,52,53,65,69,70,94,99,105,111,112,126,129,10000)
And xmfs.M_DepartmentID = 1 -- Legal
And Isnull(XDB.DebtorBalance,0) > 10000.00
-- And XH.H_DateTime <= GETDATE()
And CAST(ba_m.DateInstructed-36163 as DateTime) < DATEADD(YEAR, -1, GETDATE());
-- 2 Seconds
WITH CTE AS (
SELECT
[Reference]
, [Debtor Surname]
, [Instructed]
, [Debtors Balance]
, ISNULL(CONVERT(char(10), XLP.LastPaymentDate, 111), '') AS 'Last Receipt'
, Isnull(XLP.LastPaymentAmt,0) As 'L/R Amount'
, [Department]
, [Book]
, [Branch]
, [Status]
, [BA Instructed]
, ROW_NUMBER() OVER (PARTITION BY [Reference] ORDER BY [Reference], XLP.LastPaymentDate desc) AS RowNum
FROM #TempALLPS
LEFT JOIN (
SELECT
pmt.DB_IDX,
MAX(pmt.DB_Date) AS LastPaymentDate,
pmt.DB_Credit AS LastPaymentAmt
FROM (
SELECT
ID,
DB_IDX,
DB_Date,
DB_Credit,
DB_Transaction_TypeID,
ROW_NUMBER() OVER (PARTITION BY DB_IDX ORDER BY DB_Date DESC) AS rn
FROM Debtor_Balance
WHERE DB_Transaction_TypeID = 1
AND DB_Transaction_ParticularsID = 1
) AS pmt
LEFT JOIN Debtor_Balance AS rev ON rev.DB_PaymentID = pmt.ID
AND rev.DB_Transaction_TypeID = pmt.DB_Transaction_TypeID
AND rev.DB_Transaction_ParticularsID = 2
WHERE rev.ID IS NULL
-- AND pmt.rn = 1
GROUP BY pmt.DB_IDX, pmt.DB_Credit
) AS XLP ON XLP.DB_IDX = [M_IDX]
)
SELECT
[Reference]
, [Debtor Surname]
, [Instructed]
, [Debtors Balance]
, [Last Receipt]
, [L/R Amount]
, [Department]
, [Book]
, [Branch]
, [Status]
, [BA Instructed]
FROM CTE
WHERE RowNum = 1
ORDER BY [Reference]
-- 1 Second
IF OBJECT_ID('tempdb..#TempALLPS') IS NOT NULL DROP TABLE #TempALLPS;
-- 4 Seconds