Solved

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

Posted on 2008-10-08
2
387 Views
Last Modified: 2012-05-05
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
Comment
Question by:nomar2
2 Comments
 
LVL 18

Accepted Solution

by:
lludden earned 48 total points
ID: 22674870
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
 

Author Comment

by:nomar2
ID: 22682090
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question