Results 1 to 5 of 5

Thread: TSQL Help

  1. #1
    Join Date
    Mar 2004
    Location
    Greensboro, NC, USA
    Posts
    12

    Question Unanswered: TSQL Help

    I need help with a query that I want to incorporate into a scheduled DTS within SQL Server 2000. I have a process that brings in records from an Excel file, compares that day's records with any related information already stored in the database, then saves a table of joined records. This leaves me with any matches to the data. I need to filter out all but the most current record, which would be identified by a column named [DATE]. Ex:

    Table tblMatchedRecords

    M BLah Blah2 [DATE]
    1 this that 20040101
    2 this them 20040102
    3 this that 20040630

    In this example, I would like to have the record with [DATE] = 20040630 kept, and the others deleted. All columns do not always have to match exactly. I want to be able to specify which fields that need to match and pull only the latest date.

    Make sense? Can anyone offer any suggestions?
    JS
    MCP, MCAD.Net, MCSD.Net

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    delete tblMatches
    from tblMatches
    left outer join (Select max(DATE) DATE from tblMatches) Keepers
    on tblMatches.DATE = keepers.DATE
    where keepers.DATE is null

    or

    delete
    from tblMatches
    hwere DATE <> (Select max(DATE) DATE from tblMatches)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Location
    Greensboro, NC, USA
    Posts
    12
    Ah I should have explained a little better. There are multiple different entries that need to be filtered. Ex:

    Table tblMatchedRecords

    M BLah Blah2 [DATE]
    1 this that 20040101
    2 this them 20040102
    3 this that 20040630
    4 them that 20040202
    4 them this 20040404

    See? Now I would need to keep only the latest record for this and them, deleting all the rest..
    JS
    MCP, MCAD.Net, MCSD.Net

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm sure what you are trying to do can be done without a lot of difficulty, but your example demonstrates that your data is poorly normalized, and obviously is using fake data. I can't justify spending time on a solution which may not be appropriate for your situation. If you want more assistance, please be more specific and give me the structure and content of your data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2004
    Location
    Greensboro, NC, USA
    Posts
    12
    I agree that the data is not normalized. I am dealing with communication of multiple legacy systems. The records contain patient demographic/insurance information. Any time a site views/updates a patient account, a new record is brought into our system. Ridiculous, I know.. but the problem is some people may send us an updated insurance identitfier field and another site may send us an updated demographic field, then the records don't match etc.etc... At any rate, I end up with a lot of bogus records that I am filtering. I did finally accomplish what I wanted, outside of the database with a .Net Windows app that I can schedule.
    JS
    MCP, MCAD.Net, MCSD.Net

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •