Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • 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...
0
nomar2
Asked:
nomar2
1 Solution
 
lluddenCommented:
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

0
 
nomar2Author Commented:
Perfect....

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
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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