grouping problem - 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

SQL Server Development (2000)

grouping problem

Author

Topic

onder
Starting Member

6 Posts

Posted - 2005-12-09 : 08:09:36

I am a developer in a production facility; we do not have a database developer here, do i need help for a database query.
I have a table which contains items to be processed in a production line. There are more than 40 columns in the table so i did not want to list them here. Three of the columns are for sorting items ( i.e. ORDER BY C38, C39, C40 ). I need to return the table data sorted via these three columns but i also need to give each item a 'lot number' which is given with respect to another 4 columns! Each item with their 4 column values equal should be given the same lot number.

How can i manage this?

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-12-09 : 08:14:41

Not clear.
Can you post your table structure, few sample records and desired output?

Surendra

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2005-12-09 : 08:16:28

select cast(c1 as varchar)+'-'+Cast(c2 as varchar)+'-'+cast(c3 as varchar)+'-'+Cast(c4 as varchar) as LotNumber, C5, C6, C7, C...
from table
order by C38,c39,c40

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

onder
Starting Member

6 Posts

Posted - 2005-12-09 : 08:34:28

i will try to simplify;

Table:
ItemID C1 C2 C3 C4 C5 ... C38 C39 C40
------ ---- ---- ---- ---- ---- ----- ----- -----
112134 12 23 26 30 A23 1 1 1
112135 12 23 27 30 A23 1 1 2
112136 12 23 26 30 A42 1 1 4
112137 12 23 27 30 A31 1 1 3
112138 12 23 26 30 A23 1 1 5

Desired Output:
ItemID C1 C2 C3 C4 C5 ... C38 C39 C40 LotNo
------ ---- ---- ---- ---- ---- ----- ----- ----- -----
112134 12 23 26 30 A23 1 1 1 1
112135 12 23 27 30 A23 1 1 2 2
112137 12 23 27 30 A31 1 1 3 2
112136 12 23 26 30 A42 1 1 4 1
112138 12 23 26 30 A23 1 1 5 1

As you see, item 112137 is now at row number 3 to obey the sorting due to C40.
Also we have two seperate lots ( their C3 value is different ) and they are given lot numbers starting from 1

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2005-12-09 : 09:15:50

create table #tmp (
ItemId varchar(6),
C1 int,
C2 int,
C3 int,
C4 int,
C5 varchar(3),
C38 int,
C39 int,
C40 int
)

BULK INSERT #tmp
FROM '\\jhocuttd01\C$\Temp\Import.txt'
WITH
(
FIELDTERMINATOR = ' ',
BATCHSIZE = 10000,
TABLOCK
)

create table #tmp2 (
C1 int,
C2 int,
C3 int,
batch int
)
GO

insert into #tmp2 values (12, 23, 26, 1)
insert into #tmp2 values (12, 23, 27, 2)

select t1.ItemId, t1.C1, t1.C2, t1.C3, t1.C4, t1.C5, t1.C38, t1.C39, t1.C40, t2.batch
from #tmp t1
left outer join #tmp2 t2 on t1.c1=t2.c1 and t1.c2=t2.c2 and t1.c3=t2.c3
order by t1.c38, t1.c39, t1.c40

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

onder
Starting Member

6 Posts

Posted - 2005-12-09 : 09:37:52

i could not understand your solution; you hardcoded the sample records in your solution?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2005-12-09 : 10:01:55

Yes, I created a secod table that defined the batch based on the 3 fields.
Are you trying to dynamically create the batch, if so the batch numbers will not be the same from one run to another as the data set changes.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

onder
Starting Member

6 Posts

Posted - 2005-12-09 : 10:24:55

That table is dynamic itself; it changes every minute or so. Every half an hour i need to return the desired output from the query.
So i need another solution.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-09 : 10:53:41

aha...the requirements change after a solution is delivered!
Plus ca change!

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-12-09 : 15:50:45

Probably not very helpful, I know, but this sort of problem becomes much simpler in SQL Server 2005:


SELECT *, DENSE_RANK() OVER (
  PARTITION BY C1, C2, C3, C4 ORDER BY ItemID) AS LotNo
FROM Process_Items
ORDER BY C38, C39, C40

onder
Starting Member

6 Posts

Posted - 2005-12-10 : 11:25:41

i only wanted to give some sample records found in the table. This table has nearly 300 records which are updated frequently ( in fact this is an inventory table ).
Sorry Arnold, we use SQL server 2000.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-10 : 12:57:50

Why did someone create a mess like this? Is this an off-the-shelf product you bought?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

onder
Starting Member

6 Posts

Posted - 2005-12-11 : 05:16:43

Original table is a table from the production execution system and it has no idea of what a 'lot' is. It has been in operation for years with no problems.
We need to feed the process computer of one of the production lines and that computer needs data in terms of lots which i have explained above. So, at least for me, this is the normal lifecycle for the production facilities :)
Anyway, if there is no good solution for the problem, i will get all the records in the table and evaluate the lots in a c# program.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources