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

Package Not Picking Up Latest Config File Changes

Nov 7, 2006

Hi,

I've created a solution with 5 packages in. I've scripted a config file for each package where I would be able to change the source file connection and the database servers (source and destination). On the first package, I am able to change the config attributes and the affects are picked up, but the rest of the packages do no pick up the changes.

For example, if the source file is "C:Filessource.txt" and this is changed to "C:Filessource123.txt" (Just to make sure it fails) it wont pick up the new changes and still uses "C:Filessource.txt".

Also, I tried changing the name of config file itself and the package still ran as if nothing had changed. It's as if it's not recognising the config file.

Any ideas ?

Thanks, Richie.

View 9 Replies View Related

SQL Server 2014 :: Restore The Latest Transactional Log File Job?

Sep 23, 2015

I'm trying find a way of crating an sql server job which would restore only transactional log file just once a day. The trouble I'm having is trying to get only the latest transactional log file it it possible to achieve through e.g powershell etc as I can't find a way through sql query?

View 9 Replies View Related

Code To Find The Latest File In A Folder And Export It To A Sql Server Table

Feb 15, 2008

Hi Experts,

I have to find the latest file in a folder and export data to a table in sql server.
The code should be something that has to be incorporated into a t-sql stored procedure.

The file name would for example abc_defYYYYMMDD.xls.
would i be able to find the latest file in the folder using the the datestamp (YYYYMMDD) in the filename.

Please note i would have files in other format and names with datestamp attached to it, so the code has to pick specific file for which the file name starts with 'abc_def'

and export data to a table.

Any help would be highly appreciated

View 10 Replies View Related

SQL Server 2008 :: Load Latest CSV Files From File Server Automatically

Feb 4, 2015

I need to load the latest csv files from file server , The files are placed in a folder called -

Posted 02022015- --> csv files .

I am able to copy the csv files from filserver using bulk insert (manually) , giving the file location

I am having difficulty picking up the latest folder which is posted on the server and import it into database using a stored proc .

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

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

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

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

SQL To Get Latest Data.

Sep 4, 2007

I need help in constructing SQL. I have the following table

Document
PKEY id varchar

DocumentRev
PKEY docID -> Document.id
PKEY rev int

TransmitDoc
PKEY transmitNo int

PKEY projectID varchar
PKEY documentID -> DocumentRev.docID
PKEY rev -> DocumentRev.rev

Given a transmitNo and projectID, what is the SQL can be used to get all document that have latest/largest rev that is not in TransmitDoc?

example I have 4 document.
documentA rev 1
documentA rev 2
documentB rev 1
documentB rev 2

if the given transmitNo and projectID have
documentA rev 2

The result should only show
documentB rev 2

documentA rev 1 and documentB rev 1 should not be shown because it is not the largest.

Thanks,
Max

View 4 Replies View Related

How To Get The Latest Information...

Feb 28, 2008

Hi,

I have the following situation: For a list of contacts I need to return all the contacts with the latest campaign that he is associated...
Here is my tables..

Code Snippet
CREATE TABLE [MAILING_TAB-Contatos] (
[CODContato] [int] NOT NULL ,
[CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[CPF] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[RazaoSocial] [varchar] (55) COLLATE Latin1_General_CI_AS NULL ,
[Endereco] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Cidade] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Bairro] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Estado] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[CEP] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Telefone] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Telefone2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Telefone3] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[FAX] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Responsavel] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[ResponsavelTelefonia] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Celular] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Email] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Site] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[IndicadoPor] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Notas] [text] COLLATE Latin1_General_CI_AS NULL ,
[Interesses] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[CODMail] [int] NULL ,
[CODRamoAtividade] [int] NULL ,
[CODTipoAtividade] [int] NULL ,
[Porte] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_MAILING_Main-Contacts] PRIMARY KEY CLUSTERED
(
[CODContato]
) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [MAILING_TAB-ClienteCampanha] (
[GerenteVendas] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[CODContato] [int] NOT NULL ,
[CODCampanha] [int] NOT NULL ,
[DATAAtribuicao] [datetime] NULL CONSTRAINT [DF_MAILING_TAB-ClienteCampanha_DATAAtribuicao] DEFAULT (getdate()),
CONSTRAINT [PK_MAILING_ClienteCampanha] PRIMARY KEY CLUSTERED
(
[CODContato],
[CODCampanha]
) ON [PRIMARY] ,
CONSTRAINT [FK_MAILING_ClienteCampanha_MAILING_Campanhas] FOREIGN KEY
(
[CODCampanha]
) REFERENCES [MAILING_TAB-Campanhas] (
[CODCampanha]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_MAILING_ClienteCampanha_MAILING_Main-Contacts] FOREIGN KEY
(
[CODContato]
) REFERENCES [MAILING_TAB-Contatos] (
[CODContato]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]

CREATE TABLE [MAILING_TAB-Campanhas] (
[CODCampanha] [int] IDENTITY (1, 1) NOT NULL ,
[DescricaoCampanha] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_MAILING_Campanhas] PRIMARY KEY CLUSTERED
(
[CODCampanha]
) ON [PRIMARY]
) ON [PRIMARY]

