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
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.WarehouseDescription
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 LEFT OUTER JOIN
dbo.OrbitImport ON dbo.ESNTracking.EsnId = dbo.OrbitImport.OrbitImportId
WHERE (dbo.ESNTracking.OrbitImportId =
(SELECT MAX(OrbitImportId) AS Expr1
FROM dbo.ESNTracking AS ESNTracking_1))
ORDER BY dbo.AssetType.Description
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?Â
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
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
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?
The following SQL query :-SELECT CardHolder.RecordID, History.GenTime, History.Link1FROM History FULL OUTER JOINCard ON History.Param3 =LTRIM(RTRIM(Card.CardNumber)) FULL OUTER JOINCardHolder ON Card.CardHolderID =CardHolder.RecordIDWHERE (Card.Deleted = 0) AND (History.GenTime IS NOT NULL)ORDER BY CardHolder.RecordID, History.GenTime DESCreturns :-RecordID GenTime Link12 04/06/2004 15:30:00 1232 01/06/2004 16:00:00 1232 01/06/2004 08:00:00 1101155 02/06/2004 11:30:00 1231155 02/06/2004 08:00:00 1103925 03/06/2004 09:00:00 1233925 03/06/2004 08:00:00 1104511 06/06/2004 11:30:00 1234511 06/06/2004 10:30:00 110Is there a way of modifying this query to just return the lastestgenTime for each RecordID ??? ie return just rows 1,4,6 & 8.I assume it is something to do with MAX, but I can't seem to get myhead around it.Any help, or pointers would be appreciated.Oh, running query on Microsoft SQL Server 2000.RegardsDave
View 3 Replies View RelatedI have table in which month & year are stored, Like this
Month Year
10 2014
11 2014
12 2014
1 2015
2 2015
3 2015
4 2015
I wanted a query in which it should return the value in a status field which has latest year & month.
I have two table, tblCharge and tblSentence, for each charge, there are one or more sentences, if I join the two tables together using ChargeID such as:
select * from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID
, all the sentences for each charge are returned. There is a field called DateCreated in tblSentence, I only want the latest sentence for each charge returned, how can I do this?
I tried to create a function to get the latest sentence for a chargeID like the following:
select * from tblCharge c join tblSentence s on s.SentenceID=LatestSentenceID(c.ChargeID) but it runs very slow, any idea to improve it?
thanks,
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!
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 RelatedHow 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
if there are 2 records with different date how to write query for ----> get record with latest date
View 4 Replies View RelatedHello,
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
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
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
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
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?
Hi,
How to get the latest date from ColumnDateTime?
and the earlier as well ?
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
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
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?
> 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 RelatedI 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 Relatedon SQL Server 2000
They show only Create date
but I need know update date
because I install my system on customer's site and solve problem on customer site
and I can't bring all stored procedure back to my office and restore all stored
because of my database have two projects.
Please Help me.....
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]...
Without having to update it every day.
This is so no one enters a value higher than the current date.
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (Closed_Date>)
Below is the table information. I want the Code information with latest EDate only.
CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);
[Code] ....
Expected output :
Â
2015-06-23 00:00:00.000 CL 20150701 73
2011-04-08 00:00:00.000 XP 20110501 37
2015-06-23 00:00:00.000 HO 20150701 22
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
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.
I have 2 tables:
TransactionsImport (which is the destination table)
TransactionsImportDelta
I need to do the following:
Get the records with the latest date and time in the destination table TransactionsImport
Get the records with the latest date and time in the destination table TransactionsImportDelta table
Insert the records from the TransactionsImportDelta table into TransactionsImport that have a greater date & time than the current records in TransactionsImport table.
Problem is date & time are in separate columns:
Table structure:
Date Time ID
2011121305154107142201008300100
2011121305154122B1L13ZY0000A07YD
2011121304504735142201090002600
2011121304504737142201095008300
2011121304504737142201090002600
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 RelatedItemlookup tableField names : index_id (primary key), itemno, description.It has a child table, which is ItemPriceHistory tableThe relationship to the child table is one (parent table)-to-many(child table). - It is possible to have no child record for some rowsin the parent table.ItemPriceHistory tableField names: index_id (primary key), itemlookupID (foreign key of theItemlookup table), date begin, priceIt is a child table of the itemlookup table.How can I get all records for both tables with the latest begin date ifexists?I also need to show the records in the parent table if there is norelated record in the child table.Please help
View 4 Replies View Related