Cursor or Temp Files?

  • Hi Everyone.

    I have a collection of records linked by a customer order number. For each like customer order number I want to check the status on them. If all of their status is C, I want to keep that order and combine them into one summary record.

    If the status is O on any one of the records with the same Customer Order number then I do not want to save any of the records and want to move to the next set of records with the same customer order.

    What is the easiest way to do this?

    Below is an example of my data.

    Any help would be very, greatly appreciated

    Thanks.

    Gillian

    So for order 905055 I wouldn't want to keep any records because one of the lines has an O status. But for

    order AW00000253 I would want to keep those records and combine them into a summary record.

    Order# coline itemnum Statusallocate

    905055 1222-63-05C12/5/2013

    905055 2222-63-47C12/5/2013

    905055 3222-63-80C12/5/2013

    905055 4222-23-72O12/11/2013

    AW00000253 1222-63-147C11/21/2013

    AW00000253 2222-63-247C11/21/2013

  • Gillian_Pappas2002 (12/12/2013)


    Hi Everyone.

    I have a collection of records linked by a customer order number. For each like customer order number I want to check the status on them. If all of their status is C, I want to keep that order and combine them into one summary record.

    If the status is O on any one of the records with the same Customer Order number then I do not want to save any of the records and want to move to the next set of records with the same customer order.

    What is the easiest way to do this?

    Below is an example of my data.

    Any help would be very, greatly appreciated

    Thanks.

    Gillian

    So for order 905055 I wouldn't want to keep any records because one of the lines has an O status. But for

    order AW00000253 I would want to keep those records and combine them into a summary record.

    Order# coline itemnum Statusallocate

    905055 1222-63-05C12/5/2013

    905055 2222-63-47C12/5/2013

    905055 3222-63-80C12/5/2013

    905055 4222-23-72O12/11/2013

    AW00000253 1222-63-147C11/21/2013

    AW00000253 2222-63-247C11/21/2013

    You don't want to use a cursor OR a temp file. You can use a HAVING clause for this.

    select OrderNum

    from yourTable

    group by OrderNum

    having max(Status) = 'C'

    If you want/need more specific coding assistance please take a few minutes and read the first link in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • A cursor certainly wouldn't be needed here; this could be accomplished with a temp table or CTE quite easily. You could do something like so:

    CREATE TABLE #Temp(Order# varchar(100))

    INSERT INTO #Temp(Order#)

    SELECT Order#

    FROM (table)

    WHERE Status = 'O'

    DELETE FROM (table)

    FROM (table)

    INNER JOIN #Temp

    ON (table).Order# = #Temp.Order#

    This will find any order numbers that have a status of O at some point, and delete anything having those order numbers from the table. As always, be certain you test this before going ahead with it!

    Also, if this is going to be deleting a large number of rows, you'll want to break up the delete into batches to avoid bloating your transaction log.

    There may well be a more efficient means of doing this, but it's the most to-the-point method I could think of.

    EDIT: Aha! Sean's method certainly works too, and would more than likely be a smoother setup for the deletion 🙂

    - 😀

  • Code solutions depends on a number of things. First, it would be helpful to see the actual table descriptions.

    Second, I'd want to know the scope of record volumes, in other words, how many records are you processing?

    None the less this can be done using either temporary tables, table variables or one or more CTEs. No reason to be processing row by row regardless how many records you are processing.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • Thank you very much, I will try that right now.

  • Thank you very much, I am going to try that right now.

  • Hi. Thank you for your response. I really appreciate all of the help here and so quickly too. I will give this a try too.

  • Hi. Thank you again for your response. That works very well to get rid of either status C or O, but what they want me to do is to look at all of the same customer order numbers and their status. If the same customer order number has ANY status of 'O', they do not want to see any of those customer order records. They only want to see like customer orders that All have a 'C' status.

    Below is the before and after applying your solution. They do not want to see ANY Orders 905055 because it has one with a status of 'O'. but they would want to see order AW00000253 because it doesn't have any status's of 'O". I hope this makes sense and I appreciate all of your help.

    conum colineitemnumcoitemstatconumcolineitemnumcoitemstat

    905055 1222-63-05C9050551222-63-05C

    905055 2222-63-47C9050552222-63-47C

    905055 3222-63-80C9050553222-63-80C

    905055 4222-23-72OAW000002531222-63-147C

    AW00000253 1222-63-147CAW000002532222-63-247C

    AW00000253 2222-63-247CSF000003891222-23-11C

    AW00000306 2222-23-31OSF000003902222-23-11C

    AW00000306 10222-23-72OSF000004451222-23-11C

    AW00000306 12222-23-47OSF000029943222-63-81C

    SF00000389 1222-23-11CSF000040991222-63-13C

    SF00000390 2222-23-11CSF000073145222-23-31C

  • Gillian_Pappas2002 (12/12/2013)


    Hi. Thank you again for your response. That works very well to get rid of either status C or O, but what they want me to do is to look at all of the same customer order numbers and their status. If the same customer order number has ANY status of 'O', they do not want to see any of those customer order records. They only want to see like customer orders that All have a 'C' status.

    Below is the before and after applying your solution. They do not want to see ANY Orders 905055 because it has one with a status of 'O'. but they would want to see order AW00000253 because it doesn't have any status's of 'O". I hope this makes sense and I appreciate all of your help.

    conum colineitemnumcoitemstatconumcolineitemnumcoitemstat

    905055 1222-63-05C9050551222-63-05C

    905055 2222-63-47C9050552222-63-47C

    905055 3222-63-80C9050553222-63-80C

    905055 4222-23-72OAW000002531222-63-147C

    AW00000253 1222-63-147CAW000002532222-63-247C

    AW00000253 2222-63-247CSF000003891222-23-11C

    AW00000306 2222-23-31OSF000003902222-23-11C

    AW00000306 10222-23-72OSF000004451222-23-11C

    AW00000306 12222-23-47OSF000029943222-63-81C

    SF00000389 1222-23-11CSF000040991222-63-13C

    SF00000390 2222-23-11CSF000073145222-23-31C

    PLEASE take a few minutes and read the first link in my signature. The sample data you posted is a mess. It is impossible to tell what is what. You have multiple columns with the same name. Turn this into some actual ddl and sample data (create table and insert statements).

    This is really not a difficult query but without tables to work with we are guessing.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thank you Sean.

    Sorry about the mess.

    Hi. Thank you again for your response. That works very well to get rid of either status C or O, but what they want me to do is to look at all of the same customer order numbers and their status. If the same customer order number has ANY status of 'O', they do not want to see any of those customer order records. They only want to see like customer orders that All have a 'C' status.

    Below is the before status before applying your solution. They do not want to see ANY Orders 905055 because it has one with a status of 'O'. but they would want to see order AW00000253 because it doesn't have any status's of 'O". I hope this makes sense and I appreciate all of your help.

    Order# Line# STATUS

    905055 1 C

    905055 2 C

    905055 3 C

    905055 4 O

    AW00000253 1 C

    AW00000253 2 C

  • Well with a little tinkering here is what I came up with:

    ;with C_Orders_CTE (conum, itemnum, coitemstat) as (

    select conum

    , itemnum

    , coitemstat

    from @Orders

    where coitemstat = 'C'

    ), O_Orders_CTE (conum, itemnum, coitemstat) as (

    select conum

    , itemnum

    , coitemstat

    from @Orders

    where coitemstat = 'O'

    )

    select distinct ord.conum

    from @Orders ORD inner join

    C_Orders_CTE C_CTE on ord.conum = C_CTE.conum

    and ord.itemnum = C_CTE.itemnum left join

    O_Orders_CTE O_CTE on C_CTE.conum = O_CTE.conum

    where O_CTE.conum is null

    Here is the result I get:

    conum

    AW00000253

    SF00000389

    SF00000390

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • Hi Sean.

    Here is my table.

    DECLARE @TempCOGGP TABLE

    (conum varchar(10),

    coline varchar(10),

    itemnum varchar(35),

    custnum varchar(10),

    custseq int,

    qtyorder decimal (10,8),

    qtyready decimal (10,8),

    qtyship decimal (10,8),

    promise date,

    ship date,

    orderdate date,

    coitemstat varchar(3),

    allocate date,

    pattern varchar(3),

    room varchar(1),

    Tag varchar(1)

    )

  • Thank you very much Kurt. That is awesome and is exactly what they are looking for.

    Pardon my ignorance but I have never seen ;with before, and are you using alias's? Not exactly sure how to plug that in correctly into my .sp.

    Thanks again for your patience and extreme help.

    Gillian

  • Gillian_Pappas2002 (12/12/2013)


    Thank you Sean.

    Sorry about the mess.

    Hi. Thank you again for your response. That works very well to get rid of either status C or O, but what they want me to do is to look at all of the same customer order numbers and their status. If the same customer order number has ANY status of 'O', they do not want to see any of those customer order records. They only want to see like customer orders that All have a 'C' status.

    Below is the before status before applying your solution. They do not want to see ANY Orders 905055 because it has one with a status of 'O'. but they would want to see order AW00000253 because it doesn't have any status's of 'O". I hope this makes sense and I appreciate all of your help.

    Order# Line# STATUS

    905055 1 C

    905055 2 C

    905055 3 C

    905055 4 O

    AW00000253 1 C

    AW00000253 2 C

    You really need to actually read the article that has been suggested. If you had read it you would see that just sticking some values in a post is nothing like consumable data. We want and need to see ddl (create table) and data (insert statements). What you should have posted would look like this.

    create table #Something

    (

    OrderNum varchar(20),

    LineNum int,

    Status char(1)

    )

    insert #Something

    select '905055', 1, 'C' union all

    select '905055', 2, 'C' union all

    select '905055', 3, 'C' union all

    select '905055', 4, 'O' union all

    select 'AW00000253', 1, 'C' union all

    select 'AW00000253', 2, 'C';

    Then we would be able to see and work with the same thing. This makes it really easy for us to help you.

    Somebody like myself can then come along and post a query that meets your requirements. Something like this one...

    select *

    from #Something

    where OrderNum in

    (

    select OrderNum

    from #Something

    group by OrderNum

    having MAX(Status) = 'C'

    )

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Gillian_Pappas2002 (12/12/2013)


    Thank you very much Kurt. That is awesome and is exactly what they are looking for.

    Pardon my ignorance but I have never seen ;with before, and are you using alias's? Not exactly sure how to plug that in correctly into my .sp.

    Thanks again for your patience and extreme help.

    Gillian

    WITH is the first part of a common table expression (cte). In actuality the ;with is a little odd. Many people got in the habit of starting a line of code like that but the truth is that a cte requires the previous line of code to end with a semicolon. Remember that the semicolon is a line terminator, not a line beginninator (credit to Lynn Pettis for the phrasing). 😀

    In essence it is like an inline view. Check them out on BOL here. http://msdn.microsoft.com/en-us/library/ms175972.aspx

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply