Grouping within a range - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Grouping within a range

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2004-03-11 : 15:10:56

I have a table with the following columns:



acct_code      emplID         amount
70             123456         32500
92             123456         32498
70             222456         42500
90             422456         52498
70             456456         62500
90             456456         62500
.              .              .
.              .              .
.              .              .

what I want is to do is select emplID and amount and group them by emplid and amount. I want the grouping to be effective when there is +/- $5 difference in the amount for a particular emplID. This is when I get stuck.

The results I want is


emplID         amount
123456         32500
222456         42500
422456         52498
456456         62500
.              .
.              .
.              .

Any suggestions?

Thank you.
PKS.

gpl
Posting Yak Master

195 Posts

Posted - 2004-03-11 : 15:36:27

PKS
Im not too sure what you are after here

Firstly, in the amounts you give, is there an implicit decimal point, that is should the first amount read 325.00 ?

Secondly, what do you mean by
"I want the grouping to be effective when there is +/- $5 difference in the amount for a particular emplID"

Do you mean that you want all emplIDs to be treated as having the same amount if the variance is within +/- $5 ? what defines the 'base' value to which the others would be compared ? what if there is a run of values ? 325, 329 and 333 ? which pair would be grouped ?

I think the reason you are stuck is because you are unclear as to the requirement.

If you could define it more completely, I think the answer would drop out.

Graham

sahu74
Posting Yak Master

100 Posts

Posted - 2004-03-11 : 16:05:41

Sorry for not being precise. First, the amounts are all in $ (no decimals). Second and the most important thing is: if there are multiple records for a emplID with amount differing by less than $5, I want them grouped with the base value larger of the two.

For Eg.


acct_code      emplID         amount
70             123456         32500
92             123456         32498

When I run the query

select emplid, amount
from xyz
group by emplID, Amount

for empId 123456, I want only one record with

emplID amount
123456 32500

If the difference between the amount is more than $5 for a particular emplID, then I donot want them to be grouped. Is this possible?

Thank you.
PKS.

gpl
Posting Yak Master

195 Posts

Posted - 2004-03-11 : 17:34:31

PKS
Most things are possible.

Im prepared to be jumped on here, but Id suggest using a cursor (sorry)

This may well fit the bill, but be prepared for syntax errors

Declare @emplID Int
Declare @OldEmplID Int
Declare @amount Int
Declare @OldAmount Int

Set @OldEmplID = 0
Set @OldAmount = 0

Create Table #Output (
EmplID Int,
Amount Int
)

Declare Grouping_Cursor Cursor For
Select emplID,
amount
From dbo.WhateverMyTableIsCalled
Order By emplID ASC, amount DESC

Open Grouping_Cursor

Fetch Next
From Grouping_Cursor
Into @emplID, @amount

While @@FETCH_STATUS = 0 Begin

If @emplID != @OldEmplID Begin --- new EmplID, always save it
Insert Into #Output Values (@emplID, @amount)
Set @OldEmplID = @emplID
Set @OldAmount = @amount
End
Else Begin --- same EmplID - is the amount more than $5 different from last saved value ?
If @OldAmount - @amount > 5 Begin --- Yes, store the row and save the amount
Insert Into #Output Values (@emplID, @amount)
Set @OldAmount = @amount
End
End

--- now go back for the next one
Fetch Next
From Grouping_Cursor
Into @emplID, @amount

End --- Loop

Close Grouping_Cursor
Deallocate Grouping_Cursor

--- Output what we stored away

Select *
From #Output
Order By emplID ASC, amount DESC

Drop Table #Output

Let me know if this is what you needed
Graham

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-11 : 18:17:11

This might be a OK...

Please provide us with CREATE TABLE and INSERTs please!!!!!!!


