Sorting Problem? Most Recent Entry When Selecting Multiple Columns
Oct 19, 2007
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 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?
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car where Price=@Price1 or Price=@price2 or Price=@price=3 and where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3 and where Model=@Model1 or Model=@Model2 or Model=@Model3 and where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
I have a view in my database detailing the expiry date of each credential for each employee. The view is designed as to display one record per employee and in that record is the expiry date of each credential and the days remaining. So the columns are as follows:-
Employee CodeExpiry Date (x8 columns) (named as credential e.g. [Passport]) Days Remaining (x8 columns) (named as "TS_" + Credential)
I'm trying to use the CASE function to compare each DATETIME column with one another and retrieve the minimum. How can I return the minimum date as a run-time column and sort the view by this column? My code is as follows:-
SELECT [Passport],[TS_Passport],[Visa],[TS_Visa],[Civil_ID],[TS_Civil_ID],[KOC_Pass],[TS_KOC_Pass],[JO_Pass],[TS_JO_Pass], [Ratqa_Pass],[TS_Ratqa_Pass],[Driving_License],[TS_Driving_License],[Health_Book],[TS_Health_Book], CASE WHEN Passport <= Visa AND Passport <= Civil_ID AND Passport <= KOC_Pass AND Passport <= JO_Pass AND
[code]....
I've been told that this is the most efficient given the number of records in my database. The Min_Date is always NULL. I need the minimum of the 8 dates to be the Min_Date.
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.
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.
I have a product table that has a productID column and a productName column. Then I have a productCategory table that associates productIDs with categoryIDs. And lastly I have a category table containing a categoryID and categoryName. I want to write a query that will return a table with three columns...productID, productName, and and calculated column containing the categories the product belongs to separated by a comma. A sample output would be...
If any of this isn't clear please feel free to write with questions. So far I have this query - SELECT Products.ProductID, ProductName, CategoryName FROM Products, ProductCategory, Categories WHERE Product.ProductID = ProductCategory.ProductID AND Categories.CategoryID = ProductCategory.CategoryID
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?
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
Im just curious how i would take multiple columns from multiple tables.... would it be something like this ??? table: Products COLUMNS ProductName, ProductID table: Categorys COLUMNS CategoryName, CategoryID,ProductID SELECT Products.ProductName, Categorys.CategoryName,Products.ProductID,Categorys.CategoryID,Categorys.ProductID FROM Categorys, Tables WHERE Products.ProductID = Categorys.ProductID
I am having a problem in outputting the last two dates that a bill has been sent out.
Background: A customers recieves a bill 4 times a year. The dates for issuing these bills are not fixed and I dont know the date that bills are usually sent out.
The database that I am using stores all of the dates that a bill would have been sent out.
Issue: A customer recently requested to see the dates of last 2 bills along with their value.
I know that you can view a customer last date by using the following statement.
Select max(issuedate)LastBill from statements
but i don't know how i could output the last 2 bills that were issued
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
Hello, I have a table of names/dates as such: Class 1 1/1/2007Class 1 1/3/2007Class 1 1/5/2007Class 2 2/1/2007Class 2 2/3/2007Class 3 3/1/2007 What I want to do is select only the nearest entry from the list for each distinct class, so the results are like this: Class 1 1/1/2007Class 2 2/1/2007Class 3 3/1/2007 So basically, I want the first upcoming class in the list for each distinct class name. How do I do this? I'm using SQL 2005 Express. Thanks.
This is feeling very hard for me, but is surely very easy for many of you. I have 2 Tables. "Events" and "Meals". Both have a columns named "EventDate" and "EventTime". I need to be able to compile a list of both and sort by event date and time. For example, a Meal @ 5:30 would place itself between a 4:00 Event, and a 6:30 Event.
iam new to MS SQL 7 server...i have two tables in my database say Table1 and Table2 having a comman field--- Name String(30). I want that dual data entry should be made for any single entry. That is if a name is entered in Table1, then same entry should be automatically entered in table2
I am new to jscript and trying to learn how to pull multiple entry's from a table. I know with php you can use a while loop but that doesn't seem to work with jscript. Here is what I have so far.
var sSql = "select nIndex,sDescription from StatisticalDiskIdentification " + "JOIN PivotStatisticalMonitorTypeToDevice ON " + "StatisticalDiskIdentification.nPivotStatisticalMonitorTypeToDeviceID = " + "PivotStatisticalMonitorTypeToDevice.nPivotStatisticalMonitorTypeToDeviceID " + "where (PivotStatisticalMonitorTypeToDevice.nStatisticalMonitorTypeID='2') and " + "PivotStatisticalMonitorTypeToDevice.nDeviceID = " + nDeviceID;
while (oRs = oDb.Execute(sSql)){
if ( !oRs.EOF ) { // Display various columns in the debug log (Event Viewer). var sDisplay; nIndex = "" + oRs("nIndex"); Context.LogMessage("nIndex=" + nIndex); sDesc = "" + oRs("sDescription"); Context.LogMessage("Description =" + sDesc); } Context.SetResult( 0, " Ok"); oRs.MoveNext(); }
is there a way when making a stored procedure if i can enter the information in multiple tables?? say the primary key into another table to link the relationship? or should i just pull it out and then put in?
I have the following table (which is an import from another system Ican't mod):-CREATE TABLE [tbl_wsg_maternity_observations] ([documentname] [varchar] (40),[clientguid] [decimal](16, 0) ,[docguid] [decimal](16, 0) ,[displayname] [varchar] (80),[valuetext] [varchar] (255) ,[valuenum] [float] NULL) ON [PRIMARY]GOWheredocumentname is the name of the documentclientguid is the unique identifier for my patientdocguid is the unique id for the documentdisplayname is the dataitem (e.g. diagnosis)valuetext is the "answer" (e.g. kidney failure)valuenum is used instead if the valuetext is an integer (e.g.number of toes)I am trying to split/change this table so that I have a different tableper document, with one row per patient occurance with the displaynamesas columns.I have been using the following but it is slow and for large tablestakes hours (literally) to run:-SELECT distinctclientguid,(SELECT DISTINCT case when t2.[ValueText] is null thencast(t2.[Valuenum] as varchar(10)) else t2.[ValueText]end FROMtbl_wsg_maternity_observations t2 WHERE 'How many vessels present incord' = t2.[Displayname] AND t1.ClientGUID = t2.ClientGUID ANDt1.docGUID = t2.docGUID) as [How many vessels present in cord],<SNIP...more identical lines, one per dataitem>INTOtbl_wsg_baby_delivery_detailsFROMtbl_wsg_maternity_observations t1WHEREdocumentname = 'Mat Baby Delivery Details'Does anyone have any ideas how to do this faster and preferably moresimply?Will
Hey all,I am having some serious trouble getting the correct syntax for a select statement to work the way I need it, any help I could get on this would be greatly appreciated.I have a table called Units which stores computers and a table called Software which stores software. I have a bit field in Units called OEM, when this is set to true I don't want the select statement to pull this unit down when I am assigning software to other units.Here is my select statement: SELECT Software.SID, Software.SN, Software.Name, Users.First + ' ' + Users.Last AS 'Assigned User', Units.Make + '-' + Units.Model AS 'Assigned Unit' FROM dbo.Software LEFT JOIN dbo.Units ON Software.SN = Units.SN LEFT JOIN dbo.Users ON Units.UID = Users.UID WHERE (Units.OEM = 'FALSE') AND (Software.SN LIKE '%' + @SearchString + '%')Everything works as expected, unless of course the unit has no software assigned to it yet, it won't return it because its not tied to a Units.OEM field. Is there anyway to have it return ALL records that even arn't joined OR are joined but have OEM set to false?Thanks, let me know if I need to clear anything up.-Matthew
I have a database of automobiles. I have many many columns, it is blank of course until I can start filling it with information. I will have four main rows. Yearnum, Make, Model, and VehicleStyle columns I will use Honda Accord as an example. Honda has made the Accord since probably the 80's I know that if I SELECT yearnum FROM YearNum ORDER BY yearnum
I am using C++ Builder too.. it will put all my years in order, in a combo box. But I believe it will also have duplicates, like for example They may have made a 1995 Honda Accord and then made a 1995 Honda accord LS which may have different wiring colors and speakers sizes than the regular accord. Is there anyway to filter out multiple years, so I could just have the regular order of years?
Has anyone ever looked at the way the grid data viewer sorts it data when clicking on a column header? If you click on a column header, something happens to the sorting of the data in the viewer, but it's not always clear to me _what_ is happening. It appears the data is sorted ascending on the column you clicked on. If you click again the sort order seems to inverse to descending. However, if you look closely, it turns out that the data is not always sorted correctly, especially when you click on an integer valued column.
I found this when doing a demo on the AdventureWorks demo extracting data from the SalesOrderDetail table. If you sort on the OrderQty column, data is correctly sorted in ascending quantities. However, if you click the column again, orders with an order quantity of 2 are displayed on top (while there are orders with a much higher order quantity) and if you scroll down the list, you notice that there is no clear sorting anymore. The same happens with other columns.
Is this supposed to work as I would expect it to do or is there a logical explanation for the behaviour I see?
-- Best regards,
Hans Geurtsen Docent Kenniscentrum
Info Support De Smalle Zijde 39 3903 LM Veenendaal The Netherlands www.infosupport.nl
We have a matrix report which displays columns in a default sorting order. This report columns vary dynamically depending on the user input.
e.g. If user wants to see the report for column Alfa, Beta , Gama then a report will be genarted with column Alfa, Beta , Gama sorted in alphabetical order.
Site %Risk Alfa Beta Gama
X 2 1 2 3
Y 10 4 5 6 However the users want the Columns to be sorted in the order which they provide the inputs e.g. if the user entered Gama, Alfa, Beta the report should display the columns in the same order instead of applying the default sorting order.
Site %Risk Gama Alfa Beta
X 2 3 1 2
Y 10 6 4 5
Any thoughts on ways to achieve this in SSRS matrix report would really help.
Now I would like to sort these by using the Interactive sort functions - but I have seen elsewhere that this is not possible..(I'm also getting an error when trying..)Is there not a way that I can bypass this (using Code function or similar) ? The datasource for the data is a OLAP cube
I have a table that has 3 different types of dates (date1, date2, date3), and they represent the edit times of the 3 different sections on the website. How do I select only one of those three that's the most recent? (It would represent the most recent edit on the website overall). It would be an easy task if it was only one column - I would simply select the MAX, but I have to be selective between 3 different columns and pick the one I "like".
Hi, I have a dbf file and that file has around 154 columns and in that i want to pull just 88 columns to my sql server database... I am using a OLedb connection and a data reader to read the data from the DBF file and then using a sqlbulcopy to insert the data into SQL server 2005 database. I have created a destination table for the 88 columns. This is my select statement for the dbf file. I have also used a Rownumber which is Int identity so i am using a 0 in the first column. Dim command As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select 0,* from FUND.DBF", connection) Now my question is Since i want to pull 88 columns instead of 154 column, I was thinking to give a select statement like ("Select, 0,Column1,.....Column88 From Fund.DBF", connection) SO instead of doing this is there is a way that i can specify in the sql statement that will tell it not to pull the rows after the 88th column.
