CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloperplsqldeveloper
user3172075
user3172075

Reputation: 133

My query didn't work

I tried many time to get the right result but I'm always fail. I can't find what's wrong in my codes. I want also to distinct the create_time and close_time, so that the date will not repeat.Please help me.

This is the sample output that I need:

ID         NAME      CREATE_TIME     CREATEDCOUNT    CLOSE_TIME   CLOSEDCOUNT     

1          POS        01-JAN-14           1           01-JAN-14      1
2          EMAIL      02-JAN-14           10          02-JAN-14     10

THIS IS MY QUERY:

SELECT t.create_time, t.queue_id, q.name
FROM APP_ACCOUNT.OTRS_TICKET t
LEFT JOIN APP_ACOUNT.OTRS_QUEUE q
ON t.queue_id = q.id
WHERE t.CREATE_TIME BETWEEN SYSDATE -7 AND SYSDATE
GROUP BY trunc(t.create_time), t.queue_id, q.name
ORDER BY trunc(t.create_time), t_id, q.name;

THE TABLE SCHEMA FOR APP_ACCOUNT.OTRS_TICKET (some data)

 ID TITLE                         QUEUE_ID ... .... .... .... ....  ....  ....        CREATE_TIME   .... .... CLOSE_TIME ..... ..... 

 39 Cannot access the Zimbra Email 11      5     1     3   2  hcph  hcph  POS-generic 15-OCT-13       5    \N  15-OCT-13 5    Office 
 40 Slow WIFI Connection           21      5     1     3   2  hcph  hcph  POS-generic 15-OCT-13       5    \N  15-OCT-13 5    Office 
 41 Change phone number in IDM      7      5     1     3   2  hcph  hcph  hcphuser   15-OCT-13       5    \N 15-OCT-13  5    Office

TABLE SCHEMA FOR APP_ACCOUNT.OTRS_QUEUE (SOME DATA)

 ID      NAME
 58 Facilities and Procurement::Office Furniture Request
 59 Facilities and Procurement::Supplies Request ��� Marketing & Sales
 60 Facilities and Procurement::Supplies Request ��� Office Supplies
 2  Raw
 66 Procurement::Supplies Request - Office Supplies

Upvotes: 0

Views: 118

Answers (1)

San
San

Reputation: 4538

Check this query:

SELECT t.queue_id, 
       q.name,  
       trunc(t.create_time), 
       count(t.create_time) createcount,  
       trunc(t.close_time), 
       count(t.close_time) closedcount, 
  FROM APP_ACCOUNT.OTRS_TICKET t
       LEFT JOIN APP_ACOUNT.OTRS_QUEUE q
       ON t.queue_id = q.id
 WHERE t.CREATE_TIME BETWEEN SYSDATE -7 AND SYSDATE
 GROUP BY trunc(t.create_time), t.queue_id, q.name, trunc(t.close_time);

If this doesn't solves your problem then give the details of both tables and some sample data because I created this query on various assumptions.

Upvotes: 2

Related Questions