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
>

Help with insert stored procedure

I'm trying to make sure that a user does not allocate more to funds than they have to payments. Here is what my stored procedure looks like now: I listed th error below

ALTER PROCEDURE [dbo].[AddNewFundAllocation]

@.Payment_IDInt,
@.Receipt_IDInt,
@.Fund_IDInt,
@.Amount_allocatedmoney,
@.DateEntereddatetime,
@.EnteredBynvarchar(50)

AS

SELECT (SUM(tblReceiptsFunds.Amount_allocated) +@.Amount_allocated)AStotal_allocations,Sum(tblReceipts.AmountPaid)astotal_payments
FROM tblReceiptsFundsINNERJOIN
tblReceiptsON tblReceiptsFunds.Receipt_ID = tblReceipts.Receipt_ID
WHERE tblReceipts.Payment_ID=@.Payment_ID

IF (total_allocations<total_payments)

INSERT INTO tblReceiptsFunds ([Receipt_ID],[Fund_ID],[Amount_allocated],DateEntered,EnteredBy)Values (@.Receipt_ID,@.Fund_ID,@.Amount_allocated,@.DateEntered,@.EnteredBy)ELSE BEGINPRINT'You are attempting to allocate more to funds than your total payment.'END

I get the following error when I try and save the stored procedure:

Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26

Invalid column name 'total_allocations'.

Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26

Invalid column name 'total_payments'.

Try this or something like it. I'm not sure if the data types are correct.

ALTER PROCEDURE [dbo].[AddNewFundAllocation] @.Payment_IDInt,@.Receipt_IDInt,@.Fund_IDInt,@.Amount_allocatedmoney,@.DateEntereddatetime,@.EnteredBynvarchar(50)ASDeclare @.total_allocationsdecimal(18,2)Declare @.total_paymentsdecimal(18,2)SELECT @.total_allocations = (SUM(tblReceiptsFunds.Amount_allocated) + @.Amount_allocated), @.total_payments =Sum(tblReceipts.AmountPaid)FROM tblReceiptsFundsINNERJOIN tblReceiptsON tblReceiptsFunds.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Payment_ID=@.Payment_IDIF (@.total_allocations < @.total_payments)INSERT INTO tblReceiptsFunds ([Receipt_ID],[Fund_ID],[Amount_allocated],DateEntered,EnteredBy)Values (@.Receipt_ID,@.Fund_ID,@.Amount_allocated,@.DateEntered,@.EnteredBy)ELSE BEGINPRINT'You are attempting to allocate more to funds than your total payment.'END

|||

John,

That worked with one exception, my doing. Do you know how I could get the PRINT (PRINT'You are attempting to allocate more to funds than your total payment.'
) to show on my web page?

I have a try catch like this:

Try

Con.Open()

intInsertCount = cmdInsert.ExecuteNonQuery()

Con.Close()

gvFundAllocations.DataBind()

Catch exAs Exception

Response.Write(ex.Message)

Finally

Con.Close()

Me.txtFundAmount.Text = 0

Me.FundDrop.SelectedValue =""

EndTry

|||

Yes there are 2 ways. 1 set and output parameter, and 2 have that message return in the result set like this.

ALTER PROCEDURE [dbo].[AddNewFundAllocation] @.Payment_IDInt,@.Receipt_IDInt,@.Fund_IDInt,@.Amount_allocatedmoney,@.DateEntereddatetime,@.EnteredBynvarchar(50)ASDeclare @.total_allocationsdecimal(18,2)Declare @.total_paymentsdecimal(18,2)SELECT @.total_allocations = (SUM(tblReceiptsFunds.Amount_allocated) + @.Amount_allocated), @.total_payments =Sum(tblReceipts.AmountPaid)FROM tblReceiptsFundsINNERJOIN tblReceiptsON tblReceiptsFunds.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Payment_ID=@.Payment_IDIF (@.total_allocations < @.total_payments)INSERT INTO tblReceiptsFunds ([Receipt_ID],[Fund_ID],[Amount_allocated],DateEntered,EnteredBy)Values (@.Receipt_ID,@.Fund_ID,@.Amount_allocated,@.DateEntered,@.EnteredBy)Select'All OK!'as messageELSE BEGINSelect'You are attempting to allocate more to funds than your total payment.'as message--PRINT 'You are attempting to allocate more to funds than your total payment.'END

|||

John,

So sorry I just cannot get the message to showup on the web page. Any suggestions?

The stored procedure is doing what it should, if the amount is to high it terminates.

|||

It is not throwing an exception to the web page when the amount is to high, but the stored procedure is terminating. I don't understand this.

|||

The stored procedure is returning a result set. This result being of one row and one column named "message".

A result set can be read using the SqlDataReader or the ExecuteScalar method of the command object.

Do some reading on binding a SqlDataReader to your control, or in using the ExecuteScalar method and you should be OK.

Let me know how you do.