MS SQL Server :: Query The Latest Info

SQL Server 2012 :: Query Pulling Latest Info Data From Table

Aug 29, 2014

Let's say I have a table of data as per the below..

I'm trying to extract only the green highlighted items..

The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.

As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).

I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.

View 7 Replies View Related

T-SQL (SS2K8) :: Selecting Latest Record With Info

Aug 27, 2014

I have created the following SQL snippet that is a very simple mock-up illustrating the problem (I hope!) that I am facing:

-- create table
if object_id('tempdb..#tmpdelnotes') is not null
drop table #tmpdelnotes

create table #tmpdelnotes(
DelNote int identity (1,1) ,
DelDate date not null,
Item int not null,
Customer int not null)

[code]...

What I need to retrieve is a unique list of item numbers with information about the latest (DelDate) delivery note. The "Clumsy workaround" works, but is not very pretty when doing multiple table joins. Is it really necessary to use a derived table for this kind of query? Window functions can only exist in the SELECT and ORDER BY clauses, which is understandable since the calculations take place (I would guess) after the aggregations in the HAVING clause.

View 2 Replies View Related

Query For The First And Latest Wish

Feb 22, 2007

Hi all,I have the following tableName Date Wish ValidName is person's name, date defaults to getdate() and is neverassigned directly (datetime field), Wish is some message, and Valid isbit, 1 indicates if the wish is the latest, and therefore valid. Allprevious wishes are kept in database, and are "invalidated" by settingthe Valid to 0.So, a typical data set looks like:Name Date Wish ValidJoe 02/01/2007 Ice Cream 0Joe 02/04/2007 Bicycle 0Joe 02/06/2007 PS3 0Joe 02/22/2007 XBox 360 1Mary 02/02/2007 Barbie 0Mary 02/04/2007 Cindy 0Mary 02/06/2007 Barbie house 0Mary 02/20/2007 Get married 1My users want to see the initial wish at some point and another onesome time later (they provide dates). So, if someone wanted to seechanges in wishes between 02/03 and till 02/15, they would get thatJoe's initial wish was Bicycle and the latest that he wanted was PS3.As for Mary, she started wanting Cindy and ended up thinking about theBarbie house.I can do UNION, but is there another way to do that?Thank you.

View 7 Replies View Related

Need Query For Latest Time Per ID

Jun 11, 2006

I've been baffled over how to do this without using a script... I would like to get the info I need with a single query.

Here's my scenario, the table looks like this (simplified):

ID, TIME
101, 5am
101, 6am
104, 5am
260, 5am
104, 6am
260, 6am
101, 7am
260, 9am
104, 7am
101, 8am

So basically I have a column of identifications and a column of times. They won't necessarily be in order. I would like a query that gives me this:

101, 8am
104, 7am
260, 9am

It would order the IDs ascending, only showing the newest time assigned to that ID in the table.

Thanks in advance for any help :)

View 14 Replies View Related

Query To Fetch Latest Record

Jun 29, 2012

Table has details like below:

NAME UPATE-TIMESTAMP
==== ===============
mary time1
mary time2
mary time3
tom time1
tom time2
tom time3
tom time4
richard time1
richard time2

Output Expected:

NAME UPATE-TIMESTAMP
==== ===============
mary time3
tom time4
richard time2

In summary, the requirement is to fetch the latest upated record for each unique NAME.

View 1 Replies View Related

Query To Get Latest 2 Records For Each Group

Aug 21, 2014

select
DayRank = ROW_NUMBER() OVER(ORDER BY a.datedel DESC),
a.order,a.line,a.datedel,a.recpt,b.status,
b.item,b.t_sup
from historytbl a
inner join order b
on a.order = b.order
and a.line = b.line
and a.status =4
group by a.order,line,a.datedel,a.recpt,b.status,b.item,b.sup

The query is returned the results below.

Rank OrderLineDateDelrecptitemsup
----- -------------------------------
1aaa102014-18-08rc1zzz1231122
2bbb202014-08-08rc2zzz1231122
3ccc302014-04-08rc3zzz1231122
4ddd902014-08-11rc6yyy123333
5eee102014-05-11rc7yyy123333
5fff90 2014-02-11rc8yyy123333
6ggg102014-05-10rc9qqq123444
7hhh502014-04-10rc0qqq123444
8iii102014-04-10rc5rrr123555

However, I want to have the query only show most recent two records for each group of item and sup, please see the results I want below.

