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
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
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
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
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 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 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.
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.
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 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?
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:
Hi.I have a "union" table which results of a union of two tables.Occasionally I could have duplicates, when the same PIN has been addedto both tables, albeit at different Datees/Times, such as:PINNameAdded Date100411A7/11/2007 10:12:58 AM100411A7/17/2007 10:54:23 AM100413B7/11/2007 10:13:28 AM100413B7/17/2007 10:54:39 AM104229C7/6/2007 2:34:13 PM104231D7/6/2007 2:34:25 PM104869E6/10/2007 11:59:12 AM104869E6/22/2007 2:40:18 PMThe question is - how can I delete by queries the first occurence(time-wise) of these duplicates - i.e. I would want to delete thefirst occurence of 100411 (A), the first occurence of 100413 (B), andthe first occurence of 104869 (E) in the example above - records C andD show only once, so they are fine.Is there a MsAccess solution ? Is there a SQL-server solution ?Thank you very much !Alex
I have a query which finds duplicate spec_items linked to a work order. What I want to do it remove the duplicates (and in some cases there will be more than one) leaving only the record with the highest [sr.id]
select sr.id, sr.linked_to_worknumber, sr.spec_checklist_id from spec_checklist_remind sr inner join spec_checklist_remind sc on sc.linked_to_worknumber = sr.linked_to_worknumber group by sr.id,sr.linked_to_worknumber, sr.spec_checklist_id Having sr.spec_checklist_id = 30 and count(*)>1 order by sr.linked_to_worknumber
There are many duplicate records on my data table because users constantly register under two accounts. I have a query that identify the records that have a duplicate, but it only shows one of the two records, and I need to show the two records so that I can reconcile the differences.The query is taken from a post on stack overflow. It gives me 196, but I need to see the 392 records.
How to identify the duplicates and show the tow records without having to hard code any values, so I can use the query in a report, and anytime there are new duplicates, the report shows them.
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.
I have a table with 22 million Business records. I can see that there are duplicates when I group by BusinessName and Address and Phone. I'd like to place only the duplicates into a table, with a ranking, oldest business key gets a ranking of 1.
As a bonus I'd like each group to have a distinct group name (although not necessary, just want to know how to do this)
Later after I run more verifications to make sure these are not referenced elsewhere I'll delete everything with a matchRank > 1 out of the main Business table.
DROP TABLE [dbo].[TestBusiness]; GO CREATE TABLE [dbo].[TestBusiness]( [Business_pk] INT IDENTITY(1,1) NOT NULL, [BusinessName] VARCHAR (200) NOT NULL, [Address] VARCHAR(MAX) NOT NULL,
i have a datetime field in the post tables. I would like to get the records within the latest 7 days. Are there any functions for doing something like this? my current query is something like select * from post where creation_time .... Thank you
I have two tables totally unrelated but give the same information, the difference is the duration. I need to create a stored procedure that will give the recent issue dates only, accept if they have already expired. I'm not exactly sure how to do that. We only want the employees to see the current issue date as long as the exclusion has not expired. Can anyone help please
AS SELECT [dbo].[30 Day exclusion].[First Name], [dbo].[30 Day exclusion].[Last Name], [dbo].[30 Day exclusion].[Issue Date], [dbo].[Extended Exclusions].[First Name], [dbo].[Extended Exclusions].[Last Name], [dbo].[Extended Exclusions].[Issue Date] FROM[dbo].[30 Day exclusion] INNER JOIN [dbo].[30 Day exclusion].[id] ON [dbo].[Extended Exclusions].[ID] = [dbo].[30 Day exclusion].[id]
WHERE (@StartIssueDate is null or [Issue Date] >= @StartIssueDate) AND (@EndIssueDate is null or [Issue Date] <= @EndIssueDate) AND (@Enter_LastName is null or [Last Name] = @Enter_LastName) ORDER BY [Last Name]
I have a Customers table and an Orders table. The Customers table has fields... CustomerId, and CustomerName. The Orders table has Fields CustomerId, OrderDate, and ProductId.
I wish to execute a query that will return a single Customer and Order record for a given customer ID that reflects the most recent order. So, a typical join like...
Ok, I was able to find the most recent Effective date and it works well. The only problem I am having is with the CarrierName. See there are different Carriers with so then it makes us have Mult effective dates. I need to pull the most recent effective date with carrier. So, I though to max the pcsiNumber2, but that didn’t work. Do you have any other ideas? Let me know.
Select Distinct --PCSNumber, PCSINumber1, max(PCSINumber2) as mostrecentPCS, LastName, FirstName, State, --InsuranceCarrier, CarrierName, MAX(CoverageEffectiveDate)as MostRecentEff, MAX(TerminationDate) as MostRecentTerm, AmtPerClaim, Aggreg
FROM VW_Medical_Malpractice_Project
Where State = 'PA'
Group By PCSINumber1, LastName,FirstName,State,CarrierName,AmtPerClaim,Aggreg
Order By PCSINumber1
Example
000043010003MUSSER WILLIAM American Professional Agency2002-06-01
000043010002MUSSERWILLIAM Columbia Casualty2000-02-20
So, what I need is the most recent effective date which would be 2007-01-01, which I have but what is not letting me do this is the names, the 5ith column. I was trying to use the 2nd column to use the max , but it is not working. What am I doing wrong. I need the whole last row, but that number 0005 can change because there may only be 2 dup records.
hello,I have a table which contains the audit time field. This audit time fieldrecords the current date and time when a record is inserted.I would like to output the most recent top 500 rows.thank you.
If I have tables like Customer (Id, First, Last, Address, etc.), CustomerEvent (EventCode, EventDate, CustomerId), EventTypes(Code, Description) how would I get all of the CustomerIds where the latest CustomerEvent record is a cancel and it happened within the last 60 days?
If I just do a select max(EventDate) and put EventTypes.Code='cancel' in the where clause it picks up records where the customer has done other things since the cancel.
