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)