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

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

with cte(location_id,date_created,grp)
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_created) as date_created
from cte
group by location_id,grp
order by max(date_created)

On 20.04.2007 15:49, markc600 (AT) hotmail (DOT) com wrote:
I'd do

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

Am I missing something?

robert

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... (AT) googlemail (DOT) com> wrote:

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" <shortcutter (AT) googlemail (DOT) com> wrote

On 20.04.2007 15:49, markc600 (AT) hotmail (DOT) com wrote:
I'd do

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

Am I missing something?

robert

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

robert

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... (AT) hotmail (DOT) com wrote:

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" <anthonypaulo (AT) gmail (DOT) com> wrote

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... (AT) hotmail (DOT) com wrote:

"Anthony Paul" <anthonypaulo (AT) gmail (DOT) com> wrote

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


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.


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

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_date < 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_TIMESTAMP);

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.