Rank OrderLineDateDelrecptitemsup
----- -------------------------------
1aaa102014-18-08rc1zzz1231122
2bbb202014-08-08rc2zzz1231122

4ddd902014-08-11rc6yyy123333
5eee102014-05-11rc7yyy123333

6ggg102014-05-10rc9qqq123444
7hhh502014-04-10rc0qqq123444

View 4 Replies View Related

Latest Known Exchange Rates MDX Query

May 29, 2008

Hi,

I have this specific problem, that has been causing me alot of headaches for the last couple of days, and I can't seem to fiure it out on my own.

Basically i am working on a many to one currency conversion setup, where I have a fact table with exchange rates for periods and for all input currencies. The reporting currency is USD. Here is a demonstration of the data in it:

Valid Date Source Currency Rate
2008-01-01 GBP 2.0563
2008-01-01 EUR 1.4634
2008-04-01 GBP 1.9968
2008-04-01 EUR 1.5197

Normal period by period conversion is easy enough, but I want to convert the measures at the latest known rate, based on the period selected. Lets say I run an analysis without slicing on time, then it want it to convert at the last entered rate of all time, and if the user wants to run the analysis as of March 08, then it should convert all data to the rate as of January, since this is the latest one known.

I have tried to set the measure to a semi-additive behaviour, with "lastnonempty", but the problem with that is the conversion over time. Here is an example:

I want to show a measure summed "all to date" as of June 08. What I want is it to convert everything at the rates entered in April 08, but what I get is that it convert everything up until Jan 08 and the rates entered in January, and thereafter it starts to use the rated entered in April.

Period Amount LCY Currency Code Exchange Rate
Dec 07 100 GBP 2.0563 (Wrong, should be 1.9968)
Dec 07 100 EUR 1.4634 (Wrong, should be 1.5197)
Jan 08 100 GBP 2.0563 (Wrong, should be 1.9968)
Jan 08 100 EUR 1.4634 (Wrong, should be 1.5197)
Feb 08 100 GBP 1.9968
Feb 08 100 EUR 1.5197
Mar 08 100 GBP 1.9968
Mar 08 100 EUR 1.5197
Apr 08 100 GBP 1.9968
Apr 08 100 EUR 1.5197
May 08 100 GBP 1.9968
May 08 100 EUR 1.5197
Jun 08 100 GBP 1.9968
Jun 08 100 EUR 1.5197

How do I create a measure containing the latest known rates based on time selection?

Thanks in advance

View 2 Replies View Related

Select Latest Records From GROUP BY Query

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

Query Problems - Group By And Latest Date

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

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 View Related

SQL Server 2008 :: Query To Group And Find Latest

Aug 25, 2015

I have a scenario as below for one ID -

+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 10.00 | 2015-08-25 12:05:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
| 5689 | 130.00 | 2015-08-25 12:07:57.107 | 3 |
+------+--------+-----------------------------+-----+

