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

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

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

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

How To Reference A Previous Record

Mar 31, 2015

I have a query that shows me a list of what employees that are on site assuming that employee badged in correctly. My problem is I need to know when an employee has two entries in a row that are "In" without have an "Out" entry.

For example, if John badges in at 8:00 Am and leaves without badging out, when he arrives the next day at 8:00 AM and badges in the system simply would show him as in with no record of him ever leaving correctly.

I am not sure how to return the correct result. Can I formulate a query that would display a Who's In list where the previous entry was NOT and Out?

View 16 Replies View Related

Question About Getting The Latest Identity Field In A Specific Table

Jul 20, 2005

Dear All,Suppose in the program a record is added to a table whoseprimary key is a identity field. If I really want to get the lastestvalue for that field after the insertion, is it the best way to useIDENT_CURRENT() to obtain this value?Thanks for your kind attentionYours faithfully,Benny

View 2 Replies View Related

T-SQL (SS2K8) :: Return Value In A Status Field Which Has Latest Year And Month

May 11, 2015

I have table in which month & year are stored, Like this

Month Year
10 2014
11 2014
12 2014
1 2015
2 2015
3 2015
4 2015

I wanted a query in which it should return the value in a status field which has latest year & month.

View 9 Replies View Related

How To Reference The Primary Key Of A Newly Added Record In Trigger?

Jun 24, 2004

Please help me somebody solve my problem with my first :o trigger: ALTER TRIGGER partner_update ON dbo.partner FOR UPDATE AS INSERT INTO partner (name) SELECT name FROM deleted UPDATE invoice SET id_partner= *** WHERE id_partner = (SELECT id_partner FROM deleted) *** - here I want to add a "reference" to the newly added record's automatically generated primary key (not to the updated!) Is it possible?

View 1 Replies View Related

How To Get Reference From CompositePrimaryKey Field ???...

Mar 8, 2007

I have one table name Magazine_Details, it has a composite primary keyCreate table Magazine_Details
(
MagazineTitle varchar(60),
MagazineType varchar(20),
IssueType varchar(25),
IssueNumber varchar(10),
Qnty int,
Qnty_in_Hand int,
[Date] datetime
constraint PK_Magazine_Details_Composite primary key(MagazineTitle,IssueNumber)
)
Another table is Magazine_Issued and i want reference from Magazine_Details table. but i am auable to create foreign key constaraint in Magazine_Issued table.create table Magazine_Issued
(
Member_ID varchar(100),
MagazineTitle varchar(60) references Magazine_Details(MagazineTitle),
IssueNumber varchar(10) references Magazine_Details(IssueNumber),
IssueDate datetime,
Magazine_Status varchar(10)
)

View 3 Replies View Related

Can You Reference The Data In Another Field?

Dec 5, 2007

Is it possible to reference the data in another field within the same table?

Let's say that one column has a calculation, and a second column has a different calculation. Can the third column find the difference between the first two columns?

(I'm wondering if this is possible to do within the Layout View, not from the Data View)

Thank you!

View 3 Replies View Related

How To Reference A Previous Field Alias In TSQL As In Jet SQL?

Jun 1, 2006

My question is simple, I'd like to do something I do in Jet ANSI-89 SQL. Mind you I'm just adding numbers here - they are not actual columns in 'SomeTable'

SELECT 1 AS A, 2 AS B, A+B AS C
FROM SomeTable

The Jet engine evaluates and does arithmetic on the Aliased column names - handy when they contain their own functions. The resultset would show:

A B C
1 2 3

However from what I can tell SQL Server 2005 is not picking this up. Is their an equivalent?

View 1 Replies View Related

Transact SQL :: Server Reference Field From Adjacent CTE

Oct 22, 2015

I am trying to eliminate a scalar function by rewriting a view. I currently have a structure similar to the following:

With FirstQuery as (SELECT Trial, SBOI, TBOI,
FROM TU
WHERE SBOI Between 22000 and 22999 and TBOI < 10000),
strt as (SELECT TOP 1 TimeOfEvent, Trial, SBOI, TBOI
From TU ORDER BY TimeOfEvent),

[Code] ....

What I trying to do is for the Trial, SBOI, and TBOI in FirstQuery, find the Start Time and Stop Time. TimeOfEvent is an integer in milliseconds. Say I have the following data:

TrialID     SBOI     TBOI     TimeOfEvent
A             22000   5000     5
A             22000   5000     10
B             22000   5000     8
B             22000   5000     15

So the DISTINCT output would be:

     Trial     SBOI     TBOI     StartTime     StopTime
     A         22000   5000     5                  10
     B         22000   5000     8                  15

The problem I am having is that strt is selecting the smallest time period. It doesn't seem to care what the current record in cte is. I thought that by using my joins, it would make it select smallest TimeOfEvent for the Trial, SBOI, and TBOI that are selected in cte. Obviously that is not the case. So, I was trying to add a WHERE to the strt Select Statement such as

WHERE TrialID = FirstQuery.TrialID and SBOI = FirstQuery.SBOI and TBOI = FirstQuery.TBOI but it isn't working either.

What can I do?

View 5 Replies View Related

Loop Through Each Record And Then Each Field Within Each Record

Dec 15, 2005

I need to essentially do 2 loops. One loops through each record and then inside each record row, I want to perform an insert on each column.

Something like this maybe using a cursor or something else:

For each record in my table (I'll just use the cursor)
For each column in current record for cursor
perform some sql based on the current column value
Next
Next

So below, all I need to do is figure out how to loop through each column for the current record in the cursor

AS

DECLARE Create_Final_Table CURSOR FOR

SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1

OPEN Create_Final_Table

FETCH FROM Create_Final_EBN_Table INTO @FieldName, @AcctNumber, @Screen, @CaseNumber, @BKYChapter, @FileDate, @DispositionCode, @BKUDA1, @RMSADD2, @RMSCHPNAME_1, @RMSADDR_1,
@RMSCITY_1, @RMSSTATECD_1, @RMSZIPCODE_1, @RMSWORKKPHN, @BKYMEETDTE, @RMSCMPNAME_2, @RMSADDR1_2, @RMSCITY_2, @RMSSTATECD_2,
@RMSZIPCODE_2, @RMSHOMEPHN, @BARDATE, @RMSCMPNAME_3, @RMSADD1_2, @RMSADD2_3, @RMSCITY_3, @RMSZIPCODE_3, @RMSWORKPHN_2

WHILE @@FETCH_STATUS = 0
BEGIN

@Chapter = chapter for this record

For each column in current record <---- not sure how to code this part is what I'm referring to

do some stuff here using sql for the column I'm on for this row

Next

Case @Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record

Case 13

Insert Record
Insert Record
Insert Record

Case 11

Insert Record
Insert Record
Insert Record

Case 12

Insert Record
Insert Record
Insert Record

END

close Create_Final_Table
deallocate Create_Final_Table

View 2 Replies View Related

Transact SQL :: Find Latest Start Date After A Gap In Date Field For Each ID

Apr 23, 2015

My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario

ID StartDate
1 2014-01-01
1 2014-02-01
1 2014-05-01-------After Gap Restarted
1 2014-06-01
1 2014-09-01---------After last gap restarted
1 2014-10-01
1 2014-11-01
2 2014-01-01
2 2014-02-01
2 2014-03-01
2 2014-04-01
2 2014-05-01
2 2014-06-01
2 2014-07-01

For Id 1 the start date after the latest gap is 2014-10-01 and for id=2 there is no gap so i need the minimum date  2014-01-01

My Expected Output
id Startdate
1   2014-10-01
2   2014-01-01

View 4 Replies View Related

Can I Use The Value Of Field In Last Record?

Apr 3, 2007

I handle a output by a function with three parameters

the first is a value of Field [a1] in current record,

the second is value of Field [a1] in last record,

the third is value of Field [a1] in next record,

is it possible in RS2005?

View 1 Replies View Related

Running SP Or UDF On A Field In Every Record Of A Set, In SQL

Aug 26, 2005

Is there a way to run a function or sproc on a specific field in every row of a returned dataset, without using a cursor? i.e.

Code:

Select encryptedSSN, (exec udf_decrypt(encryptedSSN, decryptedSSN as OUTPUT) as ssn
From...

or something like?

Here is my scenario: We are encrypting (in storage) ssns. I need to generate a report that lists results by (decrypted) ssn. The dataset needs to have the decrypted SSN in it (I can't decrypt each record line by line, as you would in an application.)

Any possible solutions appreciated -

View 1 Replies View Related

How To Get Last Record In A Field In Reporting..

Dec 3, 2007

I am converting a crystal report to a SQL 2000 reporting services..

Now in on field there is a formula. :

Code Block
If OnLastRecord and {al_work;1.Pot} = 0

also another line

Code Blockif next({al_GetSchedDoneWork;1.Pot}) <> 0

Now i cant find its equivalent in SSRS .. In Crystal Reports OnLastRecord means "last record of the field" , BOOLEAN TRUE and next means "next field"

How do i imitate this in SSRS 2000

THIS IS NOT IN A DATASET.. its in a Field formula. al_work is a SP from us, pot is a field in our DB

View 1 Replies View Related

Identifying Bad Record And Field Value

Nov 14, 2006

To All:

I'm importing a data file of 600,000 records. Some records contain bad data.

What's the best way to identify the text file row and field that caused my package to bomb AT THE TIME IT bombs?

Thanks,

r

View 1 Replies View Related

SQL 2012 :: DTSX Giving Errors - Object Reference Not Set To Instance Reference

Sep 10, 2014

I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:

Error system.NullReferenceException: Object refrence not set to an instance reference.

In PreExecute section
TextReader = new system.io.streamreader(" file name")
In the CreateNewOutputRows:
dim nextLine as string
nextLine = textReader.ReadLine

[code]...

is there something which i did not install or what can be the error?

View 0 Replies View Related

Find Record Where 1 Field Value Has Changed

Apr 2, 2015

I need to find all items in an inventory table where a field has been unticked in the last 24 hours (there is no audit trail), as well as the contract number of the contract it has been added to (it will not have existed in that table before).

These are the two table querys in their basic form:

select item (nvarchar(20)), inactive (bit) from inventory

select item (nvarchar(20) , contract (nvarchar(20)) , original_start_rent (datetime) from deltickitem

I would like to see just the item number and the contract number it has been added to.

View 11 Replies View Related

Smalldatetime Field Goes To Null When I Update Record

Dec 17, 2007

Hello - I have a column in a table (SQL 2005 EE) with a Data Type of smalldatetime and a Default Value of getdate(). When I insert a record from my webpage the new record contains the correct date via getdate(). However if I update the record from my webpage the date of the record then becomes NULL. Is this normal? Is there anything I can do about this from sql server? I am inserting/updating via an formview and ODS, using standard insert/update methods.
Cheers
Marco

View 4 Replies View Related

Updating Field Based On Record Count

Oct 18, 2004

I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.

Update CurrRank = ??? from tblAppKitOffers
where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D')
ORDER BY tblAppKitOffers.OfferAmount Desc

All help is greatly appreciated.

View 2 Replies View Related

Comma Seperated Field To Arrays For Each Record?

May 12, 2008

Hello,

My SQL knowledge is limited so if I get stuff wrong then correct me... but I can imagine this task will be quite testing...

I am working on a system that logs ([Audit] table) the changes to fields on some tables using a Trigger on UPDATE. I need to produce a 'quick' report that returns the date when the tables overallStatus field was set to 1.

In the [Audit] table I can find all the field changes for the record in question using this SQL...

select *
from audit
where rowid = 1309606
order by auditID asc

My problem is filtering this data. The fields I need are formatted as below, see records returned, in ASC order...

audit.AuditID = 2652583
audit.OperationTime = 2008-04-24 15:12:07.740
audit.ColumnDetail = 'estimatedProductionPriceactualProductionCost'
audit.EditDetail = '0.00000.0000'

audit.AuditID = 2658460
audit.OperationTime = 2008-04-25 10:51:47.930
audit.ColumnDetail = 'overallStatusInsertionStatus'
audit.EditDetail = '05'

audit.AuditID = 2665723
audit.OperationTime = 2008-04-25 22:06:50.200
audit.ColumnDetail = 'overallStatusdespatchDateInsertionStatus'
audit.EditDetail = '1� 3'

audit.AuditID = 2711092
audit.OperationTime = 2008-04-30 17:22:12.593
audit.ColumnDetail = 'overallStatusInsertionStatus'
audit.EditDetail = '34'

audit.AuditID = 2713217
audit.OperationTime = 2008-04-30 20:46:34.817
audit.ColumnDetail = 'clientOrderNumber'
audit.EditDetail = 'PAT12P7640'

The funny character, , is ASCII 127.

So need to find when 1309606's overallStatus was changed to 1. Manually looking at the data, I can see overallStatus was modified in 3 of the above 5 [Audit] records. It started life as 0, then went to 1 and then to 3. I'm aware that I need to look at the previous Audits date for when it was changed to the value I'm looking for... So it was changed to 1 on 2008-04-25 10:51:47.930.

What is the best way to approach this problem? I'm hoping to use T-SQL only and not have to use an external scripting language, unless I can embed vbscript or jscript inside a T-SQL function and then use arrays, etc?

Somehow I need to convert the ASCII(127) seperated list of fieldname into an array or list, find the index of the fieldname 'overallStatus' and then lookup that value in the datafield.

Hope that makes sense and any help would be great!

Cheers,
Nick

View 6 Replies View Related

How To Automatically Populate A Field With A Value From A Previous Record?

Mar 16, 2008

Was wondering if there is easy anyway to autopopulate a field by pulling a value from a field in the previous record?

For example, I have a table with fields name "Dist_From" and "Dist_To". When I add a new record I would like it to populate the "Dist_From" field with the "Dist_to" value from the previous record.

TIA

Todd

View 3 Replies View Related

Selecting A Top Record Based On A Datestamp Field

Jan 30, 2008

This is a simple one, and I know that it has to be fairly common, but I just can't figure out an elegant way to do it. I have a table with the following fields:
OrderID (FK, not unique)
InstallationDate (Datetime)
CreateDtTm (Datetime)

There is no PK or Unique ID on this table, though an combo of OrderID and CreateDtTm would ostensibly be a unique identifier.

For each OrderID, I need to pull the InstallationDate that was created most recently (based on CreateDtTm). Here's what I've got so far, and it works, but man is it ugly:

SELECT a.OrderID, InstallationDate

FROM ScheduleDateLog a

INNER JOIN

(SELECT OrderID, max(convert(varchar(10),CreateDtTm,102)+'||' +convert(varchar(10), InstallationDate,102)) as TopRecord

FROM ScheduleDateLog GROUP BY OrderID) as b

ON convert(varchar(10),CreateDtTm,102)+'||' +convert(varchar(10), InstallationDate,102)=b.TopRecord

AND a.OrderID = b.OrderID

View 8 Replies View Related