in a simple table query, I try to Get only repeat call that are between 15min and 3 days.
Code:
SELECT
DupeInRAW.SUBSCRIBER_NUMBER,
DupeInRAW.DEPT,
DupeInRAW.[Chat#DIRECTOR_NAME],
DupeInRAW.[Chat#OPS_MGR_NAME],
DupeInRAW.[Chat#TEAM_MGR_NAME],
DupeInRAW.[Chat#Agent_Name],
DupeInRAW.TECHNICAL_RESULT,
DupeInRAW.CHAT_DATE_MST AS date1,
DupeInRAW_1.CHAT_DATE_MST AS date2,
DateDiff("s",DupeInRAW.CHAT_DATE_MST,DupeInRAW_1.CHAT_DATE_MST) AS Diff
FROM DupeInRAW, DupeInRAW AS DupeInRAW_1
GROUP BY
DupeInRAW.SUBSCRIBER_NUMBER,
DupeInRAW.DEPT,
DupeInRAW.[Chat#DIRECTOR_NAME],
DupeInRAW.[Chat#OPS_MGR_NAME],
DupeInRAW.[Chat#TEAM_MGR_NAME],
DupeInRAW.[Chat#Agent_Name],
DupeInRAW.TECHNICAL_RESULT,
DupeInRAW.CHAT_DATE_MST,
DupeInRAW_1.CHAT_DATE_MST,
DateDiff("s",DupeInRAW.CHAT_DATE_MST,DupeInRAW_1.CHAT_DATE_MST),
DupeInRAW.SUBSCRIBER_NUMBER,
DupeInRAW.CHAT_DATE_MST
HAVING (
(
DateDiff("s",DupeInRAW.CHAT_DATE_MST,DupeInRAW_1.CHAT_DATE_MST)<259200
And
DateDiff("s",DupeInRAW.CHAT_DATE_MST,DupeInRAW_1.CHAT_DATE_MST)>900
)
AND
DupeInRAW.SUBSCRIBER_NUMBER=DupeInRAW_1.SUBSCRIBER_NUMBER
AND
DupeInRAW.CHAT_DATE_MST<DupeInRAW_1.CHAT_DATE_MST
)
ORDER BY DupeInRAW.CHAT_DATE_MST;