Looking For How To Return The Previous Payment

Aug 21, 2007

I use the following sql in a view to return the last payment date and amount made by clients. What I need is a way to return the payment date and amount for the payment prior to the last one.

Any help is appreciated very much,

SELECT     dbo.tblPaymentReceipts.Client_ID, dbo.tblPaymentReceipts.PaymentDate AS LastPayDate, SUM(dbo.tblPaymentReceipts.AmountPaid) AS LastPayAmt
FROM         dbo.tblPaymentReceipts INNER JOIN
                          (SELECT Client_ID, MAX(PaymentDate) AS LastPayDate
                            FROM dbo.tblPaymentReceipts AS tblPaymentReceipts_1
                            GROUP BY Client_ID) AS A ON dbo.tblPaymentReceipts.Client_ID = A.Client_ID AND dbo.tblPaymentReceipts.PaymentDate = A.LastPayDate
GROUP BY dbo.tblPaymentReceipts.Client_ID, dbo.tblPaymentReceipts.PaymentDate 

View 6 Replies


ADVERTISEMENT

How To Return Records Based On Previous Month

Jan 11, 2005

I'm storing records that contain a date/time data type. I am needing two links on a reports page (asp), the first should return all records for the current month and the second link should return all records for the last three months (including current month). I have no idea how to just sort by month.

I'm also not sure what to include here in this post to help you answer my question. On the form that is submitted initially the text field is named "txtSubmitDate" and in the database it's stored in a field called "submitdate" and is 8 characters in length.

I've tried:
'SELECT TODAY'S MONTH
SqlJunk = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE())-1"

'SELECT TODAY'S MONTH and the last 2 months
SqlJunk2 = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE()) OR MONTH(submitdate) = MONTH(GETDATE())-1 OR MONTH(submitdate) = MONTH(GETDATE())-2 ORDER BY submitdate ASC"

These are not working because it can't handle the change in year (going from january 2005 back to december 2004, etc).

Any ideas?

View 7 Replies View Related

Transact SQL :: Return Previous Row Value If It Is Negative In Current Row

Sep 25, 2015

I need to return the previous row value if it is negative in current row.  For example, in the below table for ID=7 i need the value 1305(ID=4) since 6,5 are negative values. 

Existing
  values

           ID
       Input
           ID
     Input

[code]...

View 9 Replies View Related

T-SQL (SS2K8) :: Calculate And Return Previous Date At 18:00 Hours

Mar 4, 2014

How can I calculate and return the previous Date at 18:00 Hours?

Here is a miserable attempt:

DECLARE @RunDate SmallDateTime
DECLARE @CurrentDate SmallDateTime
DECLARE @RunDateWoTime SmallDateTime
SET @CurrentDate = GETDATE()

SET @RunDate = DATEADD(day,-1,@CurrentDate)-- AS CurrentDate
SELECT @RunDate AS RunDate

-- Desired Result is the following:

-- 2014-03-03 18:00

View 9 Replies View Related

Pls Help W/ A Query To Return Running Balances From The Previous Rolling 3 Month Period.

Oct 18, 2007

Please refer to this table in this discussion:






Charges

Date


1

1/1/07


2

2/1/07


3

3/1/07


4

4/1/07


5

5/1/07


6

6/1/07


7

7/1/07


8

8/1/07


9

9/1/07


10

10/1/07


11

11/1/07


12

12/1/07
What i'm trying to do is return a result with total charges from 3 months previous based on a date parameter or just as a whole for that matter. For example:

If my date parameter is 6/14/07, i'd like my total charges to be 15 (6+5+4).
If my date parameter is 7/10/07, the charges would be 18 (7+6+5)

I hope that makes sense of where i'm going with this. I've played with this using the "Guru's Guide" solution by Ken Henderson, but still to no avail yet. Here's his code syntax:





Code Block

SELECT a.DayCount, a.Sales, SUM(b.Sales)
FROM Sales a CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Here is the result set i'm looking for:






Charges

Date


1

1/1/07


2

2/1/07


6

3/1/07


9

4/1/07


12

5/1/07


15

6/1/07


18

7/1/07


21

8/1/07


24

9/1/07


27

10/1/07


30

11/1/07


33

12/1/07
Each date's charges are a culmination of 3 months worth of charges.

Any help would be greatly appreciated. I'll be trying to figure this one out concurrently and if i do, i'll post back!

Thanks in advance!

View 6 Replies View Related

How To Get Last Payment And Amt Of All

Jun 20, 2007

This will get me what I need based on an entered client id, but what is I want it to return the last payment date and amt for all loans? I tried removing the two where clauses and it only returned the last payment entered but not for all loans.SELECT dbo.tblLoan.Client_ID, MAX(dbo.tblPayments.PaymentDate) AS [Last Pay Date], SUM(dbo.tblPayments.AmountPaid) AS [Last Pay Amt]
FROM dbo.tblLoan INNER JOIN
dbo.tblPayments ON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_ID

WHERE (dbo.tblLoan.Client_ID = @Client_ID)
AND dbo.tblPayments.PaymentDate =
(
SELECT TOP 1 p.PaymentDate
FROM dbo.tblPayments p
INNER JOIN dbo.tblLoan l
ON l.Loan_ID = p.Loan_ID
WHERE l.Client_ID = @Client_ID
ORDER BY p.PaymentDate DESC
)

GROUP BY dbo.tblLoan.Client_ID
 

View 13 Replies View Related

How To Get Last Payment Made

Jun 20, 2007

I need to get the clientid, last payment date, and last payment amount. I have tried using MAX but this does not work. Can anyone see what I'm doing wrong? I get all payments and all dates. I'm summing the payment amount incase they made two payments on the same day, not likely but...
SELECT     dbo.tblLoan.Client_ID, MAX(dbo.tblPayments.PaymentDate) AS [Last Pay Date], SUM(dbo.tblPayments.AmountPaid) AS [Last Pay Amt]FROM         dbo.tblLoan INNER JOIN                      dbo.tblPayments ON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDGROUP BY dbo.tblLoan.Client_IDHAVING      (dbo.tblLoan.Client_ID = @Client_ID) 

View 3 Replies View Related

How To Handle Payment Schedule

Mar 2, 2007

I'm trying to learn how to handle a payment schedule for small loan payments. I have the following structure:
tblClient, ClientID
tblLoan, LoanID, ClientID
tblPayments, PayID, PayDate, PayAmt, LoanID
tblLoanPaymentSchedule, ScheduleID, LoanID, PayDueDate, PayDueAmt, ReminderPrintDate, ReminderSentDate, PaymentRecDate
I have a page that batches (holds) all the payments in a work table and I can post them to the correct loan record but I'm just not sure how I would ask a stored procedure to mark the tblLoanPaymentSchedule with the PaymentRecDate for each payment posted. The schedule is pregenerated so that a payment due reminder can be sent out.
I appreciate any suggestions,

View 1 Replies View Related

Help With Keeping Track Of Payment

Nov 13, 2007

Hello All,

I have a problem concerning keeping track of a value within a query.
I have a table that tracks invoices recieved and payments made.
For each invoice number there may be multiple payments made against it.
I need something that will check and make sure that each invoice number has its payments equal to its received amount.

Any help would be greatly appreciated.
Thanks,

View 5 Replies View Related

Find Cust Invoice With No Payment

Oct 18, 2001

Hi,

I'm using SQL Server 7. I have two tables as follows:



Table Invoices:
CustNo
DocDate
Amount

Table Payments:
CustNo
DocDate
Amount

Can anyone think of a process to find the invoice with no payment for each customer? I need to do some kind of process of elimination; I suppose.

Any Suggestions?
Thanks,
Denise

View 3 Replies View Related

SQL Statement To Generate Customer List Whose Last Payment Date Is Jan 04

Jul 20, 2005

Hi all,I have a problem trying to generate the u/m list of customer.I am trying to generate a list of customer whoes last commence date isjan 04 to current.It is part of a billing system which the customer come in and pay fortheir season parking in carpark. They can pay for various periodshortest being 1 week.so i will have customer paying for 1 week, 1 month, 2 months or even 1year. Everytime a customer come in to pay, a new line at the incvoicewill be generated.My DB structure is as followedCustomer Table-Cust_Acc_No ----> (Primary Key)-Customer Name-Customer AddressInvoiceCust_acc_no-- Link to customer tableInvoice_no -- primary keyinvoice detailsinvoice_no -- link to invoice tablecommence_dateexpiry_dateamount_paidif i do aselect * from customer a, invoice b, invoice_details cwhere a.cust_acc_no=b.cust_acc_no andb.invoice_no=c.invoice_no andc.commence_date>1/1/04it doesnt works as it will showjohn, 1/1/04 - 31/1/04john, 1/2/04 - 29/1/04i do not want repetitive customer number just the latest commencedate.can anyone help?thanks

View 1 Replies View Related

Transact SQL :: How To Deduct Retention Amount From Arrears Payment

Aug 27, 2015

We have retention policy , and pay at the time year completion , now policy change and it is converted from yearly to monthly and this with effect from April-15. 

if calculate the pay system will generate the Arrear payment of the employee from the month of April  onward but i already paid the retention amount for month for two month April and May which i need to deduct the same otherwise this will double amount .

View 2 Replies View Related

Date Of Previous Year Previous Month

Sep 10, 2013

Need getting a query which I will get previous year, previous month first day everytime i run the query.

Ex: If i run the script on 9/10/2013 then result should be 8/1/2012. (MM/DD/YYYY)

View 4 Replies View Related

Search Day Before Last Day Of The Previous Month + Day Last Day Of The Previous Month

Dec 22, 2007

how can i do this
search between 2 rows
day before Last day of the Previous Month + day Last day of the Previous Month"





Code BlockSELECT empid, basedate, unit_date, shift, na
FROM dbo.empbase
WHERE (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5)

AND

(basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)




TNX

View 5 Replies View Related

Return Error Code (return Value) From A Stored Procedure Using A Sql Task

Feb 12, 2008


I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.

I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.

I have two parameters mapped:

tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1

I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.

The first part of the sp is below and I set the value @i and return.


CREATE procedure [dbo].[Marketing_extract_history_load_TEST]

@table_name varchar(200),

@i int output

as

Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.

View 2 Replies View Related

How To Return SqlDataReader And Return Value (page Count) From SPROC

Jan 2, 2006

This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0

    Function get_all_events() As SqlDataReader
        Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
        Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
        parameterState.Value = Request.Params("State")
        myCommand.Parameters.Add(parameterState)

        Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
        parameterPagesize.Value = 20
        myCommand.Parameters.Add(parameterPagesize)

        Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
        parameterPagenum.Value = pn1.SelectedPage
        myCommand.Parameters.Add(parameterPagenum)

        Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
        parameterPageCount.Direction = ParameterDirection.ReturnValue
        myCommand.Parameters.Add(parameterPageCount)

        myConnection.Open()
        'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
        Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    End Function

Variable Pages is global integer.

This is what i am calling
        DataList1.DataSource = get_all_events()
        DataList1.DataBind()

How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck

View 3 Replies View Related

'Return' Statement In A Function,Get,or Operator Must Return A Value....Question

Aug 28, 2007


I'm a Reporting Services New-Be.

I'm trying to create a report that's based on a SQL-2005 Stored Procedure.

I added the Report Designer, a Report dataset ( based on a shared datasource).

When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.

I'll only reproduce one instance of the error for the sake of brevity.

[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.

I've searched on this error and it looks like it's a Visual Basic error :

http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx

My guess is that BIDS is creating some VB code behind the scenes for the report.

I can't find any other information that seems to fit this error.

The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.

BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.

Can anyone help ?

Thanks,

View 5 Replies View Related

Using Output Clause And Return Timestamp And Return Value

Oct 28, 2006

I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)

I have used the old goto method for trapping errors committing or rolling back the transaction.

Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?

I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).

Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?

So how to return timestamp and a return value and how to check for concurrency all in the try/catch.

by the way I read that you could not return an identity in the output clause but I had no problem.

Thanks for help on this

SM haig

View 5 Replies View Related

Why SP Return SQLException When No Data Return ?

Jul 24, 2006

hi, good day,

i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(

anyone know how to handle the problem when SP return no data ?

thanks in advance

View 1 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 Replies View Related

Next/previous Record

May 2, 2007

Hi. Is it possible in SQL query to find record previous or next in comparison with record found with clause WHERE (example of query below)? I need to find record with ProblemID less than or greater than 10. Regards Pawelek.
SELECT     ProblemIDFROM         dbo.tblProblemsWHERE     (ProblemID = 10)

View 2 Replies View Related

Last And Previous Query

Mar 20, 2004

I'm kindof at a lose for how to ask my database to show me the last payment amount and payment date for clients and also show me the previous payment amount and payment date for clients based on a date range.

Could anyone offer any pointers or examples?

Thank you very much.

We could use a very simple table and field setup like this just so you could show an example:

tblClient
Client_ID

tblOrder
Order_ID
Client_ID

tblOrderPayments
Payment_ID
Order_ID
Client_ID
PaymentDate
PaymentAmount

View 3 Replies View Related

How To Get Last Day Of Previous Month

Jun 7, 2000

Hi Friends
do you have any soluyion or function for last day of Previous Month
thanks
Nilesh

View 1 Replies View Related

Previous Value Best Method

Jan 25, 2006

I'm not really strong in SQL. My goal is to compare the beginning mileage of a vehicle record with it's previous ending mileage reading. I have something that works, but it feels clunky. I wonder if there is a better method, ie a join. Here's what I have:

SELECT A.Trolley_num, A.Date, A.Speedo_start, A.Speedo_end,
(SELECT B.Speedo_end FROM Daily_Trolley AS B
WHERE B.Trolley_num = A.Trolley_num
AND B.Date =
(SELECT Max(Date) FROM Daily_Trolley AS C WHERE C.Trolley_num = A.Trolley_num
And C.Date < '1/23/2005')) AS PrevSpeedoEnd
FROM Daily_Trolley AS A
WHERE A.Date='1/23/2005'

ps: I inherited this db; I'm aware that "Date" should not have been used as a field name.

View 2 Replies View Related

Previous Value Retrieval

Jul 10, 2006

I have a table of vehicle usage records with fields including vehicle number, driver, date/time, starting odo, ending odo. I'm looking to compare the starting odo of a given record to the ending odo of the last time that vehicle was used. I also need to return other fields from that previous record, like the date/time and driver.My users basically run a report for any given day, and the vehicles used on that day need to be compared to their most recent usage (most recent relative to the record at hand). This is to ensure that the vehicle hasn't been used in the interim and not recorded (which may indicate theft).I've got a very convoluted process in place, but I'd like to see if it can be streamlined. The current process is done in Access and has a number of queries built on other queries. It is very slow. The data is in SQL Server. Any thoughts on the best ways to accomplish this?TIA

View 4 Replies View Related

Previous Months

Mar 19, 2007

I have a View there I need my criteria to select 13 months from previous month.

Example. This is 3/1/2007 - I need to query 2/1/2007 to 2/1/2006.

View 7 Replies View Related

How To Get The Previous And Next IDs From A Table

May 21, 2007

I have a table that holds images. I want to get the previous image id and the next image id based on the current ID:

ImageID

4
21
56
74
99

So if the current image ID is "21" I'd like to return 4 and 56.

Thanks!!

View 3 Replies View Related

From Previous Post

Sep 28, 2007

Ok so someone answered my last quesiton about finding what tables are in a Stored procedure. Is there an opposite of that. If I wanted to type in a table and it shows all the stored procedures that use that table.

View 3 Replies View Related

Get Previous Record

Dec 5, 2005

Please see DDL and INSERT statements below.Let's say that some process throws out the second row, where theClocktime = '02/01/2005 12:34'Without the use of a cursor, how can I retrieve the PREVIOUS value forthat employee? Pseudo SQL might be something like:SELECT*FROMtblTestWHEREfldCLocktime = THE-ONE-IMMEDIATELY-BEFORE '02/01/2005 12:34'ANDfldEmployeeID = 1TIAEdwardif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTest]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblTest]GOCREATE TABLE [dbo].[tblTest] ([fldTestID] [int] IDENTITY (1, 1) NOT NULL ,[fldEmployeeID] [int] NULL ,[fldClocktime] [datetime] NULL ,) ON [PRIMARY]GOINSERT INTO tblTest(fldEmployeeID,fldClocktime)VALUES(1,'01/01/2005 12:34')INSERT INTO tblTest(fldEmployeeID,fldClocktime)VALUES(1,'02/01/2005 12:34')INSERT INTO tblTest(fldEmployeeID,fldClocktime)VALUES(1,'03/01/2005 12:34')

View 3 Replies View Related

Last Day Of Previous Month

Jul 20, 2005

How can i calculate the last day of the previous month?Help me,please

View 3 Replies View Related

PREVIOUS FUNCTION

Mar 27, 2008

Has anyone used the 'previous' function to return the previous row of data? I have tried this
=previous(Fields!SomeColumn.value) - when I run the report, no data returns.

View 4 Replies View Related

Previous Month On First Day

Aug 29, 2007

Newbie question. I am using a query that pulls month-to-date data that has the following where clause:

WHERE (MONTH(datefield) = MONTH(GETDATE())) AND (YEAR(datefield) = YEAR(GETDATE()))

this works just fine but what I would like for it to do is give me the previous month of data if the if it's
the first day of the month and then any other day give me month to date. Is this possible?

Thanks in advance,

Marco

View 12 Replies View Related

How To Sum Previous Row Values?

May 15, 2008

Hi,

I need your help for my SQL query. I have a table like this

ClmA ClmB ClmType
------+------+----------
1 | 10 | 0
2 | 20 | 0
3 | 30 | 1
4 | 40 | 0
5 | 50 | 1

And its the result that i want to get.

ClmA ClmB ClmType ClmResult
------+------+---------+-------
1 | 10 | 0 | 10
2 | 20 | 0 | 30
3 | 30 | 1 | 30
4 | 40 | 0 | 70
5 | 50 | 1 | 80


Let me explain. When retrieving a row, an extra column should be added.It's value should be the sum of previous rows whose type is the same with the encountered one. I made it with a function but it's performance was terible with large tables. I have tables larger then fifty housands rows.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved