July 8, 2011 at 4:03 pm
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
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
Thanks Champ. It works like a charm.
July 8, 2011 at 5:11 pm
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