Querying for historical "spans of time"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Querying for historical "spans of time"

Author

Topic

Thp1111
Starting Member

3 Posts

Posted - 2002-12-11 : 17:03:57

Well I hope this is the right place for this question - and I think it's a stumper. We have a situation where historical information needs to be preserved for items (for example, an ID tag tracking time and attendance). As this itemis transferred from user to user (one guy quits, his tag is passed on to the next employee), this is recorded in the software. So Adam might have owned it on 8/13/02, but he quit, and it was given to Bil on 8/14/02.

We need to preserve this historical information. Here's the problem - creating a single SQL query that says: "Give me all users of a particular IDTag who were using this tag between 8/18 and 10/17"

Let's say there are five records for a particular tag, with the following values for columns TagOwner and TagDate:
Adam 7/31/02
Bill 8/14/02
Charles 9/03/02
David 10/16/02
Edgar 10/24/02

This means that the above query would return Bill, Charles, and David. The tricky one is Bill - because any query using TagDate as a parameter wouldn't include Bill, because the starting date, 8/18, is after the TagDate for Bill.

The real question is how do you deal with the concept of a "span" of time for a particular record. Another example is that a query from 10/15 to 10/17 would return Charles and David, whereas 10/16/17 would return only David.

This is a thorny problem for us - we've tried doing a bunch of things to no avail. Any ideas would be greatly appreciated.

Thank you,

Tony

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-11 : 17:32:09

What you really should do is add a column to your table to hold an explicit end-date, instead of relying on inference. What if a tag does not get reassigned right away? Edgar could've been fired yesterday, the day before, or a month ago, and no one has gotten his old tag yet. The query would not be able to assume he still has it, if no one else gets it after him; the same applies to David: he could've given it back any time between 10/16 and 10/24.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-11 : 17:34:27

declare @t table (tagID int, tagOwner varchar(10), tagdate datetime)

set nocount on
insert into @t values (1,'Adam','7/31/02')
insert into @t values (1,'Bill','8/14/02')
insert into @t values (1,'Charles','9/03/02')
insert into @t values (1,'David','10/16/02')
insert into @t values (1,'Edgar','10/24/02')
insert into @t values (2,'Jeff','4/30/02')
insert into @t values (2,'Pete','8/12/02')
insert into @t values (2,'Willy','10/11/02')
set nocount off

print 'First, get the date range each person had the tags for by deriving the end dates.'
print 'We will use an end date of 12/31/2050 for guys who still have the tag (last record'
print 'for each tag_Id): '
print ''

SELECT T1.TagID, T1.TagOwner, T1.TagDate as StartDate, ISNULL(DateAdd(d,-1,MIN(T2.TagDate)),'12/31/2050') as EndDate
FROM
@t T1
LEFT OUTER JOIN
@t T2
ON
T2.TagDate > T1.Tagdate AND
T2.TagID = T1.TagID
GROUP BY T1.TagID, T1.TagOwner, T1.TagDate

print ''
print ' Using the above data, if we want to know who had the tag for a given'
print ' date range (between @d1 and @d2) we can use the following criteria:'
print ''
print ' StartDate < @d2 and EndDate > @d1'
print ''
print ' So, for 8/18 through 10/17: '

declare @d1 datetime;
declare @d2 datetime;

set @d1 = '8/18/02';
set @d2 = '10/17/02';

SELECT * FROM
(
SELECT T1.TagID, T1.TagOwner, T1.TagDate as StartDate, ISNULL(DateAdd(d,-1,MIN(T2.TagDate)),'12/31/2050') as EndDate
FROM
@t T1
LEFT OUTER JOIN
@t T2
ON
T2.TagDate > T1.Tagdate AND
T2.TagID = T1.TagID
GROUP BY T1.TagID, T1.TagOwner, T1.TagDate
) A
WHERE
StartDate < @d2 and EndDate > @d1

- Jeff

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-11 : 17:38:36