create table Acc(acct_code INT NOT NULL, emplID INT NOT NULL, Amount Money NOT NULL)
GO
truncate table acc
GO
Insert acc
SELECT  70,             123456    ,     32500
UNION ALL
SELECT  92,             123456     ,    32498
UNION ALL
SELECT  92,             123456     ,    43000
UNION ALL
SELECT  70 ,            222456       ,  42500
UNION ALL
SELECT  90  ,           422456   ,      52498
UNION ALL
SELECT  70   ,          456456    ,     62500
UNION ALL
SELECT  90    ,         456456     ,    62500
go
Select *  from acc
GO
CREATE VIEW Grouped
AS
Select EmplID, AMOUNT
from acc
GROUP BY EmplID, AMOUNT
GO
SELECT DISTINCT A.EmpliD, MAX(A.Amount)
from Grouped A
INNER JOIN Grouped B ON A.EmpliD = B.EmpliD AND A.Amount BETWEEN B.Amount-5 AND B.Amount+5
WHERE A.Amount >= b.Amount
GROUP BY A.EmpliD, B.Amount
GO

DavidM

"A Human Clone is a result of not implementing a Key Constraint."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-11 : 18:45:02

Yeah, no cursor solution!

David, wouldn't you just need this (minus the WHERE clause):

SELECT DISTINCT A.EmpliD, MAX(A.Amount)
from Grouped A
INNER JOIN Grouped B ON A.EmpliD = B.EmpliD AND A.Amount BETWEEN B.Amount-5 AND B.Amount+5
GROUP BY A.EmpliD, B.Amount

Tara

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-11 : 18:48:25

Yeah that seems right Tara.....

DavidM

"A Human Clone is a result of not implementing a Key Constraint."

gpl
Posting Yak Master

195 Posts

Posted - 2004-03-11 : 18:58:38

Tara
If you add the following row to David sample data above
Insert acc
SELECT 92, 123456 , 32494

The query produces a spurious row -

123456 32498.0000

it ought to be grouped with this row

123456 32500.0000

Graham

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-11 : 19:06:58

Indeed Graham.....Bugger....

DavidM

"A Human Clone is a result of not implementing a Key Constraint."

gpl
Posting Yak Master

195 Posts

Posted - 2004-03-11 : 19:12:11

sorry

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-11 : 19:16:18

That should take care of it.....


SELECT DISTINCT A.EmpliD, MAX(A.Amount)
from Grouped A
INNER JOIN Grouped B ON A.EmpliD = B.EmpliD AND A.Amount BETWEEN B.Amount-5 AND B.Amount+5
GROUP BY A.EmpliD, CASE WHEN B.Amount BETWEEN A.Amount-5 AND A.Amount+5 THEN B.Amount ELSE 1 END
HAVING MAX(A.Amount) = MAX(B.Amount)

DavidM

"A Human Clone is a result of not implementing a Key Constraint."

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-11 : 19:18:30

Oh that was overkill

Just add the having clause....


SELECT DISTINCT A.EmpliD, MAX(A.Amount)
from Grouped A
INNER JOIN Grouped B ON A.EmpliD = B.EmpliD AND A.Amount BETWEEN B.Amount-5 AND B.Amount+5
GROUP BY A.EmpliD, B.Amount
HAVING MAX(A.Amount) = MAX(B.Amount)

DavidM

"A Human Clone is a result of not implementing a Key Constraint."

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-11 : 19:19:30

Actually I don't think either work....It is recursively grouping....

DavidM

"A Human Clone is a result of not implementing a Key Constraint."

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-11 : 19:30:38

It all depends on what he wants to do with the groups

ie.. 996 is grouped with 1000..But should 994 be grouped with 996 or 1000 or on its own...

This groups a 994 on its own....


SELECT A.EmpliD, B.AMOUNT
from Grouped A
INNER JOIN Grouped B ON A.EmpliD = B.EmpliD AND A.Amount BETWEEN B.Amount-5 AND B.Amount+5
GROUP BY A.EmpliD, B.Amount
HAVING MAX(A.Amount) = MAX(B.Amount) OR MAX(B.AMOUNT)+5 !> MAX(A.AMOUNT)

EDIT: Don't need DISTINCT or MAX(B.Amount)

DavidM

"A Human Clone is a result of not implementing a Key Constraint."

sahu74
Posting Yak Master

100 Posts

Posted - 2004-03-12 : 15:57:37

Thank you so much for all the suggestions. It works great.

Thank you again.
PKS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources