Getting The Latest Row From A Batch
Jul 20, 2005
Hi All
This is a belter that my little brain can't handle.
Basically I have 1 SQL table that contains the following fields:
Stock Code
Stock Desc
Reference
Transaction Date
Qty
Cost Price
Basically this table stores all the transaction lines of when a user
books stock items into stock so that they can look at a journal of
this goods in as and when they please.
My task is that the user wants a list of all the stock items with the
last cost price that they were booked in at.
So I think I have to find the last transaction date used for each
stock code and then bring this back as 1 row per stock code with the
above fields of data.
How the whats-its can I do this? Is it acutally possible?
Any help you can give is much appreciated.
Rgds
Laphan
View 3 Replies
ADVERTISEMENT
Aug 7, 2007
HELP,
I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.
Please help
Phil
View 4 Replies
View Related
Dec 5, 2006
I am using the following batch file to execute a script that creates a db and all its objects in the local sql express:
sqlcmd -S (local)SQLExpress -i C:CreateDB.sql
This works fine, but I'm wondering if there's an easy way to put the script in the batch file, so users don't have to worry about putting the script in the C drive. I tried getting rid of the i parameter and pasting the script from the sql file into the batch file, but it didn't work.
Thanks,
Dave
View 1 Replies
View Related
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
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
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
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
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
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
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
Feb 6, 2008
Hi,
this will be my first post to sqlteam.com.
I've been trying to work out this problem for awhile, and figured I really need help. I have a set of data that shows the results of a number of tests that I have been doing on a software. Each test I have done records the test_ID, test_Result, and the date_Tested. An example of the columns would be like this:
test_ID | test_Result | date_Tested
3112 | PASS | 2007-11-23 09:29:40.230
3112 | FAIL | 2007-02-22 09:22:00.230
3112 | FAIL | 2007-01-21 09:21:40.234
3113 | PASS | 2007-11-23 09:29:40.230
3114 | PASS | 2007-11-23 09:29:40.230
3115 | FAIL | 2008-01-23 09:29:40.230
3115 | PASS | 2007-11-23 09:29:40.230
3116 | FAIL | 2007-12-25 09:29:40.230
3116 | PASS | 2007-11-23 09:29:40.230
Now what I want is ONLY the latest result for each test_ID, I do not want repeated test_IDs to show up. So my expected result should be:
test_ID | test_Result | date_Tested
3112 | PASS | 2007-11-23 09:29:40.230
3113 | PASS | 2007-11-23 09:29:40.230
3114 | PASS | 2007-11-23 09:29:40.230
3115 | FAIL | 2008-01-23 09:29:40.230
3116 | FAIL | 2007-12-25 09:29:40.230
Is there a way I can query this? I've tried using "distinct test_ID" but it would still show the repeated test_ID due to the date_Tested not being the same.. also Group By will not work. I'm not sure what to do here. Any ideas anyone?
Thanks in advance.
View 4 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
May 9, 2008
does anyone know how to check the latest updates to a table? like a new record added or updated or deleted. thanks in advance.
View 3 Replies
View Related
Aug 8, 2013
Here's what I'm trying to do
TransIDMemberIDFundIDBuyPriceDate
1501101$1042013-01-24
2501102$1012013-04-23
3501102$1182013-02-04
4501102$982013-05-19
5501103$532013-05-21
6501103$392013-07-09
I'm trying to get the latest buy price for each fund a member bought.
So in the above example, I'm trying to formulate a query to retrieve all the latest bought price for memberid 105:
Desire Query Result
TransIDMemberIDFundIDBuyPriceDate
1501101$1042013-01-24
4501102$982013-05-19
6501103$392013-07-09
So far, I've tried using Group By clause on FundID and BuyPrice and MAX() function for the date, but I can' get the TransID.
View 3 Replies
View Related
