November 26, 2008 at 12:27 pm
Hello all,
I have a following situation: my table contains data of category assignments in certain period in time. Records have a timestamp and an action ID which "holds" a set of categories together:
create table #Assignment (
CustomerID int,
ActionID int,
AssignmentDate datetime
);
create table #CategoryAction (
ActionID int,
CategoryID int
);
create table #Category (
CategoryID int,
CategoryName varchar(10)
);
insert into #Assignment (AssignmentDate, CustomerID, ActionID)
select '2008-11-25 09:00:00.222',123,30 union all
select '2008-11-23 10:12:13.125',123,20 union all
select '2008-11-20 06:05:09.333',123,10 union all
select '2008-11-15 22:06:15.333',456,5;
insert into #CategoryAction (ActionID, CategoryID)
select 30,1 union all
select 30,2 union all
select 30,3 union all
select 20,1 union all
select 20,2 union all
select 10,3 union all
select 10,4 union all
select 5,1;
insert into #Category (CategoryID, CategoryName)
select 1,'BLUE' union all
select 2,'RED' union all
select 3,'YELLOW' union all
select 4,'WHITE';
This means that e.g. customer 123 had following assignments:
- to "YELLOW" and "WHITE" on 2008-11-20 06:05:09.333 (ACTION_ID 10)
- this combination was valid until 2008-11-23 10:12:13.125 when customer was assigned a new set of categories: "BLUE" and "RED" (ACTION_ID 20)
- which was valid until 2008-11-25 09:00:00.222 when finally a set of "BLUE", "RED" and "YELLOW" was assigned (ACTION_ID 30).
I am trying to select a set of records that would describe the category assignment for a certain patient on a certain point in time such as e.g. "show a set of categories valid on 2008-11-23 11:11:33 for customer 123".
Expected result here would be "BLUE" and "RED" (ACTION_ID 20) as "BLUE" and "RED" were assigned on to customer 123 on 2008-11-23 10:12:13.125 and valid until the next assignment which was on 2008-11-25 (ACTION_ID 30)
Many thanks,
Marin
November 26, 2008 at 12:32 pm
For the sample data that you've posted, what's the output that you want? What records do you want back?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2008 at 12:45 pm
...more specifically, are you expecting each category to be in a separate record in the resultset or are you expecting them all to be in the same row? (pivoting) Another question, will you always be trying to get the results for just 1 customer or will you need to query the point in time categories for multiple customers at the same time? These will change how you write the query.
November 26, 2008 at 1:08 pm
Hello Gail/Chris,
thanks for the quick responses!
This is what I expect actually:
insert into #Result (AssignmentDate, CustomerID, CategoryName)
select '2008-11-23 10:12:13.125',123,'RED' union all
select '2008-11-23 10:12:13.125',123,'BLUE' union all
select '2008-11-15 22:06:15.333',456,'BLUE';
select * from #Result
Meaning that Chris is right assuming that I will query for multiple customers for a certain point in time. The explanation for the result table would be: on 2008-11-23 11:11:33 the categories assignment was "RED" and "BLUE" for customer 123 and "RED" for customer 456 (pivoting not needed)
Thanks,
Marin
November 26, 2008 at 1:11 pm
...customer 456 had assignment to "BLUE" of course... Sorry for the mistake.
Marin
November 26, 2008 at 1:41 pm
This should do what you are looking for:
SELECT a.AssignmentDate, a.CustomerID, c.CategoryName
FROM
(SELECT CustomerID, ActionID, AssignmentDate,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY AssignmentDate DESC) AS recentness
FROM #Assignment
WHERE AssignmentDate <= @PointInTime) a
INNER JOIN #CategoryAction ca ON a.ActionID = ca.ActionID
INNER JOIN #Category c ON ca.CategoryID = c.CategoryID
WHERE a.recentness = 1
ORDER BY a.CustomerID, c.CategoryName
an alternative method, slightly slower but works in versions before SQL Server 2005:
SELECT a.AssignmentDate, a.CustomerID, c.CategoryName
FROM #Assignment a
INNER JOIN #CategoryAction ca ON a.ActionID = ca.ActionID
INNER JOIN #Category c ON ca.CategoryID = c.CategoryID
INNER JOIN
(SELECT CustomerID, MAX(AssignmentDate) AS LastAssignment
FROM #Assignment a
WHERE AssignmentDate <= @PointInTime
GROUP BY CustomerID) la ON a.CustomerID = la.CustomerID AND a.AssignmentDate = la.LastAssignment
ORDER BY a.CustomerID, c.CategoryName
November 26, 2008 at 5:25 pm
Chris,
thanks a million for your quick responses and excellent solutions, both work like a charm!
Many greetings,
Marin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply