Home » SQL & PL/SQL » SQL & PL/SQL » Frequency Distribution with future date place holders (SQL)
| Frequency Distribution with future date place holders [message #636763] |
Fri, 01 May 2015 14:50  |
 |
fluffycone
Messages: 27
Registered: June 2011
|
Junior Member |
|
|
I need to create a resultset like the following. It is a two column result set of the date and the number of people who completed a task on that date.
Date, Count
Mon Apr-27,3
Tue Apr-28,58
Wed Apr-29,40
Thu Apr-30,0
Fri May-1,14
Sat May-2,0
...
Sat Jun-27,0
Sun Jun-28,0
Mon Jun-29,0
Tue Jun-30,0
My existing query looks like this:
select to_char(completion_date, 'Dy, Mon-DD') as "Date",
count(*) as "Count"
from sat2015_imp_v
where completion_date is not null
group by completion_date
order by completion_date
;
My table looks like this:
Emp_ID, completed (Y,N), Completion_date
1001, N, (null)
1002, Y, April 28, 2015
1003, N, (null)
1004, N, (null)
1005, N, (null)
1005, Y, April 29, 2015
...
The problem is that the result set only includes dates where an employee actually completed the task. So instead of getting rows with future dates as place holders and a value of 0, I get something like the following. Notice that Thursday is missing because no one completed the task on that date and the value should be 0.
Date, Count
Mon Apr-27,3
Tue Apr-28,58
Wed Apr-29,40
Fri May-1,14
The result set is used to generate a Frequency Distribution Chart/Graph.
Let me know if I need to provide more information.
|
|
|
|
|
|
|
|
| Re: Frequency Distribution with future date place holders [message #637029 is a reply to message #636768] |
Thu, 07 May 2015 15:24  |
|
fluffycone
Messages: 27
Registered: June 2011
|
Junior Member |
|
|
Sorry, my bad. Been away for a while performing "Other duties as assigned".
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
See attachment for create Table Statement and Insert Statements. I left out the emp_id column.
CREATE TABLE "SAT2015"
( "MODULE_1" VARCHAR2(255 BYTE),
"MODULE_1_COMPLETION_DATE" DATE,
"MODULE_2" VARCHAR2(255 BYTE),
"MODULE_2_COMPLETION_DATE" DATE
) ;
Column Descriptions:
Module_1: values Y OR N
Module_1_Completion_date: Date module was completed
Module_2: values Y OR N
Module_2_Completeion_date: Date module was completed
For staff members to be finished, column Module_1 must be 'Y' and column Module_2 must be 'Y'.
The only solution that I can think of is to create a table with one date column and generate a row from the start date to the end date. Is there a clear solution to do this dynamically, without having to physically create the table?
I did find this solution, but I can't figure it out past "select to_char()..." The "...connect by level..." stuff has me confused about how it works.
SQL> def date_start = '13/11/2010'
SQL> def date_end = '22/11/2010'
SQL> with
2 data as (
3 select to_date('&date_start', 'DD/MM/YYYY') date1,
4 to_date('&date_end', 'DD/MM/YYYY') date2
5 from dual
6 )
7 select to_char(date1+level-1, 'DD/MM/YYYY') the_date
8 from data
9 connect by level <= date2-date1+1
10 /
THE_DATE
----------
13/11/2010
14/11/2010
15/11/2010
16/11/2010
17/11/2010
18/11/2010
19/11/2010
20/11/2010
21/11/2010
22/11/2010
I hope this clears it up a little better.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 10:15:52 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