I want to fetch below 3 records from the above scenario i.e. latest record of each amount (Latest is determined using "descr" column i.e. 4 is greater then 3 -

+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
+------+--------+-----------------------------+-----+

But in case of same amounts I am unable to fetch the latest status as even using partitioning will treat them as one.

CREATE TABLE #TMP
(
ID INT,
AMOUNT DECIMAL,
[DATE] DATETIME,
DESCR VARCHAR(10)
)

INSERT INTO #TMP VALUES
(5689,10.00,'2015-08-25 12:10:57.107','4')
,(5689,10.00,'2015-08-24 12:07:57.107','3')
,(5689,10.00,'2015-08-25 12:05:57.107','3')
,(5689,130.00,'2015-08-24 12:07:57.107','4')
,(5689,130.00,'2015-08-25 12:07:57.107','3')

View 8 Replies View Related

SQL Query - Duplicate Records - Different Dates - How To Get Only Latest Information?

Mar 17, 2006

I have a SQL query I need to design to select name and email addressesfor policies that are due and not renewed in a given time period. Theproblem is, the database keeps the information for every renewal inthe history of the policyholder.The information is in 2 tables, policy and customer, which share thecustid data. The polno changes with every renewal Renewals in 2004would be D, 2005 S, and 2006 L. polexpdates for a given customer couldbe 2007-03-21, 2006-03-21, 2005-03-21, and 2004-09-21, with polno of1234 (original policy), 1234D (renewal in 2004), 1234S (renewal in2005), and 1235L (renewed in 2006).The policy is identified in trantype as either 'rwl' for renewal, or'nbs' for new business.The policies would have poleffdates of 2004-03-21 (original 6 monthpolicy) 2004-09-21 (first 6 month renewal) , 2005-03-21 (2nd renewal,1 year), 2006-03-21(3rd renewal, 1 yr).I want ONLY THE LATEST information, and keep getting earlyinformation.My current query structure is:select c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%s'and p.trantype like 'rwl'and c.email is not nullunionselect c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%'and p.trantype like 'nbs'and c.email is not nullHow do I make this query give me ONLY the polno 123%, or 123%Sinformation, and not give me the information on policies that ALSOhave 123%L policies, and/ or renewal dates after 2006-03-31?Adding a 'and not polexpdate > 2006-03-31' does not work.I am working with SQL SERVER 2003. Was using SQL Server 7, but foundit was too restrictive, and I had a valid 2003 licence, so I upgraded,and still could not do it (after updating the syntax - things likeusing single quotes instead of double, etc)I keep getting those policies that were due in the stated range andHAVE been renewed as well as those which have not. I need to get onlythose which have NOT been renewed, and I cannot modify the database inany way.*** Free account sponsored by SecureIX.com ****** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***

View 24 Replies View Related

Help With An MS SQL Server Query To Return The Latest Dates Against Each RecordID.

Jul 20, 2005

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 Related

Can We Pass Some Info From Sql Query To Dos

Oct 3, 2004

suppose i get date from MSsql how do i pass that to dos command is there a way so i can maipulate date and pass on to dos command to run at certain time

View 14 Replies View Related

SQL 2012 :: Query That Gives Info Twice?

Aug 13, 2015

I would like to do a query that would give me the info twice, with just a small difference.

Let's say I have a table with elements A, B and C. Whatever it is, it does not matter.

I would like to get all the data so I just do that:

SELECT A, B, C

Then I would like to add a bit of text, so I'll add:

SELECT A, B, C, 'Text1' as D

The thing is that I would like every single elements (A,B,C) to be repeated twice: Once with Text1, once with Text2.

Let's say my data looks like that:

1 2 3
2 1 3
4 1 2

Then my query would give me this:

1 2 3 Text1
2 1 3 Text1
4 1 2 Text1
1 2 3 Text2
2 1 3 Text2
4 1 2 Text2

(in no particular order)

Is there a way I can do this in just one query?

View 9 Replies View Related

Multiple Types Of Info In One Query?

Sep 21, 2006

Hi,

I am trying to display a report from a bug tracker that shows the number of issues logged per month, per year and how many issues have been closed in that month. I have no difficulty displaying the number of issues that have been logged within a particular months, but when I need to display the number of issues closed - I get stuck. I am calling from one table 'bugs', which contains date_reported, status_id. I can do a count on the number of bugs between certain dates but I cannot list the number of closed issues in that exact time. I get the feeling I need a select statement within a select statement? If anyone can help. Thanks

View 3 Replies View Related

Amending Query To Get Info From New Table

Oct 19, 2013

I used to use this code to extract coupon usage from two tables orders and coupons

select distinct o.OrderDate, p.SKU, c.CustomerID, c.CustomerLevelID, c.Email, c.FirstName, c.LastName from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber

[code]....

Following a database upgrade the Coupon Code is no longer used. Instead there are two new SQL tables called Promotions and PromotionsUsage.. The link between the two tables are

ID in Promotions

and

PromotionID in PromotionUsage

Both are the same value. So in order to see new orders for coupon usage how would I change this line in the original SQL to retireve this?

View 5 Replies View Related

Dates In Query Analyser - More Info

Apr 21, 2006

I am trying to test sp in Query Analyser as below but get the error

Server: Msg 8114, Level 16, State 4, Procedure spRMU_GetChaseFiles, Line 0
Error converting data type varchar to datetime.

How do I put the date as a parameter ? I think it may be to do with the fact that MaxDateOut is an expression in my view that the sp is based on. It is taking this as char when it should be datetime. So, how do I make this expression type date ?

EXEC spRMU_GetChaseFiles
@strStartDate ='01/01/05',
@strEndDate = '21/04/06',
@strDepartment = 'Central Units',
@strDivision = 'Personnel',
@strSection = ''

View 20 Replies View Related

How To Query Active Users - Where Is This Info Stored ???

Jul 25, 2001

I know how to look at active users from Enterprise Manager, but how can I query out the information - what table is that info stored in ??? I don't want a list of all the logins, I just want the list of active users....

Help... thanks in advance,
Nancy

View 2 Replies View Related

Is There A System Table With Timestamp Info Or DTS Job Info?

May 7, 2007

I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.

ddave

View 3 Replies View Related

Query Info Between Time Range & Date Range

Aug 16, 2006

I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!

View 6 Replies View Related

Latest Record

Sep 20, 2007

My table are
Customer: customerId ,name
Order: orderId, customerId, product,date
I want to display latest order from the customer

View 2 Replies View Related

What Is The Latest Version Of T-SQL?

Jul 26, 2000

Hi everybody,

In PL/SQL, they hv versions. Is there any versions available for T-SQL?. If so, what is the latest version available in T-SQL?.

advance in thanks,
Srini

View 1 Replies View Related

Get Latest Version

Nov 21, 2007

I use a table called "version" to store version number for every module
my question is how to get highest version number in this table?

table:
MODULE|MAJOR|MINOR|REVISION
modul1|6|1|0
modul2|6|3|1
modul3|6|2|8

in this case i want modul2|6|3|1 as the result
already try some subquery but got problem and a lot of result

tq...

View 9 Replies View Related

Help With Getting Row With Latest Date

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

How To Get Latest File

Feb 25, 2004

how to get the name of the latest file of a particular directory
thru query

i.e
i have files in a directory

1130am.txt
11.45am.txt
1200am.txt

my query should return 1200am.txt

View 2 Replies View Related

Help With Finding Out Latest Changes

Apr 8, 2008

So here is my dilemma:

We have licensed software from a 3rd party and we do not have the source code, nor can we do anything to change the database schema, write our own procs, triggers, etc.

With that said, we want to know which records in certain tables have been added or modified since a certain time (for example, in the past 24 hours). However, none of the tables has any datetime or timestamp columns on them for create date, update date, etc.

I tried to look into hacking thru DBCC log (dbname, 2) to see if that would help, what a mess. Using a tool like that from Luminent or Red Gate doesnt help either, because long term this needs to be automated if possible. Does anyone have any ideas on how to do this, or is the answer simply, "Sorry mate, you need to get them to allow you to change the database to add new columns and/or triggers". :)

