locked
TSQL For Next Loop RRS feed

  • Question

  • User1122355199 posted

    Hello everyone and thanks for your help in advance.  I need to develop a routine to loop through each record in a database.  Within each loop a Select SUM function must be performed to retrieve a total balance based on the ID of each  row, then update a column in the particular row based on the value of the Select SUM function.  I tried the following:

    DECLARE @PatientID VARCHAR(50)
    DECLARE @PatientBalance money
    
    DECLARE db_cursor CURSOR FOR  
    SELECT PatientID, @PatientBalance FROM tblPatients
    
    OPEN db_cursor    
    FETCH NEXT FROM db_cursor Into @PatientID, @PatientBalance,
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
    	   
           FETCH NEXT FROM db_cursor Into @PatientID, @PatientBalance
    
    	SET @PatientBalance = Select Sum PatientBalance from tblPatientCharges
    	UPDATE tblPatients SET PatientBalance = @PatientBalance WHERE PatientID = @PatientID
    END   
    
    CLOSE db_cursor   
    DEALLOCATE db_cursor

    but this doesn't work.  Any help would be appreciated.

    Friday, March 25, 2016 6:22 PM

Answers

  • User-866979696 posted

    Try:

    with CTE_Sum as
    (
        select
            p.PatientID,
            p.PatientBalance,
            sum(c.PatientBalance) as PatientBalanceCharges
        from tblPatients as p
        left join tblPatientCharges as c
            on c.PatientID = p.PatientID
        group by
            p.PatientID,
            p.PatientBalance
    )
    
    update CTE_Sum
    set PatientBalance = PatientBalanceCharges

    or

    update p
    set p.PatientBalance = (select sum(c.PatientBalance) 
                            from tblPatientCharges as c
                            where c.PatientID = p.PatientID)
    from tblPatients as p

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 26, 2016 3:07 AM

All replies

  • User-866979696 posted

    Hi,

    Try:

    with CTE_Sum as
    (
        select
            p.PatientID,
            p.PatientBalance,
            sum(c.PatientBalance) as PatientBalanceCharges
        from tblPatients as p
        left join tblPatientCharges as c
            on c.PatientID = p.PatientID
    )
    
    updat CTE_Sum
    set PatientBalance = PatientBalanceCharges

    Hope this help

    Friday, March 25, 2016 8:22 PM
  • User1122355199 posted

    Thanks for the response.  The query returns:

    Column 'tblPatients.PatientID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Saturday, March 26, 2016 12:37 AM
  • User-866979696 posted

    Try:

    with CTE_Sum as
    (
        select
            p.PatientID,
            p.PatientBalance,
            sum(c.PatientBalance) as PatientBalanceCharges
        from tblPatients as p
        left join tblPatientCharges as c
            on c.PatientID = p.PatientID
        group by
            p.PatientID,
            p.PatientBalance
    )
    
    update CTE_Sum
    set PatientBalance = PatientBalanceCharges

    or

    update p
    set p.PatientBalance = (select sum(c.PatientBalance) 
                            from tblPatientCharges as c
                            where c.PatientID = p.PatientID)
    from tblPatients as p

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 26, 2016 3:07 AM