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,
AnthonyIf 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@.hotmail.com wrote:
Quote:
Originally Posted by
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)
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...@.googlemail.comwrote:
Quote:
Originally Posted by
On 20.04.2007 15:49, markc...@.hotmail.com wrote:
>
Quote:
Originally Posted by
If you are using SQL Server 2005, you can do this
>
Quote:
Originally Posted by
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)
>
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|||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@.googlemail.comwrote in message
news:58rv9iF2ikhoqU1@.mid.individual.net...
On 20.04.2007 15:49, markc600@.hotmail.com wrote:
Quote:
Originally Posted by
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)
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:
Quote:
Originally Posted by
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|||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...@.hotmail.com wrote:
Quote:
Originally Posted by
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)
--
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@.gmail.comwrote in message
news:1177078960.725463.69600@.y80g2000hsf.googlegro ups.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...@.hotmail.com wrote:
Quote:
Originally Posted by
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)
"Anthony Paul" <anthonypaulo@.gmail.comwrote in message
news:1177078960.725463.69600@.y80g2000hsf.googlegro ups.com...
Quote:
Originally Posted by
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.
;-)
Quote:
Originally Posted by
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.
Quote:
Originally Posted by
>
Regards,
>
Anthony
>
On Apr 20, 9:49 am, markc...@.hotmail.com wrote:
Quote:
Originally Posted by
>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)
>
>
--
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.
