Calculating a result set based within each group

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)

Calculating a result set based within each group

Author

Topic

Charlow80
Starting Member

9 Posts

Posted - 2009-11-20 : 15:03:51

I want to subtract the value end from the first record with id 1 from the start value
of the id = 2 and continue with the values from end value of record 1 minus the start value of the
next record as long as they are within the grouping of the same lgh_tractor ids then restart calculating the values
based on the a different set of lgh_tractor ids.
Also, possibly display o for missing miles in the this record of the different lgh_tractor id value column.
Does anyone know how to do this, I am not sure if I am going to have to create a cursor or it
t-sql has another way of subtracting these values in different records based on grouping sets?

Below are first 4 records from a simpe select statemnt with missingmiles set to 0

id lgh_tractorId lgh_number start end missingmiles
1 302297 794 100 885 0
2 302297 798 885 1295 0
3 386050 747 63690 64236 0
4 386050 750 64237 64786 0

select t1.*,t2.start as start2, missingmiles = t2.start-t1.end
from #missingmiles2 t1
inner join #missingmiles2 t2 on t1.id = t2.id - 1

I know applying a the self join logic below will give me the results based on the value of each record
against the the following one but it doesn't account for the grouping of lgh_tractorid like in the query below
which would work if it did not need to be based on the same lgh_tractorid.

I have figured out how to get the value of record 1 minus the value of record 2 and display that value, but I want to
go one step further and only subtract the value only within the grouping set based on the value lgh_tractor meaning i want to
subtract the end value of row 1 minus the start of row 2 only if they have the same lgh_tractor id.
If any knows any soulution that can accomplish this please let me know.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-22 : 01:14:20

[code]select t1.*,t2.start as start2, missingmiles = isnull(t2.start-t1.end,0)
from #missingmiles2 t1
outer apply(select top 1 start
from #missingmiles2
where id > t1.id
order by id)t2
[/code]

Charlow80
Starting Member

9 Posts

Posted - 2009-11-22 : 22:23:59

Thanks,

But if I want to take this soulition and subtract only the values that are within the same grouping of tractorId's and basically have the value in missing miles show 0 for the first record for lgh_tractor when the record change to a new group of tractorid's would this statement (lgh_tractorid = lgh_tractor)added to the where clause in the subquery work? If not how would do get this, if I have to use a cursor thats fine to either way am just not sure of the code that would do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 09:43:39

sorry i didnt get you. can you illustrate with some sample data?

Charlow80
Starting Member

9 Posts

Posted - 2009-11-23 : 21:32:42

select t1.*,t2.lgh_number as Rnumber,t2.lgh_odometerstart as start2, missingmiles = isnull(t2.lgh_odometerstart-t1.lgh_odometerend,0)
from #missingmiles2 t1
outer apply(select top 1 lgh_number, OddFlag, lgh_odometerstart
from #missingmiles2
where id > t1.id
and lgh_tractor = t1.lgh_tractor
order by id)t2

Id tractor Lnum Start end RNum start2 missingmiles
1 302297 794 100 885 798 885 0
2 302297 798 885 1295 NULL NULL 0
3 386050 683 61564 62110 708 62543 433
4 386050 708 62543 63074 687 63120 46
5 386050 687 63120 63388 695 63389 1
6 386050 695 63389 63690 747 63690 0
7 386050 747 63690 64236 750 64237 1
8 386050 750 64237 64786 770 64787 1
9 386050 770 64787 65286 640 67465 2179
10 386050 640 67465 68015 NULL NULL 0
1 1 405243 616 205822 206016 682 214728 8712
12 405243 682 214728 214981 669 215040 59

Want to ensure that only the calculation logic will work within each tractor id, once that record changes to a new tractorid I want to begin subtracting with the same logic as the first record. Where different tractorid’s do not calculate against each other, which would not be accurate for this report. To restart the logic once a new tractorid begins would the tweaks I made to query above be the way of accomplishing this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources