help w/ sql - 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

SQL Server 2000 Forums

Transact-SQL (2000)

help w/ sql

Author

Topic

ncr333
Starting Member

1 Post

Posted - 2008-08-11 : 18:04:55

hi, i'm trying to combine the following two statements into one and i am having a problem as when i do, the calculation on some of the values are skewed (e.g. DSO). does anyone have any suggestions on how to do this? is it feasible? Thanks!

STATEMENT 1
select distinct c.aban8, c.abalph, d.a5trar, e.pnptd, d.a5acl, a.rtcrcd, a.rtaap/100 / (sum(b.glaa/100)/-90) as 'DSO', a.rtaap/100 as 'OpenBal', a.rtoinv as 'Ttl OpenInvs',
a.rtpda/100 as 'PastDue Bal', a.rtpdin as 'Ttl O/S Invs', a.rtcurr/100 as 'Current Bal', a.rtcuin as 'Current Invs',
a.rtuaa/100 as 'Unapplied Cash', f.rsahb/100 as 'HighBal Amt', f.rsdhbj as 'HighBalDt', f.rsdfij as '1st InvDt', f.rsdlij as 'Last InvDt',
f.rsdlp as 'DtLastPd', f.rsalp/100 as 'AmtLastPd', f.rsasty/100 as 'AmtInv-CurrYr', f.rsspye/100 as 'AmtInv-PrevYr', f.rspaap/100 as 'AmtPd',
sum(f.rsmwo/100) as 'Minor W/O Amt', sum(f.rsbdbt)/100 as 'BadDbtAmt', f.rsavdn as 'W. AvgDays'
from proddta.f03b15 a, proddta.F0911 b, proddta.f0101 c, proddta.f0301 d, proddta.f0014 e, proddta.f03b16s f
where a.rtan8 = b.glan8 and a.rtan8 = c.aban8 and a.rtan8 = d.a5an8 and a.rtan8 = f.rsan8 and a.rtpart = f.rspart
and a.rtco = f.rsco and d.a5trar = e.pnptc and b.globj = '4051' and b.gldct <> 'JE' and a.rtco = '01023'
and a.rtpart = 'C' and b.gllt = 'AA' and b.glglc <> 'IC' and d.a5arc <> 'IC' and and a.rtan8 = '149632' and
(DateDiff(day, getdate(), dateadd(day, b.gldgj - cast(b.gldgj/1000 as int)*1000 -1, str(1900 + b.gldgj /1000) + '-1-1')) >= -90
and DateDiff(day, getdate(), dateadd(day, b.gldgj - cast(b.gldgj/1000 as int)*1000 -1, str(1900 + b.gldgj /1000) + '-1-1')) <= 0)
group by c.aban8, c.abalph, a.rtaap, a.rtcrcd, a.rtoinv, a.rtpda, a.rtpdin, a.rtcurr, a.rtcuin, a.rtuaa, d.a5trar, e.pnptd, d.a5acl, f.rsahb, f.rsdhbj,
f.rsdfij, f.rsdlij, f.rsdlp, f.rsalp, f.rsasty, f.rsspye, f.rspaap, f.rsmwo, f.rsbdbt, d.a5arc, f.rsavdn
order by 2

STATEMENT 2
select sum(rzpaap/100) from proddta.f03b14 where rzan8 = '146932' and rzkco = '01023' and
(DateDiff(day, getdate(), dateadd(day, rzdgj - cast(rzdgj/1000 as int)*1000 -1, str(1900 + rzdgj /1000) + '-1-1')) >= -180
and DateDiff(day, getdate(), dateadd(day, rzdgj - cast(rzdgj/1000 as int)*1000 -1, str(1900 + rzdgj /1000) + '-1-1')) <= 0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 00:31:43

What are the fields on which the two queries are related? how do you decide which sum should be included with which row from first query? some data sample might help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources