Can't sort duplicates SQL Server 2000

[dhutton]

Hey Everyone -

What would be the best way to sort data that has common fields? Here is my example:


Feature Date Order Number
===========================================
FLCHS 20070814 073576
STRCH 20070814 073576
AA10 20070814 073576

What I need is just one of the Items under feature based on a Hierarchy of the features. Its from the same order and I have to figure out how to build my query to say " IF features share the same Order Number, THEN check IF AA10 and FLCHS THEN Return only the STRCH. This customer is only getting STRCH and when I do a COUNT on this Order Number it returns "3" Items instead of just "1" which it should be just one and not 3. I need Logic to filter out the FLCHS and AA10. Any Ideas?

Thanks

[azimmer]

Hey Everyone -

What would be the best way to sort data that has common fields? Here is my example:


Feature Date Order Number
===========================================
FLCHS 20070814 073576
STRCH 20070814 073576
AA10 20070814 073576

What I need is just one of the Items under feature based on a Hierarchy of the features. Its from the same order and I have to figure out how to build my query to say " IF features share the same Order Number, THEN check IF AA10 and FLCHS THEN Return only the STRCH. This customer is only getting STRCH and when I do a COUNT on this Order Number it returns "3" Items instead of just "1" which it should be just one and not 3. I need Logic to filter out the FLCHS and AA10. Any Ideas?

Thanks

Will something like this do the job?

select a.[order number], a.[Date], a.[Feature]
from T1 a, t1 b, t1 c
where a.[order number]=b.[order number] and a.[order number]=c.[order number]
and b.[Feature]='AA10' and c.[Feature]='FLCHS' and a.[Feature]='STRCH'
 

[dhutton]

Will something like this do the job?

select a.[order number], a.[Date], a.[Feature]
from T1 a, t1 b, t1 c
where a.[order number]=b.[order number] and a.[order number]=c.[order number]
and b.[Feature]='AA10' and c.[Feature]='FLCHS' and a.[Feature]='STRCH'
 

Thanks for responding - Not sure about the FROM part. What does the T1a, t1 b, and t1 c represent? Anyway you could kind of break it down it simple terms for me?
I ge the "WHERE" logic - (sort of) I would like to say IF b.[Feature] = 'AA10' and c.[Feature] = 'FLCHS' and a.[Feature] = 'STRCH' THEN return ONLY 'STRCH'

Thanks

[azimmer]

Thanks for responding - Not sure about the FROM part. What does the T1a, t1 b, and t1 c represent? Anyway you could kind of break it down it simple terms for me?
I ge the "WHERE" logic - (sort of) I would like to say IF b.[Feature] = 'AA10' and c.[Feature] = 'FLCHS' and a.[Feature] = 'STRCH' THEN return ONLY 'STRCH'

Thanks

What I did was to join the same table (T1 in my example as you did not provide the name of your table) three times. One does this in SQL by assigning different aliases to the table (a,b, and c in my example).
The first part of the WHERE clause (first two terms) are the join ones, they make sure each row in the join table belong to the same order number. But if you go this far only all possible combinations of features will show up. (Do try it: SELECT * FROM T1 a, t1 b, t1 c
WHERE a.[order number]=b.[order number] and a.[order number]=c.[order number])
The next part of the WHERE clause makes sure only the relevant one line is selected (if there's any): if it has an AA10 feature (say in alias 'b' but it doesn't really matter), it has an FLCHS feature (in alias 'c' in my case), and also has a STRCH feature, return the row. If any of those do not show up (i.e. at least one is missing) there will be no matching combo and thus the row will not show up. Obviously if the combo does exist it shows up only once (in the join table all possible permutations (related to the same order number) show up once and only once).
Hope it makes sense.

[dhutton]

Yeah - that makes sense but.....

What if I'm using JOINS already - here are my actual tables:


=================================================

SELECT so.Order#, sv7.Item, so.soCreated,

FROM ctcmaster.dbo.ServiceOrders AS so JOIN
ctcsec.dbo.SV02 AS sv2 ON
so.order# = sv2.SV2SO# JOIN
ctcsec.dbo.SV07 AS sv7 ON
so.order# = sv7.Order#
==================================================
The SV02 table doesnt matter in this case but how would I apply your example with my JOINED tables? (If you help me fix this I will pay you - I swear)

[azimmer]

Yeah - that makes sense but.....

What if I'm using JOINS already - here are my actual tables:


=================================================

SELECT so.Order#, sv7.Item, so.soCreated,

FROM ctcmaster.dbo.ServiceOrders AS so JOIN
ctcsec.dbo.SV02 AS sv2 ON
so.order# = sv2.SV2SO# JOIN
ctcsec.dbo.SV07 AS sv7 ON
so.order# = sv7.Order#
==================================================
The SV02 table doesnt matter in this case but how would I apply your example with my JOINED tables? (If you help me fix this I will pay you - I swear)

OK, a bit of explanation before we get to the answer. It may not be technically 100% but hopefully understandable.

You can assign a (table) alias to anything you can SELECT from, that is whatever appears in the FROM clause: these are usually tables and/or views but can be sub-selects or joined tables as well.

Let's say you have (without proper definitions):
table1: idcol and col1
table2: idcol and col2
table3: idcol and col3
table4: idcol and col4

You can "alias a join" like this:

SELECT x.col1, x.col2
FROM (table1 a inner join table2 b on a.idfield=b.idfield) as x
 

NB: Although the example shows two different tables (table1, table2) SQL doesn't care about this: you can join a table to itself (it's called a self join).

A step further you can select from two joins, like this:

SELECT x.col1, x.col2, y.col3
FROM (table1 a inner join table2 b on a.idfield=b.idfield) as x inner join (table3 c inner join table4 d on c.idfield=d.idfield) as y on x.col1=y.col1
 

Well, here we go, you only need to replace T1 with whatever generates your table, in your case:
T1 <= ctcmaster.dbo.ServiceOrders AS so JOIN
ctcsec.dbo.SV02 AS sv2 ON
so.order# = sv2.SV2SO# JOIN
ctcsec.dbo.SV07 AS sv7 ON
so.order# = sv7.Order#

Though it's perfectly OK for SQL Server this way, I usually find - after a certain point - that readability reasons kind of force me to create view(s); especially that you need the same select three times (typos often come in, etc). Then simply create T1 as a view and use the original select.

Hope it makes sense. If it gets much more complicated we are going to have a hard time fix it like this. It would also help to see how far you've got by yourself (so that I can assess what level of explanation makes sense to you).

PS: You can't pay me; I won't let you ;). Our clients pay my company and that's OK - it's a different ballgame. Swear to help somebody else in need.

[dhutton]

Wow - great stuff, Thanks!

I actually ran your example and it works - but I cant seem to get it to work with my FROM statement below - I'm not sure how to plug in the t1 b and t1 c

So make T1 = FROM ctcmaster.dbo.ServiceOrders AS so INNER JOIN
ctcsec.dbo.SV02 AS sv2 ON
so.order# = sv2.SV2SO# INNER JOIN
ctcsec.dbo.SV07 AS sv7 ON
so.order# = sv7.Order# INNER JOIN
ctcsec.dbo.BLSEIT AS bl
ON sv7.Item = bl.SEIITM

Then the other t1b and t1c wold go something like this

FROM ctcmaster.dbo.ServiceOrders AS so INNER JOIN
ctcsec.dbo.SV02 AS sv2 ON
so.order# = sv2.SV2SO# INNER JOIN
ctcsec.dbo.SV07 AS sv7 ON
so.order# = sv7.Order# INNER JOIN
ctcsec.dbo.BLSEIT AS bl
ON sv7.Item = bl.SEIITM JOIN
t1 b, t1 c

[azimmer]

Wow - great stuff, Thanks!

I actually ran your example and it works - but I cant seem to get it to work with my FROM statement below - I'm not sure how to plug in the t1 b and t1 c

So make T1 = FROM ctcmaster.dbo.ServiceOrders AS so INNER JOIN
ctcsec.dbo.SV02 AS sv2 ON
so.order# = sv2.SV2SO# INNER JOIN
ctcsec.dbo.SV07 AS sv7 ON
so.order# = sv7.Order# INNER JOIN
ctcsec.dbo.BLSEIT AS bl
ON sv7.Item = bl.SEIITM

Then the other t1b and t1c wold go something like this

FROM ctcmaster.dbo.ServiceOrders AS so INNER JOIN
ctcsec.dbo.SV02 AS sv2 ON
so.order# = sv2.SV2SO# INNER JOIN
ctcsec.dbo.SV07 AS sv7 ON
so.order# = sv7.Order# INNER JOIN
ctcsec.dbo.BLSEIT AS bl
ON sv7.Item = bl.SEIITM JOIN
t1 b, t1 c

OK, let's make it simpler and do it in two steps.

First step:

CREATE VIEW myJoinView
AS
SELECT *
FROM ctcmaster.dbo.ServiceOrders AS so JOIN
ctcsec.dbo.SV02 AS sv2 ON
so.order# = sv2.SV2SO# JOIN
ctcsec.dbo.SV07 AS sv7 ON
so.order# = sv7.Order#
 
GO
 

Second step:

select a.[order number], a.[Date], a.[Feature]
from myJoinView a, myJoinView b, myJoinView c
where a.[order number]=b.[order number] and a.[order number]=c.[order number]
and b.[Feature]='AA10' and c.[Feature]='FLCHS' and a.[Feature]='STRCH'
 

Let's see if it works out for you.

[dhutton]

Ok - This is what I have so far:

======================

SELECT COUNT(a.Order#) AS Total, a.Item

FROM dh_JoinView a, dh_JoinView b, dh_JoinView c, dh_JoinView d

WHERE a.Order# = b.Order# AND a.Order# = c.Order# AND d.Item
LIKE 'AA%' AND c.Item LIKE 'AB%' AND b.Item = 'FLCHS' AND a.Item = 'HBOCH'

GROUP BY a.Item

======================

These are my actual field names I'm using:

so.Order# (so. is from my JOIN of ServiceOrders AS so) and sv.Item (sv7 from my JOIN of SV07 AS sv7

************** Problem is I'm getting the following error when I start my query:


Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'Order#'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'Order#'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'Order#'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'Order#'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'Order#'.

***********************************************

So I suspect the way I have my view defined my be the problem - here is my view:

CREATE VIEW dh_JoinView
AS
SELECT COUNT(DISTINCT so.Order#) AS Total, sv7.Item AS Item, so.soCreated, so.Order# AS OrderNumber, so.OrderType AS OType, so.BillCycle
FROM ctcmaster.dbo.ServiceOrders AS so JOIN
ctcsec.dbo.SV02 AS sv2 ON
so.order# = sv2.SV2SO# JOIN
ctcsec.dbo.SV07 AS sv7 ON
so.order# = sv7.Order#

GROUP BY sv7.Item, so.soCreated, so.Order#, so.Ordertype, so.BillCycle

================================================== =====

I followed your instructions on the create view - but when I ran it, an error came up complaining about what I have in my SELECT wasn't defined by a GROUP BY - so I added a GROUP BY statement as you see above.

Thanks for your help - U da Man!

[azimmer]

Ok - This is what I have so far:

======================

SELECT COUNT(a.Order#) AS Total, a.Item

FROM dh_JoinView a, dh_JoinView b, dh_JoinView c, dh_JoinView d

WHERE a.Order# = b.Order# AND a.Order# = c.Order# AND d.Item
LIKE 'AA%' AND c.Item LIKE 'AB%' AND b.Item = 'FLCHS' AND a.Item = 'HBOCH'

GROUP BY a.Item

======================

These are my actual field names I'm using:

so.Order# (so. is from my JOIN of ServiceOrders AS so) and sv.Item (sv7 from my JOIN of SV07 AS sv7

************** Problem is I'm getting the following error when I start my query:


Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'Order#'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'Order#'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'Order#'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'Order#'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'Order#'.

***********************************************

So I suspect the way I have my view defined my be the problem - here is my view:

CREATE VIEW dh_JoinView
AS
SELECT COUNT(DISTINCT so.Order#) AS Total, sv7.Item AS Item, so.soCreated, so.Order# AS OrderNumber, so.OrderType AS OType, so.BillCycle
FROM ctcmaster.dbo.ServiceOrders AS so JOIN
ctcsec.dbo.SV02 AS sv2 ON
so.order# = sv2.SV2SO# JOIN
ctcsec.dbo.SV07 AS sv7 ON
so.order# = sv7.Order#

GROUP BY sv7.Item, so.soCreated, so.Order#, so.Ordertype, so.BillCycle

================================================== =====

I followed your instructions on the create view - but when I ran it, an error came up complaining about what I have in my SELECT wasn't defined by a GROUP BY - so I added a GROUP BY statement as you see above.

Thanks for your help - U da Man!

Could you, please, post the table definitions with some sample data so that I can try?

[dhutton]

Could you, please, post the table definitions with some sample data so that I can try?

Ok- Here is 50 Lines of Data that the query can be used on.

These Item codes are cable TV packages and what I;m trying to do is get a count on New Installs and Disconnects for a daily, weekly, or monthly reports. I then post these on the SQL Reporting Services web via visual studio.NET - anyways...

In the data below there are some good examples of data I'm trying to parse and get a good count on. For example, if you take a look at OrderNumber 067950
you will see a total of 4 Item Codes AA001, AA01, AB00 and TLCHS.
This customer is getting a premium package called Total Package with the Item code of TLCHS. The Item codes list in a hierarchy kind of way.

================================================
Basic = (AA00, AA000, AA001, AA01, and AA10) any one of these Item codes = Basic

Prime = (AB00, AB01 and AB10) any one of these Item codes = Prime

Full = FLCHS

Total = TLCHS

=================================================


So this customer is getting TLCHS but has the AA's and AB's Item codes in there as well and I don't need to count those - I just need to count the TLCHS.

FULL
If the customer was getting the Full package the Item codes would list as AA(something) AB(something) and FLCHS (AA00, AB01 and FLCHS)

PRIME
If the customer was getting Prime the Item codes would be AA% (wild card for something) and AB% (AA10 and AB00)

BASIC
If the customer was getting just Basic service the Item code would be AA something. (AA10)

================================================== ==
***Note
By the way.... I could simply remove the Item field and get a good count for each OrderNumber listed - but would lose the benefits of displaying what Item was actually being installed, upgraded or disconnected.

================================================== ==
Other packages exist and once I get the logic figured out I can apply it towards them also, like the OrderNum 067916 below.


So my Tables I'm getting my data from are as follows:

Item comes from a table called SV07

soCreated from table ServiceOrders

OrderNumber is what I JOIN the tables with - its on both ServiceOrders and SV07. There are other citeria I need to make the OrderNumbers valid towards my total counts and below is the complete FROM statement I use:

FROM ctcmaster.dbo.ServiceOrders AS so INNER JOIN
ctcsec.dbo.SV02 AS sv2 ON
so.order# = sv2.SV2SO# INNER JOIN
ctcsec.dbo.SV07 AS sv7 ON
so.order# = sv7.Order# INNER JOIN
ctcsec.dbo.BLSEIT AS bl
ON sv7.Item = bl.SEIITM

================================================== ==

WHERE Statement

Here is a sample of my WHERE statement I use:

WHERE so.soCreated BETWEEN '20070701' AND '20070731' AND so.PhoneNum BETWEEN '9990000000' AND '9990109999' AND so.OrderType IN ('NI', 'MI', 'BR', 'RE') AND so.Status <> 'D' AND sv7.Status <> 'D' AND bl.SEITRF = ' ' AND bl.SEISTP = ' ' AND sv7.ITEM IN ('BNDMX', 'BNDST', 'BNDSH', 'BNDHB', 'BNDSP', 'BNDSN','MAXCH', 'STRCH', 'SHOWT', 'HBOCH', 'SPCHS', 'SNCHS', 'AA00', 'AA000', 'AA001', 'AA01', 'AA10', 'PRIME', 'AB00', 'AB01', 'AB10', 'FULL', 'FLCHS', 'TOTAL', 'TLCHS', 'HDALL' ) AND sv7.InOut = 'I'

================================================== ==

So the bottom line of what I'm trying to achieve is basically per OrderNumber :

CHECK AA% AB% FLCHS and TLCHS

IF OK THEN Count Only TLCHS

IF NOT OK

THEN CHECK AA% AB% and FLCHS

IF OK THEN Count Only FLCHS

IF NOT OK

THEN CHECK AA% AB%

IF OK THEN Count Only AB%

IF NOT OK

THEN CHECK AA%

IF OK THEN Count only AA%

================================================== ====

I tried adding the data below in a comma delimited form but doesnt seem to work - wanted to give you something you could import into Exel or something.

Thanks!!


Total_____Item___soCreated__OrderNumber
=================================
1 PRIME 20070702 67886
1 BVP62 20070702 67916
1 BNDMX 20070702 67916
1 BNDST 20070702 67916
1 AA01 20070702 67966
1 BVP53 20070702 67966
1 AA01 20070702 67995
1 AA01 20070702 68020
1 AB10 20070702 68020
1 AA01 20070702 68029
1 AB01 20070702 68029
1 FULL 20070702 67918
1 PRIME 20070702 67938
1 AA00 20070702 67902
1 AA10 20070702 68000
1 AB10 20070702 68000
1 AA01 20070702 67977
1 AB10 20070702 67977
1 AA01 20070702 67979
1 PRIME 20070702 67981
1 AA01 20070702 67988
1 PRIME 20070702 67992
1 AA01 20070702 67993
1 BVP60 20070702 67993
1 AB00 20070702 67983
1 FLCHS 20070702 67989
1 AB00 20070702 68015
1 AA00 20070702 68015
1 AB00 20070702 67948
1 AA00 20070702 67948
1 AB00 20070702 67939
1 AA00 20070702 67939
1 TLCHS 20070702 67950
1 AB00 20070702 67950
1 AA01 20070702 67950
1 AA001 20070702 67950
1 FLCHS 20070702 67958
1 AB00 20070702 67996
1 AB10 20070703 68103
1 AA01 20070703 68103
1 AB01 20070703 68129
1 AA01 20070703 68129
1 AA01 20070703 68158
1 FULL 20070703 68187
1 PRIME 20070703 68188
1 FULL 20070703 68076
1 PRIME 20070703 68110
1 FULL 20070703 68153
1 FULL 20070703 68153
1 AA001 20070703 68074

[azimmer]

Hi, I read your post and hopefully understand most of it. IMHO only a smaller portion is needed for the solution, I'll quote it as we walk through. The data was extremely helpful.

Instead of just giving you a SELECT I want you to understand how and why it works. (Don't worry, you'll have the SELECT by the end of the preach ;-)-)

Let's say that query that you used to generate the data in the post is Q1. For clarity first create a view as follows:

CREATE VIEW MyOrder
AS
< Q1 >
GO
 

(Simply copy-paste the query instead of "< Q1 >"). Check it by running SELECT * FROM MyOrder

(Note: My understanding is that your query ("real" Q1) is the one whose "FROM" you posted, thus eventually you'll need:

CREATE VIEW MyOrder
AS
SELECT so.order# as OrderNumber, Item, Total, soCreated
FROM ctcmaster.dbo.ServiceOrders AS so INNER JOIN
ctcsec.dbo.SV02 AS sv2 ON
so.order# = sv2.SV2SO# INNER JOIN
ctcsec.dbo.SV07 AS sv7 ON
so.order# = sv7.Order# INNER JOIN
ctcsec.dbo.BLSEIT AS bl
ON sv7.Item = bl.SEIITM
 

but I'm not 100% sure.)

Now, to the point:

...
So the bottom line of what I'm trying to achieve is basically per OrderNumber :

CHECK AA% AB% FLCHS and TLCHS

IF OK THEN Count Only TLCHS

IF NOT OK

THEN CHECK AA% AB% and FLCHS

IF OK THEN Count Only FLCHS

IF NOT OK

THEN CHECK AA% AB%

IF OK THEN Count Only AB%

IF NOT OK

THEN CHECK AA%

IF OK THEN Count only AA%
...

First, we'll turn your table into crosstab format with only AA%, AB%, FLCHS and TLCHS columns (i.e. all other items are disregarded):

    select OrderNumber,
        sum(case left(Item,2) when 'AA' then 1 else 0 end) as AA,
        sum(case left(Item,2) when 'AB' then 1 else 0 end) as AB,
        sum(case Item when 'FLCHS' then 1 else 0 end) as FLCHS,
        sum(case Item when 'TLCHS' then 1 else 0 end) as TLCHS
    from MyOrders
    group by OrderNumber
 

Do run it against the data you sent me. This is what I got:

OrderNumber AA          AB          FLCHS       TLCHS       
----------- ----------- ----------- ----------- ----------- 
67886       0           0           0           0
67902       1           0           0           0
67916       0           0           0           0
67918       0           0           0           0
67938       0           0           0           0
67939       1           1           0           0
67948       1           1           0           0
67950       2           1           0           1
...
68158       1           0           0           0
68187       0           0           0           0
68188       0           0           0           0
 
(33 row(s) affected)
 

It's quite simple: the case statements generate 1 or 0 in each column depending on whether the row meets the condition (obviously there will be no more than one '1' colmn in each line). The SUMs make sure that all of these rows are summarized to each order number. (You can try to remove the "sum"s and replace "group by" with "order by" to see the individual rows -- and hopefully see what I was trying to explain.)

Now comes the final SELECT that pulls data from this crosstab:

select OrderNumber,
    (case    when (AA>0 and AB>0 and FLCHS>0 and TLCHS>0) then TLCHS
        when (AA>0 and AB>0 and FLCHS>0) then FLCHS
        when (AA>0 and AB>0) then AB
        when (AA>0) then AA
        else 0
     end) as FinalCount
from (
    select OrderNumber,
        sum(case left(Item,2) when 'AA' then 1 else 0 end) as AA,
        sum(case left(Item,2) when 'AB' then 1 else 0 end) as AB,
        sum(case Item when 'FLCHS' then 1 else 0 end) as FLCHS,
        sum(case Item when 'TLCHS' then 1 else 0 end) as TLCHS
    from MyOrders
    group by OrderNumber
) as OrdersCrossTab
 

It checks your conditions (one after the other) and makes sure that the proper columns of the crosstab is counted. (NB: we make use of the precedence property of the case statement: it returns when first finds a matching condition; i.e.: all lines that match condition #2 also match condition #3 but it doesn't matter because 'case' returns when found match at condition #2.)

For me, it returns:

OrderNumber FinalCount  
----------- ----------- 
67886       0
67902       1
67916       0
...
68129       1
68153       0
68158       1
68187       0
68188       0
 
(33 row(s) affected)
 

Hope it's clear and helps.