none
T-SQL challenge

    Question

  • Hi,

    I have table called addr_history and address. The address table stores recent data and addr_history stores the history of the address. In the below query I need minimum create_operator and minimum create_datetime combined with the address table for last update operator and last update datetime. Since addr_history has number of records against a person_id it is very difficult for me to filter the old records. Any thoughts??? 

    SELECT * FROM
     (    
    SELECT [pn_mship].[mship_id],
           [pn_membership].[person_id],
           [addr_history].[create_operator] as first_create_datetime,  --Added 
           [addr_history].[create_datetime] as first_create_datetime,  --Added
           [addr].[create_operator] as last_update_operator,
           [addr].[create_datetime] as last_update_datetime,
           [addr].[address_line1],
           [addr].[address_line2],
           [addr].[address_line3],
           [addr_type].[address_type],
           [addr].[locality],
           [addr].[state],
           [addr].[postcode],
           [addr].[barcode37],
           [pn_history].[deceased_flag],
           [address].dpid,
           ROW_NUMBER() over (PARTITION by [pn_mship].[mship_id],
                                           [pn_mship].person_id,
                                           [pn].[surname],
                                           [pen].[first_name]
                                           order by [pn_mship].[mship_id])rn     
      FROM [addr],
           [addr_history],
           [pn], 
           [addr_type],
           [pn_history],
           [pn_mship]
     where [pn_mship].[pn_id]=[pn].[person_id]
     AND   [pn].[person_id]=[addr].[person_id]
     AND   [pn_membership].[pn_id]=[pn_history].[person_id]
     AND   [pn_ship].termination_date is NULL
    ) t 
    where rn=1
    ORDER BY [create_datetime],[mship_id],[pn_id],[postcode]

    Regards,

    kccrga




    • Edited by kccrga Monday, November 18, 2013 1:22 AM
    Monday, November 18, 2013 1:19 AM

Answers

  • I got the required results after changing the query as below.

       ROW_NUMBER() over (PARTITION by [pn_mship].[mship_id],
                                           [pn_mship].person_id,
    [pn].[surname],
    pen].[first_name]				
     ORDER BY  [addr].[create_datetime] DESC
     addr_history].[create_datetime] asc,
     [pn_mship].[mship_id]  

    
    Tuesday, November 19, 2013 10:31 PM

All replies

  • Since addr_history has number of records against a person_id it is very difficult for me to filter the old records. Any thoughts??? 

    I believe you can use the same ROW_NUMBER technique, ordering by addr_history.create_datetime descending, and filter where that row number = 1:

           ROW_NUMBER() over (PARTITION by [pn_mship].[mship_id],
                                           [pn_mship].person_id,
                                           [pn].[surname],
                                           [pen].[first_name]
    									   [pen].[first_name]
                                           ORDER BY [pn_mship].[mship_id], [addr_history].[create_datetime] DESC) AS rn2


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, November 18, 2013 1:37 AM
  • I am still not able to achieve the results as required.

    I need the first_updated_operator and first_create_date from address_history against a particular address and last_updated_operator and last_create_datetime from address table.

    This need to combined and showed as single table. If there are single record on address table and single record on address_history table it works fine. There might be more than one on either the address table and address_history table to fetch the records.


    • Edited by kccrga Monday, November 18, 2013 5:49 AM
    Monday, November 18, 2013 4:52 AM
  • PLEASE POST DDL+DML

    [Personal Site] [Blog] [Facebook]signature

    Monday, November 18, 2013 5:12 AM
    Moderator
  • Post DDL & DML Please.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, November 18, 2013 7:20 AM
  • I got the required results after changing the query as below.

       ROW_NUMBER() over (PARTITION by [pn_mship].[mship_id],
                                           [pn_mship].person_id,
    [pn].[surname],
    pen].[first_name]				
     ORDER BY  [addr].[create_datetime] DESC
     addr_history].[create_datetime] asc,
     [pn_mship].[mship_id]  

    
    Tuesday, November 19, 2013 10:31 PM