Giving a total on a column value change

I have the following data in a table
date StartTm ElapseTm shift Job# Op#
WorkCtr Qty1 Qty2
20051218 0030 0030 13 165990 225 9 .0000 0
20051218 0100 0030 13 165990 223 9 .0000 0
20051218 0130 0145 13 165990 200 9
26219.0000 28220
20051218 0315 0030 13 165990 300 9 .0000 0
20051218 0345 0115 13 165988 100 9 .0000 0
20051218 0500 0030 13 165900 300 9 .0000 0
20051218 0530 0130 13 165975 100 9 .0000 0
20051218 2300 0100 13 166436 300 9 .0000 0
20051218 2400 0030 13 165990 100 9 .0000 0
I need to know how to calculate the quantities based on totals of
successive jobs.
For example the first total for job 165990 would be 26219
the next total for job 165990 would be 0 for Qty1
the first total for job 165988 would be 0 for Qty1
so aggregated I would want to see
165988 0
165990 26219
165990 0
I need to be able to apply a identifier to these groups of successive
jobs by date and by WorkCtr.
I group the values by work_ctr, day, start_tm, job_no
Within that grouping when the job changes give a total.
I hope I am being clear. Thanks for your time in advance.I'm .
Why is the row with 26219 the first total for job 165990? It seems that
based on start time this row is the 3rd of 4 for this job. What is the
criteria you want to use for ordering the rows for a specific job.
When you say aggregated what do you mean? Do you just mean ordered? Or are
you intending to combine the rows somehow.
Also, I am by your last set of statements. what do you mean by
apply an identifier to the groups. And what does it mean when you say when
the job changes give a total?
If you can give the full version of your desired output based on the data
that you provide and answer by above confusion it will make it easier for us
.
Thanks
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"g3000" wrote:

> I have the following data in a table
> date StartTm ElapseTm shift Job# Op#
> WorkCtr Qty1 Qty2
> 20051218 0030 0030 13 165990 225 9 .0000 0
> 20051218 0100 0030 13 165990 223 9 .0000 0
> 20051218 0130 0145 13 165990 200 9
> 26219.0000 28220
> 20051218 0315 0030 13 165990 300 9 .0000 0
> 20051218 0345 0115 13 165988 100 9 .0000 0
> 20051218 0500 0030 13 165900 300 9 .0000 0
> 20051218 0530 0130 13 165975 100 9 .0000 0
> 20051218 2300 0100 13 166436 300 9 .0000 0
> 20051218 2400 0030 13 165990 100 9 .0000 0
> I need to know how to calculate the quantities based on totals of
> successive jobs.
> For example the first total for job 165990 would be 26219
> the next total for job 165990 would be 0 for Qty1
> the first total for job 165988 would be 0 for Qty1
> so aggregated I would want to see
> 165988 0
> 165990 26219
> 165990 0
> I need to be able to apply a identifier to these groups of successive
> jobs by date and by WorkCtr.
> I group the values by work_ctr, day, start_tm, job_no
> Within that grouping when the job changes give a total.
> I hope I am being clear. Thanks for your time in advance.
>|||Sorry, I actually solved the problem my issue was this
Date Job# Qty
20051201 165203 20
-- break here and total is 20 unique id
20051201 165202 15
-- break here and total is 20 unique
id
20051201 165203 10
20051201 165203 10
20051201 165203 10
20051202 165203 10
-- break here and total is
40. The above four records shoud have a unique group id
20051202 165222 05
blah blah blah
bottom line I have jobs that run and stop then start again
if the same job is started, stopped and restarted in succession that is
a unique group
if the next job is not the same as the first i needed a new unique
identifier and a total
I wanted to get totals for these groups but had no way to group those
by
just did a cursor to a table and looped through and had new colun in my
table
clear as mud?|||It's actually quite simple to solve if you don't let
the complexities of sql cloud the issue.Every block
gets an ascending unique integer.Then you can group
by this new integer.The convoluted sql comes in when
trying to derive a new integer for a new block.It's a rank
problem at most.
Ranking was implemented years ago in the RAC utility:)
Perhaps someone can wrap their head around sql 2005
ranking functions and solve it,if not I can post RAC solution:)
Check out RAC @.
www.rac4sql.net|||On 12 Jan 2006 12:40:25 -0800, g3000 wrote:

>Sorry, I actually solved the problem my issue was this
>Date Job# Qty
>20051201 165203 20
> -- break here and total is 20 unique id
>20051201 165202 15
> -- break here and total is 20 unique
>id
>20051201 165203 10
>20051201 165203 10
>20051201 165203 10
>20051202 165203 10
> -- break here and total is
>40. The above four records shoud have a unique group id
>20051202 165222 05
> blah blah blah
>bottom line I have jobs that run and stop then start again
>if the same job is started, stopped and restarted in succession that is
>a unique group
>if the next job is not the same as the first i needed a new unique
>identifier and a total
>I wanted to get totals for these groups but had no way to group those
>by
>just did a cursor to a table and looped through and had new colun in my
>table
>clear as mud?
Hi g3000,
You don't need a cursor for this. The following single query should
produce the same results. You might wish to compare this with your
current solution and retain the version that performs best or is easiest
to maintain (dfepending on what ranks top priority in your DB).
SELECT a.Job#, SUM(a.Qty)
FROM YourTable AS a
GROUP BY a.Job#, (SELECT COUNT(DISTINCT b.Job#)
FROM YourTable AS b
WHERE b."Date" <= a."Date")
Hugo Kornelis, SQL Server MVP|||thank you Hugo,
I will try that. Didnt think about that. Looks like what I
want. I am not familiar with ranking. So ill have to look into that
later.