Listed below is a query that works fine. What I would like to do is remove the date range section and just add the entered field in one of the select statements and display the entered field in the query results. I know this will return all the rows but I want to capture the entered field in order to use this query in a report tool which will allow me to implement a date range function from the reporting tool. Right now when I try to define the report paremeters with the reporting tool, all I see is the root, description and sum(a.ctn) as columns available. So If I was able to add the entered date field in this query, then it would allow me to use the report paremters to specify date ranges. Can anyone help me with this?
select b.root, b.description, sum(a.cnt)
FROM
(select TICKETID,count(*) cnt from TICKET
where ENTERED between to_date('01-jan-2006') and to_date('15-feb-2006') group by ticketid) a,
(select id, description, cat_level,connect_by_root description root from ticket_category_tmp connect by prior id=parent_cat_code) b
WHERE a.TICKETID=b.id and b.cat_level<=1
GROUP BY root, description
Order By root
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Is this what you are looking for?
select b.root, b.description, sum(a.cnt), a.entered_date
FROM
(select TICKETID,count(*) cnt, ENTERED as entered_date
from TICKET
group by ticketid) a,
(select id, description, cat_level,connect_by_root description root from ticket_category_tmp connect by prior id=parent_cat_code) b
WHERE a.TICKETID=b.id and b.cat_level<=1
GROUP BY root, description
Order By root
Sorry, try this one:
select b.root, b.description, sum(a.cnt), a.entered_date
FROM
(select TICKETID,count(*) cnt, MAX(ENTERED) as entered_date
from TICKET
group by ticketid) a,
(select id, description, cat_level,connect_by_root description root from ticket_category_tmp connect by prior id=parent_cat_code) b
WHERE a.TICKETID=b.id and b.cat_level<=1
GROUP BY root, description
Order By root
Add another MAX function to the outer select. This should work.
select b.root, b.description, sum(a.cnt), MAX(a.entered_date) as entered_date
FROM
(select TICKETID,count(*) cnt, MAX(ENTERED) as entered_date
from TICKET
group by ticketid) a,
(select id, description, cat_level,connect_by_root description root from ticket_category_tmp connect by prior id=parent_cat_code) b
WHERE a.TICKETID=b.id and b.cat_level<=1
GROUP BY root, description
Order By root
Yes, if you run the script in sqlplus:
select b.root, b.description, sum(a.cnt), MAX(a.entered_date) as entered_date
FROM
(select TICKETID,count(*) cnt, MAX(ENTERED) as entered_date
from TICKET
where ENTERED between to_date('&dt1','DD-MON-YYY
group by ticketid) a,
(select id, description, cat_level,connect_by_root description root from ticket_category_tmp connect by prior id=parent_cat_code) b
WHERE a.TICKETID=b.id and b.cat_level<=1
GROUP BY root, description
Order By root
Geotiger,
Can you convert this query to work in SQL Server(T-SQL)?
select b.root, b.description, sum(a.cnt), MAX(a.entered_date) as entered_date
FROM
(select TICKETID,count(*) cnt, MAX(ENTERED) as entered_date
from TICKET
where ENTERED between to_date('&dt1','DD-MON-YYY
group by ticketid) a,
(select id, description, cat_level,connect_by_root description root from ticket_category_tmp connect by prior id=parent_cat_code) b
WHERE a.TICKETID=b.id and b.cat_level<=1
GROUP BY root, description
Order By root
Business Accounts
Answer for Membership
by: Omega002Posted on 2006-06-02 at 12:47:51ID: 16819680
This is how I would like the query to look once executed:
root description sum(a.cnt) entered
------- -------------- ------------- -----------
Admin Server Down 2 01/1/2006
Admin Exchange Down 1 01/2/2006
right now it does this:
root description sum(a.cnt)
------- -------------- -------------
Admin Server Down 2
Admin Exchange Down 1
PS. the entered field is in the ticket table.