January 5, 2012 at 7:33 am
I am trying to pull data from a table which has scheduled runs of our trucks. I need to see a result set where all trucks are listed beyond today but only once and with the next day they are running.
For instance, truck 21, 22, 23, 25 will run again tomorrow 1/6, but truck 24 is not schedule to run again until 1/7. What I want to see is:
truck run date
21 1/6/2012
22 1/6/2012
23 1/6/2012
24 1/7/2012
25 1/6/2012
I need to say SELECT Truck, RunDate FROM tbl_TruckSchedule WHERE RunDate > GETDATE(),
but that will obviously show all runs beyond today.
How do I select the next run date for each truck when there is no consistency as to when the next run is. It could be tomorrow, in two days, in three days, etc.
January 5, 2012 at 7:39 am
Have a look at the result set from the following query - you should be able to quickly figure out what to do next:
DECLARE @Today DATE
SET @Today = DATEADD(dd,1,GETDATE())
SELECT @Today
SELECT
Truck,
RunDate,
rn = ROW_NUMBER() OVER(PARTITION BY Truck ORDER BY RunDate)
FROM tbl_TruckSchedule
WHERE RunDate >= @Today
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 5, 2012 at 7:49 am
Thank you very much!! That gave me what I wanted, although now it made me rethink this whole process and is giving me more work to do. lol Regardless, it worked perfectly, thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply