Select Query To Pull The Last/latest Event By Date
Oct 30, 2007
I have the following table called BADSANTA:
varchar(30) datetime
NAME WHENBAD
OJ Simpson 2007-1-12 xx:xx:xx:xxx
OJ Simpson 2007-4-2 xx:xx:xx:xxx
Monica Lewinsky 2006-7-4 xx:xx:xx:xxx
Monica Lewinsky 2006-10-31 xx:xx:xx:xxx
Bill Clinton 2006-7-4 xx:xx:xx:xxx
Bill Clinton 2006-10-31 xx:xx:xx:xxx
Bart Simpson 2006-11-2 xx:xx:xx:xxx
Bart Simpson 2006-2-25 xx:xx:xx:xxx
Bart Simpson 2005-07-27 xx:xx:xx:xxx
I want the records of the persons latest WHENBAD date. It should return one recordset for each person.
Thanks in advanced.
View 4 Replies
ADVERTISEMENT
May 24, 2005
Hello,
If I have a TransactionTableT including with fields like this:
TransactionDateDT
QuantityIN
ItemID,
ItemCodeCH
ItemNameCH
PriceFL
Each time the user make an entry a new record is inserted.
How can I pull only the latest (TransactionDateDT) record for each item. I've tried MAX(TransactionDateDT), but how can I get ItemCodeCH, ItemNameCH and PriceFL.
Basically I need to create a report with the latest price for each item.
Any feedback welcome!
Edi
View 2 Replies
View Related
Dec 3, 2012
I want to select the latest single value based on ProductID. The value should be the last value inserted from the Date column.
I want to use this as part of a query to delete this value when a button is slected in ASP.Net C#.
Here is my code which does not work
select basketID, SessionID, UserID, MAX(Date), productID from ShoppingBasket
where userID = '1'
group by productID, basketID, SessionID, UserID
View 2 Replies
View Related
Dec 8, 2013
I have a equipment table and the equipment has a coding for each place / location or custody it has had during its life. I need to select the latest (newest) row for each piece of equipment by getting the newest from_date and to_date field combination.The following is an example. I know how to get MAX date for one column but not with two columns (from and to DATES).
ID
NAME
FROM_DATE
TO_DATE
[code]...
View 1 Replies
View Related
Nov 29, 2014
I have a select query that results all the dbs and their several backup dates×. Howover I only need the latest backup dates for each of the db.How can I write that in tsql?
View 5 Replies
View Related
Apr 6, 2008
Hello. I have an "Events" table with a datetime column containing dates (the time is unnecessary). I want to select an upcoming event (one post) from the table where the date is either today or the day nearest to today.
Dim today As Date = Date.Now
I have declared a 'today' variable but I don't know how to use it in
the SQL query. Will I have to convert the today variable to something before using it?
Should a Label be involved? The event will be displayed in DetailsView.
View 7 Replies
View Related
Feb 7, 2008
I found an answer on your web site but it was for SQL 2005 and I am using SQL 2000. The question was posted on 2/7/08 from genelam ---- SQL to get latest result from each ID. Below is my sql:
select distinct
po.LastEditDate,
podel.OrdResc,
po.po
from Protean.dbo.poPurchaseOrder AS po
inner join protean.dbo.poline poln
on poln.parentobjectid = po.objectid
inner join protean.dbo.podelivery podel
on podel.parentobjectid = poln.objectid
and po.posite= 'LIG'
and podel.OrdRescSite = 'LIG'
order by podel.OrdResc
SQL would return the data below:
LastEditDate OrderResc PO
01/18/2008 130-100218 PG00251
02/04/2008 130-100218 PG00252
02/21/2007 130-100218 PG15477
02/21/2005 130-100218 PG12577
01/24/2004 130-100218 PG12489
01/22/2003 130-100214 PG12188
02/10/2001 130-100214 PG78892
12/10/2005 130-100214 PG98612
I need the SQL to give the result below by pulling only one PO for that OrderResc number with the latest and greatest LastEditDate and the result should be below:
LastEditDate OrderResc PO
02/04/2008 130-100218 PG00252
12/10/2005 130-100214 PG98612
This should be easy but I can't get an solution from anyone ---- please some HELP! Thanks, Deb
View 2 Replies
View Related
Mar 21, 2007
Hi all,
hopefully someone can suggest the best way of implementing the problem i am trying to resolve. We have a table which contains rows relating to tests run on our product. This table is populated from an SSIS job which parses CSV files.
There are multiple rows per serial number relating to multiple tests. The only tests i am interested in are the ones with an ID of T120. Here is the query i have so far which should make it a little easier to explain:
SELECT [SerialNumber]
,Param1
,[TimeStamp]
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1, [TimeStamp]
ORDER BY SerialNumber
What i have above is fine to a point. The problem i am encountering is that in test T120 it specifies a part which can be be one of about 6 in field Param1. If during testing there is a problem with the part then it is replaced and the test run a second time up until the whole product passes the test. The query above returns all instances of replacements so i may have the out put as follows:
SerialNumber Param1 TimeStamp
0 Part1 15/03/07
0 Part2 15/03/07
0 Part2 16/03/07
0 Part3 15/03/03
What i really need is to only list the last part that is installed, hence the one with the latest timestamp:
SerialNumber Param1 TimeStamp
0 Part1 15/03/07
0 Part2 16/03/07
0 Part3 15/03/03
Can someone please help me to alter the above query so that it will show only those Param1 fields that have the latest date for each part.
Many thanks in advance,
Grant
View 8 Replies
View Related
Jun 26, 2015
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
View 4 Replies
View Related
Feb 26, 2014
I have a table T (a1, ..., an, time, id). I need to select those rows that have different id (GROUP BY id), and from each "id group" the row that has the latest field 'time'. Something like SELECT a1, ..., an, time, id ORDER BY time DESC GROUP BY id. This is the wrong syntax and I don't know how to handle this.
View 3 Replies
View Related
Apr 16, 2008
How do I pull only the latest shipdate record from a multiple record key?
I tried the following, but it doesn't pull only the lastest date record:
Select catnum, Max(shipdate) as shipdate from table_a group by catnum, shipdate
Thanks in advance.
View 7 Replies
View Related
Mar 19, 2014
I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.
Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .
Example:
Create Table #EmpIDs (
EmplID int
)
Create Table #EmpRoles (
EMPID int,
[Code] ....
Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .
My Final Output of select query should be like below.
EmpID Role
1 Role1
2 Role2
3 Role1
View 5 Replies
View Related
Nov 26, 2015
CREATE TABLE #Dateissue
(ID int,
Code nvarchar (20),
Datein datetime,
Declined datetime )
[Code] .....
I have a table here.  I want  find a way of getting the latest date, when the code is the same.  If the Declined date is null.  Then I still want the latest date.  E.g. ID 3. Â
If the declined date is filled in. Â Then I want to get the row, when the Datein column value is greater then the declined date only.
I tried grouping it by max date, but  i got an error message when trying this out.  Against the code Â
WHERE MAX(Datein) > Declined
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. Â What do I need to do to get both my outputs working?Â
View 9 Replies
View Related
Apr 19, 2015
My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.
Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01
[code]...
 In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01
