Match timed events

/*
Anyone?
An Object is. An object has an owner.
Events happen to an object at given times
I would like to list out all events for an object
and show who was the owner of it at that time.
However my mind is a blank!
My legacy data structure looks something like this
*/
CREATE TABLE EVENT (
EVENTID Int IDENTITY(1,1) PRIMARY KEY,
OBJECTID CHAR(4),
EVENTDATE CHAR(8)
)
CREATE TABLE OBJECTOWNER (
OBJECTID CHAR(4) NOT NULL,
OWNERID CHAR(4) NOT NULL,
OWNERFROM CHAR(8) NOT NULL
)
ALTER TABLE [OBJECTOWNER] WITH NOCHECK ADD
CONSTRAINT [PK_OBJECTOWNER] PRIMARY KEY CLUSTERED
(
[OBJECTID],
[OWNERID],
[OWNERFROM]
)
INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '19920101')
INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20030601')
INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20060122')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'A111', '19801201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'A692', '19921201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'B386', '20011201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'F279', '20041201')
INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
'F111', '20060310')
/*I presume I need the MAX OWNERFROM value that less then or equal to the
EventDate, along the lines of*/
SELECT TOP 1 MAX(OwnerFrom) As 'Most Recent owner date', OwnerID AS 'Most
recent Owner' from ObjectOwner
WHERE ObjectID = '1234' AND OwnerFrom <= '19971207' group by OwnerID
/*Which gives the owner of the object for a given date,
and I am sure I need to join in a select returning the max <=...
I could do it by inserting into a temp table and updating
but I though there must be a more elegant solution
The listing I'd like to retrive is
EVENTID, OBJECTID, EVENTDATE, OWNERID,
EVENTID OBJECTID EVENTDATE OWNERID
-- -- -- --
1 1234 19920101 A111
2 1234 20050601 B386
3 1234 20060122 F279
Any help would be greatly appreciated.
Cheers!
Simon
*/Different ways...
One with correlated subqueries in SELECT clause :
SELECT EVENTID, OBJECTID, EVENTDATE,
(SELECT OWNERID
FROM OBJECTOWNER O
WHERE O.OBJECTID = E.OBJECTID
AND OWNERFROM = (SELECT MAX(OWNERFROM)
FROM OBJECTOWNER O2
WHERE OWNERFROM <= E.EVENTDATE
AND O2.OBJECTID = E.OBJECTID)) AS
OWNERID_AT_TIME_EVENT
FROM EVENT E
A +
Simon a crit :
> /*
> Anyone?
> An Object is. An object has an owner.
> Events happen to an object at given times
> I would like to list out all events for an object
> and show who was the owner of it at that time.
> However my mind is a blank!
> My legacy data structure looks something like this
> */
>
> CREATE TABLE EVENT (
> EVENTID Int IDENTITY(1,1) PRIMARY KEY,
> OBJECTID CHAR(4),
> EVENTDATE CHAR(8)
> )
> CREATE TABLE OBJECTOWNER (
> OBJECTID CHAR(4) NOT NULL,
> OWNERID CHAR(4) NOT NULL,
> OWNERFROM CHAR(8) NOT NULL
> )
> ALTER TABLE [OBJECTOWNER] WITH NOCHECK ADD
> CONSTRAINT [PK_OBJECTOWNER] PRIMARY KEY CLUSTERED
> (
> [OBJECTID],
> [OWNERID],
> [OWNERFROM]
> )
> INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '19920101')
> INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20030601')
> INSERT INTO EVENT (OBJECTID, EVENTDATE) VALUES ('1234', '20060122')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'A111', '19801201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'A692', '19921201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'B386', '20011201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'F279', '20041201')
> INSERT INTO OBJECTOWNER (OBJECTID, OWNERID, OWNERFROM) VALUES ('1234',
> 'F111', '20060310')
>
> /*I presume I need the MAX OWNERFROM value that less then or equal to the
> EventDate, along the lines of*/
> SELECT TOP 1 MAX(OwnerFrom) As 'Most Recent owner date', OwnerID AS 'Most
> recent Owner' from ObjectOwner
> WHERE ObjectID = '1234' AND OwnerFrom <= '19971207' group by OwnerID
> /*Which gives the owner of the object for a given date,
> and I am sure I need to join in a select returning the max <=...
> I could do it by inserting into a temp table and updating
> but I though there must be a more elegant solution
>
> The listing I'd like to retrive is
> EVENTID, OBJECTID, EVENTDATE, OWNERID,
> EVENTID OBJECTID EVENTDATE OWNERID
> -- -- -- --
> 1 1234 19920101 A111
> 2 1234 20050601 B386
> 3 1234 20060122 F279
> Any help would be greatly appreciated.
> Cheers!
> Simon
> */
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||SQLpro [MVP] wrote:
> Different ways...
> One with correlated subqueries in SELECT clause :
>
> SELECT EVENTID, OBJECTID, EVENTDATE,
> (SELECT OWNERID
> FROM OBJECTOWNER O
> WHERE O.OBJECTID = E.OBJECTID
> AND OWNERFROM = (SELECT MAX(OWNERFROM)
> FROM OBJECTOWNER O2
> WHERE OWNERFROM <= E.EVENTDATE
> AND O2.OBJECTID = E.OBJECTID)) AS
> OWNERID_AT_TIME_EVENT
> FROM EVENT E
> A +
Much appreciated, that is exactly what I needed. I ahd just the (SELECT
MAX(OWNERFROM)
> FROM OBJECTOWNER O2
> WHERE OWNERFROM <= E.EVENTDATE
> AND O2.OBJECTID = E.OBJECTID))
JOINed as was getting nowhere fast.
Cheers!