And sample data
-----Contatos----
16228 61349031000147 91055652949 *** ATLETICA FLORESTA R PRIMITIVA VIANCO 405 OSASCO CENTRO SP 06010000 1136818676 1136826872 NULL NULL TATIANA DA SILVA NULL 1136853252 NULL NULL NULL NULL NULL 700 NULL NULL NULL

16229 60504586000153 00444817808 COOPERATIVA MISTA TRAB DA GDE S PAULO LTDA R ANTONIO AGU 751 OSASCO CENTRO SP 06013000 1136543715 1136816656 NULL NULL EVANDECI JORGE CERQ OLIVEIRA NULL 1136816743 NULL NULL NULL NULL NULL 700 NULL NULL NULL

16230 00124567000170 36903809520 CALCADOS GABRIELLA OSASCO LTDA R ANTONIO AGU 115 OSASCO CENTRO SP 06013006 1136996011 1136996018 NULL NULL GILSON BASTOS DOS SANTOS NULL 1136996305 NULL NULL NULL NULL NULL 700 NULL NULL NULL

16231 00182385000155 07929808831 PRYMUS BEGNINI COM DE CALCADOS E CONFECCOES LTDA R ANTONIO AGU 177 OSASCO CENTRO SP 06013006 1136995410 NULL NULL NULL CLAUDIO DE OLIVEIRA COSTA NULL NULL NULL NULL NULL NULL NULL 700 NULL NULL NULL

16232 53679098000111 00368397874 ANGELA MODAS LTDA R ANTONIO AGU 293 OSASCO CENTRO SP 06013006 1136854672 NULL NULL NULL JORGE MIGUEL DA SILVA NULL NULL NULL NULL NULL NULL NULL 700 NULL NULL NULL

---Campanhas----
1 Teste
2 teste2
3 teste3
4 teste4

--- ClienteCampanha ---
1 16228 1 2008-02-28 00:00:00.000
1 16228 2 2008-02-26 00:00:00.000
2 16229 1 2008-02-28 00:00:00.000
4 16230 3 2008-02-25 00:00:00.000

--The result for my query must be contatcs 16228, 16229, 16230, for campaigns 1, 1, 3
I need to see all fields from contatcs, the name of the campaign, and it's date...

It's like if the campaign number 2 has been disabled or expired
Thanks

View 3 Replies View Related

Getting The LATEST DATE In The Table

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

Get Record With Latest Date

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

Get Latest Update In A Table

Oct 4, 1998

I think you experts must know how to do this.. I just can`t find in any book a good example or related information....

1) I have a time-logged table like this

Product Status Datetime Where
A Received 1/1/90 23:00 Chicago
A Sold 1/7/90 08:30 New York
A Returned 1/8/90 04:30 Paris
B Bought 1/12/91 06:39 Dallas
B Received 1/13/91 04:30 Chicago
B Returned 1/14/91 14:23 Chicago
C SendOut 1/15/91 07:45 Chicago
D Bought 1/25/92 06:30 London
etc..

All A`s are the same unique product as B and C are too, above is thus a history log of each product item. The records are not updated, just insertions only. (a DELETE on some record in time is likely, but no UPDATES...) I use the history log for several analisys purposes

2) Now, I want by one single SQL query get ONLY latest status of all products,
Like this (No I do not want to keep a separate "status" table each time I insert to the above table)

Product Status Datetime Where
A Returned 1/8/90 04:30 Paris
B Returned 1/14/91 14:23 Chicago
C SendOut 1/15/91 07:45 Chicago
D Bought 1/25/92 06:30 London

