Post reply

Stop Date

  • September 12, 2012 at 8:58 am

    #393162

    I'm new to SQL all together, but I have the following issue I really need help solving;

    We have a list of customer transactions that are grouped together by customer. Each transaction has a start date that is pulled from the database. However we have supplied a stop date by looking at the next "like" transaction in the list and making that start date the current transactions stop date. But we need the last transaction to stop IF there is a stop date for another type of transaction. IF there isn't a stop date for that other transaction then there should NOT be a stop date for the last transaction....

    Is there a way to make a transaction look for another type of transaction and pull that transactions stop date?

    Thanks for any help you can give me.

  • September 12, 2012 at 9:24 am

    #1536779

    dianne.howard (9/12/2012)

    I'm new to SQL all together, but I have the following issue I really need help solving;

    We have a list of customer transactions that are grouped together by customer. Each transaction has a start date that is pulled from the database. However we have supplied a stop date by looking at the next "like" transaction in the list and making that start date the current transactions stop date. But we need the last transaction to stop IF there is a stop date for another type of transaction. IF there isn't a stop date for that other transaction then there should NOT be a stop date for the last transaction....

    Is there a way to make a transaction look for another type of transaction and pull that transactions stop date?

    Thanks for any help you can give me.

    Hi and welcome to SSC. The scenario describe can probably be accomplished fairly easily. The challenge is that we don't have anywhere near enough detail to be able to provide much assistance. In order to provide any kind of assistance with the actual code we would need to have ddl (create table statements), sample data (insert statements) and desired output based on the sample data. Take a look at the first link in my signature for best practices when posting questions.

    Once there are enough details you will lots of people around here willing and able to help pretty quickly.

    _______________________________________________________________

    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/

  • September 12, 2012 at 2:32 pm

    #1536917

    dianne.howard (9/12/2012)

    I'm new to SQL all together, but I have the following issue I really need help solving;

    We have a list of customer transactions that are grouped together by customer. Each transaction has a start date that is pulled from the database. However we have supplied a stop date by looking at the next "like" transaction in the list and making that start date the current transactions stop date. But we need the last transaction to stop IF there is a stop date for another type of transaction. IF there isn't a stop date for that other transaction then there should NOT be a stop date for the last transaction....

    Is there a way to make a transaction look for another type of transaction and pull that transactions stop date?

    Thanks for any help you can give me.

    Using things like NULL for stop dates that haven't happened is a huge PITA annd will slow downn searches. Make the stop date be '9999-12-30'. (this missinng day at the end of "SQL time" is so that you can handle all date range lookups in a "Closed/Open" fashionn for start annd ennd dates respectively.

    --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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 12, 2012 at 6:25 pm

    #1536969

    Jeff Moden (9/12/2012)

    dianne.howard (9/12/2012)

    I'm new to SQL all together, but I have the following issue I really need help solving;

    We have a list of customer transactions that are grouped together by customer. Each transaction has a start date that is pulled from the database. However we have supplied a stop date by looking at the next "like" transaction in the list and making that start date the current transactions stop date. But we need the last transaction to stop IF there is a stop date for another type of transaction. IF there isn't a stop date for that other transaction then there should NOT be a stop date for the last transaction....

    Is there a way to make a transaction look for another type of transaction and pull that transactions stop date?

    Thanks for any help you can give me.

    Using things like NULL for stop dates that haven't happened is a huge PITA annd will slow downn searches. Make the stop date be '9999-12-30'. (this missinng day at the end of "SQL time" is so that you can handle all date range lookups in a "Closed/Open" fashionn for start annd ennd dates respectively.

    PITA? How does eating a sandwich help?

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • September 12, 2012 at 8:21 pm

    #1536984

    dwain.c (9/12/2012)

    Jeff Moden (9/12/2012)

    dianne.howard (9/12/2012)

    I'm new to SQL all together, but I have the following issue I really need help solving;

    We have a list of customer transactions that are grouped together by customer. Each transaction has a start date that is pulled from the database. However we have supplied a stop date by looking at the next "like" transaction in the list and making that start date the current transactions stop date. But we need the last transaction to stop IF there is a stop date for another type of transaction. IF there isn't a stop date for that other transaction then there should NOT be a stop date for the last transaction....

    Is there a way to make a transaction look for another type of transaction and pull that transactions stop date?

    Thanks for any help you can give me.

    Using things like NULL for stop dates that haven't happened is a huge PITA annd will slow downn searches. Make the stop date be '9999-12-30'. (this missinng day at the end of "SQL time" is so that you can handle all date range lookups in a "Closed/Open" fashionn for start annd ennd dates respectively.

    PITA? How does eating a sandwich help?

    Heh... not unless you sit on the sandwich to consume it. "PITA" is a standard abbreviation for "Pain In The ...".

    --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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 5 (of 5 total)

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