Return a set of most recent records with same timestamp

Question

Post reply

Return a set of most recent records with same timestamp

marin-231997

SSC Eights!

Points: 973
More actions
November 25, 2008 at 2:26 pm

#215684

Hello all,
I have a following situation: my table contains data of category assignments in certain period of time. Records have a timestamp and an action ID which "holds" a set of categories together e.g.
Table ASSIGNMENTS:
TIMESTAMP------------------ CUSTOMER_ID---------ACTION_ID
2008-11-25 09:00:00.222-----------123------------------30
2008-11-23 10:12:13.125-----------123------------------20
2008-11-20 06:05:09.333-----------123------------------10
2008-11-15 22:06:15.333-----------456-------------------5
TABLE CATEGORIES_ASSIGN:
ACTION_ID CATEGORY_ID
----30--------------1
----30--------------2
----30--------------3
----20--------------1
----20--------------2
----10--------------3
----10--------------4
-----5--------------1
TABLE CATEGORIES:
CATEGORY_ID-------CATEGORY_NAME
-----1---------------------BLUE
-----2---------------------RED
-----3---------------------YELLOW
-----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).
How can I return a set of categories valid on e.g. from 2008-11-23 10:12:13.125 until 2008-11-25 09:00:00.222 for customer 123 (which would be "BLUE" and "RED")?
Many thanks for any hints...

Viewing 7 posts - 1 through 6 (of 6 total)

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Please refer to the article in my signature on how to post sample data on this forum.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

First, yes, you should read the article that both Seth and I have in our signature blocks. It would make working on your "opportunities" easier. Luckily, things are slowing down around here as people start leaving for the Thanksgiving Break (Yea! 5 day weekend!!).

If you carefully look at the code I am about to impart, you will see the things you should be posting when asking for help; create statements for tables, sample data in the form of insert statements. What i am not posting is expected results.

create table dbo.Assignment (

CustomerID int,

ActionID int,

AssignmentDate datetime

);

create table dbo.CategoryAction (

ActionID int,

CategoryID int

);

create table dbo.Category (

CategoryID int,

CategoryName varchar(10)

);

insert into dbo.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 dbo.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 dbo.Category (CategoryID, CategoryName)

select 1,'BLUE' union all

select 2,'RED' union all

select 3,'YELLOW' union all

select 4,'WHITE';

select * from dbo.Assignment;

select * from dbo.CategoryAction;

select * from dbo.Category;

select

a.CustomerID,

c.CategoryName

from

dbo.Assignment a

inner join dbo.CategoryAction ca

on (a.ActionID = ca.ActionID)

inner join dbo.Category c

on (ca.CategoryID = c.CategoryID)

where

CustomerID = 123

and AssignmentDate between '2008-11-23 10:12:13.125' and '2008-11-25 09:00:00.222'

group by

a.CustomerID,

c.CategoryName

having

count(c.CategoryName) > 1

drop table dbo.Assignment;

drop table dbo.CategoryAction;

drop table dbo.Category;

Hope this helps you get where you are going!

Happy Turkey Day!!

marin-231997 SSC Eights! Points: 973 More actions · Answer 3

Hello Lynn/Seth,

apologies for not posting in the suggested format - will certainly do the next time.

Lynn, many thanks for your quick response and for showing me the right way to post - it really helps.

As for the solution:

1. I have forgot to mention that here can also be only one category assigned in a certain period

2. the query should be able to return the valid set of categories (or a single category if there was only one assigned) at a certain point of time - I can not query with BETWEEN in this case. Applied to my example it would be like: "show a set of categories valid on e.g. 2008-11-23 11:11:33.333" for customer 123"

Many thanks again!!!

Marin

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Me thinks you will need to repost your question here on this thread, complete with tables, sample data, and expected results.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Thread continues here: http://www.sqlservercentral.com/Forums/FindPost609378.aspx

Marin: It's better to continue posting on a single thread than to start a new one. That way the people helping you can see when new posts are made and we don't get questions answered in two places. In the future, please keep it to one thread for a question. Thanks

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

marin-231997 SSC Eights! Points: 973 More actions · Answer 6

Hello Gail,

sorry, :ermm: was not really sure what to do here... Will certainly keep the same thread the next time...

Marin