Post reply

Help SQL Query

  • July 8, 2011 at 4:03 pm

    #254212

    Guys, I am not developer, just a pure DBA guy needing help with SQL-Fu. There is a table keeping the access level of customers. When there is a new customer or when the access level of a customer changed, a new row is inserted. I made up a sample table with data below. How do I get the access level of customers as of 1/1/2011? Thank you.

    create table #Customers

    (CustID int

    , AccessLevel char (1)

    , DateTimeChanged datetime)

    insert #Customers values (1, 'A', '5/2/2010')

    insert #Customers values (2, 'A', '5/15/2010')

    insert #Customers values (3, 'B', '5/16/2010')

    insert #Customers values (4, 'C', '8/2/2010')

    insert #Customers values (2, 'D', '8/15/2010')

    insert #Customers values (4, 'A', '9/11/2010')

    insert #Customers values (5, 'C', '10/25/2010')

    insert #Customers values (6, 'A', '11/15/2010')

    insert #Customers values (7, 'D', '11/25/2010')

    insert #Customers values (8, 'A', '12/1/2010')

    insert #Customers values (6, 'B', '12/15/2010')

    insert #Customers values (8, 'D', '12/21/2010')

    insert #Customers values (9, 'D', '12/25/2010')

    insert #Customers values (10, 'A', '12/26/2010')

    insert #Customers values (9, 'A', '12/29/2010')

    insert #Customers values (1, 'D', '1/2/2011')

    insert #Customers values (3, 'D', '1/16/2011')

  • July 8, 2011 at 4:48 pm

    #1350678

    This should work for you

    Declare @asofDate Date = '1/1/2011'

    ;

    With accesslevel as (

    Select Custid,AccessLevel,DateTimeChanged

    ,rowid = ROW_NUMBER() over (PARTITION by custid order by datetimechanged desc)

    From #Customers

    Where DateTimeChanged <= @asofDate

    )

    Select Custid,AccessLevel,DateTimeChanged

    From accesslevel

    Where rowid = 1

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 8, 2011 at 4:55 pm

    #1350680

    Thanks Champ. It works like a charm.

  • July 8, 2011 at 5:11 pm

    #1350686

    You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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