• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

VB.NET How to select the earliest and latest record of a set of rows based on timestamp and ID Sql server

I have many rows being returned which contain a autonumber, qty, timestamp and foreignKeyID fields...what I need is to get the totalqty of all the rows, and the time difference between the latest timestamp and the earliest timestamp all based on the same foreign key ID

Based on these variables I need to see the totalqty/time difference ...which will give the # of units counted per minute.

I am using VB.Net and Sql Server 2005... I am just a bit fuzzy on the actual grabbing of the earliest & latest timestamp... I could use the AutoNumber field ( Max / Min)  of the table based on the foreign key...but is there a better way...

Any help or insight would be much appreciated...
1 Solution
From SQL couldn't you just do:

SELECT COUNT(*) AS Qty, MAX(timestamp) as MaxTime, Min(TimeStamp) as MinTime, ForeignKey
FROM table
GROUP BY ForeignKey

nomar2Author Commented:

I changed the code a little but this is what I came up with based on your suggestions...

SELECT     SUM(iquantity) / DATEDIFF(minute, MAX(editeddate), MIN(editedDate)) AS OverCount
FROM         tInvDetail
WHERE     (iInvoiceID = 33202)

This returns the # of items counted per minute
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now