Need Syntax - 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 2005 Forums

Transact-SQL (2005)

Need Syntax

Author

Topic

ishchopra
Starting Member

24 Posts

Posted - 2011-06-03 : 07:07:09

Hello Experts,

I have some data which is in below mentioned shape:-

Level.............Territory

Parent...............T1
Child................T2
Child................T1
Child................T3

Parent...............T4
Child................T4
Child................T4

These are two examples in which the territory is same in one group and other have mix of T1,T2 etc.

What i am looking?

Is there any way that i can mark those groups which has mix of territories? May be to identify we can putting 'Not Matching' in new column in front of each group values.

I am fine with the group which has identical territories.

the best part is parent always comes in first place and child next to them. so it should always consider parent and child one group, the moment it find next parent then it should consider it as second group and keep the loop till it finds last child of its parent.

I am sorry for not explaining well but any questions are welcome.

Looking forward for some clues.

Thanks,

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-06-03 : 07:12:00

The territory will always be T or will have some other text ?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 07:13:14

Remember that there is no ordering to data in a table.
How are your groups defined? i.e. you have two parents T1 and T4 and 4 childs (ignoring the duplicate row) how are these allocated to groups?

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

ishchopra
Starting Member

24 Posts

Posted - 2011-06-03 : 07:40:17

90% it always start from T but not all of them are T

the data is not grouped based on any rule but it is uploaded to a table in this format, i am sure if we put some text next to territory column and fill any sort of identifier then it can be controlled and grouped.

ishchopra
Starting Member

24 Posts

Posted - 2011-06-03 : 07:52:55

please help

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 07:57:25

>> These are two examples in which the territory is same in one group and other have mix of T1,T2 etc.
What is a group
Level.............Territory

Parent...............T1
Parent...............T4
Child................T1
Child................T2
Child................T3
Child................T4
Child................T4

How do you decide what is in which group?
Don't mention order without an order by statement.

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

ishchopra
Starting Member

24 Posts

Posted - 2011-06-03 : 08:01:12

ahh.. i understand you now.. i am using group because it seems to be grouped in top format but the reality is: if we sort it by any other column or mix it like you did then we will not be able to identify which one belongs to which.

does that answer your question ?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 08:07:12

Yes - you don't have any means of grouping so you can't do what you ask.

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

ishchopra
Starting Member

24 Posts

Posted - 2011-06-03 : 08:35:50

Is there any way we can bring them into groups ? what is the best approach ?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 08:41:54

Add a group column to the table?

depends on what you want - is the parent the group - doesn't look like it as T1 is both parent and child..

Level.............Territory....group

Parent...............T1..........1
Child................T2..........1
Child................T1..........1
Child................T3..........1

Parent...............T4..........2
Child................T4..........2
Child................T4..........2

Territory....parent

T1..........null
T2..........T1
T1..........T1
T3..........T1

T4..........null
T4..........T4
T4..........T4

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

ishchopra
Starting Member

24 Posts

Posted - 2011-06-03 : 09:27:36

Thanks for the this, but let me explain the objective here

Group 1
Parent...............T1..........1
Child................T2..........1
Child................T1..........1
Child................T3..........1

Group 2
Parent...............T4..........2
Child................T4..........2
Child................T4..........2

suppose if i will manage to give individual item a group name as above.. My ultimate objective is to check if parent and Child are sharing similar territory or not. if they have different territories then i want to flag them.

can you give any solution to achieve this ?

thanks for your help so far

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 09:42:50

select t.*
from tbl t
join (select group, territory from tbl where level = 'parent') t2
on t.group = t2.group
and t.territory <> t2.territory

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources