473,837 Members | 1,550 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Interesting SQL problem : How to track movement history

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

Apr 20 '07 #1
9 2482
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)
Apr 20 '07 #2
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
Apr 20 '07 #3
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

Apr 20 '07 #4
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

Apr 20 '07 #5
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
Apr 20 '07 #6
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)

Apr 20 '07 #7
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)

Apr 20 '07 #8


"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
Apr 21 '07 #9
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.

Apr 21 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1631
by: Ezekiël | last post by:
hello, I was wondering how to track all changes on tables by using some sort of a history table. What i would like is a generic history table where i can see who updated, inserted, deleted or executed(stored procedures, triggers) what value in what table with a date when it was occured. Could somebody help me with this?
1
1529
by: John | last post by:
I have a table that contains a product's forecasted revenue for each month. So: tbl_Forecast Forecast_ID Product_ID Year Month Revenue What's a good table design to track different versions of a
1
1589
by: Gomez | last post by:
I have set up two tables ****current and ****history. The history table contains all the fields which I want to track changes on. The current table contains just the most recent information from those fields (history not important). The entry forms are set up with a query to access each field from it's correct table. I have a 2 field primary key in the history table (field 1 is a facility ID, and field 2 is the date of inspection). ...
3
2261
by: RR | last post by:
We have cards that are numbered consecutively. These cards are given out to different people in different sized batches. One group might get 5, the next group might get 20. What is a good way to set up to keep track of which numbered cards are given out, and to who?
2
2507
by: Kevin K | last post by:
Hi, I'm having a problem with extracting text from a Word document using StreamReader. As I'm developing a web application, I do NOT want the server to make calls to Word. I want to simply open the Word document via StreamReader and extract the text. Here's the problem, the users insist on leaving the "Track Changes" features on. Because of this, the raw text portion of the file contains the change history. I don't want the...
16
10948
by: dfaber | last post by:
Hi all, I have been searching for a keyboard and mouse tracker on linux. I've read solutions (watch at sourceforge) which look at /proc/interrupts to check keyboard or mouse activity. I also read one post where "watch" seems to have difficulty tracking usb keyboards and mice. So, I'm out of ideas here. My goal are: 1. Check keyboard activity. I'm not interested in logging which keys are pressed or record them.
27
2344
by: Frederick Gotham | last post by:
I thought it might be interesting to share experiences of tracking down a subtle or mysterious bug. I myself haven't much experience with tracking down bugs, but there's one in particular which comes to mind. I was writing usable which dealt with strings. As per usual with my code, I made it efficient to the extreme. One thing I did was replace, where possible, any usages of "strlen" with something like: struct PtrAndLen { char *p;
4
6981
by: mike | last post by:
I have the opportunity to rescue a project that uses a mouse to sense the relative position of a machine. The hardware is built...just needs to be programmed. Stop snickering!!! I didn't do it...I just gotta fix it. I need to make some calculations on the measurements and VB6 is my language. Yes, the system mouse will corrupt the measurement, but it's an auditing function and that's acceptable.
32
2373
by: Andy | last post by:
To further follow up on my last post regarding the docmd.quit vs. Application.quit using access 2007, I noticed that docmd.quit will correctly compact the database (program file) if you have the "Compact on Close" option set for the current database. However, even with Compact on Close set, if you use the Application.Quit acQuitSaveNone, the compacting will not fire. This behavior actually seems desirable, as you can now make a program...
0
9851
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9695
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10902
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10583
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10286
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9420
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5863
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4060
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3128
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.