I know this is a mess - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I know this is a mess

Author

Topic

boy_1der
Starting Member

6 Posts

Posted - 2009-04-12 : 23:26:07

Ok i know this is a mess, but I am trying to write a stored procedure with the ideas working below. The select statements works fine. I need
help with the if statement and how to show the AR column. I am expecting to see results as follow. Help, Ideas, or GOOD refrences are appreciated.

10x is a 10digit number

category practiceid batchactper catactper catid AR validation
xxx 10x 200805 200805 10x 300.23 ar_ok

Declare

@AR money,
@acctperiod varchar(6)

set @AR = (select sum(amount) from tbldchrg) + (select sum(amount) from tbldpaym) + (select sum(amount) from tbldadj);
set @BatchAR =(select sum(totchrg) from tbldbatch)+(select sum(totpaym)from tbldbatch) + (select sum(totadj)from tbldbatch) -(select sum(totkeyahead)from tbldbatch);

select tmp.category,
tbldpractice.idcontract as 'practiceid', tbldbatch.actper as 'batchactper',
tmp.actper as 'catactper', tmp.idcontract as 'catID',tmp.validation
from tbldpractice
inner join tbldbatch on
tbldpractice.idcontract = tbldbatch.idcontract
inner join
(select actper,idcontract,'client' as category from tblsperfclient
union
select actper,idcontract,'Dos' from tblsARAgedDOS
union
select actper,idcontract,'Doe' from tblsARAgedDOE
union
select actper, idcontract,'CredBal'from tblsCreditBalance
union
select actper, idcontract,'CredBalSG'from tblsCreditBalanceSiteGrp
)tmp
on
tbldbatch.idcontract = tmp.idcontract
where not exists( select 1 from tbldbatch where actper =tmp.actper)

If(@AR == @BatchAR)
tmp.validation = 'ok'

else

If (@AR < @BatchAR)
tmp.validation ='AR_low'

else

if (@AR > @BatchAR)
tmp.validation ='AR_high'

boy_1der
Starting Member

6 Posts

Posted - 2009-04-12 : 23:30:07

sorry about the confusing results but here is another try.

catergory practiceid batchactper catactper catid AR Validation
xyz 10x 200805 200805 10x 300.23 ar_0k

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 13:21:16

can i ask what your exact reqmnt is? explain giving some sample data in below format what you want

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 13:46:04

[code]Declare

@AR money,
@acctperiod varchar(6)

set @AR = ;

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)
from tbldbatch

select tmp.category,
tbldpractice.idcontract as 'practiceid',
tbldbatch.actper as 'batchactper',
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
from tbldpractice
inner join tbldbatch on tbldpractice.idcontract = tbldbatch.idcontract
inner join (
select actper, idcontract, 'client' as category from tblsperfclient
union select actper, idcontract, 'Dos' from tblsARAgedDOS
union select actper, idcontract, 'Doe' from tblsARAgedDOE
union select actper, idcontract, 'CredBal'from tblsCreditBalance
union select actper, idcontract, 'CredBalSG'from tblsCreditBalanceSiteGrp
) as tmp on tbldbatch.idcontract = tmp.idcontract
where not exists (select * from tbldbatch where actper = tmp.actper)[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources