CODEWITHSUNDEEP
sqlsql-servercursor
Mickey
Mickey

Reputation: 313

Using Set Based Query Instead of Cursor

Need to run query based on multiple sets of accounts as criteria. Trying to avoid cursor/while loop scenario. The @MasterGroup variable is cursor iterator and derives directly from Group table, which is a one to many with accounts from Group table that are then used in subquery in WHERE clause. The end result gets populated into new table with @MasterGroup integers. Any way to avoid using cursor/loop here? Many thanks...

Insert Into SAGTranTmp
SELECT 
        @MasterGroup,
        C1.Cust_Type,
        T1.Exclude,
        T1.Acct_No,
        SUM(Tran_Amt) AS [Amount]
FROM dbo.Trans AS [T1]
     JOIN dbo.Customers AS C1 ON T1.Acct_No = C1.Acct_No
WHERE C1.Acct_No IN (
        SELECT AcctNo 
        FROM SignAcc_Group
        WHERE MasterGroup = @MasterGroup)

Upvotes: 0

Views: 409

Answers (2)

TriV
TriV

Reputation: 5148

Assume that #MasterGroup AS TABLE( MasterGroup int). You could try this:

;WITH 
temp AS (SELECT DISTINCT
    mg.MasterGroup,
    T1.Acct_No
 FROM dbo.Trans AS [T1]
       Inner JOIN dbo.Customers AS C1 ON T1.Acct_No = C1.Acct_No
       inner join SignAcc_Group as sg ON C1.Acct_No = sg.AcctNo 
       inner join #MasterGroup mg ON sg.MasterGroup  = mg.MasterGroup        
         )
 Insert Into SAGTranTmp
 SELECT 
    t.MasterGroup,
    C1.Cust_Type,
    T1.Exclude,
    T1.Acct_No,
    SUM(Tran_Amt) AS [Amount]
 FROM temp t 
 INNER JOIN dbo.Trans AS [T1] ON T1.Acct_No = t.Acct_No 
 INNER JOIN dbo.Customers AS C1 ON T1.Acct_No = C1.Acct_No
 GROUP BY t.MasterGroup,
       C1.Cust_Type,
       T1.Exclude,
       T1.Acct_No

Upvotes: 1

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

I may be over simplifying this and apologies if I am but please try this and hope it helps.

IF OBJECT_ID('tempdb..#MasterGroup') IS NOT NULL
    DROP TABLE #MasterGroup
GO

SELECT DISTINCT AcctNo, MasterGroup
INTO #MasterGroup
FROM SignAcc_Group 

CREATE NONCLUSTERED INDEX NCX_Temp_MasterGroup 
ON #MasterGroup (AcctNo)
GO

;WITH cte_BuildResult AS
(
SELECT M1.MasterGroup,
    C1.Cust_Type,
    T1.Exclude,
    T1.Acct_No,
    SUM(Tran_Amt) AS [Amount]
FROM dbo.Trans AS [T1]
INNER JOIN dbo.Customers AS [C1]
    ON T1.Acct_No = C1.Acct_No
INNER JOIN @MasterGroup [M1]
    ON C1.Acct_No = M1.AcctNo 
)
INSERT INTO SAGTranTmp
SELECT MasterGroup,Cust_Type,Exclude,Acct_No,[Amount]
FROM cte_BuildResult;

GO

IF OBJECT_ID('tempdb..#MasterGroup') IS NOT NULL
        DROP TABLE #MasterGroup
GO

Upvotes: 0

Related Questions