Help with Algorithm - 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

Old Forums

CLOSED - General SQL Server

Help with Algorithm

Author

Topic

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-07-21 : 06:27:24

Can somebody help with writing an SPROC that accept the input below then display the 2 output below

This is the input

16001485401060132700000013C54E51;4400000013CF5951;C00000001401EE51;A600000013C64451;

Output 1

Commercial Part no.: 160
Product Part no.: 1485
Quantity 4
Revision 01
Date Code 601
Cell code 3

OUTPUT 2
Number SerialNo

1 2700000013C54E51
2 4400000013CF5951
3 C00000001401EE51
4 A600000013C64451

Q
Yak Posting Veteran

76 Posts

Posted - 2006-07-21 : 06:36:50

You could do something with the length of a record I think.
Something like
case (len(data)>16) then do something to create Output 1 and Output 2
else create Output 2 (right(data, 16))

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-21 : 07:21:22

Something along these lines...

--stored procedure
create proc dbo.TestSp1 @v varchar(1000) as

select name + ' ' + value from (
          select 'Commercial Part no.:' as name, substring(@v, 1, 3) as value
union all select 'Product Part no.:', substring(@v, 5, 4)
union all select 'Quantity', substring(@v, 9, 1)
union all select 'Revision', substring(@v, 10, 2)
union all select 'Date Code', substring(@v, 13, 3)
union all select 'Cell code', substring(@v, 16, 1)) a

set @v = substring(@v, 17, len(@v)-17)
select * from dbo.Split(@v, ';')
go

--calculation
exec dbo.TestSp1 '16001485401060132700000013C54E51;4400000013CF5951;C00000001401EE51;A600000013C64451;'

/*results
------------------------- 
Commercial Part no.: 160
Product Part no.: 1485
Quantity 4
Revision 01
Date Code 601
Cell code 3

----------- ---------------------------------------------------------------------------------------------------- 
1           2700000013C54E51
2           4400000013CF5951
3           C00000001401EE51
4           A600000013C64451
*/

Note that the 'split' function is available here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-07-21 : 07:48:09

Well Ryan your solution works but the problem is that
I can not individually get the Serialnumbers for me to insert it on my table.
How do I get it individual including the Quantity etc
Thanks

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-21 : 08:15:28

Okay - it sounds like you are saying that you didn't provide the full story in your original post.

Please provide the full story of what you're trying to achieve...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-07-21 : 09:11:35

Ok I want to loop ir

Start Loop
Serial = GetSerialNumber() ' How do I get Individual Serial number ?
Insert Into SerialNumber Table
End Loop

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-21 : 09:16:51

quote:
Originally posted by OBINNA_EKE

Ok I want to loop ir

Start Loop
Serial = GetSerialNumber() ' How do I get Individual Serial number ?
Insert Into SerialNumber Table
End Loop

No. That's not your bigger picture. What is your bigger picture? What table do you have with what data? And what table do you want create with what data? Give examples (plural) of your data and how you want to manipulate it.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-07-21 : 09:46:31

The truth is that
(1)I just want to retrieve the serial number individualy and insert into my serialnumber table
(3)Then get all those headers eg Commercial, Product Part no, Quantity ,Revision ,Date ,Cell and put it inside a table
That's the whole truth

My Table Structure is

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SerialNoTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SerialNoTable]
GO

CREATE TABLE [dbo].[SerialNoTable] (
[SerialNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Commercial] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Product] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Part no] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-21 : 10:05:28

Aha! A cross join between output 1 and output 2 ?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-21 : 10:18:42

Editing Ryan's solution to this maybe?

create proc dbo.TestSp1
(
	@s varchar(1000)
)
as

set nocount on

declare @t varchar(1000)

select	@t = substring(@s, 17, len(@s) - 17)

select		s.Data,
		z.CommPartNo,
		z.ProdPartNo,
		z.Quantity,
		z.Revision,
		z.DateCode,
		z.CellCode
from		(
			select	substring(@s, 1, 3) CommPartNo,
				substring(@s, 4, 5) ProdPartNo,
				substring(@s, 9, 1) Quantity,
				substring(@s, 10, 2) Revision,
				substring(@s, 12, 4) DateCode,
				substring(@s, 16, 1) CellCode
		) z
cross join	dbo.Split(@t, ';') s

call with

exec dbo.TestSp1 '16001485401060132700000013C54E51;4400000013CF5951;C00000001401EE51;A600000013C64451;'

and the output is

Data              CommPartNo  ProdPartNo  Quantity  Revision  DateCode  CellCode
----------------  ----------  ----------  --------  --------  --------  --------
2700000013C54E51  160         01485       4         01        0601      3
4400000013CF5951  160         01485       4         01        0601      3
C00000001401EE51  160         01485       4         01        0601      3
A600000013C64451  160         01485       4         01        0601      3

Peter Larsson
Helsingborg, Sweden

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-07-24 : 03:54:10

Somebody pls help me
I just want to retrieve the serial number individually and insert into my serialnumber table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-24 : 04:17:28

Use Ryan's solution

Peter Larsson
Helsingborg, Sweden

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-07-24 : 05:34:42

What do you mean by "Use Ryan's solution"
I want to pick the Serial numeber, work on it, spit on it then insert into my table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-24 : 07:04:33

For picking serial number 1 use SUBSTRING(parameter, 17, 16).
For picking serial number 2 use SUBSTRING(parameter, 34, 16).
For picking serial number 3 use SUBSTRING(parameter, 51, 16).
For picking serial number 4 use SUBSTRING(parameter, 68, 16).

Peter Larsson
Helsingborg, Sweden

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-07-24 : 09:00:44

Where do I put the code pls ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-24 : 10:24:58

In your stored procedure which accepts the string in the first place.

Peter Larsson
Helsingborg, Sweden

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-07-24 : 11:04:28

Well It looks like no real help is coming
Thanks everybody for your help (80%)
I will just figure out the remaining 20% quite new to SQL 2000 thou

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-07-24 : 11:14:08

quote:
Originally posted by OBINNA_EKE

Well It looks like no real help is coming

Wow. Nice to see that you appreciate the efforts put in by several people to help you out. The "no real help" is what you are providing to help them help you.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-24 : 11:34:15

Well, sometime no matter how hard you try to help, you realize the original poster is way over his/her head in the current situation anyway, and they are so stressed that they don't take any help in at all.
Maybe because they don't understand what they are trying to do, or they don't understand SQL at all.

I wonder what will happen if they actually tried one or two of the solutions provided?
Maybe they will have some insight of what the solution is doing?

I often dissect other people's solutions here at SQL Team, just to learn new way of thinking.

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-24 : 11:36:37

If 80% is "no real help" wtf does 20% count as? No F* work at all?

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-07-25 : 08:32:45

quote:
Originally posted by OBINNA_EKE

I will just figure out the remaining 20% quite new to SQL 2000 thou

By "figure out the remaining 20%", do you mean "do your job" ?

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources