Home » SQL & PL/SQL » SQL & PL/SQL » Need help to get the right query to generate report (Oracle)
Need help to get the right query to generate report [message #393256] Fri, 20 March 2009 19:28 Go to next message
Messages: 1
Registered: March 2009
Junior Member

I need help to write a query to get the following output.

The query result I am looking for is the number of defects submitted in a month and the number of defects closed in a month.

The database table contains the following data:

DefectId | SubmitDate | ClosedDate
1 | JAN | NULL
2 | JAN | JAN
3 | JAN | FEB
4 | JAN | APR
5 | FEB | APR

I need output in the following manner:

Month | Count(SubmitDate) | Count(ClosedDate)
JAN | 4 | 1
FEB | 1 | 1
MAR | 0 | 0
APR | 0 | 2

[Updated on: Fri, 20 March 2009 19:44]

Report message to a moderator

Re: Need help to get the right query [message #393257 is a reply to message #393256] Fri, 20 March 2009 19:45 Go to previous messageGo to next message
Messages: 25568
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.

Please understand that we don't do homework assignments.

It appears you might need to construct a "calendar table".

Please do not hesitate to search this forum for previous solutions to this FAQ.

Re: Need help to get the right query to generate report [message #393284 is a reply to message #393256] Sat, 21 March 2009 01:36 Go to previous message
Michel Cadot
Messages: 65135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "row generator" and "calendar".

Post a Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).


Previous Topic: how to import SP from...
Next Topic: Beginner: How to write this trigger?
Goto Forum:

Current Time: Wed Aug 16 23:06:05 CDT 2017

Total time taken to generate the page: 0.09846 seconds