Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Cursor or Temp Files? Expand / Collapse
Author
Message
Posted Monday, December 16, 2013 2:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:19 PM
Points: 36,724, Visits: 31,172
Status = 'O' means "Open"???
Status = 'C' means "Closed"???

Are there any other statuses than just 'O' or 'C'????


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1523425
Posted Monday, December 16, 2013 3:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
Hi There.

Yes, there can be other statuses, but I am just looking for each customer order that may have 1 to n customer order lines, and that the line statuses ALL must be = C. I think I got it to work just now by using aliases. It tests correctly now. I never did get that cte code to work right.

Thanks.

Gillian
Post #1523448
Posted Monday, December 16, 2013 3:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:19 PM
Points: 36,724, Visits: 31,172
Gillian_Pappas2002 (12/16/2013)
Hi There.

Yes, there can be other statuses, but I am just looking for each customer order that may have 1 to n customer order lines, and that the line statuses ALL must be = C. I think I got it to work just now by using aliases. It tests correctly now. I never did get that cte code to work right.

Thanks.

Gillian


So we're not actually looking to disqualify a customer if they have an "O" status... just to replay what you said above, we're actually looking for only those customers that have all "C" status. ANY OTHER status will act as a disqualifier for the customer, not just those with an "O" status, correct?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1523467
Posted Monday, December 16, 2013 4:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:19 PM
Points: 36,724, Visits: 31,172
Pending a reply, if the answer to my question is "Yes", then the code can be greatly simplified (change the * to the column names you desire and add any joins you desire to the outer query).

--===== Create and populate a test table on-the-fly.
-- This is NOT a part of the solution. We're just building a test table here.
SELECT *
INTO #TestTable
FROM (
SELECT '905055' , 1,'222-63-05' ,'C' UNION ALL
SELECT '905055' , 2,'222-63-47' ,'C' UNION ALL
SELECT '905055' , 3,'222-63-80' ,'C' UNION ALL
SELECT '905055' , 4,'222-23-72' ,'O' UNION ALL
SELECT 'AW00000253', 1,'222-63-147','C' UNION ALL
SELECT 'AW00000253', 2,'222-63-247','C' UNION ALL
SELECT 'AW00000306', 2,'222-23-31' ,'O' UNION ALL
SELECT 'AW00000306',10,'222-23-72' ,'O' UNION ALL
SELECT 'AW00000306',12,'222-23-47' ,'O' UNION ALL
SELECT 'SF00000389', 1,'222-23-11' ,'C' UNION ALL
SELECT 'SF00000390', 2,'222-23-11' ,'C'
) tt (CoNum, CoLine, ItemNum, CoItemStat)
;
--===== Create an index to boost performance
CREATE INDEX IX_TestTable_CoNum_CoItemStat
ON #TestTable (CoNum, CoItemStat)
;
--===== Return only those lines where all the CoItemStat's = 'C' for each CoNum
SELECT *
FROM #TestTable
WHERE CoNum NOT IN (SELECT CoNum FROM #TestTable WHERE CoItemStat <> 'C')
;

Results...
CoNum      CoLine      ItemNum    CoItemStat
---------- ----------- ---------- ----------
AW00000253 1 222-63-147 C
AW00000253 2 222-63-247 C
SF00000389 1 222-23-11 C
SF00000390 2 222-23-11 C

(4 row(s) affected)


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1523473
Posted Monday, December 16, 2013 4:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
That is spot on Sir.

Thanks.

Gillian
Post #1523487
Posted Monday, December 16, 2013 8:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:19 PM
Points: 36,724, Visits: 31,172
Perfect. That also makes the next logical permutation of adding another status to the acceptable statuses super easy. For example, if someone later decides that the status of 'C' and or 'T' is acceptable, then a very small change is all that is needed.

DROP TABLE #TestTable

--===== Create and populate a test table on-the-fly.
-- This is NOT a part of the solution. We're just building a test table here.
SELECT *
INTO #TestTable
FROM (
SELECT '905055' , 1,'222-63-05' ,'C' UNION ALL
SELECT '905055' , 2,'222-63-47' ,'C' UNION ALL
SELECT '905055' , 3,'222-63-80' ,'C' UNION ALL
SELECT '905055' , 4,'222-23-72' ,'O' UNION ALL
SELECT 'AW00000253', 1,'222-63-147','C' UNION ALL
SELECT 'AW00000253', 2,'222-63-247','C' UNION ALL
SELECT 'AW00000306', 2,'222-23-31' ,'O' UNION ALL
SELECT 'AW00000306',10,'222-23-72' ,'O' UNION ALL
SELECT 'AW00000306',12,'222-23-47' ,'O' UNION ALL
SELECT 'SF00000389', 1,'222-23-11' ,'C' UNION ALL
SELECT 'SF00000390', 2,'222-23-11' ,'C' UNION ALL
SELECT 'MI12345678', 1,'AAA-BB-CC' ,'C' UNION ALL --Added data here down
SELECT 'MI12345678', 2,'AAA-BB-CC' ,'T' UNION ALL
SELECT 'MI12345678', 3,'AAA-BB-CC' ,'A' UNION ALL
SELECT 'RI87654321', 1,'AAA-BB-DD' ,'C' UNION ALL
SELECT 'RI87654321', 2,'AAA-BB-DD' ,'T' UNION ALL
SELECT 'CA99999999', 2,'AAA-BB-EE' ,'T'
) tt (CoNum, CoLine, ItemNum, CoItemStat)
;
--===== Create an index to boost performance
CREATE INDEX IX_TestTable_CoNum_CoItemStat
ON #TestTable (CoNum, CoItemStat)
;
--===== Return only those lines where all the CoItemStat's = 'C' or 'T' for each CoNum
-- Notice the second "NOT IN"
SELECT *
FROM #TestTable
WHERE CoNum NOT IN (SELECT CoNum FROM #TestTable WHERE CoItemStat NOT IN ('C','T'))
;

Results...

CoNum      CoLine      ItemNum    CoItemStat
---------- ----------- ---------- ----------
AW00000253 1 222-63-147 C
AW00000253 2 222-63-247 C
SF00000389 1 222-23-11 C
SF00000390 2 222-23-11 C
RI87654321 1 AAA-BB-DD C
RI87654321 2 AAA-BB-DD T
CA99999999 2 AAA-BB-EE T

(7 row(s) affected)


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1523515
Posted Tuesday, December 17, 2013 5:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
Thanks very much Jeff. I appreciate all of your help.

Gillian
Post #1523627
Posted Tuesday, December 17, 2013 6:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:19 PM
Points: 36,724, Visits: 31,172
You're welcome. Thank you for the feedback.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1523659
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse