Please help with grouping - 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)

Please help with grouping

Author

Topic

digitalcandy
Starting Member

13 Posts

Posted - 2004-07-21 : 14:17:44

MS SQL 2000

I have two tables, dbo.TravelerLot and dbo.Process. Process has a "many to one" relationship with TravelerLot.

dbo.TravelerLot INNER JOIN
dbo.Process ON dbo.TravelerLot.CurrentProcID = dbo.Process.ProcessID

I am trying to make a view that has grouping. I'm having a little trouble with the code to make the grouping the way I want.

Without grouping I have something like so, (I used commas to seperate the data in the example's columns);

TravelerLot.LotID, TravelerLot.TravLotID, Process.ProcNum
1, 100, 5,
1, 101, 10
1, 102, 3
1, 103, 30
2, 220, 500
2, 210, 23
2, 240, 400
3, 45, 100
3, 46, 90

With the propper grouping I'd like my view to look like the example below based on the data example above, (data is returned based on the lowest ProcNum);

TravelerLot.LotID, TravelerLot.TravLotID, Process.ProcNum
1, 102, 3
2, 210, 23
3, 46, 90

I need the grouping to happen based on TravelerLot.LotID, however, I also need TravelerLot.TravLotID to be visible but it needs to return the the TravLotID that has the lowest Process.ProcNum value.

Can one of you experts make this happen? I don't know much about SQL programming but this would really help for report writing.

TIA.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-21 : 16:02:42

i wonder if this would work...

group by TravelerLot.LotID, TravelerLot.TravLotID, Process.ProcNum
having Process.ProcNum = min(Process.ProcNum)

Go with the flow & have fun! Else fight the flow :)

digitalcandy
Starting Member

13 Posts

Posted - 2004-07-21 : 17:17:18

I've tried very hard to use the grouping function to no avail. However, that does not mean I know what I'm doing either because I am by no means a SQL programmer.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-21 : 17:19:55

It would be very helpful if you provided the tables in CREATE TABLE format, the sample data with INSERT INTO statements. Read through this for more information (make sure to look at the link provided in the initial post):

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29090

With this information, we can then run this on our machines to give you a good answer. Without this, we end having to type out the information so that makes it less likely for us to answer.

Tara

digitalcandy
Starting Member

13 Posts

Posted - 2004-07-21 : 17:53:28

tduggan,

Thanks for the heads up. Unfortunately I'm just a "report boy" trying to make reports with Crystal Report writer using SQL views. I don't really know SQL code. Are DDLs easy to generate?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-21 : 17:58:30

Do try to give us the CREATE and INSERT statements. This stuff takes forever, and who knows if it's right. :)



DECLARE @TravelerLot TABLE(LotID INT, TravLotID INT, CurrentProcID INT)

INSERT @TravelerLot(LotID, TravLotID, CurrentProcID)
	SELECT 1,100,1 UNION ALL
	SELECT 1, 101,2 UNION ALL
	SELECT 1, 102,3 UNION ALL
	SELECT 1, 103,4 UNION ALL
	SELECT 2, 220,5 UNION ALL
	SELECT 2, 210,6 UNION ALL
	SELECT 2, 240,7 UNION ALL
	SELECT 3, 45,8 UNION ALL	
	SELECT 3, 46,9

DECLARE @Process TABLE(ProcessID INT, ProcNum INT)

INSERT @Process(ProcessID, ProcNum)
	SELECT 1,5 UNION ALL
	SELECT 2,10 UNION ALL
	SELECT 3,3 UNION ALL
	SELECT 4,30 UNION ALL
	SELECT 5,500 UNION ALL
	SELECT 6,23 UNION ALL
	SELECT 7,400 UNION ALL
	SELECT 8,100 UNION ALL
	SELECT 9,90

SELECT
	tl.LotID,
	tl.TravLotID,
	p.ProcNum
FROM
	@TravelerLot tl
	INNER JOIN @Process p ON tl.CurrentProcID = p.ProcessID

SELECT
	tl.LotID,
	tl.TravLotID,
	p1.ProcNum
FROM
	@TravelerLot tl
	INNER JOIN @Process p1 ON tl.CurrentProcID = p1.ProcessID
	INNER JOIN (
		SELECT 
			t2.LotID, 
			MIN(p2.ProcNum) AS ProcNum
		FROM 
			@TravelerLot t2
			INNER JOIN @Process p2 ON t2.CurrentProcID = p2.ProcessID
		GROUP BY
			t2.LotID) tp ON tp.LotID = tl.LotID
			AND tp.ProcNum = p1.ProcNum

	

-- TravelerLot.LotID, TravelerLot.TravLotID, Process.ProcNum
-- 1, 100, 5, 
-- 1, 101, 10
-- 1, 102, 3
-- 1, 103, 30
-- 2, 220, 500
-- 2, 210, 23
-- 2, 240, 400
-- 3, 45, 100
-- 3, 46, 90
-- 
-- 
-- 
-- With the propper grouping I'd like my view to look like the example below based on the data example above, (data is returned based on the lowest ProcNum);
-- 
-- 
-- TravelerLot.LotID, TravelerLot.TravLotID, Process.ProcNum
-- 1, 102, 3
-- 2, 210, 23
-- 3, 46, 90

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

digitalcandy
Starting Member

13 Posts

Posted - 2004-07-21 : 18:07:42

I found the "Generate SQL Script" command and was able to make a script for "CREATE".

TravelerLot;

CREATE TABLE [dbo].[TravelerLot] (
[TravLotID] [int] IDENTITY (1, 1) NOT NULL ,
[LotID] [int] NOT NULL ,
[CurrentProcID] [smallint] NOT NULL ,
) ON [PRIMARY]
GO

Process;

CREATE TABLE [dbo].[Process] (
[ProcessID] [smallint] NOT NULL ,
[ProcNum] [smallint] NOT NULL ,
) ON [PRIMARY]
GO

All I need is to figure out how to get the insert statements you all are asking for.

digitalcandy
Starting Member

13 Posts

Posted - 2004-07-21 : 18:12:23

If I could provide more information you looking for I would. Here is a sample of the two tables in question;

TravelerLot

CurrentProcId | LotId | TravLotId
________________________
1001| 1| 101
1002| 1| 102
1003| 1| 103
1004| 1| 104
1005| 2| 210
1006| 2| 220
1007| 2| 240
1008| 3| 45
1009| 3| 46

Process

ProcessId | ProcNum
______________
1001 | 5
1002 | 10
1003 | 3
1004 | 30
1005 | 500
1006 | 23
1007 | 400
1008 | 100
1009 | 90

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-21 : 18:12:55

??? Did my solution work? Also, it has a sample insert statement for you. Just drop the @ off of it and it works.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

digitalcandy
Starting Member

13 Posts

Posted - 2004-07-21 : 18:13:33

Primary Key on TravelerLot = TravLotID

Primary Key on Process = ProcessID

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-21 : 18:17:08

Hellooooooooo. Is anyone home??????

Helllllllloooooooooooo.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

digitalcandy
Starting Member

13 Posts

Posted - 2004-07-21 : 18:18:02

After I removed the "@" character the view did run, however, it still returned multiples of the same LotID.

digitalcandy
Starting Member

13 Posts

Posted - 2004-07-21 : 18:29:02

Derrick,

Here is a screenshot of the view with your code. Notice there are still multiple LotIDs for the same LotID.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-21 : 18:34:33

Of course it is. You said you wanted it based on the lowest ProcNum for each LotID. If there are multiple entries for the ProcNum/LotID combination you'll have duplicates. How are you goint to pick which TravLotID you want? You going to take the lowest on that one also?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

digitalcandy
Starting Member

13 Posts

Posted - 2004-07-21 : 18:37:04

Ah, then we are half way there.

In that case....Since TravLotID is a unique field choose the lowest one if there are multiples of the lowest ProcNum. Your help and others is much appreciated. Thanks.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-21 : 18:51:07

You should be able to figure it out for yourself now. Do the same thing I did to get the current grouping. Repeat it slightly different for the MIN(TravLotID). After you've tried to write it, show us what you have if it doesn't work; and we'll help you along.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

