Remove unwanted records

[reecem]

I have a query which calculates distances between faults on a roll of material but I only want to show the minimum distance and not all distances between each fault. Can anyone help please. My query is below

select batch, roll, Faults as Quantity,
isnull((faultstart - (select top 1 isnull(faultend,0)
from faultTbl t2
where t2.batch = t1.batch
and t2.roll = t1.roll
and t2.faultstart < t1.faultstart
order by faultstart desc)),0) as Distance
from rollTbl r1 LEFT outer Join faultTbl t1
ON r1.batch = t1.batch
and r1.roll = t1.roll
ORDER BY batch, Roll, Distance

The query produces the data below and will produce a 0 distance on the first fault as there is no other fault to calculate a distance from. This works fine if there is only 1 fault but I need to remove this for Rolls that have more than 1 fault. I also want to remove all other distances apart from the lowest number.

Batch Roll Quantity Distance
878731365 1 0 0.0
878731365 2 0 0.0
878731365 3 0 0.0
878731365 4 0 0.0
878731365 5 5 0.0
878731365 5 5 0.5
878731365 5 5 3.0
878731365 5 5 3.5
878731365 5 5 5.0
878731365 6 1 0.0
878731365 7 0 0.0
878731365 8 2 0.0
878731365 8 2 4.5
878731365 9 0 0.0
878731365 10 1 0.0

This is how I want my data to look with only the 0.5 distance for Roll 5 as this is the lowest distance.

Batch Roll Quantity Distance
878731365 1 0 0.0
878731365 2 0 0.0
878731365 3 0 0.0
878731365 4 0 0.0
878731365 5 5 0.5
878731365 6 1 0.0
878731365 7 0 0.0
878731365 8 2 4.5
878731365 9 0 0.0
878731365 10 1 0.0

Thanks in advance for you help.

 

[gmmastros]

Are the quantities for each roll ALWAYS the same? Looking at your data, Roll 5 has 5 rows, with a quantity of 5 for each. Roll 8 has 2 rows with a quantity of 2 for each. Will this ALWAYS be true? If it is, the query will be easier to write. If it's not, it can still be done, but will be a little more difficult.

Answer this question, and I should be able to help you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[reecem]

No the quantities are not always the same. Basically the quantity is the number of faults on that particular roll so this can change for different Batch numbers.

It can also change for the same batch if faults get removed or combined together. e.g roll 5 has 5 faults but combine 2 faults together because they are too close which means roll 5 will now have quantity of 4.

Hope this helps and thanks.

 

[gmmastros]

Well... that makes things a bit messier. Given your data, I would suggest that you create a table variable. Then, insert the data from your original query in to the table variable. Then, we can be a bit creative to get your final results.

Since the Quantity can be different, there's another decision you'll need to make. What happens if there is a tie. I mean... if you have 2 rows, with the same batch and row AND MinDistance, but the Quantity is different. Which row should be returned then. Ex:

[tt]878731365 8 2 0.0
878731365 8 2 4.5
878731365 8 3 0.0[/tt]

The min distance for Roll 8 is 0.0, but that min distance exists with Quantity 2 & 3. In that case, which row should be returned?

In the query I show below, I will assume that you want the row with the min quantity. It would be easy enough to change this to the max quantity instead.

[green]-- Create a table variable to store intermediate results[/green]
Declare @Temp Table(Batch Int, Roll Int, Quantity Int, Distance Decimal(10,1))

Insert Into @Temp(Batch, Roll, Quantity, Distance)
select batch, roll, Faults as Quantity, 
isnull((faultstart - (select top 1 isnull(faultend,0) 
              from faultTbl t2 
              where t2.batch = t1.batch 
              and t2.roll = t1.roll
              and t2.faultstart < t1.faultstart
              order by faultstart desc)),0) as Distance
from rollTbl r1 LEFT outer Join faultTbl t1
ON r1.batch = t1.batch
and r1.roll = t1.roll

[green]
-- Remove rows where there are multiple rows.
-- We want to remove the row with quantity = 0
-- because the MIN value later is reall the min
-- value > 0.[/green]
Delete T
From   @Temp T
       Inner Join (
         Select Batch, Roll
         From   @Temp
         Where  Distance > 0
         Group By Batch, Roll
         Having Count(*) > 0
         ) As A
       On T.Batch = A.Batch
       And T.Roll = A.Roll
Where  T.Distance = 0

[green]
-- use the intermediate table to get the min value
-- for quantity, then join back to itself to get
-- all the data in the row.  Ties in distance
-- will return the row with the min quantity.[/green]
Select T.Batch,
       T.Roll,
       Min(T.Quantity) As Quantity,
       T.Distance
From   @Temp T
       Inner Join (
         Select Batch, Roll, Min(Distance) As MinDistance
         From   @Temp
         Group By Batch, Roll
         ) As A
         On T.Batch = A.Batch
         And T.Roll = A.Roll
         And T.Distance = A.MinDistance
Group By T.Batch, T.Roll, T.Distance
ORDER BY T.batch, T.Roll

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[reecem]

Thats great, thanks so much for your help.