I suspect that you're having problems because your data does not contain the end date of use of tags - ie
Adam actually had the tag from 7/31/02 until 8/14/02 - so you're first step is to build up that data:

--first set up the table with data
declare @tags table (id int identity, name nvarchar(50), startdate datetime)

insert into @tags (name, startdate)
select 'Adam',convert(datetime, '7/31/02')
insert into @tags (name, startdate)
select 'Bill',convert(datetime, '8/14/02')
insert into @tags (name, startdate)
select 'Charles',convert(datetime, '9/03/02')
insert into @tags (name, startdate)
select 'David',convert(datetime, '10/16/02')
insert into @tags (name, startdate)
select 'Edgar',convert(datetime, '10/24/02')

now - you can work out the start and endtime for each person

select a.name, a.startdate, (select min(startdate) from @tags where startdate > a.startdate) as enddate
from @tags a

now I declare some test dates - as per your question (I'm only doing this for clarity - you could embed these straight into the query)

declare @starttest datetime
declare @endtest datetime
set @starttest = convert(datetime, '8/18/02')
set @endtest = convert(datetime, '10/17/02')

Then you use this query as a subquery - but the trick is there are three conditions to check for:
1. the start date of tag ownership is between the test dates
2. the end date of tag ownership is between the test dates
3. the test dates are within the span of tag ownership

and here it is

select name
from
(select a.name, a.startdate, (select min(startdate) from @tags where startdate > a.startdate) as enddate
from @tags a ) b
where
( startdate between @starttest and @endtest )
or ( enddate between @starttest and @endtest)
or ( startdate < @starttest and enddate > @endtest)

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-11 : 18:35:43

or, I guess the easiest way might be:

SELECT TagOwner, TagDate
FROM
Tags
WHERE
TagDate BETWEEN @Start AND @End
OR
TagDate = (SELECT Max(TagDate) FROM Tags WHERE TagDate <= @Start)

Though I do think maybe storing end dates in your table could be a good idea for the reason's Rob mentioned.

- Jeff

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-11 : 18:41:51

sorry j - one of those 2 second things...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-11 : 22:14:51

no problem --- very similiar solutions! kind of freaky!

- Jeff

Thp1111
Starting Member

3 Posts

Posted - 2002-12-12 : 16:53:23

WOW! Thanks for the quick and varied replies - I'm going to try them to see which ones will work for me.

One comment on creating an end date - with no end date, there is no possibility of an overlap in ownership.

So if I have one IDTag assigned on 2/3/02 and another on 4/18/02 then I'm ok if the date changes (and it can for a variety of reasons) from 4/18 to 4/14.

But if we have a begin/end of 2/3/02 and 4/17/02 for the first IDTag and a begin/end of 4/18/02 and 5/12/02 for the second, then we have a problem if the 4/18/02 is changed to 4/14. We now have a situation that requires some (in my mind) crazy logic to have to adjust two dates now, since an IDTag could never be owned by two people at the same point in time.

But any ideas would be appreciated, since this issue of "ownership during a time period" does make our SQL life quite quite crazy.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-12 : 17:40:49

It all depends on what your data is - for example - if you're keeping track of who's using PCs in your organisation at any given time.

Normally, only one PC per person - and so your point is valid. But of course people can share - and as robvolk points out - people can go - leaving no ownership at all.

However - there is one way you could solve both issues. Consider a library - the database may hold book borrowings
rrb borrowed "Gurus Guide to SQL" on 24/10/2002 - but where was it before? "on the shelf" had it up until 24/10/2002.

In other words, I think you can circumvent robvolks suggestion by making sure you include a new owner "no one" - so that if someone leaves, you can include a record which states that from 30/10/2002 it was owned by no-one.

However - this leaves you with a more complex query (one that you couldn't resolve) - and that's the problem.

Then you just have to hope that no-one will ever be able to share tags - otherwise you'll have to re-design your table.

Hope that helps

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources