Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Returning first and last record of each day

Author  Topic 

knox203
Starting Member

31 Posts
Posted - 2008-09-25 : 11:18:26
Hello all! Can anyone point me in the right direction query wise as to get the first, and last record on a per day basis? I have a database filled with hundreds of usernames, along with thousands of entries per username. I would like my query to return the first, and last record for each user, per day within a given time range. Thanks for your help!

- Adam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-25 : 11:21:25
[code]SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY Username ORDER BY datefield) AS FSeq,ROW_NUMBER() OVER(PARTITION BY Username ORDER BY datefield DESC) AS BSeq,*
FROM YourTable
WHERE datefield BETWEEN @RangeStart AND @RangeEnd
)t
WHERE t.FSeq=1
OR t.BSeq=1[/code]
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-09-25 : 11:25:07
What is first and last?
By ID, or by some date?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-09-25 : 11:26:56
[code]SELECT s.*
FROM Table1 AS s
INNER JOIN (
SELECT UserID,
MIN(colDate) AS minDate,
MAX(colDate) AS maxDate
FROM Table1
WHERE colDate >= '20080101'
AND colDate < '20090101'
GROUP BY UserID,
DATEDIFF(DAY, '19000101', colDate)
) AS w ON w.UserID = s.UserID
WHERE s.colDate IN (w.minDate, w.maxDate)[/code]
E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

knox203
Starting Member

31 Posts
Posted - 2008-09-25 : 11:30:08
Sorry I didn't clarify, I have a timestamp column that I would like it to read. Does this help?

* Checking submitted queries, thanks guys! *
Go to Top of Page

knox203
Starting Member

31 Posts
Posted - 2008-09-25 : 11:31:32
Maybe this will help as well, here's my base code along with the necessary JOINS:

SELECT    *
FROM         Driver INNER JOIN
                      Job ON Driver.DriverID = Job.DriverID INNER JOIN
                      DispatchGroup ON Driver.DispatchGroupID = DispatchGroup.DispatchGroupID INNER JOIN
                      JobStop ON Job.JobID = JobStop.JobID
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-09-25 : 11:34:04
You want ALL columns? Really?

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-09-25 : 11:34:56
In which table is the DateTime column?
JobStop?

See http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

knox203
Starting Member

31 Posts
Posted - 2008-09-25 : 11:35:59
quote:
Originally posted by Peso

You want ALL columns? Really?

E 12°55'05.63"
N 56°04'39.26"


Sorry, no, not at all... I just need "Driver.DriverCode" along with the first, and last record DateTime for each day.
Go to Top of Page

knox203
Starting Member

31 Posts
Posted - 2008-09-25 : 11:37:35
quote:
Originally posted by Peso

In which table is the DateTime column?
JobStop?

See http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx


E 12°55'05.63"
N 56°04'39.26"


The necessary DateTime is in the "JobStop" as "JobStop.ArriveDateTime"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-25 : 11:37:48
quote:
Originally posted by knox203

Sorry I didn't clarify, I have a timestamp column that I would like it to read. Does this help?

* Checking submitted queries, thanks guys! *

Use timestamp column in place where i specified datefield to get first and last for particular day value in timestamp.
Go to Top of Page

knox203
Starting Member

31 Posts
Posted - 2008-09-25 : 11:58:19
quote:
Originally posted by visakh16

quote:
Originally posted by knox203

Sorry I didn't clarify, I have a timestamp column that I would like it to read. Does this help?

* Checking submitted queries, thanks guys! *

Use timestamp column in place where i specified datefield to get first and last for particular day value in timestamp.


Here's what I currently have:
SELECT     *
FROM         (SELECT     ROW_NUMBER() OVER (PARTITION BY Driver.DriverCode
                       ORDER BY JobStop.ArriveDateTime) AS FSeq, ROW_NUMBER() OVER (PARTITION BY Driver.DriverCode
ORDER BY JobStop.ArriveDateTime DESC) AS BSeq, Driver.DriverCode, JobStop.ArriveDateTime
FROM         Driver INNER JOIN
                      Job ON Driver.DriverID = Job.DriverID INNER JOIN
                      DispatchGroup ON Driver.DispatchGroupID = DispatchGroup.DispatchGroupID INNER JOIN
                      JobStop ON Job.JobID = JobStop.JobID
WHERE     JobStop.ArriveDateTime BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AND DATEADD(DAY, - 1, DATEADD(MONTH, 
                      DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0))) t
WHERE     t .FSeq = 1 OR
                      t .BSeq = 1
ORDER BY t .DriverCode, t .ArriveDateTime

Were getting closer, but I'm not sure that this query is doing exactly what I want it to do... as I'm now noticing, not all drivers have a date in "JobStop.ArriveDateTime" sometimes it's "NULL", if it is "NULL" the date lies in the "JobStop.PodDateTime" column. Could this be messing with the query?

Currently, the results look like this:

FSeq: "1"  --  BSeq: "232"  --  DriverCode: "994"  --  ArriveDateTime: "9/2/2008 7:47:00 AM"
FSeq: "232"  --  BSeq: "1"  --  DriverCode: "994"  --  ArriveDateTime: "9/25/2008 8:34:00 AM"

So on and so forth...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-25 : 12:30:50
may be this

SELECT     *
FROM         (SELECT     ROW_NUMBER() OVER (PARTITION BY Driver.DriverCode,DATEADD(dd,DATEDIFF(dd,0,COALESCE(JobStop.ArriveDateTime,JobStop.PodDateTime)),0)
                       ORDER BY COALESCE(JobStop.ArriveDateTime,JobStop.PodDateTime)) AS FSeq, ROW_NUMBER() OVER (PARTITION BY Driver.DriverCode,DATEADD(dd,DATEDIFF(dd,0,COALESCE(JobStop.ArriveDateTime,JobStop.PodDateTime)),0)
ORDER BY COALESCE(JobStop.ArriveDateTime,JobStop.PodDateTime) DESC) AS BSeq, Driver.DriverCode, JobStop.ArriveDateTime,JobStop.PodDateTime
FROM         Driver INNER JOIN
                      Job ON Driver.DriverID = Job.DriverID INNER JOIN
                      DispatchGroup ON Driver.DispatchGroupID = DispatchGroup.DispatchGroupID INNER JOIN
                      JobStop ON Job.JobID = JobStop.JobID
WHERE     JobStop.ArriveDateTime BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AND DATEADD(DAY, - 1, DATEADD(MONTH, 
                      DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0))) t
WHERE     t .FSeq = 1 OR
                      t .BSeq = 1
ORDER BY t .DriverCode, t .ArriveDateTime
Go to Top of Page

knox203
Starting Member

31 Posts
Posted - 2008-09-25 : 12:33:45
quote:
Originally posted by visakh16

may be this

SELECT     *
FROM         (SELECT     ROW_NUMBER() OVER (PARTITION BY Driver.DriverCode,DATEADD(dd,DATEDIFF(dd,0,JobStop.ArriveDateTime),0)
                       ORDER BY JobStop.ArriveDateTime) AS FSeq, ROW_NUMBER() OVER (PARTITION BY Driver.DriverCode,DATEADD(dd,DATEDIFF(dd,0,JobStop.ArriveDateTime),0)
ORDER BY JobStop.ArriveDateTime DESC) AS BSeq, Driver.DriverCode, JobStop.ArriveDateTime
FROM         Driver INNER JOIN
                      Job ON Driver.DriverID = Job.DriverID INNER JOIN
                      DispatchGroup ON Driver.DispatchGroupID = DispatchGroup.DispatchGroupID INNER JOIN
                      JobStop ON Job.JobID = JobStop.JobID
WHERE     JobStop.ArriveDateTime BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AND DATEADD(DAY, - 1, DATEADD(MONTH, 
                      DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0))) t
WHERE     t .FSeq = 1 OR
                      t .BSeq = 1
ORDER BY t .DriverCode, t .ArriveDateTime



That right there is a thing a beauty, works like a charm! Thanks so much visakh16, also thanks to everyone else for responding!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-25 : 13:16:46
quote:
Originally posted by knox203

quote:
Originally posted by visakh16

may be this

SELECT     *
FROM         (SELECT     ROW_NUMBER() OVER (PARTITION BY Driver.DriverCode,DATEADD(dd,DATEDIFF(dd,0,JobStop.ArriveDateTime),0)
                       ORDER BY JobStop.ArriveDateTime) AS FSeq, ROW_NUMBER() OVER (PARTITION BY Driver.DriverCode,DATEADD(dd,DATEDIFF(dd,0,JobStop.ArriveDateTime),0)
ORDER BY JobStop.ArriveDateTime DESC) AS BSeq, Driver.DriverCode, JobStop.ArriveDateTime
FROM         Driver INNER JOIN
                      Job ON Driver.DriverID = Job.DriverID INNER JOIN
                      DispatchGroup ON Driver.DispatchGroupID = DispatchGroup.DispatchGroupID INNER JOIN
                      JobStop ON Job.JobID = JobStop.JobID
WHERE     JobStop.ArriveDateTime BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AND DATEADD(DAY, - 1, DATEADD(MONTH, 
                      DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0))) t
WHERE     t .FSeq = 1 OR
                      t .BSeq = 1
ORDER BY t .DriverCode, t .ArriveDateTime



That right there is a thing a beauty, works like a charm! Thanks so much visakh16, also thanks to everyone else for responding!!

welcome Buddy
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site