digitalcandy
Starting Member

13 Posts

Posted - 2004-07-21 : 18:54:25

I can surely give it a shot. Is this like; "Give a man a fish and he'll eat for a day....Teach a man to fish and he'll eat for a lifetime?" hahaha. :D

digitalcandy
Starting Member

13 Posts

Posted - 2004-07-21 : 19:13:00

The closest I could come with without getting errors was the code below, however, it's not generating the unique results I'm looking for.

SELECT TOP 100 PERCENT tl.LotID, tl.TravLotID, p1.ProcNum
FROM dbo.TravelerLot tl INNER JOIN
dbo.Process p1 ON tl.CurrentProcID = p1.ProcessID INNER JOIN
(SELECT t2.LotID, MIN(p2.ProcNum) AS ProcNum, MIN(t2.TravLotID) AS TravLotID
FROM TravelerLot t2 INNER JOIN
Process p2 ON t2.CurrentProcID = p2.ProcessID
GROUP BY t2.LotID) tp ON tp.LotID = tl.LotID AND tp.ProcNum = p1.ProcNum
ORDER BY tl.LotID

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-21 : 22:57:35

This seems INCREDIBLY random to me. Haven't you guys designed some business rules to determine which one of these you should be using????



DECLARE @TravelerLot TABLE(LotID INT, TravLotID INT, CurrentProcID INT)

INSERT @TravelerLot(LotID, TravLotID, CurrentProcID)
	SELECT 1,100,1 UNION ALL
	SELECT 1, 101,2 UNION ALL
	SELECT 1, 102,3 UNION ALL
	SELECT 1, 103,4 UNION ALL
	SELECT 2, 220,5 UNION ALL
	SELECT 2, 210,6 UNION ALL
	SELECT 2, 210,6 UNION ALL
	SELECT 2, 213,6 UNION ALL
	SELECT 2, 214,6 UNION ALL
	SELECT 2, 215,6 UNION ALL
	SELECT 2, 210,6 UNION ALL
	SELECT 2, 210,6 UNION ALL
	SELECT 2, 240,7 UNION ALL
	SELECT 3, 45,8 UNION ALL	
	SELECT 3, 46,9

DECLARE @Process TABLE(ProcessID INT, ProcNum INT)

INSERT @Process(ProcessID, ProcNum)
	SELECT 1,5 UNION ALL
	SELECT 2,10 UNION ALL
	SELECT 3,3 UNION ALL
	SELECT 4,30 UNION ALL
	SELECT 5,500 UNION ALL
	SELECT 6,23 UNION ALL
	SELECT 7,400 UNION ALL
	SELECT 8,100 UNION ALL
	SELECT 9,90

SELECT
	tl.LotID,
	tl.TravLotID,
	p.ProcNum
FROM
	@TravelerLot tl
	INNER JOIN @Process p ON tl.CurrentProcID = p.ProcessID

SELECT
	tl.LotID,
	MIN(tl.TravLotID) AS TravLotID,
	p1.ProcNum
FROM
	@TravelerLot tl
	INNER JOIN @Process p1 ON tl.CurrentProcID = p1.ProcessID
	INNER JOIN (
		SELECT 
			t2.LotID, 
			MIN(p2.ProcNum) AS ProcNum
		FROM 
			@TravelerLot t2
			INNER JOIN @Process p2 ON t2.CurrentProcID = p2.ProcessID
		GROUP BY
			t2.LotID) tp ON tp.LotID = tl.LotID
			AND tp.ProcNum = p1.ProcNum
GROUP BY
	tl.LotID,
	p1.ProcNum

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

digitalcandy
Starting Member

13 Posts

Posted - 2004-07-22 : 11:56:21

Thank you so much for your help. The code looks to be working as I wanted. We do not have "business rules" for reporting. We are a small company and do not have resources for such rules. We do what we can.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-22 : 12:48:39

quote:
Originally posted by digitalcandy

We do not have "business rules" for reporting. We are a small company and do not have resources for such rules. We do what we can.

Neither did Enron, WorldCom, Andersen Consulting...

Just havin' a little fun with ya.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources