Interesting SQL problem : How to track movement history

[Anthony Paul]

Hello everyone,

There's an interesting SQL problem I've come across that I'm currently
banging my head against. Given the following table that contains item
location information populated every minute :

location_id date_created
=========== ============
5 2000-01-01 01:00 <-- Don't need
5 2000-01-01 01:01 <-- Don't need
5 2000-01-01 01:02 <-- Need
7 2000-01-01 01:03 <-- Don't need
7 2000-01-01 01:04 <-- Need
5 2000-01-01 01:05 <-- Need
2 2000-01-01 01:06 <-- Don't Need
2 2000-01-01 01:07 <-- Need
7 2000-01-01 01:08 <-- Need

how would you generate a result-set that returns the item's location
history *without* duplicating the same location if the item has been
sitting in the same room for a while. For example, the result set
should look like the following :

location_id date_created
=========== ============
5 2000-01-01 01:02
7 2000-01-01 01:04
5 2000-01-01 01:05
2 2000-01-01 01:07
7 2000-01-01 01:08

This is turning out to be a finger twister and I'm not sure if it
could be done in SQL; I may have to resort to writing a stored-proc.

Regards,

Anthony

[markc600]

If you are using SQL Server 2005, you can do this

with cte(location_id ,date_created,g rp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_create d) as date_created
from cte
group by location_id,grp
order by max(date_create d)

[Robert Klemme]

On 20.04.2007 15:49, ma******@hotmai l.com wrote:

If you are using SQL Server 2005, you can do this

with cte(location_id ,date_created,g rp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_create d) as date_created
from cte
group by location_id,grp
order by max(date_create d)

I'd do

select location_id, max(date_create d) date_created
from your_table
group by location_id
order by max(date_create d)

Am I missing something?

robert

[Anthony Paul]

Hello Robert,

That wouldn't work since it groups by location_id and would return the
following :

location_id date_created
=========== ============
5 2000-01-01 01:05
2 2000-01-01 01:07
7 2000-01-01 01:08

I am looking into Mark's solution as we speak...

Regards,

Anthony

On Apr 20, 9:59 am, Robert Klemme <shortcut...@go oglemail.comwro te:

On 20.04.2007 15:49, markc...@hotmai l.com wrote:

If you are using SQL Server 2005, you can do this

with cte(location_id ,date_created,g rp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_create d) as date_created
from cte
group by location_id,grp
order by max(date_create d)

I'd do

select location_id, max(date_create d) date_created
from your_table
group by location_id
order by max(date_create d)

Am I missing something?

robert

[Tom Moreau]

Yep. What if the item returns to a location that it once occupied? Your
solution would not pick up the previous history.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Robert Klemme" <sh*********@go oglemail.comwro te in message
news:58******** *****@mid.indiv idual.net...
On 20.04.2007 15:49, ma******@hotmai l.com wrote:

If you are using SQL Server 2005, you can do this

with cte(location_id ,date_created,g rp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_create d) as date_created
from cte
group by location_id,grp
order by max(date_create d)

I'd do

select location_id, max(date_create d) date_created
from your_table
group by location_id
order by max(date_create d)

Am I missing something?

robert

[Robert Klemme]

On 20.04.2007 16:08, Tom Moreau wrote:

Yep. What if the item returns to a location that it once occupied? Your
solution would not pick up the previous history.

Right, thanks Tom and Anthony for correcting me. Stupid me. I should
have spent few more CPU cycles on this. :-)

robert

[Anthony Paul]

Hello Mark,

So fast on the trigger! What do you do, have a notification bell ring
whenever something is posted on this newsgroup? :P

Your solution worked perfectly... I'm used to regular ol' SQL and what
you've just shown me is amazing; I didn't know SQL had these
capabilities. rank, over and partition are new to me. Do you have any
book recommendations ?

Regards,

Anthony

On Apr 20, 9:49 am, markc...@hotmai l.com wrote:

If you are using SQL Server 2005, you can do this

with cte(location_id ,date_created,g rp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_create d) as date_created
from cte
group by location_id,grp
order by max(date_create d)

[Tom Moreau]

www.insidetsql.com
--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Anthony Paul" <an**********@g mail.comwrote in message
news:11******** *************@y 80g2000hsf.goog legroups.com...
Hello Mark,

So fast on the trigger! What do you do, have a notification bell ring
whenever something is posted on this newsgroup? :P

Your solution worked perfectly... I'm used to regular ol' SQL and what
you've just shown me is amazing; I didn't know SQL had these
capabilities. rank, over and partition are new to me. Do you have any
book recommendations ?

Regards,

Anthony

On Apr 20, 9:49 am, markc...@hotmai l.com wrote:

If you are using SQL Server 2005, you can do this

with cte(location_id ,date_created,g rp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_create d) as date_created
from cte
group by location_id,grp
order by max(date_create d)

[Greg D. Moore \(Strider\)]

"Anthony Paul" <an**********@g mail.comwrote in message
news:11******** *************@y 80g2000hsf.goog legroups.com...

Hello Mark,

So fast on the trigger! What do you do, have a notification bell ring
whenever something is posted on this newsgroup? :P

I can't speak for Mark, but some of us have way too much time on our hands.
;-)

Your solution worked perfectly... I'm used to regular ol' SQL and what
you've just shown me is amazing; I didn't know SQL had these
capabilities. rank, over and partition are new to me. Do you have any
book recommendations ?

Look for books by Itzak Ben-Gan. (Inside Microsoft SQL Server 2005; T-SQL
Querying covers this. it's a island/gap problem.)

I remember the first time I saw him demo those functions (they're new in SQL
2005).

Some pretty incredible stuff.

>
Regards,

Anthony

On Apr 20, 9:49 am, markc...@hotmai l.com wrote:

>If you are using SQL Server 2005, you can do this

with cte(location_id ,date_created,g rp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_create d) as date_created
from cte
group by location_id,grp
order by max(date_create d)

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

[--CELKO--]

The usual design error is to have only one time in a row to capture
when an event started, then do horrible self-joins to get the duration
of the status change. Let me use a history table for price changes.
The fact to store is that a price had a duration:

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL
REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_dat e < end_date),
PRIMARY KEY (upc, start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price 0.0000),
etc.);

You actually needs more checks to assure that the start date is at
00:00 and the end dates is at 23:59:59.999 Hrs. You then use a
BETWEEN predicate to get the appropriate price.

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTA MP);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;

Look up the Rick Snodgrass book on Temporal Queries in SQL at the
University of Arizona website; it is a free download.