Transact SQL :: Return Most Recent Entry On Joined Table?
Sep 29, 2015
Empid 1 has 2 entries for the date 09/01/2015 and my left join returns both of those entries. What do I need to alter to make it so that only the most recent entry is returned not both entries?
Hello-I'm fairly new to writing SQL statements and would greatly appreciatesome help on this one.I'm working on a project for a non-profit that I volunteer for. Partof the database tracks membership using tables like this:PersonInfo-------------------PersonID (primary key)FirstNameLastNameetc..PeopleMemberships-------------------PPLMembershipIP (primary key)PersonIDMembershipTypeIDFeePaidMembershipTypes--------------------MembershipTypeID (primary key)MembershipYearStandardFeeMembershipDescription (varchar)Just because a person is in PersonInfo, doesn't mean they have anythingin PeopleMemberships (they can be in the databse for other reasons andnot have or have ever had a membership).Membership fees vary by year and type of membership and they want toretain a history of a person's memberships.What I'm looking to do here is write a query (a view in SQL Server)that will return the following InfoPersonID, MostRecentMembershipYear, FeePaidForThatMembership,DescriptionOfThatMembershipI'm thinking that I'd use max(MembershipYear), but that requires groupby for the other columns, so I'm getting all of the people'smemberships returned.I'm pretty sure this can be best done with a subquery, but I'm not surehow.Can someone please point me in the right direction or provide a samplethat I can learn from?Kindly,Ken
We have 2 Tables, tblMain, which contains unique products and tblHistory, which contains a history of the product Records are added to tblHistory daily and then the prices in tblMain are updated with those prices. The problem that we have is that since there are multiple instances of the same product in tblHistory, we're not always updating using the most recent price. Below is the current SQL that we are using.
Update TM SET TM.Price = TH.PriceHist, TM.DateUpdated = TH.DateUpdatedHist FROM tblHistory TH LEFT JOIN tblMain TM ON TH.ProdCode = TM.ProdCode
Below is a sample of data in tblHistory and the records that I would need to be used for the update.
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'
what i need is query the tbl1 for a range of serials,get the pcb and for those pcb's query the tbl2 for data1,data2 The resultSet should be a join on the two tables, Columns {serial} from tbl1 and {pcb,date_time,data1,data2} from tbl2
Please follow my simple example: Suppose tbl1 has these 2 records tbl1 = pcb1,sn1,pass pcb2,sn2,pass pcb3,sn3,pass
where date1 is the most recent date and date6 the least recent
Request:what i want is for serial>=sn1 and serial<=sn2,get the pcbs from tbl1(which are pcb1 and pcb2) and based on these, query the tbl2 for the other data but retrieve only most recent records.
and not pcb1,date1,pass,dataX1,dataY1 pcb1,date2,pass,dataX2,dataY2 pcb1,date5,pass,dataX5,dataY5 pcb2,date3,pass,dataX3,dataY3 pcb2,date6,pass,dataX6,dataY6
What i already did is this:
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;
This works correctly for getting serial from tbl1, date_time and pcb from tbl2.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 (by using this OR philosophy).I mean, it would give all records containing (pcb1,pcb2),much like my example
I know how to select the most recent row from a database: SELECT TOP (1) Location, Date FROM Images ORDER BY Date DESC But how do I select the second to most recent? or the third most recent? or the 4th, ect, ect, ect. There must be some method to it, anyone have any suggestions?
There is a large data table called emp_history on an SQL Server, which contains the employment history of each employee. The significant columns are as follows:
entry_id employee_id start_date position
where entry_id is the primary key and auto-increments.What I need, is to create a query to extract the employee_id and position, based on the most recent record (most recent start_date) for each employee.
I have tested the code below to remove duplicate table entries based on the field IntOOS. This code works; however, I want to ensure I am removing the oldest entries and keeping the most recent entry. The field to key on this would be something like Max(ID). What would be the best way to ensure I keep the most recent table entry?
/*** Removes duplicate rows from ampfm.rpt_AdtVisitDiag table
by Chuck King 20070928
***/
;WITH CTE
as
(
SELECT
ROW_NUMBER() OVER (Partition By IntOOS Order BY IntOOS) AS ROWID
I'm trying to determine the oldest timestamp that meets given criteria which includes a specific action_type code. That code can legitimately be performed more than once on an item (all actions are recorded in an actions table, so the full history is available).
I'm having trouble getting the most recent timestamp for the given action_type I'm looking for, when that action_type has been performed more than once.
My intent with the query below is to get the most recent action_time and it's request_id that meets the criteria
SELECT TOP 1 a.action_time, r.request_id FROM requests r JOIN incident i ON r.request_id = i.request_id LEFT JOIN actions a ON r.request_id = a.request_id WHERE i.refund_type = 1 AND (r.status_code = 3 OR r.status_code = 14) AND a.action_type = 3 ORDER BY a.action_time
So for example, if the item with request_id 1334 has had action_type 3 performed on it twice, once at 2007-10-15 13:30:00 and then again at 2007-10-17 14:40:00, it's picking up the former, when I want the later. That it had action_type 3 performed on it twice is legitimate. (larger context an item was approved by a manager, then rejected by a processor, and then approved by a manager again after resubmission)
Hopefully this has made sense so far.
So, how do I make sure I'm getting the most recent action time, when I don't know if the item in particular will have more than one of this action type performed on it?
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
Here is my SQL Query. The Table where the data is coming from has 19,000 rows of data however, this only returns 2550 rows of data. In the where statment I am call all the select tables, with the OrbitimportId that is most recent. But it only returns 2500. I need help retirenving the entire liste with the most recent OrbitimportId.
Help!
SELECT TOP (100) PERCENT dbo.AssetType.Description, dbo.AssetAttribute.AssetDescription, dbo.Asset.Barcode, dbo.Asset.SKU, dbo.ESNTracking.ReportTime, dbo.ESNTracking.CurrLocStreet, dbo.ESNTracking.CurrLocCity, dbo.ESNTracking.CurrLocState, dbo.ESNTracking.CurrLocZip, dbo.ESNTracking.CurrLocCounty, dbo.ESN.EsnNumber, dbo.ESNTracking.DistanceMiles, dbo.ESNTracking.MapUrl, dbo.InventoryOrigin.WarehouseDescriptionFROM dbo.AssetType INNER JOIN dbo.Asset ON dbo.AssetType.AssetTypeId = dbo.Asset.AssetTypeId INNER JOIN dbo.InventoryOrigin ON dbo.Asset.WarehouseId = dbo.InventoryOrigin.WarehouseId INNER JOIN dbo.AssetAttribute ON dbo.Asset.AssetAttributeId = dbo.AssetAttribute.AssetAttributeId INNER JOIN dbo.EsnAsset ON dbo.Asset.AssetId = dbo.EsnAsset.AssetId INNER JOIN dbo.ESN ON dbo.EsnAsset.EsnId = dbo.ESN.EsnId LEFT OUTER JOIN dbo.ESNTracking ON dbo.EsnAsset.EsnId = dbo.ESNTracking.EsnId LEFT OUTER JOIN dbo.AssetVehicle ON dbo.EsnAsset.AssetId = dbo.AssetVehicle.AssetId LEFT OUTER JOIN dbo.AssetCustomAttribute ON dbo.EsnAsset.AssetId = dbo.AssetCustomAttribute.AssetId LEFT OUTER JOIN dbo.AssetCustomAttributeDef ON dbo.AssetCustomAttribute.AssetTypeId = dbo.AssetCustomAttributeDef.AssetTypeId LEFT OUTER JOIN dbo.OrbitImport ON dbo.ESNTracking.EsnId = dbo.OrbitImport.OrbitImportIdWHERE (dbo.ESNTracking.OrbitImportId = (SELECT MAX(OrbitImportId) AS Expr1 FROM dbo.ESNTracking AS ESNTracking_1))ORDER BY dbo.AssetType.Description
I'm running a query on a set of electricity meters for various sites and wish to determine the most recent metre read for each site, by selected site group.
There are 4 tables : CONTACTS - Containing all the Site details LOOKUP - Containing the Site Group details POINTS - Containing all the meter id details DATAELECTRICITY - Contains all the meter readings
The system I am using is a bespoke system which I believe is running transact sql. The query below returns all of the meter readings for each site,but I would like :
a) Only return the latest M1-M8 readings for the maximum DataElectricity.Date b) Only return values for the selected Site Group
PARAMETERS [Site Group] Text ( 255 ); SELECT Lookup.Lookup_Name AS [Group], Contacts.Name AS Site, Points.Number AS MPAN,
I need to return all the rows. Where the ESNnumber only returns the most recent one that is associsted with the Asset.
Basically, I need the info most current ESN number only. They are 19,00 rows of each ESN number but it returns 40,000. Duplicates.
SELECT TOP (100) PERCENT dbo.AssetType.Description, dbo.AssetCustomAttributeDef.Name AS [Custom Asset], dbo.ESN.EsnNumber AS [ESN #], dbo.AssetAttribute.AssetDescription AS [Description Detail], dbo.Asset.Barcode, dbo.Asset.SKU, dbo.InventoryOrigin.WarehouseDescription AS [Inventory (W/H)], dbo.ESN.DateImplemented, dbo.ESNTracking.TraceTime, dbo.ESNTracking.PreviousTraceTime, dbo.ESNTracking.HasMoved, dbo.ESNTracking.DistanceMiles, dbo.ESNTracking.Direction, dbo.ESNTracking.Landmark, dbo.ESNTracking.FemaLocation AS Fema, dbo.ESNTracking.ReportTime AS [Report Time], dbo.ESNTracking.CurrLocStreet AS Address, dbo.ESNTracking.CurrLocCity AS City, dbo.ESNTracking.CurrLocState AS State, dbo.ESNTracking.CurrLocZip AS Zipcode, dbo.ESNTracking.CurrLocCounty AS County, dbo.ESNTracking.MapUrl AS [Map Link], dbo.ESNTracking.ReplaceByDate AS [Replace Batt.], dbo.ESNTracking.CurrMileFromStratix AS [From Stratix Now], dbo.ESNTracking.PrevMileFromStratix AS [From STratix Then] FROM dbo.AssetType INNER JOIN dbo.Asset ON dbo.AssetType.AssetTypeId = dbo.Asset.AssetTypeId INNER JOIN dbo.InventoryOrigin ON dbo.Asset.WarehouseId = dbo.InventoryOrigin.WarehouseId INNER JOIN dbo.AssetAttribute ON dbo.Asset.AssetAttributeId = dbo.AssetAttribute.AssetAttributeId INNER JOIN dbo.EsnAsset ON dbo.Asset.AssetId = dbo.EsnAsset.AssetId INNER JOIN dbo.ESN ON dbo.EsnAsset.EsnId = dbo.ESN.EsnId LEFT OUTER JOIN dbo.ESNTracking ON dbo.EsnAsset.EsnId = dbo.ESNTracking.EsnId LEFT OUTER JOIN dbo.AssetVehicle ON dbo.EsnAsset.AssetId = dbo.AssetVehicle.AssetId LEFT OUTER JOIN dbo.AssetCustomAttribute ON dbo.EsnAsset.AssetId = dbo.AssetCustomAttribute.AssetId LEFT OUTER JOIN dbo.AssetCustomAttributeDef ON dbo.AssetCustomAttribute.AssetTypeId = dbo.AssetCustomAttributeDef.AssetTypeId
I tried to create a dynamic table, fill in it and return it as recordset. The codes as this:
Declare @tbl Table(id int, name varchar(100), age int) Insert Into @tbl(id, name, age) Values(1, 'James, Lee', 28), (2, 'Mike, Richard', 32), (3, 'Leon Wong', 29) Select * From @tbl Order By age
It works well in "SQL Query Ananizer". But return no records in ASP page.
I don't know why this is so difficult. What I want to do is take a table name as a parameter to build a query and get an integer value from the result of the query. But from all of the research I have been doing, Dynamic SQL is bad in SQL server because of SQL Injections. But my users are not going to be supplying the table names.
Things I have learned:
- SQL Functions cannot use Exec to execute query strings. - SQL Functions can return a concatenated string that could be used by a stored procedure to Exec the query string.
So how can I write a stored procedure that will
1. take a parameter 2. Pass the parameter to a function that will return a string 3. Execute that string as SQL 4. Get a return value from that SQL statement 5. Then finally, from a View, how can I pass a parameter to the stored procedure and get the returned value from the stored procedure to be used as a field in the View?
Numbers 3, 4, and 5 are where I am really stuck. I guess I don't know the proper syntax and limitations of SQL Server.
1 Banana2 Orange3 Apple Order tblOrder, tblOrder: CustID; ProductID; Amount
1 ;2 ;$20 – means Peter ordered $20 oranges
How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?
Trying to create a report... Report should show * documents on hold then depending on the "on-hold type" look in the corresponding table and SELECT a few fields. Here is what I have. Where do I SET the @profile variable to return the profile from my queue table?
DECLARe @profilevarchar(256) SELECT q.[profile],q.on_hold,q.on_hold_message,q.dbc_state FROM QueueASq
This is my query, from my nonono table I need to return the most recent date from attempted (column name) How could I modify this to do such
Select * FROM [nonono] td WHERE (NOT EXISTS (SELECT [First], [second] FROM [yesyesyes] AS d WHERE ([First] = td.[First]) AND ([second] = td.[second]) AND ([Worksite] = td.[Worksite])))
I've to write a function to return a comma delimited values from a table columns
If a table has Tab1 ( Col1,Col2,Col3).
E.g. as below ( the columnName content I want to use as columns for my pivot table
CREATE FUNCTION [RPT].[GetListOfCol] ( @vCat NVARCHAR(4000) ) RETURNS @PList AS BEGIN SELECT @PList += N', [' + [ColumnName] +']' FROM [ETL].[TableDef] WHERE [IsActive] = 1 AND [Category] = @vCat RETURN; END;
I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....
My goal is to show the most recent 12 months of data including the current month based on a patients discharge date (relative to the query execution time). There could be many years of data so I want to be sure I am pulling the most recent 12 monthsThis seems to work (on the surface anyway … not quite sure if this is the best logic to use).
I would like to INSERT an array of integer into a table in MSSQL Server, then count the number of rows in the table with c++ using ODBC. Here you find my code to do this task:
In my code, I would like to Insert the array and then count the number of rows:
SQLTCHAR Statement[] = _T("INSERT INTO information1(Wert1,Wert2) VALUES(?,?) select count(*) as a from information1 ") ;
Problem : My expectation is, that first 9 rows are inserted into table then comes 9 as result to user (if the table is empty) but this code returns me 1 if first the table is empty. If the table is not empty, it returns 1+number of existing rows in the table. If I take a look inside the table, the 9 rows are successfully inserted in it. Only the number of rows in the table is wrong.
Hint : If I monitor the database using SQL Profiler. It looks like this:
I have tables and a function as representated by the code below. The names for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.
When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.
SELECT A.CID, A.ANI, A.PID, B.RD FROM T1 AS A CROSS APPLY GIVERD(A.PID) B
Which means, I want to count for each transaction, the latest entry based on date. And there can be many countries like this. And I need to count both entries for ABC & XYZ.Normal count will give me duplicate entries.
In such a case any query to reflect all the columns with an additional column reflecting the said Staff's latest Department?
In other words, ones still working in the organization reflecting in the latest Department field, the Current Department and those already resigned/fired reflecting the last department they worked at with respect to the Report_Date.
I am wanting to create a query so that I can combine each of the found duplicates into one entry.
An example of this is:
Name |ID |Tag |Address |carNum ------------------------------------------------------- Bob Barker |2054| 52377 |235 Some road |9874 Bill Gates |5630| 69471 |014 Washington Rd. |3700 Bob Barker |2054| 97011 |235 Some road |9874 Bob Barker |2054| 40019 |235 Some road |9874 Steve Jobs |8501| 73051 |100 Infinity St. |4901 John Doe |7149| 86740 |7105 Bull Rd. |9282 Bill Gates |5630| 55970 |014 Washington Rd. |3700 Tim Boons |6370| 60701 |852 Mnt. Creek Rd. |7059
In the example above, Bob Barker and Bill gates are both in the database more than once so I would like the output to be the following:
Bob Barker |2054|52377/97011/40019|235 Some road |9874 Bill Gates |5630|69471/55970 |014 Washington Rd.|3700 Steve Jobs |8501|73051 |100 Infinity St. |4901 John Doe |7149|86740 |7105 Bull Rd. |9282 Tim Boons |6370|60701 |852 Mnt. Creek Rd. |7059
Notice how Bob Barker & Bill Gates appends the tag row (the duplicated data) into one row instead of having multiple rows. This is because I do not want to have to check the previous ID and see if it matches the current id and append to the data.
I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above). When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer.
Is there a way to build hierarchies within my Customer dimension based on the same Customer table? What I want is to view the data in Excel and see the Customer dimension. Within Customer, I want four hierarchies.
I have a table with almost a million rows, although it's quite slim with just ID, date, userID, JobID etc.
Now I want to the ability to add comments to some (probably less than 1%) of those lines.
The question is whether to create a separate comments table to join to it, or to create a comments field within the existing table? The comments field would obviously default to NULL, so wouldn't bloat the table unnecessarily if I add that field (right?), and would always be selected with the row from that table, so I'm leaning towards the latter alternative.