View 10 Replies
View Related
Apr 23, 2015
My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario
ID Â Â Â Â Â StartDate
1 Â Â Â Â Â Â 2014-01-01
1 Â Â Â Â Â Â 2014-02-01
1 Â Â Â Â Â Â 2014-05-01-------After Gap Restarted
1 Â Â Â Â Â Â 2014-06-01
1 Â Â Â Â Â Â 2014-09-01---------After last gap restarted
1 Â Â Â Â Â Â 2014-10-01
1 Â Â Â Â Â Â 2014-11-01
2 Â Â Â Â Â Â 2014-01-01
2 Â Â Â Â Â 2014-02-01
2 Â Â Â Â Â Â 2014-03-01
2 Â Â Â Â Â Â 2014-04-01
2 Â Â Â Â Â Â 2014-05-01
2 Â Â Â Â Â Â 2014-06-01
2 Â Â Â Â Â Â 2014-07-01
For Id 1 the start date after the latest gap is  2014-10-01 and for id=2 there is no gap so i need the minimum date  2014-01-01
My Expected Output
id       Startdate
1 Â Â Â Â Â Â Â 2014-10-01
2 Â Â Â Â Â Â Â 2014-01-01
View 4 Replies
View Related
Sep 28, 2012
I have to display the last temperature reading from an activity table for all the dates in a selected date range.So if I select the date range from 09/01/2012 to 09/30/2012, the results should look like this:
Date Temperature
09/01/2012 73.5
09/02/2012 75.2
09/03/2012 76.3
09/04/2012 73.3
09/05/2012 77.0
09/06/2012 74.5
and so on.
I am using this to get the dates listed:
WITH CTE_DatesTable
AS
(
SELECT CAST('20120901' as date) AS [Date]
UNION ALL
SELECT DATEADD(dd, 1, [Date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [Date]) <= '20120930'
)
SELECT [Date]
FROM CTE_DatesTable
How could I get the temperature if I did a sub-query here?
View 5 Replies
View Related
Nov 2, 2007
Hi all,
Can we get the event properties by using a query?
Are there any extended stored procuder to get the above?
Scenario:
>Desktop>Right Click on My Computer
>Go to Manage and click
>Expand System Tools
>Expand Event Viewer
>Application
click on one event.We can get the log info which is the manual procudure.
But now i want to get the event properties through the Query analyzer...
Any help would be great?
Thanks,
View 4 Replies
View Related
Aug 22, 2006
have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.
i have tried with the following query, i need help putting my queries together
select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')
when i run this query i get this message
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15
please help
mustfa
View 6 Replies
View Related
Sep 3, 2007
guys help please...I have a table with 3 columns (TransactionID, CustomerID, TrasanctionDate) and there could be posibility that one or more rows could have the same value for CustomerID. Now my question is it posible to retrieve the row of latest TrasanctionDate made by a particular customer?
Ex:
TransactionID | Customer ID | TrasanctionDate
1 | 2 | 9/3/2007 12:00:00 AM
2 | 3 | 9/4/2007 12:00:00 AM
3 | 2 | 9/5/2007 12:00:00 AM
4 | 2 | 9/6/2007 12:00:00 AM
Say, i want to retive the latest trasanction of customer with CustomerID equals to 2. The output shoudl be is row 4 since it has the lates date with CustomerID equals to 2
4 | 2 | 9/6/2007 12:00:00 AM
Any help will be greatly appreciated. Thanks in advance!
View 3 Replies
View Related
Jun 14, 2006
I have a column which stores dates (datetime data type). I would liketo fetch one data which is the most latest date among them. So if thereare 04/01/06, 05/08/06, 05/12/06, 06/15/06, then 06/15/06 is the one Ineed to output.how can I write this in sql stmt?select datebeginfrom testtablewhere datebegin = ?????I don't think it is simple as I thought. I have no idea what I need towrite in where clause to make this work.thanks.
View 1 Replies
View Related
Mar 5, 2007
How to modify the below procedure in order to get the latest date ( the biggest value ) in the column “Deadline�, type smalldatetime, ( example: 24/02/2007 00:00:00 ) for a given customer “UserName� ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE GetLatestDate
(@UserName VARCHAR(50)
AS
SELECT Deadline
FROM CustomerItems
WHERE UserName = @UserName
View 2 Replies
View Related
Jun 4, 2007
if there are 2 records with different date how to write query for ----> get record with latest date
View 4 Replies
View Related
May 8, 2008
Hello all. I ma using the following query to pull back data. The MergeHistory table has a column named DateMerged. I am looking to pull back the one record with the most recent DateMerged. I have managed to get the query as far as below but not sure how to select the most recent one. Can anyone help with this? I was told it may be along the line of SELECT TOP 1 or something?
INSERT INTO @List (IndexID, IndexName, MergeSystem, Status, DateCreated, CreatedBy, DataTag, MergedDate)
SELECT DISTINCT
RT.IndexId,
isnull(dbo.ufn_GetBestIdentifier(RT.IndexId), dbo.ufn_GetBestVirtualIdentifier(RT.IndexId)),
dbo.ufn_GetEntitySystemName(RT.IndexId),
RT.Status,
CONVERT(varchar, RT.DateCreated, 106) as DateCreated,
RT.CreatedBy,
RT.DataTag,
MH.MergedDate
FROM @resulttable AS RT, MergeHistory AS MH
WHERE RT.IndexId = MH.EntityID
View 4 Replies
View Related
Dec 10, 2013
Aim – Get the latest [hst_date_processed] for every [hst_merchnum]
My query so far is
SELECT top 5
[hst_merchnum]
,[hst_date_processed]
FROM [FDMS].[dbo].[Fact_Financial_History]
where hst_merchnum = '878001897'
group by [hst_merchnum],hst_date_processed
which return the following results
hst_merchnumhst_date_processed
8780018972011-12-01
8780018972012-07-01
8780018972011-11-01
8780018972011-02-01
8780018972011-05-01
However results i need are:
hst_merchnumhst_date_processed
8780018972012-07-01
View 2 Replies
View Related
Apr 10, 2014
I want to get latest updated date on each transid and only for status =approved .
-------------------
out put would be
------------
idtransid date status
31013/1/2014 approved
61031/2/2014 approved
table
-----------
idtransid date status
11011/1/2014 approved
21012/1/2014 close
31013/1/2014 approved
41021/2/2014 approved
51022/2/2014 close
61031/2/2014 approved
View 1 Replies
View Related
Jan 20, 2006
I am trying to return the latest date in a stored procedure as shown below, but get the error 'incorrect syntax near @strLastDate'
CREATE PROCEDURE spRB_GetLastDateBookingDates
@strLastDate datetime OUTPUT
AS
SELECT max(BD_DateRequired) as @strLastDate
FROM tblRB_BookingDates
GO
RETURN
View 8 Replies
View Related
Sep 27, 2007
Hello!
I have a table something like this:
ID INTEGER
Info VARCHAR (actually several columns but that is not important here)
DAT DateTime
For each ID there are several dates and for each of these dates there are several rows with different info. I would like to select the latest info for each ID. For example:
ID - DAT - Info
1 - 2007-02-01 - Info1
1 - 2007-02-01 - Info2
1 - 2006-02-01 - Info3
2 - 2007-05-05 - Info4
2 - 2007-02-01 - Info5
2 - 2006-02-01 - Info6
I would like to get:
Info1
Info2
Info4
This has to be done in one Query. Can anybody help me?
View 6 Replies
View Related
Mar 1, 2007
Hi,
How to get the latest date from ColumnDateTime?
and the earlier as well ?
View 5 Replies
View Related
Apr 10, 2014
i want to get latest updated date on each transid and only for status =approved .
-------------------
out put would be
------------
idtransid date status
31013/1/2014 approved
61031/2/2014 approved
table
-----------
idtransid date status
11011/1/2014 approved
21012/1/2014 close
31013/1/2014 approved
41021/2/2014 approved
51022/2/2014 close
61031/2/2014 approved
View 2 Replies
View Related
Mar 26, 2015
I have the following table:
Item Date
A 2015-01-02
A 2015-03-01
A 2015-02-17
B 2015-01-07
B 2015-03-20
C 2015-01-02
C 2015-03-17
C 2015-01-14
C 2015-02-02
D 2015-03-25
E 2015-02-09
E 2015-02-09
Is it possible to get a list with only one of each item and then the latest date possible?
View 2 Replies
View Related
Jul 20, 2005
> This is what I want to do with the data table below.[color=blue]>> I only want it to show one id for each issue. ( ie, 4001 only once,> 4002 only once, etc. But only the one with the latest date and time)>> Ie. 4001 should only be in the table once, with the latest date -> which should be :> 400109/12/200317:12:09 (as I only want to show the last time> the id was updated - do you get me now )>> Ie. 4002 should show only 400211/12/200315:25:13>[/color][color=blue]>>> idhs_change_datehs_change_time> 400127/10/200310:38:27> 400109/12/200314:43:58> 400109/12/200317:12:09> 400227/10/200310:56:28> 400209/12/200314:44:11> 400211/12/200315:25:13> 400327/10/200311:13:12> 400309/12/200314:44:21> 400310/12/200310:48:02> 400310/12/200313:25:09> 400427/10/200311:28:09> 400409/12/200314:44:29> 400409/12/200317:18:28> 400527/10/200315:55:40> 400528/10/200310:18:24> 400627/10/200315:59:47> 400628/10/200310:18:38> 400609/12/200314:44:40> 400609/12/200316:47:15>[/color]
View 3 Replies
View Related
Jul 20, 2005
I have 6 columns, all with dates within them, i.e.Proposed Start Date 1Proposed Start Date 2Proposed Start Date 3Proposed Finish Date 1Proposed Finish Date 2Proposed Finish Date 3What I need to do is narrow this down into two fields:Start DateFinish DateSo I need to find the newest value from the columns. i.e.If PS1 filled PS2 and PS3 empty, then Start Date = PS1If PS3 empty and PS2 filled, then Start Date = PS2If PS3 filled then PS3and similarly for Proposed Finish Dates.Anyone knew how I can do this.(Maybe the following will help for the programmers out there:If PS3 <> nullThen StartDate = PS3Else if PS2 <> nullThen StartDate = PS2ElseStartDate = PS1End IFJagdip
View 5 Replies
View Related
Sep 21, 2007
Hello!
I have a table, where one of the columns is the date/timestamp of when each row was inserted. I want to be able to extract the most recently inserted rows.
With Sybase (a not so distant cousin of MS SQL) the following works:
select * from TABLE having date = max(date)
With MS SQL, however, the same query does not work:
Column 'TABLE.date' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
What's the solution? Thanks!
View 14 Replies
View Related
