How to Query between two tables involvng timestamps

Database
Enthusiast

How to Query between two tables involvng timestamps

Hi,

I want to know how I should write this query between two tables:  The first is a document table with all the names of attachment files.  It's structure would be as follows:

TBL.DOCUMENT

**************

DOC_ID (DECIMAL 38,0) NOT NULL 

DOC_TMSTMP (TIMESTAMP (6) NOT NULL)  ex.  06/17/2014 21:21:16.242000

DOC_FILE_NM (VARCHAR(400) NOT NULL)  ex. Interrogation_Report.doc

ENTITY_KEY  (VARCHAR (8)) NOT NULL) ex. 14564664

TBL.WORK_ACTIONS

*****************

WRK_ACTION_ID  (DECIMAL 38,0) NOT NULL

ENTITY_KEY (VARCHAR (8)) NOT NULL     

LOG_TMSTMP (TIMESTAMP (6) NOT NULL)

SELECT * 
FROM
 
(
SELECT
                   DOC_ID
                 , DOC_TMSTMP
                 , DOC_FILE_NM
                 , ENTITY_KEY
FROM          TBL.DOCUMENTS
)   AS           A        
,
(
SELECT
                  WRK_ACTION_ID
                 , LOG_TMSTMP
FROM          TBL_WORK_ACTIONS
WHERE      WRK_ACTION_ID = '1234'
)    AS         B

WHERE     A.ENTITY_KEY = B.ENTITY_KEY 

The above code seems to be the simplest and surest way to solve to return results BUT... the Timestamps are not the same.  For example if

DOC_ID = 141

DOC_TMSTMP 07/14/2014 14:14:21.386000

DOC_FILE_NM = New_Accounts_Setup.doc

ENTITY_KEY = 131516

and

DOC_ID = 102

DOC_TMSTMP 07/01/2014 08:05:58.896000

DOC_FILE_NM = Prospective_Client.doc

ENTITY_KEY = 131516

and

DOC_ID = 137

DOC_TMSTMP 07/14/2014 12:01:18.951000

DOC_FILE_NM = AP.doc

ENTITY_KEY = 131516

and

WRK_ACTION_ID = 23132

ENTITY_KEY = 131516

LOG_TMSTMP = 07/14/2014 14:15:56.596000

and

WRK_ACTION_ID = 21018

ENTITY_KEY = 131516

LOG_TMSTMP = 07/01/2014 08:06:39.745000

and

WRK_ACTION_ID = 21018

ENTITY_KEY = 131516

LOG_TMSTMP = 07/14/2014 12:02:01.110000

By business rule a document is created after the work action is taken.  However since the Timestamps are different how do I link the records so that Document_Timestamp is not greater than Work_Action LOG_TMSTMP and within the same day?

Thanks in advance for your suggestions.

2 REPLIES
Senior Apprentice

Re: How to Query between two tables involvng timestamps

What's the actual Primary Key of your tables?

You need to add a condition like this:

WHERE A.ENTITY_KEY = B.ENTITY_KEY
AND DOC_TMSTMP BETWEEN LOG_TMSTMP AND LOG_TMSTMP + INTERVAL '1' DAY

or

WHERE A.ENTITY_KEY = B.ENTITY_KEY
AND CAST(DOC_TMSTMP AS DATE) = CAST(LOG_TMSTMP AS DATE)
Enthusiast

Re: How to Query between two tables involvng timestamps

No primary keys in this table (believe it or not).   There are FK which relate to a User table.