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_ID
GROUP BY dbo.tblLoan.Client_ID
HAVING (dbo.tblLoan.Client_ID = @Client_ID)
View 3 Replies
ADVERTISEMENT
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
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
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 LastPayAmtFROM 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.LastPayDateGROUP BY dbo.tblPaymentReceipts.Client_ID, dbo.tblPaymentReceipts.PaymentDate
View 6 Replies
View Related
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
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
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
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
Sep 6, 2007
Ok I have my login controls working perfectly, my roles function perfect now come time to upload to production server I find I need to convert this to either .sql or .csv file to allow for importation.
Is there a S/W Package available for doing this?
View 1 Replies
View Related
Dec 27, 2007
All I am trying to do is use a SqlDataSource, Read that Data from the Session Variable, Do a RowFilter off the Data and then return the Data (Currently a DataSet but doesnt matter). But Since I dont use SqlDataSource that much not sure all the code I have is neccasry:
protected DataSet GetSearchAppData(string strWhere)
{
DataSet dsSearchAppTable = new DataSet("SearchAppTable");
SqlDataSource ss = new SqlDataSource();
DataView dv = new DataView();
DataSourceSelectArguments dsArgg = new DataSourceSelectArguments();
dsArgg = DataSourceSelectArguments.Empty;
ss = ((SqlDataSource)Session["SearchAppTable"]);
dv = (DataView)ss.Select(dsArgg);
dv.RowFilter = strWhere;
dsSearchAppTable.Tables.Add(dv.Table.Clone());
return dsSearchAppTable;
}
And to mention it, dsSearchAppTable returns a Table with 0 rows. Not sure what the heck I'm doing wrong.
J
View 1 Replies
View Related
Feb 19, 2008
I am trying to make a query that would list all the rows in a table called tickets. It should order the results based on the amount of rows another table has, called rating, but only the rows where the ticketid (primary key) matches and where the date is a required date or higher. It should also order them based on the osid which is located in a systems table (there is a relationship between tickets and systems). So: Order by rows in ratings table relating to ticketid that happened in last week (or another min date)Only display it where osid in systems matchs (easy enough)
View 2 Replies
View Related
Mar 28, 2007
where should be the calculations be made based on performance, in SQL or in front end?
thanks.
-Ron-
View 7 Replies
View Related
Mar 3, 2008
i need help on this one
View 8 Replies
View Related
Aug 14, 2006
Hi all, this probably is a stupid question, but here I go.
I have read in some places that you can't set up an already existent column as IDENTITY, but in Enterprise Manager, you can do it. My question is, how EM can do this? Because I need to do this in a lot of databases, and I have to do this by SQL.
Kind Reggards
Dirceu
View 4 Replies
View Related
Aug 23, 2006
I want to create a page(using GridView) where it will detect changes made in the database and display it for Administrator observation. I've created a table name history for this purpose.
History
guid (uniqueidentifier)
dateCreated(datetime)
lastDateUpdated(datetime)
changesMade(varchar(50)) ---- eg; dropdownlist
oldValue(varchar(50)) ----- compaq
newValue(varchar(50)) -----dell
updateBy(varchar(10)) <------ username of registered user
comNo(int) = foreign key for Computer table
History data will insert data whenever a changes made. Could anyone advise and direct me how to do this function. I was thinking of using stored procedure to insert the data. Thanks in advance.
View 17 Replies
View Related
Apr 13, 2005
Someone please help me with this.
I'm trying to fire off an already created DTS package. This package is stored within SQL Server's -- underneith the Data Transformation Services / Local Packages section.
HOW CAN I FIRE THIS OFF FROM A VB .NET APPLICATION
I'm familiar with strored procedures and using them in vb.net so if somone could lead me down that road I would be very much appriciated.
Thanks in advance everyone,
RB
View 4 Replies
View Related
Sep 12, 2000
Here is the situation. I have an employee who is making changes but I can't prove it. He thinks he knows more than he does and he's mess'n everything up. I would like to know if SQL or some third party product has the capability to see the change and log what was changed with the persons username attached?
View 1 Replies
View Related
Mar 7, 1999
Is it possible to capture in a log, all users who connect to the database?
View 2 Replies
View Related
Apr 25, 2007
Hi guys
Am new in SQL server. i use SQL server 2003 ans am trying to retieve the last three orders made. Can you please help me with my Select statement so that it will only select the last three coz @ the moment it selects everything
CODE:
SELECT dbo.Invoice.InvoiceNo, dbo.Sales.UnitsSold, dbo.Sales.QuantitySold, dbo.Sales.CostAmount, dbo.Item.ItemCode, dbo.Item.ItemDescription,
dbo.Item.Item, dbo.PurchaseOrder.OrderNumber, dbo.PurchaseOrder.OrderDate, dbo.PurchaseOrder.OrderStatus, dbo.PurchaseOrder.QuantityOrdered,
dbo.PurchaseOrder.QuantityReceived, dbo.Customer.CustomerCode
FROM dbo.Invoice INNER JOIN
dbo.Sales ON dbo.Invoice.InvoiceKey = dbo.Sales.InvoiceKey INNER JOIN
dbo.Item ON dbo.Sales.ItemKey = dbo.Item.ItemKey INNER JOIN
dbo.PurchaseOrder ON dbo.Sales.VendorKey = dbo.PurchaseOrder.VendorKey INNER JOIN
dbo.Customer ON dbo.Sales.CustomerKey = dbo.Customer.CustomerKey
Thanks
Noks
View 14 Replies
View Related
Feb 26, 2008
Hello there,Does anyone know of a way to track changes to an SQL Server database so thatI can easily run those changes at a later date?That is, I want to make schema changes, and record those changes so that Ican execute them 6 months later on a copy of the orignal database.Thank you kindly for any ideas anyone may haveJohn
View 20 Replies
View Related
Jul 20, 2005
I have an application that connects and edits a database's tables.My question is, what is the best way to save who did what changes tothis database? I need to be able to display this in the application.The best way I have thought of so far is, to create a new table with'user', 'date', 'table_name', 'primary_key', 'old field_value' and'new_field_value' fields. Then I can save which table row had beenchanged, by which user and on which date.I'm sure that there is a way to do this in SQL Server, but I'm notsure how.Any help would be appriciated.Jagdip
View 1 Replies
View Related
Nov 27, 2006
In every database we have 'sysobjects' table storing names of all objects(including all keys) in our data base. How can I find all columns, that the key is made of? Sorry about my english :-/
View 1 Replies
View Related
Nov 3, 1998
Hi, we are almost finished developing our database and we have a table we want to monitor because it is getting information deleted from it
and it has a delete trigger on it but we want to track the changes to
the table and were wondering how to track specific changes to a user database?
We want to see who is making the change, what the change they are making is and also what is the time they are making it. I have used
SELECT * FROM SYSPROCESSES
and I am running SQL TRACE with filter on MS SQLEW, and MS TRANS, and
Visual Basic with SQL statements on tblRoute,( the table that I want to monitor) and I want to know if there is any other way to monitor this table more closely?
View 1 Replies
View Related
Jan 24, 2005
Hi all, Hope everyone is well, my poor Steelers got chomped and spit, and I've been spending lots of time today eating my words *sigh*.
However, in between all the crow-chomping, I have run into a problem I think I am too closely involved with to see around.
I have two identical tables in two different databases. They contain (for simplicity's sake) a symbol, and a ranking for that symbol. The two tables should be the same, but are sometimes not, so I am trying to figure out a way to select from the tables in such a way that I can say:
"Symbol xxx is ranked nn in table t1, but is ranked mm in table t2"
Perhaps I am getting too fancy, but thought I could do it in a single select.
here's what I have so far:
CREATE TABLE tMyPicks (
sym VARCHAR(5) NOT NULL
, rank INT NOT NULL
)
CREATE TABLE tUrPicks (
sym VARCHAR(5) NOT NULL
, rank INT NOT NULL
)
INSERT INTO tMyPicks (sym, rank)
SELECT 'BFA', 1
UNION SELECT 'BFB', 2
UNION SELECT 'BFC', 3
UNION SELECT 'BFD', 4
UNION SELECT 'IMA', 5
UNION SELECT 'ICU', 6
UNION SELECT 'SOB', 7
UNION SELECT 'SORU', 8
UNION SELECT 'HERE', 9
INSERT INTO tUrPicks (sym, rank)
SELECT 'BFA', 1
UNION SELECT 'BFB', 2
UNION SELECT 'BFC', 3
UNION SELECT 'BFD', 5
UNION SELECT 'IMA', 7
UNION SELECT 'ICU', 6
UNION SELECT 'SOB', 8
UNION SELECT 'SORU', 4
UNION SELECT 'NHERE', 9
select sym, rank
from (select sym, rank from tMyPicks
union all
select sym, rank from tUrPicks) AS MyUnionTable
group by sym, rank
having count(*) <> 2
order by sym
DROP TABLE tMyPicks
DROP TABLE tUrPicks
This results in output that is a step away from what I want...that is, it at least identifes the individual symbols (and the associated ranking) that are NOT the same in the two tables.
IF there was a way to show which table the output of my union came from, I would be good to go, and thought I could add a literal to the select lists from each table in the UNION, but that destroys my group by clause.
Any thoughts? I suspect I will have to go away from my use of the UNION, but when I try using a join, I still have a problem with the grouping logic.
I suspect I am trying to be TOO dang "fancy" but at the moment I think I am too close to the forest to see the trees. :(
View 11 Replies
View Related
Feb 17, 2014
Is it possible to find out when a value was last changed/updated? I want to list all entries made after a specified date.
View 6 Replies
View Related
Mar 18, 2006
I have sql server 2000 Personal Edition with service pack 4 loaded on a laptop running Windows XP and I'm trying to install a program that automatically loads all the tables and files to the SQL Server.
I keep getting the following error:
Your connection could not be created. Check your settings. The error was: Login failed for user "FileArch"
The program had been previously loaded on this laptop and I had to rebuild the hard drive. When I check Enterprise manager I see in Databases that the FileArch database is in Master as opposed to its own database.
The database missing is called FileArch.
If anyone can provide me with help to solve this I'd be grateful as a new member.
Bob
View 7 Replies
View Related
Jul 20, 2005
COUNTING NUMBER OF SELECTS MADEtable mytable {id, data, hits}users view data from the table:SELECT data FROM mytable WHERE id=1 --for exampleSELECT data FROM mytable WHERE id=20 --for example....How do increment the hits column without replacing the above with the below?update mytable SET hits=hits+1 WHERE id=1;SELECT data FROM mytable WHERE id=1update mytable SET hits=hits+1 WHERE id=1;SELECT data FROM mytable WHERE id=20....I believe triggers can't be used as they only trigger on update/delete events.I'm using sql server 2000 (latest patches) with aspThanksAlex
View 3 Replies
View Related
Jul 20, 2005
Hi All,This seems like a tricky question to me. I have a Stored Procedurethat encapsulates a number of updates to various tables within atransaction.However, at a later part of the transaction I need to be able toselect records changed by an update statement made earlier within thesame stored proc (and within the same transaction) and need for thatselect to reflect the changed values.My understanding, however, is that the records aren't actually changedby the update statement until the transaction is committed, andtherefore my later select statement won't return the expected recordsbecause the update is being held until the transaction is committed.Is this accurate? And, if so, is there a reasonable workaround thatstill leaves me able to rollback the entire transaction if I strike aproblem somewhere along the way?So, a pseudo code example would be:BEGIN TRANSACTIONUPDATE mytable SET myid = @yourid WHERE myid = @idSELECT * FROM mytable where myid = @idCOMMIT TRANSACTIONIn this above example, would the select statement return the recordsthat have a myid value of @id as before the update as after theupdate?Many, many thanks in advance!Much warmth,Murray
View 2 Replies
View Related
Apr 1, 2007
While attempting to deploy from within VS2005 running on 2003 server we get the following error:
TITLE: Microsoft Semantic Model Designer
------------------------------
A connection could not be made to the report server http://xxxxx:90/ReportServer.
Running sql2005 sp2 integrated w Sharepoint 2007
Thanks..
View 12 Replies
View Related
Aug 7, 2006
Hi !
I'm trying to figure out how can i get a list of the reports made with reportbuilder. I have create a folder in ReportManger and all my report are in this folder.
When i create report with ReportDesigner i get a .RDL file, but when i create a report using reportBuilder i can't file the file created. What i want to do is to create in our web application a Web page with a list of report that our client has create using ReportBuilder. I was thinking about doing a loop on the folder for each report File but i can't find those files.
Any idea ?
Thanks!
View 7 Replies
View Related
Feb 23, 2008
hi,
I got this error when I was trying to deploy the reporting into IIS..
TITLE: Microsoft Report Designer
------------------------------
A connection could not be made to the report server http://localhost/reportserver.
------------------------------
ADDITIONAL INFORMATION:
The server committed a protocol violation. Section=ResponseStatusLine (System.Web.Services)
------------------------------
BUTTONS:
OK
------------------------------
I am not able to solve it please help.
Thanks.
View 1 Replies
View Related
Mar 20, 2008
Hi,
I just ended a case with microsoft because I had an issue with Analysis services. The thing is, one of the solutions given by microsoft was to install cumulative update 5 for SQL Server 2005 SP2. Now SQL Server is using all the memory on the server which has windows 2003 server R2 Enterprise SP2 (8GB of Ram) /3GB and /PAE enabled. The weird thing is non of the sql server processes displayed in Task manager shows more than 200MB of memory. But, the server only has 500MB of memory available and 7GB of PF Usage. How did I find out that SQL Server was using the memory, well I shut down the service and 6.8 GB of memory available and the PF Usage went down to just 1.8GB. Turned on again and then the same situation.
well it seems SQL Server is reserving memory or something.
Any clues?
View 13 Replies
View Related
Jun 7, 2007
First, thank you all for the help so far.My new, broader Question is this: What is the most efficient (read easiest) way to code in an inset statement into a remoste SQL 2003 database from IIS 6 running asp.net 2.0?Here is the situation: I have a form that requires the user to be signed in. I then want to get the value of a text box TBOne(String), a dropdownlist DDOne(Text), another Textbox TBTwo (int) and insert these values in to a NEW row of a database (DBTest) into table TTest, with the UserName (String), and todaysdate (SmallDateTime).I am a JSP programmer, and it's fairly easy to do in JSP, but I am trying to leverage the SQL Datasource, to make my life easier.Is there a simple way to do this? Something like:String username=page.someintg.User.name;String TBOne=TBOne.text;...Insert into DBTest.TTest Values(username, TBOne, ...) ? TIA Dan
View 5 Replies
View Related