generate calender with SQL or PL/SQL

15 pts.
Tags:
SQL
I am trying to create a calender with people in a department, which must be printed monthly by the department head. The user must enter the month and department and calender must be generated and printed. I want to know if this is possible with SQL. I tried a few options, but cannot get the dates (from 1 to end of month) to be displayed as column aliases and employees as rows. My query looks like this: select emp_name, to_date('&from_date','dd-mm-rrrr') DAY1, to_date('&from_date','dd-mm-rrrr')+1 DAY2, to_date('&from_date','dd-mm-rrrr')+2 DAY3, to_date('&from_date','dd-mm-yyyy')+3 DAY4, --up to date 31 to_date('&from_date','dd-mm-yyyy')+29 DAY30, to_date('&from_date','dd-mm-yyyy')+30 DAY31 from cdr_employees where dept_code = '&dept' and (sysdate between to_date('&from_date','dd-mon-yyyy') and to_date('&to_date','dd-mon-yyyy')) group by emp_name output looks something like this: EMP_NAME DAY1 DAY2 DAY3 bonita graupe 2006/01/01 2006/01/02 2006/01/03 helena david 2006/01/01 2006/01/02 2006/01/03 hento truter 2006/01/01 2006/01/02 2006/01/03 valme kruger 2006/01/01 2006/01/02 2006/01/03 I want to date value to be the column alias for DAY1, DAY2 and DAY3 and where the current dates are, must be blanks.
ASKED: January 27, 2006  5:39 AM
UPDATED: January 30, 2006  5:57 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

You don’t need your DAY1, DAY2 aliases. You should be doing something like;

select emp_name,
NULL to_date(‘&from_date’,'dd-mm-rrrr’),
NULL to_date(‘&from_date’,'dd-mm-rrrr’)+1

and so on.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    DaveInAZ, I think this is not valid in some DBMS (any ?). In Oracle 8i you can't use this kind of operation as an alias for a selected column. I would like to know if that's possible in Oracle 9i or 10g (other, e.g. Sql Server 2005). ws8oBPSQ: With PL/SQL you can use a procedure. Something like this: create or replace PROCEDURE prb_calen (fromdate IN date,todate IN date, dept in number) IS line varchar2(500):= 'EMP_NAME'; inc number := 0; BEGIN loop line := line||to_char(fromdate+inc,' DD-MM-YYYY'); inc := inc + 1; exit when fromdate+inc > todate; end loop; dbms_output.put_line(line); for i in (select emp_name from cdremp where dept_code = dept ) loop dbms_output.put_line(i.emp_name); end loop; END; That's the idea, but I guess there is a limit of 255 bytes per line, and that's not enough. I hope this helps,
    65,110 pointsBadges:
    report
  • Welcome
    Hi, You may try a select like the following: select 'EMP_NAME', to_date('&from_date','dd-mm-rrrr') DAY1, to_date('&from_date','dd-mm-rrrr')+1 DAY2, to_date('&from_date','dd-mm-rrrr')+2 DAY3, to_date('&from_date','dd-mm-yyyy')+3 DAY4, --up to date 31 to_date('&from_date','dd-mm-yyyy')+29 DAY30, to_date('&from_date','dd-mm-yyyy')+30 DAY31 from DUAL UNION ALL select emp_name, to_date(null) DAY1, -- repeat this 31 times to_date(null) DAY31 from cdr_employees where dept_code = '&dept' and (sysdate between to_date('&from_date','dd-mon-yyyy') and to_date('&to_date','dd-mon-yyyy')) group by emp_name ORDER BY 2,1 -- this makes the title line appear first / That is, generate the first (title) line using a select from DUAL. If you run this in SQL*PLUS, then you should suppress the headings, because they are generated by the select itself. Also, alternatively, if you use SQL*PLUS, then you may execute a SELECT from DUAL separately, just to generate the title DATES and store them in SQL*PLUS variables, then use those variables in COLUMN statements for your emplyees select, for example: COLUMN DAY1 NEW_VALUE DAY1 ; -- COLUMN DAY31 NEW_VALUE FAY31 SELECT to_date('&from_date','dd-mm-rrrr') DAY1, -- to_date('&from_date','dd-mm-rrrr')+30 DAY31 FROM DUAL / COLUMN D1 HEADING '&DAY1' -- COLUMN D31 HEADING '&DAY31' select emp_name, to_date(null) DAY1, -- repeat this 31 times to_date(null) DAY31 from cdr_employees where dept_code = '&dept' and (sysdate between to_date('&from_date','dd-mon-yyyy') and to_date('&to_date','dd-mon-yyyy')) group by emp_name / From the "WHERE" clause it seems that you are using ANY period (from_date, to_date), so that SYSDATE is inside it. However, the query selects a FIXED number of columns ( here 1 + 31, that is, for a single month ). If you want a query generating a variable number of columns, then I think you should use some kind of dynamic SQL. Hope this could help. Best Regards, Iudith
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following