Home » SQL & PL/SQL » SQL & PL/SQL » how to number of working days in a month
how to number of working days in a month [message #235883] Tue, 08 May 2007 04:38 Go to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
/* Formatted on 2007/05/08 12:52 (Formatter Plus v4.8.0) */
SELECT ' '
|| LPAD (NVL (TO_CHAR (a.lf_control_no), ' '),
MAX (LENGTH (NVL (TO_CHAR (a.lf_control_no), ' '))) OVER (),
' '
)
|| ' '
|| RPAD (NVL (a.agency_code, ' '),
MAX (LENGTH (NVL (a.agency_code, ' '))) OVER (),
' '
)
|| ' '
|| RPAD (NVL (a.dept_code, ' '),
MAX (LENGTH (NVL (a.dept_code, ' '))) OVER (),
' '
)
|| ' '
|| LPAD (NVL (TO_CHAR (a.account_id), ' '),
MAX (LENGTH (NVL (TO_CHAR (a.account_id), ' '))) OVER (),
' '
)
|| ' '
|| LPAD (NVL (TO_CHAR (a.status), ' '),
MAX (LENGTH (NVL (TO_CHAR (a.status), ' '))) OVER (),
' '
)
|| ' '
|| LPAD (NVL (TO_CHAR (a.bilmsg_status), ' '),
MAX (LENGTH (NVL (TO_CHAR (a.bilmsg_status), ' '))) OVER (),
' '
)
|| ' '
|| LPAD (NVL (TO_CHAR (a.msg_sent_date), ' '),
MAX (LENGTH (NVL (TO_CHAR (a.msg_sent_date), ' '))) OVER (),
' '
)
|| ' '
|| LPAD (NVL (TO_CHAR (b.timeline), ' '),
MAX (LENGTH (NVL (TO_CHAR (b.timeline), ' '))) OVER (),
' '
)
|| ' '

|| ' ' AS good_str
FROM tlri_t_reqagtlst a, tlri_m_agency b
WHERE a.status = 'D' AND a.bilmsg_status = 'S';


in the above statement ,I want to add one more column 'working days(excluding SATURDAY and SUNDAY)'.
for this column ,i want to subtract (sysdate-a.msg_sent_date).I want to insert some code to in between select statement.How can I do that?
Re: how to number of working days in a month [message #235885 is a reply to message #235883] Tue, 08 May 2007 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your query.
Post your full Oracle version.

Regards
Michel
Re: how to number of working days in a month [message #235889 is a reply to message #235883] Tue, 08 May 2007 05:00 Go to previous messageGo to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
I am sending the formatted query

SELECT a.lf_control_no,
a.agency_code,
a.dept_code,
a.account_id,
a.status,
a.bilmsg_status,
a.msg_sent_date,
b.timeline

WHERE a.status = 'D' AND a.bilmsg_status = 'S';

please reply
Re: how to number of working days in a month [message #235890 is a reply to message #235889] Tue, 08 May 2007 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read How to format your posts

And Post your full Oracle version.

Regards
Michel
Re: how to number of working days in a month [message #235892 is a reply to message #235890] Tue, 08 May 2007 05:13 Go to previous messageGo to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
SELECT   a.lf_control_no,
         a.agency_code,
		 a.dept_code,
		 a.account_id,
		 a.status,
		 a.bilmsg_status,
		 a.msg_sent_date,
		 b.timeline
		  
 WHERE a.status = 'D' AND a.bilmsg_status = 'S';


oracle version is oracle -9.02
Re: how to number of working days in a month [message #235899 is a reply to message #235889] Tue, 08 May 2007 06:17 Go to previous messageGo to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
SELECT   a.lf_control_no,
         a.agency_code,
		 a.dept_code,
		 a.account_id,
		 a.status,
		 a.bilmsg_status,
		 a.msg_sent_date,
		 b.timeline
		  
 WHERE a.status = 'D' AND a.bilmsg_status = 'S';



in the above statement ,I want to add one more column 'working days(excluding SATURDAY and SUNDAY)'.

for this column ,i want to subtract (sysdate-a.msg_sent_date).

I want to insert some code to in between select statement.How can I do that?
Re: how to number of working days in a month [message #235901 is a reply to message #235883] Tue, 08 May 2007 06:22 Go to previous message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
SELECT   a.lf_control_no,
         a.agency_code,
		 a.dept_code,
		 a.account_id,
		 a.status,
		 a.bilmsg_status,
		 a.msg_sent_date,
		 b.timeline
 FROM  FROM tlri_t_reqagtlst a, tlri_m_agency b
		  
 WHERE a.status = 'D' AND a.bilmsg_status = 'S';


in the above statement ,I want to add one more column 'working days'.

for this column ,i want to subtract (sysdate-a.msg_sent_date).

I want to insert some code to in between select statement.How can I do that?
Previous Topic: Meaning of these two tables
Next Topic: Materialized view refresh
Goto Forum:
  


Current Time: Mon Dec 05 02:40:05 CST 2016

Total time taken to generate the page: 0.13717 seconds