View 5 Replies View Related

Latest Record For Particular ID

Jan 24, 2014

I have table has column called [last modified date] it has time too , i need to get the [latest modified date] for particular ID , there are many updates to the ID and i just need the latest date.

[PAX ID] | [Last Modified Date]
1 2013-01-02 18:23:00
1 2013-01-02 11:42:00
3 2013-01-01 09:00:00
3 2013-01-08 16:05:00
5 2013-01-01 09:09:00

View 2 Replies View Related

SQL To Get Latest Result From Each ID

Feb 6, 2008

View 4 Replies View Related

Getting The Most Latest Date

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

Getting The Latest Row From A Batch

Jul 20, 2005

Hi AllThis is a belter that my little brain can't handle.Basically I have 1 SQL table that contains the following fields:Stock CodeStock DescReferenceTransaction DateQtyCost PriceBasically this table stores all the transaction lines of when a userbooks stock items into stock so that they can look at a journal ofthis goods in as and when they please.My task is that the user wants a list of all the stock items with thelast cost price that they were booked in at.So I think I have to find the last transaction date used for eachstock code and then bring this back as 1 row per stock code with theabove fields of data.How the whats-its can I do this? Is it acutally possible?Any help you can give is much appreciated.RgdsLaphan

View 3 Replies View Related

Error - Trying To Get Only Latest

Jul 20, 2005

HiI'm trying to get some data fra a few tables, but I'm having a fewproblems.What I would like is this:The tables contain somn info on manuscripts and which process thatmanuascript has received.a manuscript is represented once in manuascript tablethat manuascript can have several records in the process table.What I want is to get data for each manuscript and the last process thatthe manuscript received from the process table.This SQL gets the manuscript for each process it has received. SO if amanuscript has 5 process records I will get 5 records back...SELECTManuscript.m_id, Manuscript.uniqueIDCountry,Manuscript.uniqueIDNo, Manuscript.m_title,Manuscript.country, Manuscript.m_receivedDate,Process.p_id, Process.m_id, Process.processDate,ProcessTypes.ps_id, ProcessTypes.processNameFROMManuscript,Process,ProcessTypesWHERE [Process].m_id = [Manuscript].m_idAND [Process].ps_id = [ProcessTypes].ps_idPlease help... I'm getting desperate..*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related