Post reply

Bogus data entry question

  • December 9, 2009 at 12:05 pm

    #73411

    I have a table created by my code below for your convenience (lame.. I know.. I didn't design it so don't blast me please) where problem report records were entered with a cuslink identifier, disposition code and report date. Users started updating these dispositions at some point in the distant past (years ago) by entering a whole new record with a new disposition code and date, but with the same cuslink number instead of modifying the initial record. Now I need a count of all of these transactions and need to count these cuslink related records with differend dispcodes and different dates as one based on the records having the same cuslink, specifid dispcodes, and the referdate being within X days of each other. I've tried varying types of joins, but can't seem to get what I need (all of the records meeting the criteria) to be counted / shown as one count record.

    if object_id ('tempdb..#cuslink1','U')is not null

    DROP TABLE #cuslink1

    create table #cuslink1(

    cuslink numeric(5,0),

    DISCD varchar(4),

    rfdate datetime)

    INSERT INTO #cuslink1(cuslink,DISCD,rfdate)

    select '2232','eex','10/21/2008 12:00am' union all

    select '2232','co2','10/29/2008 12:00am' union all

    select '2232','co1','11/21/2008 12:00am' union all

    select '2241','eex','8/21/2008 12:00am' union all

    select '2241','co2','9/21/2008 12:00am' union all

    select '2241','co2','9/23/2008 12:00am' union all

    select '232','eex','4/2/2008 12:00am' union all

    select '232','eex','5/21/2008 12:00am' union all

    select '232','eex','5/26/2008 12:00am' union all

    select '92232','eex','10/21/2008 12:00am' union all

    select '92232','co2','10/21/2009 12:00am' union all

    select '92232','co3','11/21/2009 12:00am' union all

    select '92232','eex','12/21/2009 12:00am' union all

    select '92232','co2','12/23/2008 12:00am'

    results desired (with parameters set to return data with

    number of days between rfdates set to less than or equal to 10

    and DISCCD in ('EEX','CO2')

    would look like this

    CUSLINK, TOTAL

    2232, 1

    92232, 2

    Any suggestions would be appreciated.

    Thanks..

  • December 9, 2009 at 12:14 pm

    #1090127

    Hard to help with just the problem description provided. Please read the first article I reference below in my signature block regarding asking for assistance. If you follow the instructions in that article and include expected results based on the sample data, you will get several people jumping in to help, PLUS, you'll get tested code in return!

  • December 9, 2009 at 1:44 pm

    #1090201

    You may want to look at a solution posted by Lowell

    http://qa.sqlservercentral.com/Forums/Topic830694-8-2.aspx

    Agreed the problem is NOT identical to yours but is similiar in nature and may give you an idea of how to create a solution.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • December 9, 2009 at 2:25 pm

    #1090231

    Thanks.. took your suggestion and edited the initial post to contain the recommended code.

  • December 9, 2009 at 2:36 pm

    #1090240

    TSQLExplorer (12/9/2009)

    Thanks.. took your suggestion and edited the initial post to contain the recommended code.

    Okay, now how about the expected output based on the sample data provided?

  • December 9, 2009 at 2:48 pm

    #1090250

    I need a count of cuslinks with differend dispcodes and different dates as one record count based on the recordsets having the same cuslink, specified dispcodes, and the referdate being within X days of each other

  • December 9, 2009 at 3:17 pm

    #1090263

    Please don't describe the results you want, show us using the sample data. This way we have something to test against.

  • December 9, 2009 at 3:45 pm

    #1090269

    Sorry .. guess I missed that in your suggested article..

    I've edited the initial post to reflect this requirement.

  • December 9, 2009 at 4:55 pm

    #1090281

    TSQLExplorer (12/9/2009)

    Sorry .. guess I missed that in your suggested article..

    I've edited the initial post to reflect this requirement.

    Correct, it is missing from the article, but it wasn't missing from my post where I suggested reading the article.

  • December 9, 2009 at 5:03 pm

    #1090282

    What should the result set for these be? They are a part of the sample data as well.

    select '2241','eex','8/21/2008 12:00am' union all

    select '2241','co2','9/21/2008 12:00am' union all

    select '2241','co2','9/23/2008 12:00am' union all

    select '232','eex','4/2/2008 12:00am' union all

    select '232','eex','5/21/2008 12:00am' union all

    select '232','eex','5/26/2008 12:00am' union all

  • December 9, 2009 at 10:27 pm

    #1090345

    Hmmm... are you saying that the only time you want to return a count is when an EEX is followed by a CO2 that's ten day or less appart?

    --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)
    Intro to Tally Tables and Functions

  • December 10, 2009 at 8:41 am

    #1090609

    That is exactly what I need...

    THANKS!!!

  • December 10, 2009 at 9:29 am

    #1090665

    TSQLExplorer (12/10/2009)

    That is exactly what I need...

    THANKS!!!

    Okay, that helps but then how do you get a count of 1 from this, I'd assume it would be 2:

    select '92232','eex','10/21/2008 12:00am' union all --

    select '92232','co2','10/21/2009 12:00am' union all -- This is one

    select '92232','co3','11/21/2009 12:00am' union all

    select '92232','eex','12/21/2009 12:00am' union all --

    select '92232','co2','12/23/2008 12:00am' -- This is the second

  • December 10, 2009 at 10:02 am

    #1090696

    You are correct.. my bust.. missed the second one. I will edit the initial post to reflect. Thanks.

  • December 11, 2009 at 9:57 am

    #1091274

    The result set with parameters set to return data with

    number of days between rfdates set to less than or equal to 10

    and DISCCD in ('EEX','CO2')

    would look like this

    CUSLINK, TOTAL

    2232, 1

    92232, 2

    Thanks for all of your help with this!

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

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