Help with SQL Query

I have a financial database which tracks charges and payments form clients.
I need to create a query which returns only those months when a client
hasn't paid in full and the amount still owing. So, if the table looks like
this:
client date(mm/dd/yyyy) charge
payment
smith 12/01/2006 400.00
smith 12/23/2006
250.00
smith 12/28/2005
50.00
smith 01/01/2006 400.00
smith 01/13/2006
400.00
jones 02/01/2006 400.00
jones 02/05/2006
350.00
the resulting data would look like this:
smith 12/2005 100.00
jones 02/2006 50.00
thanks.
steve.Since you didn't provide DDL or insert scripts this is what I cooked up
I don't think the data for smith is correct, and what is the full
amount? 800?
create table blah (client varchar(50),date datetime, charge decimal
(10,2))
insert into blah
select 'smith', '12/01/2006', 400.00 union all
select 'smith', '12/23/2006', 250.00 union all
select 'smith' , '12/28/2005', 50.00 union all
select 'smith' , '01/01/2006', 400.00 union all
select 'smith' , '01/13/2006', 400.00 union all
select 'jones' , '02/01/2006', 400.00 union all
select 'jones' , '02/05/2006', 350.00
select client,800 -sum(charge) ,datepart(m,date) ,datepart(yyyy,date)
from blah
group by client,datepart(m,date),datepart(yyyy,da
te)
having sum(charge) < 800
http://sqlservercode.blogspot.com/|||SELECT
CLIENT,CONVERT(VARCHAR,DATEPART(mm,getda
te()))+'/'+CONVERT(VARCHAR,DATEPART(
year,getdate())) AS MONTHYEAR, CHARGE
WHERE CHARGE <= 100
Lemme me know if this is what your are looking for...
Thanks,
Sree
"molsonexpert" wrote:

> I have a financial database which tracks charges and payments form clients
.
> I need to create a query which returns only those months when a client
> hasn't paid in full and the amount still owing. So, if the table looks lik
e
> this:
> client date(mm/dd/yyyy) charge
> payment
> smith 12/01/2006 400.00
> smith 12/23/2006
> 250.00
> smith 12/28/2005
> 50.00
> smith 01/01/2006 400.00
> smith 01/13/2006
> 400.00
> jones 02/01/2006 400.00
> jones 02/05/2006
> 350.00
> the resulting data would look like this:
> smith 12/2005 100.00
> jones 02/2006 50.00
> thanks.
> steve.
>
>|||select client
, max(date) as LastActivityDate
-- Assuming the last date soemthign happened is the one you want to show
, sum(charge-payment) as Balance
-- above can also be:
--, Sum(charge) - sum(payment) as Balance
from MyPaymentsTable
group by client
having sum(charge-payment) <> 0
-- the above returns anyone with a credit or debit balance
-- the below returns only clients who owe money
-- or having sum(charge-payment) > 0
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:%23bQ2OayKGHA.952@.TK2MSFTNGP10.phx.gbl...
> I have a financial database which tracks charges and payments form
clients.
> I need to create a query which returns only those months when a client
> hasn't paid in full and the amount still owing. So, if the table looks
like
> this:
> client date(mm/dd/yyyy) charge
> payment
> smith 12/01/2006 400.00
> smith 12/23/2006
> 250.00
> smith 12/28/2005
> 50.00
> smith 01/01/2006 400.00
> smith 01/13/2006
> 400.00
> jones 02/01/2006 400.00
> jones 02/05/2006
> 350.00
> the resulting data would look like this:
> smith 12/2005 100.00
> jones 02/2006 50.00
> thanks.
> steve.
>|||-- This return months where the clients
-- charges exceed the payments. I'm
-- assuming that in your test data, the two entries for
-- 'smith' in december should be for 2005 not 2006
SELECT Client,
DATEPART(year,thedate),
DATEPART(month,thedate),
SUM(COALESCE(Charge,0)-COALESCE(Payment,0))
FROM ChargesAndPayments
GROUP BY client,
DATEPART(year,thedate),
DATEPART(month,thedate)
HAVING SUM(COALESCE(Charge,0)-COALESCE(Payment,0))>0|||I should have included the isnull or coalesce around the charge and payment
fields as markc600 did below.
Without the isnull or coalesce, you will get back nulls instead of the
correct values.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23x5XzwyKGHA.3260@.TK2MSFTNGP11.phx.gbl...
> select client
> , max(date) as LastActivityDate
> -- Assuming the last date soemthign happened is the one you want to show
> , sum(charge-payment) as Balance
> -- above can also be:
> --, Sum(charge) - sum(payment) as Balance
> from MyPaymentsTable
> group by client
> having sum(charge-payment) <> 0
> -- the above returns anyone with a credit or debit balance
> -- the below returns only clients who owe money
> -- or having sum(charge-payment) > 0
> "molsonexpert" <imdrunk@.work.ca> wrote in message
> news:%23bQ2OayKGHA.952@.TK2MSFTNGP10.phx.gbl...
> clients.
> like
>