how to do such QUERY. It tried many things without exactly what I need.
I am not a good SQL programmer as U may know now..
the QUERY also should run on MS-ACCESS MDB

3) Replication
The history table is updated by each location and SQL server must keep all table in same condition using replication. Anybody has a suggestion how to setup replication in SQL server for this. It means each insertion into the log is send to each individual site. Can I publish to the same table from each site to each site , so that each change in the log gets reflected in the other site`s history table ?

Thanks a lot !

View 2 Replies View Related

Select Latest Date Only

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

Latest Build Version?

Jul 19, 2004

Hi,

What is the latest patch level for SQL server 2000?

I am currently at this build level:

Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

How can I tell when microsoft bring out a new SQL server hotfix or patch?

View 1 Replies View Related

Query The Latest Info

Jan 6, 2005

Hi All,

I have thsi list of record

Chasis Status Date
pl1 sold 10/20/2004
pl1 return 10/21/2004
pl2 sold 10/24/2004
pl2 return 10/25/2004
pl3 sold 11/01/2004
pl4 sold 11/03/2004
pl4 return 11/04/2004
pl4 sold 11/06/2004

sp i want to list out cars that status solid has been sold

so in this case only pl3 and pl4 can be display. So anyone can advise me on this. thanks

Regards,
Shaffiq

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

How To SELECT The Latest Records?

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

Latest Unique Records, How To Get?

Dec 3, 2007

Let's say I have a data entry from a pool of employees:
table is as follow:
EmpNo Branch Date Amount
1 A101 11/30/2007 $0.90
1 A101 11/30/2007 $1.20
2 A101 11/30/2007 $0.90
3 A101 11/30/2007 $0.80

How can I select the whole table and only take in 1 unique latest entry if there are multiple entries for the same day, same branch under same employee number?

Thanks! :D

View 7 Replies View Related

Get The Latest Changed Records

Feb 13, 2008

Hi,

I hava a table with the following information

CREATE TABLE TEMP1 (REFID INT, REVISION INT, FIELDNAM VARCHAR(10), VALUE VARCHAR(10));
INSERT INTO TEMP1 VALUES(1001, 0, 'A', 'A2');
INSERT INTO TEMP1 VALUES(1001, 0, 'C', 'C2');
INSERT INTO TEMP1 VALUES(1001, 0, 'E', 'E2');
INSERT INTO TEMP1 VALUES(1002, 0, 'A', 'A3');
INSERT INTO TEMP1 VALUES(1002, 0, 'B', 'B2');
INSERT INTO TEMP1 VALUES(1002, 0, 'E', 'E3');
INSERT INTO TEMP1 VALUES(1001, 1, 'A', 'A4');
INSERT INTO TEMP1 VALUES(1001, 1, 'E', 'E4');

Here based on latest revision and refid I should get the fieldnam and value.
Expected output:
REFID FIELDNAM VALUE REVISION
1001 A A4 1
1001 E E4 1
1002 B B2 0
1001 C C2 0

View 7 Replies View Related

Latest Version Of MSDE

Jan 12, 2004

What is the latest version of MSDE (and SQL Server)
that I can install for a client that uses Windows XP ?

SQL Server 2000 SP3 ?

View 14 Replies View Related

T-SQL (SS2K8) :: Latest Comment Per ID

Jul 30, 2015

I want to get the latest comment for each id based on the maximum createdDate and not sure how to do this, tried to do Max but it was still returning all the rows.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Audit](
[id] [nvarchar](64) NOT NULL,

[Code] ....

View 2 Replies View Related

Get Latest Entry Per Userid

Apr 8, 2008

Hi,

I have the following situation:
I have a table that is log of transactions of useractions. Each time a userstatus changes, a new entry is made into the table.

The table has (currently) about 4 million records, and keeps growing each day with thousands of records. It logs the status of about 150 users (currently active).

What I now want is to get the log-entry from all the distinct users that have a log-entry on the latest day (so current day, 'where TimeStamp > 2008-04-08').

(extremely simplified) Example:
(date is y/m/d)

USERID TIMESTAMP STATUS
----------------------------------------------------
... (thousands and millions of records above)
Ed 2008-04-07 18:00:00 Logged_Out
Jim 2008-04-07 18:30:00 Blabla_Status_5
Jack 2008-04-07 19:00:00 Logged_Out
Jim 2008-04-07 19:30:00 Logged_Out
Jim 2008-04-08 06:00:00 Logged_In (<< notice new day)
Jim 2008-04-08 06:01:00 Blabla-Status_1
Bob 2008-04-08 06:03:00 Logged_In
Fred 2008-04-08 06:05:00 Logged_In
Jim 2008-04-08 06:08:00 Blabla-Status_2
Jack 2008-04-08 06:12:00 Logged_In
Fred 2008-04-08 06:20:00 Blabla_Status_5
Jack 2008-04-08 06:12:00 Logged_Out
Fred 2008-04-08 06:12:00 Blabla_Status_4
Jack 2008-04-08 06:12:00 Logged_In
Jack 2008-04-08 06:12:00 Blabla_Status_7
----------------------------------------------------

The result should be:

USERID TIMESTAMP STATUS
----------------------------------------------------
Jack 2008-04-08 06:12:00 Blabla_Status_7
Fred 2008-04-08 06:12:00 Blabla_Status_4
Jim 2008-04-08 06:08:00 Blabla-Status_2
Bob 2008-04-08 06:03:00 Logged_In
----------------------------------------------------

(How) can this be done in one stored procedure?

View 7 Replies View Related

Using Subqueries To Get Latest Value Per Type?

May 7, 2008

Hopefully someone can suggest a better solution than what I'm currently hobbling along with.

Basically, I've got a table that gets rows inserted (with a timestamp) whenever there is a change to one of the values of a particular "item". So, what I want is to return a dataset of the latest values of each particular item.

I'm guessing that what I'm trying to acheive is doable in some elegant and performant fashion. Something maybe involving a ROLLUP or WITH CUBE or some FANCY item.

But for the time being, I've got a less-elegant query that returns the correct data, just using subqueries.

Here's the T-SQL to run my scenario:

DECLARE @actionHistoryTable TABLE (
itemID int
,actionType int
,actionValue nvarchar(50) NULL
,actionTime datetime )
INSERT @actionHistoryTable VALUES( 1000, 1, 'fork', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 2, '27', '1/2/2008')
INSERT @actionHistoryTable VALUES( 1000, 3, '200', '1/12/2008')
INSERT @actionHistoryTable VALUES( 1000, 2, '1', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 3, '204', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 1, 'ball', '1/3/2008')
INSERT @actionHistoryTable VALUES( 1026, 2, '20', '1/10/2008')
INSERT @actionHistoryTable VALUES( 1026, 2, NULL, '1/5/2008')
INSERT @actionHistoryTable VALUES( 1026, 1, 'hotdog', '1/6/2008')
INSERT @actionHistoryTable VALUES( 1026, 3, '2511', '1/8/2008')
INSERT @actionHistoryTable VALUES( 1026, 3, '375', '1/7/2008')
INSERT @actionHistoryTable VALUES( 1026, 1, 'mustard', '1/5/2008')
INSERT @actionHistoryTable VALUES( 1013, 1, 'rock', '1/2/2008')
INSERT @actionHistoryTable VALUES( 1013, 1, 'paper', '1/21/2008')
INSERT @actionHistoryTable VALUES( 1013, 3, '10', '1/20/2008')

-- JUST DISPLAY THE TABLE FOR EXAMPLE
SELECT * FROM @actionHistoryTable

-- THIS ROLLS-UP THE LATEST VALUE FOR EACH ACTION_TYPE FOR EACH ITEMID
SELECT aht.itemID
,( SELECT TOP 1 aht2.actionValue
FROM @actionHistoryTable aht2
WHERE aht.itemID = aht2.itemID
AND aht2.actionType = '1'
ORDER BY aht2.actionTime DESC ) as 'latest type 1 value'
,( SELECT TOP 1 aht2.actionValue
FROM @actionHistoryTable aht2
WHERE aht.itemID = aht2.itemID
AND aht2.actionType = '2'
ORDER BY aht2.actionTime DESC ) as 'latest type 2 value'
,( SELECT TOP 1 aht2.actionValue
FROM @actionHistoryTable aht2
WHERE aht.itemID = aht2.itemID
AND aht2.actionType = '3'
ORDER BY aht2.actionTime DESC ) as 'latest type 3 value'
FROM @actionHistoryTable aht
GROUP BY aht.itemID

Is there a better way?
-Steve

View 8 Replies View Related

Selecting Latest Date

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