Join with Subselect and Sum

I have an appointment table (appts) and a charge table (charge).
For the day the query is run, I want to return anyone with an appointment
that has a balance on the charge table.
I am looking at doing a subselect but I'm having a syntax problem.
select APPTS.ACCOUNT
from APPTS
where APPTS.DATE = getdate()
and company = 'main'
INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
FROM CHARGE
WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
What am I doing incorrectly on the subselect?
THANKS,
MEGyou are missing criteria to justify your first where clause
where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
> SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
> FROM CHARGE
> WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0) = [whatever][/colo
r]
"MEG" wrote:
> I have an appointment table (appts) and a charge table (charge).
> For the day the query is run, I want to return anyone with an appointment
> that has a balance on the charge table.
> I am looking at doing a subselect but I'm having a syntax problem.
> select APPTS.ACCOUNT
> from APPTS
> where APPTS.DATE = getdate()
> and company = 'main'
> INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
> where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
> SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
> FROM CHARGE
> WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
> What am I doing incorrectly on the subselect?
> THANKS,
> MEG|||Try,
select
a.ACCOUNT
from
APPTS as a
INNER JOIN
CHARGE as c
on a.COMPANY = c.COMPANY
and a.ACCOUNT = c.ACCOUNT
where
a.[DATE] = convert(char(8), getdate(), 112)
and a.company = 'main'
group by
a.ACCOUNT
having
SUM(c.CHGAMOUNT - c.PAYINS1 - c.PAYINS2 - c.PAYGUAR - c.WRITEOFF - c.ADJUST
+ c.DEDUCTIBLE - c.ONACCT) > 0
AMB
"MEG" wrote:

> I have an appointment table (appts) and a charge table (charge).
> For the day the query is run, I want to return anyone with an appointment
> that has a balance on the charge table.
> I am looking at doing a subselect but I'm having a syntax problem.
> select APPTS.ACCOUNT
> from APPTS
> where APPTS.DATE = getdate()
> and company = 'main'
> INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
> where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
> SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
> FROM CHARGE
> WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
> What am I doing incorrectly on the subselect?
> THANKS,
> MEG|||On Wed, 7 Sep 2005 12:16:03 -0700, MEG wrote:

>I have an appointment table (appts) and a charge table (charge).
>For the day the query is run, I want to return anyone with an appointment
>that has a balance on the charge table.
>I am looking at doing a subselect but I'm having a syntax problem.
>select APPTS.ACCOUNT
>from APPTS
>where APPTS.DATE = getdate()
>and company = 'main'
>INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
>where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
>SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
>FROM CHARGE
>WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
>What am I doing incorrectly on the subselect?
Hi MEG,
The subselect in itself looks okay. But the order of the phrases in the
complete query is not quite right. Try:
select APPTS.ACCOUNT
from APPTS
INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
where APPTS.DATE = getdate()
and company = 'main'
and ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) -
SUM(ONACCT)
FROM CHARGE
WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) >
0)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

join two tables and only return the latest data for the child table

I have two table, tblCharge and tblSentence, for each charge, there are one or more sentences, if I join the two tables together using ChargeID such as:

select * from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID

, all the sentences for each charge are returned. There is a field called DateCreated in tblSentence, I only want the latest sentence for each charge returned, how can I do this?

I tried to create a function to get the latest sentence for a chargeID like the following:

select * from tblCharge c join tblSentence s on s.SentenceID=LatestSentenceID(c.ChargeID) but it runs very slow, any idea to improve it?

thanks,

if you are on 2005, you can use row_number(), as follows:

select * from(
select c.*, s.*, row_number() over(partition by s.ChargeID order by DateCreated desc) as rn
from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID
) t
where rn=1
|||

Your suggestion works, thanks.

But when I modified the query to:

select c.*, s.*, row_number() over(partition by s.ChargeID order by DateCreated desc) as rn
from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID

where rn=1

the query fails with the error "Invalid column name 'rn'.", why?

|||Because Where applies to the source fields, not the result fields, so your alias "as rn" hasn't been applied yet.

That's why Akuz nested it inside another Select. The outer Select/Where can use the alias.