April 20, 2009 at 9:24 pm
My first problem is that I keep getting this error and I can't figure out why.
[Server: Msg 8155, Level 16, State 2, Line 19: No column was specified for column 3 of 'tmp'.]
2. This sp is suppose to calculate the the Accounts Receivable for each one of the joined tables in parenthesis. I am not exactly sure of the results sine I keep getting the error above.
results shoud be:
category practice_id Batchactper AcctRec Validation
--category will give the name of the joined table
--practice_id is a unique identifier for each result
--batchactper is the missing acct.period
--AcctRec is the account rec.for that period
--validation is weather or not the AR matches the BatchAR
any help is appreciated.
Declare
@AR money,
@acctperiod varchar(6),
@BatchAR money
SELECT@ar = SUM(i)
FROM(
select sum(amount) AS i from tbldchrg union all
select sum(amount) from tbldpaym union all
select sum(amount) from tbldadj
) AS d
select@BatchAR = sum(totchrg + totpaym + totadj - totkeyahead)
fromtbldbatch
selecttmp.category,
tbldpractice.idcontract as 'practiceid',
tbldbatch.actper as 'batchactper',
--@ar as 'AcctRec',
--tmp.actper as 'catactper',
--tmp.idcontract as 'catID',
case
when @AR = @BatchAR then 'ok'
when @AR < @BatchAR then 'AR_low'
else 'AR_high'
end as validation
fromtbldpractice
inner jointbldbatch on tbldpractice.idcontract = tbldbatch.idcontract
inner join(
select actper, idcontract,@ar, 'client' as category from tblsperfclient
unionselect actper, idcontract,@ar, 'Dos' from tblsARAgedDOS --AR
unionselect actper, idcontract,@ar, 'Doe' from tblsARAgedDOE --AR
unionselect actper, idcontract,@ar, 'CredBal'from tblsCreditBalance
unionselect actper, idcontract,@ar,'CredBalSG'from tblsCreditBalanceSiteGrp
union select actper, idcontract,@ar 'DenSum'from tblsDenialSum
union select actper, idcontract,@ar,'PercentPaid'from tblsPercentClaimsPaid
union select actper, idcontract,@ar, 'SitePayPerf'from tblsSitePaycl_Perf
union select actper, idcontract,@ar,'SitePayAged'from tblsSitePaycl_ARAged --AR
) as tmp on tbldbatch.idcontract = tmp.idcontract
wherenot exists (select * from tbldbatch where actper = tmp.actper)
April 21, 2009 at 3:57 am
You need to assign a column name to @ar in
select actper, idcontract,@ar, 'client' as category from tblsperfclient
union select actper, idcontract,@ar, 'Dos' from tblsARAgedDOS --AR
union select actper, idcontract,@ar, 'Doe' from tblsARAgedDOE --AR
union select actper, idcontract,@ar, 'CredBal'from tblsCreditBalance
union select actper, idcontract,@ar,'CredBalSG'from tblsCreditBalanceSiteGrp
union select actper, idcontract,@ar 'DenSum'from tblsDenialSum
union select actper, idcontract,@ar,'PercentPaid'from tblsPercentClaimsPaid
union select actper, idcontract,@ar, 'SitePayPerf'from tblsSitePaycl_Perf
union select actper, idcontract,@ar,'SitePayAged'from tblsSitePaycl_ARAged --AR
so
select actper, idcontract,@ar as ar, 'client' as category from tblsperfclient
etc
Mike
April 24, 2009 at 11:13 am
thanks, that took care of the error.
I still need to do a calculation on each on of the union/select tables. I may need to use a group by but unsure how to to that yet.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply