SQL for report group. - 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)

SQL for report group.

Author

Topic

Imre85
Starting Member

13 Posts

Posted - 2006-12-12 : 10:28:38

Hi All. I write a report, and i don't know, how can i do solve the next task in sql: An item contains two parts: 1 source location, 2 item code. The codes for the source location are 1 to 6 digits. Theese codes are fixed in a table with their comments. The items(source location+ item code) fixed in another table. I'd like to get the things group by the comments of the source location. How can i do this? Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 10:35:45

Post some sample data here.
Also post your expected output (final result) based on the provided sample data.

How do you know when SourceLocation ends and ItemCode begins, within the column?

Peter Larsson
Helsingborg, Sweden

Imre85
Starting Member

13 Posts

Posted - 2006-12-12 : 10:45:32

So, for example:
t-shirt (code: 4323216), from China (231); full code: (2314323216)
pullover (code: 5424120), from Germany; full code: (215424120)
I would like to group by the Country's: for example:

China
T-shirt
...
Germany
Pullover
...
So this is my problem...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 10:47:54

How can you tell that China is #231 and Germany is #21 ?
Or is ItemCode always 7 digits?

Peter Larsson
Helsingborg, Sweden

Imre85
Starting Member

13 Posts

Posted - 2006-12-12 : 10:59:58

no its variable between 1-6 digits

Imre85
Starting Member

13 Posts

Posted - 2006-12-13 : 04:46:28

so, i created a c# code, what to solve my problem, but i would like to use an sql. How is it in sql? The code is the next:

///*i prefeer to this with *
int i;
for (i=6;i=1;i--)
{
string.substring(1);
}

I created an sql code:
///
SELECT T_1.fullcode, T_2.itemcode, T_2.descripton
FROM T_1
LEFT JOIN T_2
USING(*)

So can you help me?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 05:17:06

So how do you differentiate between these two codes?
Both start with 2312...

231227766 and 231234567

Green is Source Location and red is ItemCode.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 05:21:55

Please post here some SAMPLE DATA from your tables. This is the only way to determine if this is solvable.

EXAMPLE:


SourceLocations
21    Germany
231   China

Items                              Items
231227766   Some Item A     <->    231   227766    Some Item A 
231234567   Some Item B            23    1234567   Some Item B

Peter Larsson
Helsingborg, Sweden

Imre85
Starting Member

13 Posts

Posted - 2006-12-13 : 05:24:13

Can we speak on msn? It's secret data's.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 05:27:48

No can do! I am Dr Evil so I will sell your data to the highest bidder in any Middle East country. They (the talibans) will use the five lines of data to terrorize USA or any if it's interests in some terrible way.

Make some data up! Take five examples from your data, substitute all 1's with another digit, all 2's with another digits different from the ones used when substituting the 1's.
Rename the text with bogus values as above.

BTW, is the Items table using DDL like the left example or the rigth example?

Peter Larsson
Helsingborg, Sweden

Imre85
Starting Member

13 Posts

Posted - 2006-12-13 : 05:36:52

In the datatable same value is not exist. If i begin the search from back, then just back the good values. Like the phone numbers.
You have 2 tables, the rate table (how are the prefixes, for example 36 Hungary, 44 England...) and the calltable (here is the information about the calls, caller and calld numbers). If you would like to group by the calls with the location, then you must to cut the first 3 or 4 number and you search with the items (36 or 44). The result is the next:
Hungary calld England from the 365433421, 36876543456...
It's same.

Imre85
Starting Member

13 Posts

Posted - 2006-12-13 : 05:52:38

No, I'm not.

Okay. I copy the values.
ID Location Desc
1 3220 Hungary Budapest
2 32 Hungary
3 31 Germany
4 25 China
5 251 China East
some value from the location table.

ID Items Number
1 Zokni 3220112
2 Nadrág 32331
3 Kabát 25221
some value from the items table.

I would like to group by the items with the location.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 06:04:07

I remember this has been done before here at SQLTemam by either Chirag or Madhivanan. Maybe they can remember...

-- prepare test data
declare @locations table (location varchar(4), description varchar(50))

insert	@locations
select	'3220', 'Hungary Budapest' union all
select	'32', 'Hungary' union all
select	'31', 'Germany' union all
select	'25', 'China' union all
select	'251', 'China East'

declare @items table (item varchar(40), number varchar(20))

insert	@items
select	'Zokni', '3220112' union all
select	'Nadrág', '32331' union all
select	'Kabát', '25221'

-- the query starts here!
select		q.item,
		q.number,
		q.locationcode,
		l.description
from		(
			select		i.item,
					i.number,
					(select top 1 l.location from @locations l where i.number like l.location + '%' order by len(l.location) desc) locationcode
			from		@items i
		) q
inner join	@locations l on l.location = q.locationcode

Peter Larsson
Helsingborg, Sweden

Imre85
Starting Member

13 Posts

Posted - 2006-12-13 : 06:11:25

Aham. Can I call this sql command from crystal reports?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 06:23:01

Yes. Make it a SP or VIEW.

Peter Larsson
Helsingborg, Sweden

Imre85
Starting Member

13 Posts

Posted - 2006-12-13 : 06:31:45

I have a little problem. Don't understand completly. So:
insert @locations
select '3220', 'Hungary Budapest' union all
select '32', 'Hungary' union all
select '31', 'Germany' union all
select '25', 'China' union all
select '251', 'China East'

they are on the source, but not is the all. If I add the table a new item, then i must to modify this sql. No?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 06:39:51

YOU ONLY NEED THE CODE AFTER WHERE IT SAYS "the query starts here!".

All code above is only for setting up an environment for the query to run against.

In this code

select		q.item,
		q.number,
		q.locationcode,
		l.description
from		(
			select		i.item,
					i.number,
					(select top 1 l.location from <YourLocationTableNameHere> l where i.number like l.location + '%' order by len(l.location) desc) locationcode
			from		<YourItemTableNameHere> i
		) q
inner join	<YourLocationTableNameHere> l on l.location = q.locationcode

You must replace the fictive table names with the tables names you use in your environment. You might also be forced to change the column names to reflect your reality.

Peter Larsson
Helsingborg, Sweden

Imre85
Starting Member

13 Posts

Posted - 2006-12-13 : 07:09:18

Thank you! I must to go, but i have a few question. I will write.

Imre85
Starting Member

13 Posts

Posted - 2006-12-14 : 06:34:53

HI Peso!

I'm stupid.

I found another solution, but its not working as i planned:

it's an example (like phone billing database). The Called_Station_Id is a phonenumber, pattern is a prefix (for example hungary = 36) and the description is a Country.

SELECT CDR.Called_Station_Id, Rates.pattern, Rates.descrition
FROM dbo_rad_CDR INNER JOIN dbo_rad_Rates ON dbo_rad_CDR.id = dbo_rad_Rates.id
WHERE (dbo_rad_CDR.Called_Station_Id) Like 'pattern*';

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-14 : 06:41:40

quote:
Originally posted by Imre85

I found another solution, but its not working as i planned

Good for you!
Sticking with your non-working solution seems like a good idea.
Especially when a working solution is available.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-14 : 10:17:14

<<
I remember this has been done before here at SQLTemam by either Chirag or Madhivanan. Maybe they can remember...
>>

No. I forget what I have suggested for similar problem

Imre85, which version of CR are you using?

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources