on 04-21-2020 9:26 AM
hi Experts,
I have a requirement, where i need to find the total count of records that matches few conditions.
I have two tables 1) CALL_REGISTER 2) CALL_RESOLVED.
When a service request is made on call, then we make a entry with call_no as key at CALL_REGISTER Table
Once that service request is resolved, then also we make a entry with call_no as key at CALL_RESOLVED Table
Now we have a requirement where i need to get the count of records for a Dealer xxx, with call Category i.e. CATEG as C1
and having time difference of less than 24 hours.
Time difference is considered based on status .
When status is Resolved, then consider Time difference in hours between call creation Date Time and Call Resolved Date Time. Where call resolved date is captured in JOB_DONE_DATE in CALL_RESOLVED TABLE.
When status is not resolved, then consider Time Difference in hours between Call Creation Date Time and Current Time Stamp.
Below is my Query , but am getting error as shown below after the code block.
SELECT COUNT(*)OVER() AS TOTAL_C1_COUNT, A.STATUS FROM CALL_REGISTER A LEFT JOIN CALL_RESOLVED B ON A.CALL_NO = B.CALL_NO
WHERE A.DEALER_CODE IN ('XXX') AND A.CATEG IN ('C1') AND
CASE A.STATUS
WHEN 'RESOLVED'
THEN
SECONDS_BETWEEN (TO_TIMESTAMP(CONCAT(CONCAT(A.CALL_CREATION_DT,' '),A.CALL_CREATION_TIME)),TO_TIMESTAMP(CONCAT(CONCAT(B.JOB_DONE_DATE,' '),B.JOB_DONE_TIME)))/(24*3600) BETWEEN 0 AND 24
ELSE
(SECONDS_BETWEEN (TO_TIMESTAMP(CONCAT(CONCAT ( A.CALL_CREATION_DT,' '),A.CALL_CREATION_TIME ) ),CURRENT_TIMESTAMP)/(24*3600)) BETWEEN 0 AND 24
END
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "BETWEEN": line 6 col 165 (at pos 400)
Kindly do the needful.
Sorry , didn't get idea at first.
Answer is to put between out of case, case only return value depending on condition, comparison is not part of what case do
SELECT COUNT(*)OVER() AS TOTAL_C1_COUNT, A.STATUS FROM CALL_REGISTER A LEFT JOIN CALL_RESOLVED B ON A.CALL_NO = B.CALL_NO
WHERE A.DEALER_CODE IN ('XXX') AND A.CATEG IN ('C1') AND
CASE A.STATUS
WHEN 'RESOLVED'
THEN
SECONDS_BETWEEN (TO_TIMESTAMP(CONCAT(CONCAT(A.CALL_CREATION_DT,' '),A.CALL_CREATION_TIME)),TO_TIMESTAMP(CONCAT(CONCAT(B.JOB_DONE_DATE,' '),B.JOB_DONE_TIME)))/(24*3600)
ELSE
(SECONDS_BETWEEN (TO_TIMESTAMP(CONCAT(CONCAT ( A.CALL_CREATION_DT,' '),A.CALL_CREATION_TIME ) ),CURRENT_TIMESTAMP)/(24*3600))
END
BETWEEN 0 AND 24
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Please help me on query like
SELECT "DOC_ENTRY" , "DOC_TYPE", "IRN_NO", "ACK_NO" , "ACK_DATE", "QR_CODE_IMAGE"
from "@UTL_E_INV_TEST"
WHERE "DOC_ENTRY"= "DOC_TYPE"
I want to match with DocEntry with DocType
Help me on where condition where it's match with DOCENTRY AND DOCTYPE.
For ex.
AR Invoice DocEntry 111
AR Credit Memo DocEntry 110
How to match it? Please help me on Join or where condition.
Hi
Try this Query and update what is the result
SELECT COUNT(*) AS TOTAL_C1_COUNT, A.STATUS ,
CASE A.STATUS = 'RESOLVED' THEN
SECONDS_BETWEEN (TO_TIMESTAMP(CONCAT(CONCAT(A.CALL_CREATION_DT,' '),
A.CALL_CREATION_TIME)),
TO_TIMESTAMP(CONCAT(CONCAT(B.JOB_DONE_DATE,' '),
B.JOB_DONE_TIME)))/(24*3600)
ELSE 0 END AS RESOLVED_TIME,
CASE A.STATUS != 'RESOLVED' THEN
(SECONDS_BETWEEN (TO_TIMESTAMP(CONCAT(CONCAT ( A.CALL_CREATION_DT,' '),
A.CALL_CREATION_TIME ) ),
CURRENT_TIMESTAMP)/(24*3600))
ELSE 0 END AS WORKING_TIME
FROM CALL_REGISTER A LEFT JOIN CALL_RESOLVED B ON A.CALL_NO = B.CALL_NO
WHERE A.DEALER_CODE IN ('XXX') AND A.CATEG IN ('C1');
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.