Dynamic SQL/Aggregate/Sequential Group By thingy

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

SQL Server Development (2000)

Dynamic SQL/Aggregate/Sequential Group By thingy

Author

Topic

jimanny
Starting Member

3 Posts

Posted - 2004-05-16 : 16:22:02

Hello SQL Experts!

Not sure if this goes in here or the Transact-SQL forum so please forgive me if I'm in the wrong place. I'm working on an ASP.NET part numbering application which uses SQL Server 2000 for the database. I've made it through about half of the book SQL Server 2000 Bible (I started about 3 days ago) so I'm very new to SQL, but I think I understand enough now to formulate the question:

Users can request n number of parts and they usually like to get a sequential set of numbers per request. As part numbers are consumed , there may be sequential (or singular) groups of part numbers left behind that would still be available (not all were used, forgotten requests, etc.). I use the PartStatus attribute to 'remember' the state of the part 0-used(locked), 1-updating, 2-available. Now, when a user requests 1-n new part numbers, I'd like to find oldest (least?) sequential set of part numbers.

Here's a sample database:

PartSuffix  PartStatus
----------------------
0000        2
0001        0
0002        0
0003        2
0004        2
0005        2
0006        0
0007        2
0008        2

And here's what I'm trying to get:

GroupNumber GroupCount
-----------------------
1           1
2           3
3           2

With this result, I'm thinking I can compare n (requested number of parts) to GroupCount to find the next available sequential set of parts that fits the request.

Here's the code I'm playing with now:

select PartSuffix, Count(PartStatus) as 'Available'
from Part
where PartStatus = 2 
group by PartSuffix
order by PartSuffix

to get this:

PartSuffix  Available
----------------------
0000        1
0003        1
0004        1
0005        1
0007        1
0008        1

From what I've read and understand, the solution may be to SELECT by grouping (aggregate) and/or use Dynamic SQL. I'm stuck on how to determine the sequential groups in PartSuffix where PartStatus = 2. If anyone can provide some tips or links, I would greatly appreciate!

Thanks,

jimanny

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-16 : 17:08:44

Have a look at
http://www.nigelrivett.net/FindGapsInSequence.html
A variation on this will dive what you want - including the start and end IDs in the sequence.

To get the sort of set you have there. It will give the start of the sequence and number in it.

select min(PartSuffix), count(*)
from
(
select PartSuffix, cnt = (select count(*) from tbl a2 where a2.PartSuffix <= a1.PartSuffix and PartStatus = 0)
from tbl a1 where a1.PartStatus = 2
) a
group by cnt

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jimanny
Starting Member

3 Posts

Posted - 2004-05-16 : 17:38:29

nr, thanks very much! I am amazed at what this SQL can do. Your code works great. And thanks for the link as well. I have to admit being as green as I am I don't fully understand the code yet, but I will be studying it and the link closely to see if I can get it. Thanks!

- jimanny

jimanny
Starting Member

3 Posts

Posted - 2004-05-17 : 04:17:24

nr, thanks again for pointing me in the right direction. I finally understand better now about the logic that went into your code. It looks to me that it's the same that is described in my book as 'Correlated Subquerries'. It still has not fully sunk in, but enough to go to the next step. In the original post I left out a another dimension to the question. In the same [Part] table, there are 6 different categories of parts all with seperate PartSuffix numbers ranging from 0000 to XXXX. I came up with this code based on yours and it actually works when there's multiple category data in the table!:


select min(PartSuffix) as PartSuffix, count(*) as 'Parts Available'
from(
	select PartSuffix, cnt = (
		select count(*)
		from Part a2
		join (select PartCategoryID, PartCategoryCode from PartCategory) p2
			on a2.PartCategoryID = p2.PartCategoryID
		where a2.PartSuffix <= a1.PartSuffix and PartStatus = 0 and p2.PartCategoryCode = 2
	)
	from Part a1
	join (select PartCategoryID, PartCategoryCode from PartCategory) p1 
		on a1.PartCategoryID = p1.PartCategoryID
	where a1.PartStatus = 2 and p1.PartCategoryCode = 2
) a
group by cnt

Here's my test table:

Category Name   Part Number     PartStatus
Tooling 	0320-0000	2
Tooling 	0320-0001	2
Tooling 	0320-0002	0
Tooling 	0320-0003	0
Tooling 	0320-0004	0
Tooling 	0320-0005	0
Tooling 	0320-0006	2
Tooling 	0320-0007	2
Tooling 	0320-0008	2
Tooling 	0320-0009	2
Tooling 	0320-0010	2
Tooling 	0320-0011	2
Tooling 	0320-0012	2
Tooling 	0320-0013	2
Tooling 	0320-0014	0
Tooling 	0320-0015	0
Tooling 	0320-0016	0
Tooling 	0320-0017	0
Tooling 	0320-0018	0
Tooling 	0320-0019	2
Tooling 	0320-0020	2
A/W		0360-0000	0
A/W		0360-0001	0
A/W		0360-0002	0
A/W		0360-0003	2
A/W		0360-0004	2
A/W		0360-0005	0
A/W		0360-0006	0
A/W		0360-0007	0
A/W		0360-0008	2
A/W		0360-0009	2
A/W		0360-0010	2
A/W		0360-0011	2
A/W		0360-0012	2
A/W		0360-0013	2

And here's the result when PartCategoryCode = 2 (Tooling)

PartSuffix	Parts Available
0000		2
0006		8
0019		2

And when PartCategoryCode = 5 (A/W)

PartSuffix	Parts Available
0003		2
0008		6

Sweet! You can see the results check out with the table. Basically I joined two sub-selected tables one on the inside (the a2 side) of the <= and the other on the outside (the a1 side) to filter on the PartCategoryCode. Now I'm curious since the sub-selected table code: (select PartCategoryID, PartCategoryCode from PartCategory)
is the same on either side, is there a better way to do it?

Thanks,

- jimanny

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources