I'm a relative newbie to sql server and I'm having a problem with a query that uses several inner joins. It's a query that returns data based on all clients who have not paid their bills yet. Basically, everything was working fine until I needed to select the amount of a clients invoice & the amount they were overdue. What happens when I run this is 2 additional fields are returned(the "InvoiceAmt" field & the "OverdueAmt" field). Unfortunately, all of the values within these fields' rows are listed as "NULL", which it incorrect. They should display a value. So it's something wrong with the portion of my statement that begins with "(select Sum..." and ends with "...AdjId desc) as OverdueAmt".
Just in case, I've included the entire statement but in particular, it seems to be the portion of the statement that I mentioned above.
declare @BusSrcId int
declare @InvoiceNo int
select distinct tbilling.billid as InvoiceNo, tclients.clientname as ClientName, tclients.clientholdstmtind as ClientOnHoldInd, tclientcontacts.Billdate, (select Sum(IsNull(tbillingbillcodes.BBCConsFeeAmt,0))+Sum(IsNull(tbillingbillcodes.BBCServFeeAmt,0))
From TBillingBillcodes Where tbillingbillcodes.BBCBillId=@InvoiceNo) as InvoiceAmt,
(Select Top 1 IsNull((tadjustments.AdjBalAmt),0) From TAdjustments Where tadjustments.AdjBillID=@InvoiceNo Order By tadjustments.AdjId desc) as OverDueAmt from tbilling
inner join tclients on tclients.clientid=tbilling.billclientid
inner join tclientcontacts on tclientcontacts.clientcontclientid=tbilling.billclientid
inner join tbussources on tbussources.bussrcid = tclientcontacts.clientcontbussrcid
where tclientcontacts.ClientContBusSrcId=@BusSrcId and tbilling.billpmtstatus != 'PAID'
I'd appreciate any help you can provide on this.
Thanks in advance.