Select Most Recent Customer Record From Table Only?
Nov 6, 2007
If I have a table structure similar to the following, how might I query it to obtain the Transaction ID, Transaction Date, and Customer Name for the most recent transaction per customer only:
TransactionTable:
TransactionID TransactionDate TransactionType CustomerName
1 10/1/07 1 Bob
2 8/30/07 2 Janet
3 9/17/07 1 Mike
4 9/25/07 1 Bob
The following query will return all records in the table other than Janets...not what I want:
SELECT Transaction ID, TransactionDate, CustomerName
FROM TransactionTable
WHERE TransactionType = 1
ORDER BY TransactionDate DESC
The following query will return all records in the table other than Janets again, because DISTINCT will use the combo of TransactionID, TransactionDate, and Customer name for uniqueness...not what I want:
SELECT DISTINCT Transaction ID, TransactionDate, CustomerName
FROM TransactionTable
WHERE TransactionType = 1
ORDER BY TransactionDate DESC
The results set I'm looking for would be the following, where only the most recent entry per customer with TransactionType 1 is returned (i.e. one record for Bob):
TransactionTable:
TransactionID TransactionDate CustomerName
1 10/1/07 Bob
3 9/17/07 Mike
I believe I need an appropriate subquery to yield the results I desire, but can't sort out what it is? I do not want to execute multiple queries but subqueries are fine. Unfortunately there's probably an easy answer that my brain is not currently generating. Any help would be appreciated.
Note, this is not a real table, but a sample to illustrate the concept for the query I need.
Every hour we capture some values from our factory (position of pumps, valves, ...) in our sql server 2000 db.
Normally 1 record is added to the db. 00:00:00 01:00:00 02:00:00 ... 21:00:00 22:00:00 23:00:00
All of these values are displayed in an Excel sheet. One sheet contains all the data from one month.
I noticed a problem last week when 2 records were added: first one at 19:00:00 and the second one at 19:00:01
We only want to keep the most recent record (19:00:01) in a situation like this but I can't seem to work out an SQL-statement :o
This is what we have know. It used to work fine untill we had 2 record being added instead of one. SELECT TimeOfCapture, Value1, Value2, Value3 FROM FactoryTable WHERE (MONTH(TimeOfCapture) = MONTH(GETDATE())) AND (YEAR(TimeOfCapture) = YEAR(GETDATE()))
All columns are strings as the data is obtained from text file. Column F3 can have same or different dates. In the above data select 1st record as it is the most recent. If data is like - F1F2F3 F4 20016395074/7/2004 15:40:55 23516395074/7/2004 19:31:54
I am trying to pickup the most recent record by Product and Supplier. eg.
Rec Prod Supp Date 1 1000 WES01 Jan 1/2005 2 1000 WES01 Apr 8/2005 3 1001 WES01 Apr 8/2005 4 1001 NAN04 Feb 15/2005 The Result I want is as follows:
Rec Prod Supp Date 2 1000 WES01 Apr 8/2005 3 1001 WES01 Apr 8/2005 4 1001 NAN04 Feb 15/2005 I have tried various methods with the TOP 1 and DISTINCT statements, but have not been able to get the results I am looking for. Help would be appreciated.
I am working on a query that needs to return the record order number with the most recent requested delivery date.
It seems to work most of the time, but I have found some glitches for some of the items.
example is my item 10702, it is showing 2 records (both valid ones) Record 1 is order # 10450-0, requested delivery date 03/21/2014 Record 2 is order # 10510-0, requested delivery date 04/29/2014
I need to only get the records with the most recent delivery date, in this example that would be 04/29/2014
This query is what I have so far:
SELECT s.PriorQuoteNumber ,s.PriorItemNumber ,s.PriorQuoteDate FROM ( SELECT s.SalesQuoteNumberAS 'PriorQuoteNumber'
[code]....
WHERE s.rn = 1What am I missing in my query> how can I change it so it only returns the most recent date?
Given the Patients and PatientVisits tables as per below, how do I obtain the most recent (latest) Weight and Height for each patient as per http://www.hazzsoftwaresolutions.net/selectStatement.htm The result of the query should only return 3 rows/records,not 5. Thank you. Greg
Code Snippet select p.ID, p.FirstName,p.LastName,DATEDIFF(year, p.DOB, getdate()) AS age ,pv.WeightPounds, pv.HeightInches from Patients as p inner join PatientVisits as pv ON p.ID = PV.PatientID order by pv.VisitDate desc
INSERT INTO Patients (ID, FirstName,LastName,DOB) select '1234-12', 'Joe','Smith','3/1/1960' union select '5432-30','Bob','Jones','3/1/1960' union select '3232-22','Paul','White','5/12/1982' INSERT INTO PatientVisits (PatientID, VisitDate,WeightPounds,HeightInches) select '1234-12', '10/11/2001','180','68.5' union select '1234-12', '2/1/2003','185','68.7' union select '5432-30','11/6/2000','155','63.0' union select '5432-30','5/12/2001','165','63.0' union select '5432-30','4/5/2000','164','63.5' union select '3232-22','1/17/2002','220','75.0'
The "Last" function in the query below (line 4 & 5) is not exactly what I'm after. The last function finds the last record in that table, but i need to find the most recent record in the table according to a date field.
Code: SELECT tblinmate.statusid, tblinmate.activedate, Last(tblclassificationhistory.classificationid) AS LastOfclassificationID, Last(tblsquadhistory.squadid) AS LastOfsquadID, tblperson.firstname, tblperson.middlename, tblperson.lastname,
[Code] ....
The query below finds the most recent record in a table according to a date field, my problem is i dont know how to integrate this Query into the above to replace the "Last" function
Code: SELECT a.inmateID, a.classificationID, b.max_date FROM ( SELECT tblClassificationHistory.inmateID, tblClassificationHistory.classificationID,
create table dbo.customer ( customer_id int identity primary key clustered, customer_name nvarchar(256) not null ) create table dbo.purchase_order ( purchase_order_id int identity primary key clustered customer_id int not null, amount money not null, order_date date not null )
Implement a query for the report that will provide the following information: for each customer output at most 5 different dates which contain abnormally high or low amounts (bigger or less than 3 times SDTDEV from AVG), for each of these dates output minimum and maximum amounts as well.
i have a database with a list of customers and goods that they have ordered. I want to send one email to each customer regardless of the number of products he has ordered. eg.
I have to retrieve first and last record of each customer according to the Date. Each customer has 10 - 15 records in the table and there are 3000 customers. how can I retrieve this data.
I need to get all customer records with the most recent tDate. A customer should never have duplicate tDate records, as only one record per day is allowed per customer, yet there somehow there are duplicates. Given this, I need to get the record with the Max date and Max ID. the ID column is an auto-incrementing Identity column.Below is the code without the Max ID column logic
SELECT tCustID, MAX(tDate) AS tDate--get MAX tDate records FROM table1 GROUP BY tCustID
Log: Reports_Log LogID | ReportID | AccountID | ReportRunDate ---------------------------------------- 9876 | 5 | 1234 | 9/4/2007 10:35:43 AM 9875 | 5 | 987 | 9/4/2007 10:35:43 AM 9874 | 4 | 1234 | 9/4/2007 9:05:43 AM 9873 | 3 | 1234 | 9/4/2007 9:30:17 AM 9872 | 5 | 1234 | 9/3/2007 10:35:43 AM 9871 | 5 | 987 | 9/3/2007 10:35:43 AM 9870 | 4 | 1234 | 9/3/2007 9:05:43 AM 9869 | 3 | 1234 | 9/3/2007 9:30:17 AM 9868 | 5 | 1234 | 9/2/2007 10:35:43 AM 9867 | 5 | 987 | 9/2/2007 10:35:43 AM 9866 | 5 | 5677 | 9/2/2007 10:35:43 AM 9865 | 4 | 1234 | 9/2/2007 9:05:43 AM 9864 | 3 | 1234 | 9/2/2007 9:30:17 AM
so if i wanted to know what report for reportID 5 hasn't run in the past 2 days, it would be accountID of 5677 (last ran on 9/2/2007 10:35:43 AM)
I am not sure where to even start. I am thinking I can grab the latest report ran for all Accounts in the Reports_AccountID table (create temp table and insert most recent log record into that table), and do a date difference between the date and current datetime and select based on datedifference > 2 ?
or would the best way is to do an inner join between the 2 tables for all reports ran in the past 2 days, and then whatever account is not listed in that query, is a report that has not run (do a subquery?)
just trying to think of the best way to do this. any tips/advice would be appreciated
Please concider the line of code below. it doesnt quite work how i want it to. i need it to only pull records where there isnt any activity completed (actualend) after a given period. and only show the most recent activity per regardingobjecid(sales person) however it returns all the records before that period and shows me the most recent one up to that time. Please help.
here is the code
SELECT new_ratingname, regardingobjectidname, actualend, owneridname, createdbyname, activitytypecodename, count(*) AS Total FROM (SELECT filteredcontact.new_ratingname, filteredactivitypointer.regardingobjectidname, filteredactivitypointer.actualend, filteredactivitypointer.Owneridname, filteredactivitypointer.createdbyname, filteredactivitypointer.activitytypecodename, row_number() OVER (Partition BY regardingobjectid ORDER BY actualend DESC) AS recid FROM FilteredActivityPointer INNER JOIN FilteredContact ON FilteredContact.contactid = FilteredActivityPointer.regardingobjectid WHERE new_ratingname NOT IN ('dead', 'archive', 'continous updates') AND filteredactivitypointer.statecodename = 'completed' AND filteredactivitypointer.owneridname IN (@user) AND filteredactivitypointer.createdbyname NOT IN ('melvin felicien', 'suzette collymore', 'gasper ') AND filteredactivitypointer.activitytypecodename IN ('phone call', 'e-mail', 'fax') AND filteredactivitypointer.actualend <= dateadd(d, - CAST(@NeglectedDays AS INT), GetUTCDate())) AS d WHERE recid = 1 GROUP BY d .actualend, d .regardingobjectidname, d .owneridname, d .createdbyname, d .activitytypecodename, new_ratingname ORDER BY d .actualend
Essentially, what I’m attempting to do is for each Customer, Division, SalesRepType determine who the most recent assigned SalesRepNumber is and when (EnterDate) that person was assigned. So using the sample data, I would expect the following results.
I’ve tried various ways of using a CTE and ROW_NUMBER trying to get at this, but the area that is giving me the problem is in Division A, SalesRepType 1. Here is what gets me close, but I’m picking on SalesRepNumber 200 instead of 100 for Division A and
SalesRepType 1. WITH cteCust (RowNum, CustomerNumber, Division, SalesRepType, SalesRepNumber, BeginDate) AS ( SELECT
by executing this qry i will get below result ,in that TId is duplicates that is from that i want recent record with same result.i want last one if TId comes duplicate record. any one ans pl
select sa.GrandTotal, sa.TId, sa.Id,sa.Date, pr.PName, pr.PCode from Sales sa Left Outer Join Product pr on sa.Pid = pr.Id where sa.GrandTotal is not null
MSSQL2000I have a table that contains customer transactionsCustomerIDTransactionTransactionDate....I need to select the most recent record that matches a specific CustomerID.I am fairly new to SQL, could someone provide a sample select statement.TIATim Morrison-- Tim Morrison--------------------------------------------------------------------------------Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.http://www.vehiclewebstudio.com
We have a work order notes table in our ERP system, and I want to see the most recent note record for each work order. Sometimes there will one be one, so I want to see all those, but sometimes there will be several notes for each work order and in this case I want to see only the most recently added note for that work order.
The query below shows some results as an example. In this case I want to see all the records except for work order number DN-000023 where I only want to see the note dated/timed 07-12-2011 16:52 (the most recent one).
select id, worknumber, date, notes from worksordernotes
id worknumber date ----------- ------------ ----------------------- -------------------- 1 DN-000056 2011-12-07 13:22:00 13.20 PM JAMES- SPOK 2 DN-000079 2011-12-07 14:24:00 JCB HAVE TOLD ME THE 4 DN-000065 2011-12-07 15:48:00 ANDY FROM SITE RANG 5 DN-000023 2011-12-07 15:54:00 CHASED THIS 4 TIMES 6 DN-000023 2011-12-07 16:52:00 HOLTS ATTENDED THIS 7 DN-000092 2011-12-08 09:50:00 RETURNING WITH PARTS
Code Snippet select max(CONVERT(DATETIME,tbl2.date_time,103)),tbl1.serial,tbl2.pcb from tbl2 left JOIN tbl1 ON tbl2.Pcb=tbl1.pcb where tbl1.serial>='1' and tbl1.serial<='53' and tbl2."Result" like 'pass' and tbl1."result" like 'pass' group by tbl2.pcb,tbl1.serial
And this works correctly.But unfortunately i also need data1 and data2 columns from tbl2. If i include them in the Select Clause i have to include them also in the group by ,and this gives me also duplicate records.I mean it would return from tbl2, all records containing pcb1,pcb2.
How can i resolve this issue? Thank you very much,
I have a fairly complex SP with 3 tables the idea being to display a Region, Client and EventI want to show only the last event for each client I have replaced my event table with a View which returns the TOP 1 but all I get is the most recent of all records I want eg:Region 1, Client 1, Most recent event Region 1, Client 2, Most recent event Region 1, Client 3, Most recent event
I got some issues regarding selecting the most recent record from a datetime type column. The probelm consists in following: Table: News Needed columns: Letter [varchar] DateTimePublication [datetime]
The ideea is that there are letters with the same name [Letter] but different DateTimePublication ... and I need only the letters with the most recent DateTimePublication for every letter type like FR_dd_mm_yy/EN_dd_mm_yy/DU_dd_mm_yy.
I have a table full of service invoice records. Some of the invoices are continuous, meaning that there may be an invoice from 01-16-2015 through the end of that month, but then another invoice that starts on feb 1 and goes for 6 months.
I want to only pull the most recent. Keep in mind that there may be other invoices in the same table for a different period. An example might be:
i have two tables A and B. relation is one to many for A. i want to select from A only if there are more than two records of A in table B and also checking some condition in table B. if the question's not very clear please let me know.
I am having a table with student marks in that i need to select a student who is the 5 th rank based on total marks obtained by the student.how can write sql for that.
name, totmarks ----------------- kumar 145 ravi 300 jude 189 geeetha 320 rajesh 251 guru 190 ramesh 99 ----------------------
Hi!I want to do a query against a SQL DB and by sorting a datetime field, I want to get the second newest record in the table, not the newest.Can I do that?/Johan Ch
I need to write a select statement where I need to select the record randomely. Is there any function to do that? What's the best way to do that? Please help...
SELECT DISTINCT TblOrder.CustomerUID, TblOrder.OrderHiddenID, TblPayment.PaymentAmount, TblPayment.Result, TblOrder.OrderID FROM TblOrder CROSS JOIN TblPayment WHERE (TblOrder.CustomerUID = @IsCustomerID) AND (TblOrder.OrderHiddenID = @IsHiddenID) AND (TblPayment.Result = 'Pending') AND (TblOrder.OrderID IN (SELECT MAX(TblOrder2.OrderID) FROM TblOrder TblOrder2))
one customer can have more than one orders. So i need to select customer last inserted order details from database.So when i use above sql i returns null.what might be the reason for this
