SQL/SQL Server - What would you do different

[BillBoeBaggins]

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...

--//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)

[BillBoeBaggins]

/*
	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