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
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()))
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.
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.
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?
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,
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'
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.
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,
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 need this query to pull just the last scan date, i.e. if a scan ran on 3/7/2012 I need just the records (there may be few or many) that pertain to that date and no other dates. When I run this query I am still getting all of the results (i.e. scan dates of 3/7/2012, 3/5/2012, 3/1/2012, etc)
I just need the most recent date, even if there are many records for that date (i.e. 10 IP addresses that were all scanned on 3/5/2012)
I am creating a database in SQL2K for collating test results taken at regular intervals across several different sites and frequencies. The table will look something like this:
SiteDate Data A01/01/2013 ... B02/01/2013 ... C03/01/2013 ... A04/01/2013 ... B05/01/2013 ... C06/01/2013 ... And so on...
In total there will be about 300 sites, with up to 12 records per site every year. I want to be able to create a view showing the 4 most recent results for each site. Is there a simple way of doing this? Obviously getting the most recent result for each site is quite straight forward, but I can't work out how to get the last four. Unfortunately this is on a 2k box, although I could (at a push) use a 2k8 box if needed.
I have 2 tables: 1) Item table (No_, Description) 2) Sales Price HAG table (Item No_, Sales Price HAG, Starting Date)
I want to have a report within from every item the most recent sales price. But, it is possible that a unique item has more than 1 sales price in the 'Sales Price table' (if it has more than one Starting Date). I want to report the sales price with the most recent date.
I've made this statement: SELECT [DatabaseName$Item].No_, [DatabaseName$Item].Description, [DatabaseName$Sales Price HAG].[Unit Price] FROM [DatabaseName$Item] INNER JOIN [DatabaseName$Sales Price HAG] ON [DatabaseName$Item].No_ = [DatabaseName$Sales Price HAG].[Item No_] WHERE ([DatabaseName$Sales Price HAG].[Starting Date] IN (SELECT MAX([Starting Date]) AS EXPR1 FROM [DatabaseName$Sales Price HAG] AS [DatabaseName$Sales Price HAG_1] GROUP BY [Item No_]))
After running I still get from some items more than 1 price...... What went wrong?
I have the following query that should return the most recent FormNote entry for a work order where the note begins with "KPI". However if someone decides to a more recent note, it selects that one, even if it doesn't begin with "KPI".
I would like it to return the most recent record that ALSO begins with "KPI". How can I correct this?
Select wh.worknumber, wh.date_created, wh.itemcode, wn.TextEntry as [Notes] from worksorderhdr wh left join
(select ID, WorksOrder,[CreationDate], TextEntry from ( select ROW_NUMBER()over(partition by worksorder order by [CreationDate] desc) OID,* from FormNotes )orders where orders.OID=1 ) wn on wn.WorksOrder = wh.worknumber where TextEntry like 'KPI%'
Sample results below, see line 5 - this record should not have been selected as there is a record beginning with "KPI" for that work order, but it is dated before this one.
worknumber date_created itemcode Notes -------------------- ----------------------- -------------------- ----------------------------------------------------------------------------------- HU-DN-004385 2014-07-21 16:15:00 4261 KPI Hyd oil leak repaired HU-DN-004707 2014-08-06 11:39:00 8005 KPI Valve replaced on day 2. HU-DN-004889 2014-08-19 15:44:00 9275A KPI Repaired in 2 days - m/c working HU-DN-004923 2014-08-22 14:23:00 4261 KPI New tracks fitted HU-DN-005162 2014-09-12 15:04:00 9360A Mechlock key delivered to site - m/c working HU-DN-005170 2014-09-15 12:07:00 2130A KPI 28.10.14 Metlock fitted
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
Have a table that list item#, date the standard cost went into effect and the standard cost. How do I find the difference in StdCost on the last EffectiveDate and second to last EffectiveDate. 5.59 (01/05/2015) minus 5.81 (09/29/.014) = -.22.
ID Name Phone --- ---- ----- 1 John 1234567 1 John H. 2 Dave 9876543 2 Dave Smith
I want to select only one record of each ID, the output should be
ID Name Phone --- ---- ----- 1 John 1234567 2 Dave 9876543
It doesn't matter which record to select, but I need just a single complete record. I can't use select distinct ID because it will not show the other fieldnames.
I have a sql query that looks for values in a few different databases....
is there a way to select the last record in the table b/c I am pulling the hours worked on jobs and one person may have 3 job titles but i want it to show the balance of hours under the most recent job... Instead all the jobs are showing the same value even though only one of them had those hours under it. I think a way to get around this is to select the last value b/c that is under the job title most recently worked and they are in order by timesheets which can be ordered by date...any ideas?
I have view with Patients name and Appointment table where I save those patients. How to create store procedure if I would like to select one patient record based on Patient_Id value?
hi I want to lock the record on select query so that no other user can update this record, is it possible and i want to unlock record when use stop view this record.
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.
