Post reply

Tricky Query need some help

  • May 8, 2006 at 7:57 am

    #114131

    CLIENTVendorIdChangeNoUserChangeDateTableFieldNewValueOldValue
    4110000000322                                                                                0004784121LOADSC3     11/29/2005LFB1                          MINDK                         HE       
    4110000000322                                                                                0004784121LOADSC3     11/29/2005LFB1                          CERDT                         2004071300000000
    4110000000322                                                                                0004784121LOADSC3     11/29/2005LFA1                          REVDB                         2004070100000000
    41100000003220004791742LOADSC3     11/29/2005LFA1                          REVDB                         2005081020040701
    4110000000322                                                                                0004791742LOADSC3     11/29/2005LFB1                          CERDT                         2005081020040713
    4110000000322                                                                                0009193338AMVANWYK    4/26/2006LFB1                          CERDT                         2004071320050810
             
             
             
    After  Change No 478121:      
             
     VendorIdCertificationDateEndDateAccreditationDateHDSAClassificationChangeUserChangeDate 
     00000003227/13/200412/31/29997/1/2004HELOADSC311/29/2005 
             
    After Change No 4791742:      
             
     00000003228/10/200512/31/29998/10/2005HELOADSC311/29/2005 
             
    After Change No 9193338:      
             
     00000003227/13/200412/31/29998/10/2005HEAMVANWYK    4/26/2006 
             
             
    If on the 2nd Change the HDSAClassification also changed to say HO the records would look as follows:  
    I will ignore the 3rd change for simplicity sake.     
             
    CLIENTVendorIdChangeNoUserChangeDateTableFieldNewValueOldValue
    4110000000322                                                                                0004784121LOADSC3     11/29/2005LFB1                          MINDK                         HE       
    4110000000322                                                                                0004784121LOADSC3     11/29/2005LFB1                          CERDT                         2004071300000000
    4110000000322                                                                                0004784121LOADSC3     11/29/2005LFA1                          REVDB                         2004070100000000
    4110000000322                                                                                0004791742LOADSC3     11/29/2005LFB1                          MINDK                         HO       
    41100000003220004791742LOADSC3     11/29/2005LFA1                          REVDB                         2005081020040701
    4110000000322                                                                                0004791742LOADSC3     11/29/2005LFB1                          CERDT                         2005081020040713
             
             
    After  Change No 478121:      
             
     VendorIdCertificationDateEndDateAccreditationDateHDSAClassificationChangeUserChangeDate 
     00000003227/13/200412/31/29997/1/2004HELOADSC311/29/2005 
             
    After Change No 4791742:      
             
     00000003227/13/200412/31/29998/10/2005HELOADSC311/29/2005 
     00000003228/10/200512/31/29998/10/2005HOLOADSC311/29/2005 

  • May 8, 2006 at 2:22 pm

    #636615

    Michael,

    I am sorry if I can't really understand your post but:

    1. Can you write *in words* what is it that you want?

    2. Can you show an example *in tabular format* of the output. 

     

    Cheers,

     


    * Noel

  • May 9, 2006 at 12:42 am

    #636657

    Hi

     

    Okay let’s try clear it up a bit.  The table is an audit table where only the changes are recorded. We are transforming the data into our data warehouse. Now the  mappings are as follows. These are the only field types we are actually interested in

    CREDT = CertificationDate

    REVD = AccreditationDate

    MINDK – HDSAClassification

     

    What you will notice is that the ChangeNo Contains the field name that was changed and the old and new value. If you look at only the first table you should be able to see what type of transformation I am trying to achieve.

     0004784121 the row would need to look as follows;

    After  Change No 478121:

     

     

     

     

     

     

     

    VendorId

    CertificationDate

    EndDate

    AccreditationDate

    HDSAClassification

    ChangeUser

    ChangeDate

     

     

    0000000322

    7/13/2004

    12/31/2999

    7/1/2004

    HE

    LOADSC3

    11/29/2005

     

     

    Then after the following change which is 0004791742

     

    After Change No 4791742:

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    0000000322

    8/10/2005

    12/31/2999

    8/10/2005

    HE

    LOADSC3

    11/29/2005

     

     

    Now the tricky bit is we need one row with any changes made to any of the following CREDT, REVD, MINDK, but if no change was made to any of the fields then we still need the old value in one row.

     

    To throw the final spanner in the works I need to get this done with out the use of a cursor due to the fact the script needs to run on a Teradata box.

     

     

    Hope this clears it up.

     

    And any help or suggestions would be greatly appreciated.

     

    Thanks

  • May 9, 2006 at 12:39 pm

    #636844

    try:

    select  a.changeNo, a.VendorID, a.ChangeDate

      ,(SELECT NewValue as CertificationDate

      from AuditTable

      where Field = 'CREDT'

            and VendorID = a.vendorID

            and DateChange = (select  top 1 DateChange --find the latest that is less than or equal to a.Datechange

                              from AuditTable

                              where Field = 'CREDT'

                             and VendorID = a.vendorID

                             and DateChange <= a.DateChange

        order by DateChange desc, ChangeNo ) -- use changeNo to break ties

             )as CertificationDate

      ,(SELECT NewValue as AccreditationDate

      from AuditTable

      where Field = 'REVD'

            and VendorID = a.vendorID

            and DateChange = (select  top 1 DateChange --find the latest that is less than or equal to a.Datechange

                              from AuditTable

                              where Field = 'CREDT'

                             and VendorID = a.vendorID

                             and DateChange <= a.DateChange

        order by DateChange desc, ChangeNo ) -- use changeNo to break ties

             )as AccreditationDate

      , (SELECT NewValue as HDSAClassification

      from AuditTable

      where Field = 'MINDK'

            and VendorID = a.vendorID

            and DateChange = (select  top 1 DateChange --find the latest that is less than or equal to a.Datechange

                              from AuditTable

                              where Field = 'CREDT'

                             and VendorID = a.vendorID

                             and DateChange <= a.DateChange

        order by DateChange desc, ChangeNo ) -- use changeNo to break ties

             )as HDSAClassification

    from AuditTable

    where Field = 'CREDT'

    from

    (select distinct changeNo, VendorID, ChangeDate

    from AuditTable

    where VendorId = '0000000322' -- to limit the amount of rows

          and ChangeDate > = '20030101'   

    ) a


    * Noel

  • May 11, 2006 at 2:25 am

    #637174

    Hi

    Thanks for the effort but the query does not seem to work..I have foung another way of getting what I wanted.

     

    Thanks

    Michael

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

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