SQL/SQL Server - What would you do different

I have something I wrote in SQL that I want peoples opinions on as far as better practices or "could be problem" pieces.

Everywhere I go, I am generally the "expert" at SQL so I have no one to learn from. Thanks!

If you were training me, what would you have me fix...

Code:

---

--//datestamp variable
DECLARE @ASOF DATETIME;
SET @ASOF = '5/31/09';

/*
        1. BUILD INVOICE INDEX
*/
DROP TABLE CREDITAGING_INDEX
SELECT
        CONVERT(VARCHAR(1),NULL) AS EXCLUDE,
        CONVERT(VARCHAR(255),NULL) AS EXCLUDE_REASON,
        DBO_INVOICE.GROUP_ZZ,
        DBO_INVOICE.PATIENT,
        DBO_INVOICE.INVOICE,
        DBO_INVOICE.INV_NUM,
        DBO_INVOICE.INV_BAL,
        @ASOF AS AS_OF
        INTO CREDITAGING_INDEX
FROM IDX.DBO.INVOICE DBO_INVOICE
WHERE
        (DBO_INVOICE.GROUP_ZZ=4 OR DBO_INVOICE.GROUP_ZZ=16 OR DBO_INVOICE.GROUP_ZZ=18 OR DBO_INVOICE.GROUP_ZZ=21 OR DBO_INVOICE.GROUP_ZZ=24)
        AND DBO_INVOICE.INV_BAL <0
        --AND DBO_INVOICE.FSC<>999
/*
        2. EXCLUDE CHARGE CORRECTIONS NO LONGER UNDER FSC 999
*/
UPDATE CREDITAGING_INDEX
        SET
                CREDITAGING_INDEX.EXCLUDE='X',
                CREDITAGING_INDEX.EXCLUDE_REASON='CHARGE CORRECTION'
FROM CREDITAGING_INDEX
INNER JOIN IDX.DBO.TRANSAC DBO_TRANSAC ON
        CREDITAGING_INDEX.GROUP_ZZ=DBO_TRANSAC.GROUP_ZZ
        AND CREDITAGING_INDEX.PATIENT=DBO_TRANSAC.PATIENT
        AND CREDITAGING_INDEX.INVOICE=DBO_TRANSAC.INVOICE
WHERE
        (DBO_TRANSAC.GROUP_ZZ=4 OR DBO_TRANSAC.GROUP_ZZ=16 OR DBO_TRANSAC.GROUP_ZZ=18 OR DBO_TRANSAC.GROUP_ZZ=21 OR DBO_TRANSAC.GROUP_ZZ=24)
        AND DBO_TRANSAC.PAY_CODE_NUM = 97

/*
        3. PULL TRANSACTIONS FOR INVOICES
*/
DROP TABLE CREDITAGING_TRANSACTIONS
SELECT
        DBO_TRANSAC.GROUP_ZZ,
        DBO_TRANSAC.PATIENT,
        DBO_TRANSAC.INVOICE,
        CI.INV_NUM,
        DBO_TRANSAC.TRANSACTION_ZZ,
        DBO_TRANSAC.PAY_CODE,
        DBO_TRANSAC.PAY_CODE_NUM,
        CASE
                WHEN (DBO_TRANSAC.PAY_CODE_NUM = 99)
                        --//FOR CHARGE TRANSACTIONS ONLY REPORT THE CHARGE $$
                        THEN DBO_TRANSAC.CHARGE_AMOUNT
                ELSE
                        --//ALL NON-CHARGE LINES
                        (DBO_TRANSAC.DEBIT_AMT-DBO_TRANSAC.PAY_AMT-DBO_TRANSAC.ADJ_AMT-DBO_TRANSAC.CR_AMT)                       
        END AS AMT,
        DBO_TRANSAC.CHARGE_AMOUNT,
        DBO_TRANSAC.DEBIT_AMT,
        DBO_TRANSAC.PAY_AMT,
        DBO_TRANSAC.ADJ_AMT,
        DBO_TRANSAC.CR_AMT,
        DBO_TRANSAC.PX_CODE,
        DBO_TRANSAC.POST_DT_1,
        DBO_TRANSAC.POST_PD,
        CONVERT(MONEY,0) AS RUNNING_TOTAL,
        CONVERT(VARCHAR(1),NULL) AS FLAG_RUNNING_TOTAL,
        CONVERT(VARCHAR(1),NULL) AS FLAG_BECAMECREDIT,
        CONVERT(BIGINT,0) AS NEW_ORDER

        INTO CREDITAGING_TRANSACTIONS               
FROM IDX.DBO.TRANSAC DBO_TRANSAC
INNER JOIN CREDITAGING_INDEX CI ON
        DBO_TRANSAC.GROUP_ZZ=CI.GROUP_ZZ
        AND DBO_TRANSAC.PATIENT=CI.PATIENT
        AND DBO_TRANSAC.INVOICE=CI.INVOICE
WHERE
        (DBO_TRANSAC.GROUP_ZZ=4 OR DBO_TRANSAC.GROUP_ZZ=16 OR DBO_TRANSAC.GROUP_ZZ=18 OR DBO_TRANSAC.GROUP_ZZ=21 OR DBO_TRANSAC.GROUP_ZZ=24)
        AND PAY_CODE_NUM <>70        --EXCLUDE FSC TRANSFERS
ORDER BY
        --THIS IS CRUCIAL FOR THE NEXT PIECE TO WORK
        DBO_TRANSAC.GROUP_ZZ,
        DBO_TRANSAC.PATIENT,
        DBO_TRANSAC.INVOICE,
        CASE        --I WANT THE CHARGES TO BE FIRST IN THE LIST DESPITE WHEN THEY WERE POSTED
                WHEN (DBO_TRANSAC.PAY_CODE_NUM=99)
                        THEN 1
                ELSE
                        DBO_TRANSAC.POST_DT_1
        END,
        DBO_TRANSAC.TRANSACTION_ZZ

/*
        4. ADD IDENTITY FIELD
*/
ALTER TABLE CREDITAGING_TRANSACTIONS ADD ID BIGINT IDENTITY(1,1)

---

Code:

---

/*
        5. Create temporary table to store looped results
*/

drop table #tmp1
create table #tmp1
(
        group_zz int default 0,
        patient bigint default 0,
        invoice int default 0,
        minid bigint default 0,
        amt money default 0,
        running_total_prior money default 0,
        running_total_prior_flag varchar(1) default null,
        used varchar(1) default null
)
/*
        6. Update the initial transaction for each invoice
*/
--//a. Identify first record per invoice for update
insert into #tmp1 (group_zz,patient,invoice,minid)
select
        cit.group_zz,
        cit.patient,
        cit.invoice,
        min(cit.id)
from creditaging_transactions cit
where cit.flag_running_total is null
group by
        cit.group_zz,
        cit.patient,
        cit.invoice
--//b. Update first record running_total to amt
update cit
        set
                cit.running_total=cit.amt,
                cit.flag_running_total = 'x'
from creditaging_transactions cit
inner join #tmp1 tmp on
        cit.group_zz=tmp.group_zz
        and cit.patient=tmp.patient
        and cit.invoice=tmp.invoice
        and cit.id=tmp.minid
where tmp.used is null

/*
        7. Update running_total for the rest
*/

declare @rtotals bigint;
select @rtotals=count(*) from creditaging_transactions where flag_running_total is null;
--print @rtotals

while (@rtotals>0)
begin
        --//a. Updated used flag for non-use, so it's not used in section e
        update #tmp1 set used = 'x'
        --//b. Identify the first unupdated record for an invoice
        insert into #tmp1 (group_zz,patient,invoice,minid)
        select
                        cit.group_zz,
                        cit.patient,
                        cit.invoice,
                        min(cit.id)
        from creditaging_transactions cit
        where cit.flag_running_total is null
        group by
                        cit.group_zz,
                        cit.patient,
                        cit.invoice
        --//c. Update amount for #tmp1       
        update #tmp1
                set #tmp1.amt = cit.amt
        from #tmp1
        inner join creditaging_transactions cit on
                #tmp1.group_zz = cit.group_zz
                and #tmp1.patient=cit.patient
                and #tmp1.invoice=cit.invoice
                and #tmp1.minid=cit.id
        where
                used is null
        --//d. Update prior running total
        update #tmp1
                set #tmp1.running_total_prior = cit.running_total,
                        #tmp1.running_total_prior_flag='x'
        from #tmp1
        inner join creditaging_transactions cit on
                #tmp1.group_zz = cit.group_zz
                and #tmp1.patient=cit.patient
                and #tmp1.invoice=cit.invoice
        where
                cit.id=(#tmp1.minid-1)
                and #tmp1.used is null
       
        --//e. Update flag and runningtotal
        update creditaging_transactions
                set
                        creditaging_transactions.flag_running_total = 'x',
                       
                        creditaging_transactions.running_total =
                                case
                                        when tmp.amt<0
                                                then tmp.running_total_prior-(tmp.amt*-1)
                                        else
                                                tmp.running_total_prior+tmp.amt
                                end
        from creditaging_transactions
        inner join        #tmp1 tmp on
                creditaging_transactions.group_zz=tmp.group_zz
                and creditaging_transactions.patient = tmp.patient
                and creditaging_transactions.invoice = tmp.invoice
                and creditaging_transactions.id = tmp.minid
        where tmp.used is null
        --//re-pull to check for more unupdated running totals
        select @rtotals=count(*) from creditaging_transactions where flag_running_total is null
end

/*
        8. Identify earliest "became credit" transaction
*/
--//a. Identify first "became credit" transaction
drop table creditaging_credit_index
select
        cit.group_zz,
        cit.patient,
        cit.invoice,
        cit.inv_num,
        min(cit.id) as minid,
        convert(bigint,0) as newminid,
        convert(varchar(255),'') as audit_trail
        into creditaging_credit_index
from creditaging_transactions cit
where cit.running_total<0
group by
        cit.group_zz,
        cit.patient,
        cit.invoice,
        cit.inv_num
--//b. Test for invoices that became positive again after the credit to re-establish
haspositive: --//label to restart process later

update cci
        set cci.newminid=ct.id
from creditaging_credit_index cci
inner join creditaging_transactions ct on
        cci.group_zz=ct.group_zz
        and cci.patient=ct.patient
        and cci.invoice=ct.invoice
where
        ct.running_total>=0
        and ct.id>cci.minid

--//c. Find new negative
update cci
        set cci.newminid=ct.id
from creditaging_credit_index cci
inner join creditaging_transactions ct on
        cci.group_zz=ct.group_zz
        and cci.patient=ct.patient
        and cci.invoice=ct.invoice
where
        ct.running_total<0
        and ct.id>cci.newminid
        and cci.newminid<>0

--//d. Push new negative, put new negative id as min
update cci
        set cci.minid=cci.newminid
from creditaging_credit_index cci
inner join creditaging_transactions ct on
        cci.group_zz=ct.group_zz
        and cci.patient=ct.patient
        and cci.invoice=ct.invoice
        and cci.newminid=ct.id
where
        ct.running_total<0
        and cci.newminid<>0

--//e. Save for audit
update cci
        set
        cci.audit_trail=(cci.audit_trail + ', ' + convert(varchar(255),cci.newminid))
from creditaging_credit_index cci
where cci.newminid<>0

--//f. Clean up newminid
update cci
        set
        cci.newminid=0
from creditaging_credit_index cci
where cci.newminid<>0

--//g. Verify complete
if        (
        select count(*)
        from creditaging_credit_index cci
        inner join creditaging_transactions ct on
                cci.group_zz=ct.group_zz
                and cci.patient=ct.patient
                and cci.invoice=ct.invoice
        where
                ct.running_total>=0
                and ct.id>cci.minid
        )>0
        begin
                --print 'has positive'
                goto haspositive
        end
       

--//h. Flag earliest "became (and stayed) credit" transaction
update ct
        set flag_becamecredit = 'x'
from creditaging_transactions ct
inner join creditaging_credit_index cci on
        ct.group_zz=cci.group_zz
        and ct.patient=cci.patient
        and ct.invoice=cci.invoice
        and ct.id=cci.minid
/*
        9. Create the final credit aging table
*/
drop table creditaging_final
select
        ci.group_zz,
        ci.patient,
        ci.invoice,
        ci.inv_num,
        ci.inv_bal,
        dbo_dn100.name as location,
        dbo_invoice.fsc,
        dbo_invoice.ser_dt,
        dbo_dn19.name as fsc_name,
        dbo_dn77.category_name as inv_rpt_cat1,
        ci.as_of,
        bc.id,
        bc.post_dt_1,
        bc.flag_becamecredit,
        bc.transaction_zz,
        (floor(convert(float,ci.as_of)) - floor(convert(float,bc.post_dt_1))) as age,
        case
                when (floor(convert(float,ci.as_of)) - floor(convert(float,bc.post_dt_1))) <31
                        then '0-30'
                when (floor(convert(float,ci.as_of)) - floor(convert(float,bc.post_dt_1))) <61
                        then '31-60'
                when (floor(convert(float,ci.as_of)) - floor(convert(float,bc.post_dt_1))) <91
                        then '61-90'
                when (floor(convert(float,ci.as_of)) - floor(convert(float,bc.post_dt_1))) <121
                        then '91-120'
                when (floor(convert(float,ci.as_of)) - floor(convert(float,bc.post_dt_1))) >120
                        then '121+'
        end as age_bucket
        into creditaging_final
from creditaging_index ci
inner join idx.dbo.invoice dbo_invoice on
        dbo_invoice.group_zz=ci.group_zz
        and dbo_invoice.patient=ci.patient
        and dbo_invoice.invoice=ci.invoice
inner join idx.dbo.dn100 dbo_dn100 on
        dbo_invoice.loc=dbo_dn100.record_number
inner join idx.dbo.dn19 dbo_dn19 on
        dbo_invoice.fsc=dbo_dn19.record_number
inner join idx.dbo.dn77 dbo_dn77 on
        dbo_dn19.reporting_category__1=dbo_dn77.record_number
left join
        (        --join to transaction table that identifies when the invoice became credit
                select
                        group_zz,
                        patient,
                        invoice,
                        inv_num,
                        transaction_zz,
                        running_total,
                        flag_becamecredit,
                        id,
                        post_dt_1,
                        post_pd
                from creditaging_transactions
                where flag_becamecredit = 'x'
        ) bc on
ci.group_zz=bc.group_zz
and ci.patient=bc.patient
and ci.invoice=bc.invoice
--where ci.exclude is null

---