Help with query...

[tonyguate]

I want to get some information from a database in wich there are events of certain dates that I want to count and group by the date for several users.

Until now I have made the query for counting and grouping one user.

SELECT
emp.first_name || ' ' || emp.last_name AS Employee_Name,
to_char(e.start_date_time,'MM/DD/YYYY') as DATE,
count(e.start_date_time) COUNT
FROM
event e,
alignment al,
employee emp,
customer c,
event_signature es
WHERE
e.start_date_time BETWEEN TO_DATE('8/13/2007','MM/DD/YYYY') AND TO_DATE ('8/24/2007 23:59:59','MM/DD/YYYY HH24:MI:SS')
AND e.alignment_id = al.alignment_id
AND e.employee_id = emp.employee_id
AND e.customer_id = c.customer_id
AND upper(e.event_type) = 'C'
AND signature_type(+) = 'DROP'
AND emp.employee_id = 160
AND e.event_id = es.event_id (+)
AND e.event_sub_type NOT LIKE 'NV'
GROUP BY
to_char(e.start_date_time,'MM/DD/YYYY'),
emp.first_name || ' ' || emp.last_name


What I get from this:

But I want to get them all users together in one table in separate columns.

For example:

Is this Posible?
And how can I make it to work?

Please help!!

[debasisdas]

for this matrix report u need to use DECODE with COUNT function.

[tonyguate]

for this matrix report u need to use DECODE with COUNT function.

Can you please explain me how could I use this in my query, I've been trying to make it work with decode and count with no luck.

Thanks for your reply

[qhjghz]

I want to get some information from a database in wich there are events of certain dates that I want to count and group by the date for several users.

Until now I have made the query for counting and grouping one user.

SELECT
emp.first_name || ' ' || emp.last_name AS Employee_Name,
to_char(e.start_date_time,'MM/DD/YYYY') as DATE,
count(e.start_date_time) COUNT
FROM
event e,
alignment al,
employee emp,
customer c,
event_signature es
WHERE
e.start_date_time BETWEEN TO_DATE('8/13/2007','MM/DD/YYYY') AND TO_DATE ('8/24/2007 23:59:59','MM/DD/YYYY HH24:MI:SS')
AND e.alignment_id = al.alignment_id
AND e.employee_id = emp.employee_id
AND e.customer_id = c.customer_id
AND upper(e.event_type) = 'C'
AND signature_type(+) = 'DROP'
AND emp.employee_id = 160
AND e.event_id = es.event_id (+)
AND e.event_sub_type NOT LIKE 'NV'
GROUP BY
to_char(e.start_date_time,'MM/DD/YYYY'),
emp.first_name || ' ' || emp.last_name


What I get from this:

But I want to get them all users together in one table in separate columns.

For example:

Is this Posible?
And how can I make it to work?

Please help!!

Do you know beforehand what are the possible USER_IDs present, then you can do use the count & decode easily.

[tonyguate]

Do you know beforehand what are the possible USER_IDs present, then you can do use the count & decode easily.

Yes I know before hand the user_id's but the problem is that I am not searching in only one table for the id's and that is where I get stuck with the decode function, I haven't use it before so I don't know how the syntax for searching in several tables for several data is like.
I've been looking at the decode syntax and definition and I think this is not the function that I need. I think that decode returns only one value and I need more than one value.
If someone know for sure that my matrix can be a result of the decode function please tell me how I can use it.

Thanks for your reply

[amitpatel66]

Hi,

The below query wil give me the count of no of employees joined under a particular manager on a particular day:

SELECT DISTINCT
      e.HIREDATE,
      (SELECT COUNT(empno) FROM emp WHERE MGR = 1 AND hiredate=e.hiredate GROUP BY HIREDATE) MGR1,
      (SELECT COUNT(empno) FROM emp WHERE MGR = 2 AND hiredate=e.hiredate GROUP BY HIREDATE) MGR2
FROM
      emp e
 

OUTPUT:

HIREDATE    MGR1    MGR2
1/1/0005       4     8
8/28/2007       4    
8/29/2007    3           
 

The performance might be on slower side if the USERID's in your case are more because u will be giving subquery for each and every USERID.