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

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

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

Group By And Latest Record

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

How To Get Latest Date Record

Apr 10, 2014

I want to get latest updated date on each transid and only for status =approved .

-------------------
out put would be
------------
idtransid date status
31013/1/2014 approved
61031/2/2014 approved

table
-----------
idtransid date status
11011/1/2014 approved
21012/1/2014 close
31013/1/2014 approved
41021/2/2014 approved
51022/2/2014 close
61031/2/2014 approved

View 1 Replies View Related

Conditions On Latest Record

May 18, 2006

I have a table that has records layed out as so:Table:fd_Id INT IDENTITY (1, 1)fd_User VARCHAR(30)fd_Effective DATETIMEData could be as follows:1 | "user1" | 6/20/20012 | "user2" | 6/1/20023 | "user2" | 6/5/20024 | "user2" | 6/5/20025 | "user2" | 2/1/20026 | "user3" | 9/1/20037 | "user3" | 10/2/20028 | "user4" | 1/1/2005What I need to retrieve from that table is the SINGLE LATEST item ofeach fd_User.Results:1 | "user1" | 6/20/20013 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates arethe same but only 1 of them6 | "user3" | 9/1/20038 | "user4" | 1/1/2005

View 3 Replies View Related

Get Record With Latest DateTime

Oct 1, 2007

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

Latest Record For Each Reference Field

Feb 19, 2014

I have a table that contains a Ref field and a TransactionDate field. For each Ref field there are mutliple Transactions. I am trying to put together a query that selects the Latest Transaction date for each Ref field in the table.

I tried:

SELECT *
FROM tabMediaTransaction
WHERE (TransactionDate =
(SELECT MAX(TransactionDate) AS Expr1
FROM tabMediaTransaction AS tabMediaTransaction_1))

But this only returns 1 record for 1 Ref field. I need 1 record for each Ref field

View 7 Replies View Related

Unable To Get Latest Date Record?

Apr 10, 2014

i want to get latest updated date on each transid and only for status =approved .

-------------------
out put would be
------------
idtransid date status
31013/1/2014 approved
61031/2/2014 approved
table
-----------

idtransid date status
11011/1/2014 approved
21012/1/2014 close
31013/1/2014 approved
41021/2/2014 approved
51022/2/2014 close
61031/2/2014 approved

View 2 Replies View Related

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

Transact SQL :: Retrieve Latest Record Of Serial Number With Multiple Entries

Sep 30, 2015

I work for an organization that repairs serialized devices. Each time a device is repaired it's serial number is recorded in a database table along with the date it was repaired along with other information about the device. There are multiple cases where a unit has been repaired more than once.

I am trying to write a query that will return the serial only once and that record will be the record of the latest repair date. To sum it up,

Return a list of serials where if a serial exists more than once in the table, return only the instance of the serial record(s) with the max(created_dt). The end result will be a list of distinct serial numbers.

Here is my Query. The problem I believe is in my sub-query but I am not sure how to structure it.

SELECT
S.Id
, RMA
, PinSerial
, L4Serial
, L4Model

[Code] ....

View 3 Replies View Related

TOUGH INSERT: Copy Sale Record/Line Items For Duplicate Record

Jul 20, 2005

I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria

View 6 Replies View Related

How To Create An Copy Of A Certain Record Except One Specific Column That Must Be Different && Insert The New Record In The Table

Sep 1, 2006

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

View 6 Replies View Related

Ways To Make This Work: Several Selectable Related Record For One Main Record.

Apr 6, 2007

Hey all!

Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.

To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.

On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.

I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.

Thanks in advance,

Johan

View 5 Replies View Related

Query Timeouts When Updating A Record Retrieved Through A Websphere JDBC Datasource - Possible Record Locking Problem

Apr 7, 2008

Hi,

We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.

If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.

Thanks,
Sarah

The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.

This is running on a Websphere Application Server v6.1.

Code snippet - getting the record thru JDBC:

DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();

// wsCon.setAutoCommit(false); //have tried with and without this flag - same results

Statements stmt = wsCon.createStatement();

String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);

if(rs.next()){
System.out.println(rs.getString("lastName"));
}

if (rs != null){
rs.close();
}
if (stmt != null) {

stmt.close();
}
if (wsCon != null) {

wsCon.close();
}

View 1 Replies View Related

SSIS: Multi-Record File Extract With 9 Record Types

Feb 26, 2008

I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx

I have been able to create a sample file with two of my record types.

I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.

does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?

Thanks in advance for any help you might be able to provide.

View 3 Replies View Related

Add Date To Record In SQL Server Each Time Record Is Added

Mar 1, 2006

Hi

Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?
thanks

View 6 Replies View Related

Restrict Inserting Record If Record Already Exist In Table

Apr 17, 2014

Is that possible to restrict inserting the record if record already exist in the table.

Scenario: query should be

We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.

View 2 Replies View Related

Delete Record Based On Existence Of Another Record In Same Table?

Jul 20, 2005

Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray

View 3 Replies View Related

Switch Record Background Color With Each Record In Report

Jan 14, 2008

Hi Everyone-

i have a matrix report
and i want to switch the record background color with each record in the value column in that matrix report
e.g 1st record background color is gray and next record background color is white
and then the next record background color is gray ... and so on

can anyone help?

thanx
Maylo

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

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