| How to get the right information suggested about Date [message #636868] |
Mon, 04 May 2015 11:18  |
 |
sbouhila
Messages: 7
Registered: May 2015
Location: Morocco
|
Junior Member |
|
|
I'm trying through this sql query to retrieve rows in accordance with those restrictions:
The date of the Proprietaire column must be less than or equal to the date of Statut column, knowing that both of them are joined to the same Table Audit_Log; from where they get the information about the right time where the change of date occurred.
I need to enhance this query in order to get what i want and if someone has an other suggestion for me, it will be a pleasure for me.
tried query :
SELECT ONE.ID, ONE.Num_QC_EDF, ONE.Statut, TWO.Proprietaire, ONE.ChangeDate
FROM
(SELECT B.BG_BUG_ID as ID, B.BG_USER_15 as Num_QC_EDF
,AP.AP_NEW_VALUE as Statut, AL.AU_TIME as ChangeDate
FROM BUG B
INNER JOIN AUDIT_LOG AL ON AL.AU_ENTITY_ID = B.BG_BUG_ID
INNER JOIN AUDIT_PROPERTIES AP ON AL.AU_ACTION_ID = AP.AP_ACTION_ID
AND AL.AU_ENTITY_TYPE = 'BUG'
AND AL.AU_ACTION_ID= AP.AP_ACTION_ID
AND AP_FIELD_NAME= 'BG_STATUS'
AND AP_NEW_VALUE= 'TU leger OK')ONE,
(SELECT B.BG_BUG_ID as ID, B.BG_USER_15 as Num_QC_EDF,
AP.AP_NEW_VALUE as Proprietaire, AL.AU_TIME as ChangeDate
FROM BUG B
INNER JOIN AUDIT_LOG AL ON AL.AU_ENTITY_ID = B.BG_BUG_ID
INNER JOIN AUDIT_PROPERTIES AP ON AL.AU_ACTION_ID = AP.AP_ACTION_ID
AND AL.AU_ENTITY_TYPE = 'BUG'
AND AL.AU_ACTION_ID= AP.AP_ACTION_ID
AND AP.AP_FIELD_NAME = 'BG_RESPONSIBLE')TWO,
BUG B
WHERE ONE.ID= TWO.ID AND TWO.ID= B.BG_BUG_ID
AND ONE.Num_QC_EDF= TWO.Num_QC_EDF AND TWO.Num_QC_EDF= B.BG_USER_15
AND TWO.ChangeDate <= ONE.ChangeDate
ORDER BY BG_BUG_ID, BG_USER_15 :(
*BlackSwan added {code} tags. Please do so yourself in the future.
[Updated on: Mon, 04 May 2015 11:22] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to get the right information suggested about Date [message #636940 is a reply to message #636925] |
Wed, 06 May 2015 04:07  |
|
sbouhila
Messages: 7
Registered: May 2015
Location: Morocco
|
Junior Member |
|
|
I sent in image the set of rows expected from the query.
Well, the set of rows returned must respect those restrictions:
the column Proprietaire must contain data whose changedate is less than or equal to changedate of status column . This is my issue with the query.
How can i do this comparison while both columns ( Proprietaire and status) refer to same column AU_TIME from AUDIT_LOG table to get the changedate ???
I think now my question is very clear .
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
