Conditional running total 2

[Krickles]

I have the following query:

SELECT      t.ticket_id, t.truck_id, co.ship_date
FROM        ticket t
INNER JOIN  customer_order co on co.customer_order_id = t.customer_order_id
INNER JOIN  truck tr on tr.truck_id = t.truck_id
ORDER BY    co.ship_date, t.truck_id

The result set is similar to:

ticket_id truck_id ship_date
100001 241 2004-05-01
100342 241 2004-05-01
100277 241 2004-05-01
100098 276 2004-05-01
100303 276 2004-05-01

I need to make a running total field that has the following logic:

[tt]DECLARE @runningtotal int
SET @runningtotal = 1

IF t.status_id <> 10 and
ship_date = ship_date while truck_id = truck_id
THEN @runningtotal = @runningtotal + 1[/tt]

Upon the change of the truck_id I need to start the count over and the count must also start over at the change of the ship_date.

...so the result set will yield:

ticket_id truck_id ship_date rt
100001 241 2004-05-01 1
100342 241 2004-05-01 2
100277 241 2004-05-01 3
100098 276 2004-05-01 1
100303 276 2004-05-01 2

Thanks in advance for any help.

Regards,

Krickles | 1.6180

 

[Krickles]

Forgot to add that I prefer doing this in-line with a much larger query as opposed to using a cursor. But, as the cliche goes...beggars can't be choosers.

Krickles | 1.6180

 

[NathanGriffiths]

I'm not sure if you can do this in one select statement, this is a way of doing it using a temporary table;

SELECT      
	t.ticket_id, 
	t.truck_id, 
	co.ship_date,
	0 AS rt
INTO 
	#tmp
FROM        
	ticket t
INNER JOIN  customer_order co ON co.customer_order_id = t.customer_order_id
INNER JOIN  truck tr ON tr.truck_id = t.truck_id
ORDER BY    
	co.ship_date, t.truck_id

DECLARE @lasttruckid int, @lastshipdate datetime, @count int

UPDATE
	#tmp
SET
@count = 
  CASE 
    WHEN truckid = @lasttruckid AND 
         ship_date = @lastshipdate 
    THEN @count + 1 
    ELSE 1
  END,
@lastshipdate = ship_date, 
@lasttruckid = truck_id, 
rt = @count

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[Krickles]

Thanks BlueStringPudding! Your code worked well. I'm going to see if I can't figure out a way to incorporate it into my larger query.

For clarification on my original post...I have several timestamps I’m dealing with:

Employee in
Truck in service

The following times happen with each load (considered one delivery cycle):

At plant
Ticketed
Loading
To job
On job
Pouring
Washing down
To plant

At the end of the shift there may be two additional times:

Truck out of service (depends on if another driver will have his/her shift in the truck)
Employee out (always)

Additionally, trucks and employees can have multiple in/out times per day. What I’m trying to achieve is a database consolidation with automated corrections being pumped into a datamart. Since I can have multiple times per driver and per truck, I need to be able to isolate the number of loads per truck per 24-hour day (even though we work through midnight I am able to run the clock from 00:00:00 to 23:59:59) which is what I am calling the running total.

What I plan to do with the running total of load numbers is use a min and max function to correct at plant times. It’s the only correction I haven’t been able to tackle.

FYI, I'm about to call it a night because I have to get up at 4:45 and am traveling most of the day. I'll check the posting during the day as often as I can access the site.


Krickles | 1.6180

 

[ESquared]

Hi Krickles, you might like to see thread183-859742.

There's the method as bluestringpudding gave you above (which requires some variables and an update to a column). But there are two more methods...

Using a correlated subquery with COUNT()

or the probably less-expensive method as per nigelrivett of COUNT() and a one-to-many JOIN.

 

[Krickles]

Thanks for the help everyone. FYI, I used BlueStringPudding's solution. The thread link from ESquared is worthwhile to follow, too.

Krickles | 1.6180