All Forums Database
CCSlice 36 posts Joined 03/14
16 Jul 2014
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.
 
 

dnoeth 4628 posts Joined 11/04
16 Jul 2014

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)

 

Dieter

CCSlice 36 posts Joined 03/14
17 Jul 2014

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

You must sign in to leave a comment.