Home » SQL & PL/SQL » SQL & PL/SQL » calculate working days not beween date - merged
calculate working days not beween date - merged [message #644583] Tue, 10 November 2015 04:59 Go to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
i want to calculate working days without showing (exclude Sundays.) in a month without the difference of two dates..
Month	Total Days	Sundays	  Working days
August	    31	         5	      26
April	    30	         4	      26
November    30	         4	      26


[Updated on: Tue, 10 November 2015 05:00]

Report message to a moderator

Re: calculate working days not beween date [message #644586 is a reply to message #644583] Tue, 10 November 2015 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What does mean "without the difference of two dates.."?
What did you try so far?

Re: calculate working days not beween date [message #644662 is a reply to message #644586] Thu, 12 November 2015 02:46 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Dear,

You have to Implement like this:


with mm_range as
      (
              select  trunc(sysdate,'Month') + (rownum - 1) as dt
              from    dual
              connect by level <= last_day(sysdate) - trunc(sysdate,'Month')
      )
     select to_char(sysdate,'Month') as Mon,
            last_day(sysdate) total_days,
            count(case when to_char(dt,'dy','nls_date_language=english') = 'sun' then 1 else null 
                  end
                 ) as sunday_count,
            to_char(last_day(sysdate),'DD')  -  count(case when to_char(dt,'dy','nls_date_language=english') = 'sun' then 1 else null 
                  end
                 ) as working_days          
      from  mm_range
  
Re: calculate working days not beween date [message #644664 is a reply to message #644662] Thu, 12 November 2015 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not correct:
SQL> with mm_range as
  2        (
  3                select  trunc(sysdate,'Month') + (rownum - 1) as dt
  4                from    dual
  5                connect by level <= last_day(sysdate) - trunc(sysdate,'Month')
  6        )
  7       select to_char(sysdate,'Month') as Mon,
  8              last_day(sysdate) total_days,
  9              count(case when to_char(dt,'dy','nls_date_language=english') = 'sun' then 1 else null
 10                    end
 11                   ) as sunday_count,
 12              to_char(last_day(sysdate),'DD')  -  count(case when to_char(dt,'dy','nls_date_language=english') = 'sun' then 1 else null
 13                    end
 14                   ) as working_days
 15        from  mm_range
 16  /
MON       TOTAL_DAYS          SUNDAY_COUNT WORKING_DAYS
--------- ------------------- ------------ ------------
Novembre  30/11/2015 09:58:39            5           25

1 row selected.

In addition where are August and April?
Re: calculate working days not beween date [message #646402 is a reply to message #644664] Wed, 30 December 2015 01:16 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
Data Structure:
SQL> DESC ABSENT1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 ABDATE                                             DATE
 ASTUID                                             NUMBER(9)
 AREMARK                                            VARCHAR2(200)
 ABTYPE                                             VARCHAR2(45)

SQL> desc student
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 STUID                                     NOT NULL NUMBER(8)
 STATUS                                             VARCHAR2(30)
 CLASS                                              VARCHAR2(25)
 SECTION                                            VARCHAR2(30)
 SESION                                             VARCHAR2(30)
 ROLLNO                                             NUMBER(4)
 ADM_NO                                             VARCHAR2(25)
 STUDENTID                                          VARCHAR2(15)
 NAME                                               VARCHAR2(250)
 F_NAME                                             VARCHAR2(250)
------------------------------------
Main Report Query:
SELECT DISTINCT TO_CHAR(ABSENT1.ABDATE,'MONTH') AAA, COUNT(ABSENT1.ASTUID) ASD, STUDENT.STUID,  STUDENT.SECTION, 
STUDENT.CLASS, STUDENT.STUDENTID, STUDENT.NAME, STUDENT.F_NAME
FROM ABSENT1, STUDENT 
WHERE STATUS='PRESENT'
AND ABSENT1.ASTUID=STUDENT.STUID
AND CLASS=:CLS
AND SECTION=:SEC
AND TO_CHAR(ABDATE,'YYYY')=:YEA
GROUP BY 
TO_CHAR(ABSENT1.ABDATE,'MONTH') ,STUDENT.STUID,  STUDENT.SECTION, 
STUDENT.CLASS, STUDENT.STUDENTID, STUDENT.NAME, STUDENT.F_NAME
--ORDER BY ABSENT1.ABDATE

Parameter Form:     In put/Selection from User   (Class, Section, Year)



Re: calculate working days not beween date [message #646405 is a reply to message #646402] Wed, 30 December 2015 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is this?
A question? A solution?
Anyway, learn how to format a query using using SQL Formatter.

Re: calculate working days not beween date [message #646406 is a reply to message #646405] Wed, 30 December 2015 01:42 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
a question.
Re: calculate working days not beween date [message #646407 is a reply to message #646406] Wed, 30 December 2015 01:42 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
/forum/fa/12952/0/
this is i want to build. i have send u my query.
  • Attachment: 1.JPG
    (Size: 25.11KB, Downloaded 750 times)

[Updated on: Wed, 30 December 2015 02:09]

Report message to a moderator

Re: calculate working days not beween date [message #646408 is a reply to message #646407] Wed, 30 December 2015 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: calculate working days not beween date [message #646412 is a reply to message #646408] Wed, 30 December 2015 02:26 Go to previous messageGo to next message
Littlefoot
Messages: 21367
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is relatively simple to calculate number of days and Sundays in a month; here's an example for current year.
- The WITH clause creates all dates in this (2015) year
- TO_CHAR('mm') selects months
- TO_CHAR('d') = 7 represents Sundays here (where I live)

It is easy to subtract these two values and get number of working days.

SQL> WITH y2015
  2       AS (    SELECT TRUNC (SYSDATE, 'yyyy') + LEVEL - 1 datum
  3                 FROM DUAL
  4           CONNECT BY LEVEL <=
  5                           ADD_MONTHS (TRUNC (SYSDATE, 'yyyy'), 12)
  6                         - TRUNC (SYSDATE, 'yyyy'))
  7    SELECT TO_CHAR (datum, 'mm') month,
  8           COUNT (*) total_days,
  9           SUM (DECODE (TO_CHAR (datum, 'd'), 7, 1, 0)) sundays
 10      FROM y2015
 11  GROUP BY TO_CHAR (datum, 'mm')
 12  ORDER BY 1;

MO TOTAL_DAYS    SUNDAYS
-- ---------- ----------
01         31          4
02         28          4
03         31          5
04         30          4
05         31          5
06         30          4
07         31          4
08         31          5
09         30          4
10         31          4
11         30          5
12         31          4

12 rows selected.

SQL>

[Updated on: Wed, 30 December 2015 02:29]

Report message to a moderator

Re: calculate working days not beween date [message #646413 is a reply to message #646412] Wed, 30 December 2015 02:49 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
Your solution as written is not proof against client side language settings:
C:\Users\john>
C:\Users\john>set nls_lang=AMERICAN

C:\Users\john>sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 30 08:47:08 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Dec 30 2015 08:46:52 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

orclz> WITH y2015
  2           AS (    SELECT TRUNC (SYSDATE, 'yyyy') + LEVEL - 1 datum
  3                     FROM DUAL
  4               CONNECT BY LEVEL <=
  5                               ADD_MONTHS (TRUNC (SYSDATE, 'yyyy'), 12)
  6                             - TRUNC (SYSDATE, 'yyyy'))
  7        SELECT TO_CHAR (datum, 'mm') month,
  8               COUNT (*) total_days,
  9               SUM (DECODE (TO_CHAR (datum, 'd'), 7, 1, 0)) sundays
 10         FROM y2015
 11     GROUP BY TO_CHAR (datum, 'mm')
 12     ORDER BY 1;

MO TOTAL_DAYS    SUNDAYS
-- ---------- ----------
01         31          5
02         28          4
03         31          4
04         30          4
05         31          5
06         30          4
07         31          4
08         31          5
09         30          4
10         31          5
11         30          4
12         31          4

12 rows selected.

orclz>
Shahzad, you'll need to adjust the call to TO_CHAR to include the NLS setting you want.
Re: calculate working days not beween date [message #646414 is a reply to message #646413] Wed, 30 December 2015 02:59 Go to previous messageGo to next message
Littlefoot
Messages: 21367
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's why I said thatLF

TO_CHAR('d') = 7 represents Sundays here (where I live)
Re: calculate working days not beween date [message #646415 is a reply to message #646414] Wed, 30 December 2015 03:31 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
In absent1 table i am storing only (roll call of students) not storing Sundays. but when i want to find Sundays from absent1 table it is not comparable. i want to get (when user select year from parameter form) it will automatically shows its Sunday of the showing months (which is shown in the figure above).
here is the table structure of absent1 table:

SQL> Desc absent    (Master table)
Name:
Abdate      Not Null   Date     ---------Primary key


SQL> desc absent1    (Detail)

 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 ABDATE                                             DATE    ----Foreign key
 ASTUID                                             NUMBER(9)   ----Student Code
 AREMARK                                            VARCHAR2(200)
 ABTYPE                                             VARCHAR2(45)----Type of Absent

[Updated on: Wed, 30 December 2015 03:34]

Report message to a moderator

Re: calculate working days not beween date [message #646416 is a reply to message #646415] Wed, 30 December 2015 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Wed, 30 December 2015 08:43

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.


Re: calculate working days not beween date [message #646417 is a reply to message #646416] Wed, 30 December 2015 03:42 Go to previous messageGo to next message
Littlefoot
Messages: 21367
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Shahzad

In absent1 table i am storing only (roll call of students) not storing Sundays. but when i want to find Sundays from absent1 table it is not comparable


These two sentences don't match. I understand that there's no record (in the ABSENT table) which contains ABDATE = Sunday (because students aren't present on Sundays anyway). So, why do you need to find Sundays in a table that - by default - doesn't contain Sundays at all?

[Updated on: Wed, 30 December 2015 03:43]

Report message to a moderator

Re: calculate working days not beween date [message #646418 is a reply to message #646417] Wed, 30 December 2015 03:52 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
because my requirements is show total days in month, calculate absents (from absent1 table), shows non posting days of the students (sundays, and any other national holidays).
Re: calculate working days not beween date [message #646419 is a reply to message #646418] Wed, 30 December 2015 04:01 Go to previous messageGo to next message
Littlefoot
Messages: 21367
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I showed you how to display days per month.
Number of days in the ABSENT table can easily be calculated (COUNT, eh?).
I showed you how to recognize Sundays.

So - what's the problem?
Re: calculate working days not beween date [message #646420 is a reply to message #646419] Wed, 30 December 2015 04:04 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
Quote:
WITH y2015
2 AS ( SELECT TRUNC (SYSDATE, 'yyyy') + LEVEL - 1 datum
3 FROM DUAL
4 CONNECT BY LEVEL <=
5 ADD_MONTHS (TRUNC (SYSDATE, 'yyyy'), 12)
6 - TRUNC (SYSDATE, 'yyyy'))

but i want to pick year from parameter form(user input)
Re: calculate working days not beween date [message #646421 is a reply to message #646420] Wed, 30 December 2015 04:11 Go to previous messageGo to next message
Littlefoot
Messages: 21367
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Great! Adjust that code to your needs! Hint: it won't be SYSDATE but a parameter.
Re: calculate working days not beween date [message #646422 is a reply to message #646421] Wed, 30 December 2015 04:17 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
but it says:

Rep-0303 The statement you have entered is not SELECT statement.
Quote:

WITH y2015
2 AS ( SELECT TRUNC (SYSDATE, 'yyyy') + LEVEL - 1 datum
3 FROM DUAL
4 CONNECT BY LEVEL <=
5 ADD_MONTHS (TRUNC (SYSDATE, 'yyyy'), 12)
6 - TRUNC (SYSDATE, 'yyyy'))

[Updated on: Wed, 30 December 2015 04:19]

Report message to a moderator

Re: calculate working days not beween date [message #646424 is a reply to message #646422] Wed, 30 December 2015 04:25 Go to previous messageGo to next message
Littlefoot
Messages: 21367
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First make sure that query you wrote works properly in SQL*Plus (or any other tool you use). Then move it to Reports Builder. By the way, which Reports do you use? Maybe older versions (such as 6i) won't recognize WITH clause. In that case, just move that code into an inline view.

P.S. WITH syntax, if you put it exactly as you posted in your last message, lacks the final SELECT.
with y2015 as (select ...)
select * from y2015    --> something like this

[Updated on: Wed, 30 December 2015 04:26]

Report message to a moderator

Re: calculate working days not beween date [message #646425 is a reply to message #646424] Wed, 30 December 2015 04:26 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
Report Version 6i.
Query works fine in SQL. but in reports it is not recognize "WITH" clause.

[Updated on: Wed, 30 December 2015 04:27]

Report message to a moderator

Re: calculate working days not beween date [message #646427 is a reply to message #646425] Wed, 30 December 2015 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just report the definition of y2015 where you put y2015 in query.

Re: calculate working days not beween date [message #646428 is a reply to message #646427] Wed, 30 December 2015 04:58 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
WITH T 
  AS (SELECT TRUNC (:YEA, 'yyyy') + LEVEL - 1 datum
                       FROM DUAL
                 CONNECT BY LEVEL <=
                                 ADD_MONTHS (TRUNC (:YEA, 'yyyy'), 12)
                               - TRUNC (:YEA, 'yyyy'))
         SELECT TO_CHAR (datum, 'mm') month,
                COUNT (*) total_days,
                SUM (DECODE (TO_CHAR (datum, 'd'), 7, 1, 0)) sundays
         FROM T
      GROUP BY TO_CHAR (datum, 'mm')
      ORDER BY 1


Rep-0303 The statement you have entered is not SELECT statement.
Quote:
The :Yea is user parameter datatype is character.

[Updated on: Wed, 30 December 2015 05:00]

Report message to a moderator

Re: calculate working days not beween date [message #646429 is a reply to message #646428] Wed, 30 December 2015 05:03 Go to previous messageGo to next message
Littlefoot
Messages: 21367
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You are entering a year already; why do you TRUNC it?

As of the REP-0303: didn't you see what I previously said? Just move that code into an inline view.
Re: calculate working days not beween date [message #646484 is a reply to message #646429] Fri, 01 January 2016 02:20 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
but i am not succeed in building the query.please advised how i can use this query in reports 6i With my table (absent1, student). please help.
User paramter form is selection;
:Class
:Section
:Year

with 
  2    months as (
  3      select add_months(trunc(sysdate,'YEAR'),level-13) month
  4      from dual
  5      connect by level <= 36
  6    )
  7  select to_char(month,'YYYY') year,
  8         to_char(month,'Month') month,
  9         to_char(month,'Day') first_day,
 10         to_char(last_day(month),'Day DD') last_day,
 11         4+
 12         case 
 13           when to_char(last_day(month),'DD')
 14                - decode(to_char(month,'D'),1,0,8-to_char(month,'D'))
 15                >= 29
 16             then 1
 17           else 0
 18         end nb_sunday
 19  from months

[Updated on: Fri, 01 January 2016 03:33]

Report message to a moderator

Re: calculate working days not beween date [message #646485 is a reply to message #646484] Fri, 01 January 2016 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 30 December 2015 10:36

Michel Cadot wrote on Wed, 30 December 2015 08:43

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.


please help

Re: calculate working days not beween date [message #646486 is a reply to message #646485] Fri, 01 January 2016 05:09 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
 CREATE TABLE MONTHS (
 SNO NUMBER(3) PRIMARY KEY,MON DATE,MM VARCHAR2(50));

 INSERT INTO  MONTHS VALUES (1,'31-MAR-2015','MARCH');
 INSERT INTO  MONTHS VALUES (2,'30-APR-2015','APRIL');
 INSERT INTO  MONTHS VALUES (3,'31-MAY-2015','MAY');
 INSERT INTO  MONTHS VALUES (4,'30-JUN-2015','JUNE');
 INSERT INTO  MONTHS VALUES (5,'30-JUL-2015','JULY');
----------------------------------------------------------------
CREATE TABLE ABSENT (
  ABDATE DATE PRIMARY KEY);

 INSERT INTO ABSENT VALUES ('31-MAR-2015');
 INSERT INTO ABSENT VALUES ('01-APR-2015');
 INSERT INTO ABSENT VALUES ('02-APR-2015');
 INSERT INTO ABSENT VALUES ('03-APR-2015');
 INSERT INTO ABSENT VALUES ('05-APR-2015');
 INSERT INTO ABSENT VALUES ('03-MAY-2015');
 INSERT INTO ABSENT VALUES ('04-JUL-2015');
--------------------------------------------------------------------
CREATE TABLE STUDENT22 (
STUID NUMBER(2) PRIMARY KEY,STATUS VARCHAR2(20),NAME VARCHAR2(20),
F_NAME VARCHAR2(20),CLASS VARCHAR2(30),
SECTION VARCHAR2(20));

 INSERT INTO STUDENT22 VALUES (1,'PRESENT','ABC','DEF','PREP','A');
 INSERT INTO STUDENT22 VALUES (2,'PRESENT','BC','DE','PREP','A');
 INSERT INTO STUDENT22 VALUES (3,'PRESENT','B','2D','NURSERY','B');
 INSERT INTO STUDENT22 VALUES (4,'PRESENT','B43','2DE','PREP','A');
 INSERT INTO STUDENT22 VALUES (5,'PRESENT','B4','2DE','ONE','A');
 INSERT INTO STUDENT22 VALUES (6,'PRESENT','B2','D3E','NURSERY','A');
 INSERT INTO STUDENT22 VALUES (7,'PRESENT','BC55','DE','PREP','B');
 INSERT INTO STUDENT22 VALUES (8,'PRESENT','B32','D3E','NURSERY','A');
 INSERT INTO STUDENT22 VALUES (9,'PRESENT','BC35','3DE','ONE','B');
-------------------------------------------------------------------------
create table ABSENT11 (
ABDATE DATE REFERENCES ABSENT(ABDATE),ASTUID NUMBER(2),ATYPE VARCHAR2(20));

 INSERT INTO ABSENT11 VALUES ('31-MAR-2015',1,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('31-MAR-2015',3,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('31-MAR-2015',4,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('01-APR-2015',2,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('01-APR-2015',5,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('01-APR-2015',3,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('01-APR-2015',6,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('02-APR-2015',3,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('02-APR-2015',4,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('03-APR-2015',1,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('03-APR-2015',3,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('03-APR-2015',5,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('03-APR-2015',8,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('03-MAY-2015',3,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('03-MAY-2015',4,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('03-MAY-2015',1,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('03-MAY-2015',3,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('04-JUL-2015',6,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('04-JUL-2015',1,'ABSENT');
 INSERT INTO ABSENT11 VALUES ('04-JUL-2015',3,'ABSENT');
 
Please find the test case. here is output required. i am using reports 6i.
  • Attachment: 3.JPG
    (Size: 34.08KB, Downloaded 81 times)

[Updated on: Fri, 01 January 2016 05:28]

Report message to a moderator

Re: calculate working days not beween date [message #646487 is a reply to message #646486] Fri, 01 January 2016 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What are the relations between the tables?
What are the specification of each column of the result?

Re: calculate working days not beween date [message #646488 is a reply to message #646487] Fri, 01 January 2016 07:58 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
Absent is master table(pk-abdate) and its detail is absent1(fk-abdate). which stores absent date of each student.
Months is only for display months on reports
Student table contains student information data. and its pk is stuid which can be compare with absent1(astuid).

[Updated on: Fri, 01 January 2016 08:02]

Report message to a moderator

Re: calculate working days not beween date [message #646489 is a reply to message #646488] Fri, 01 January 2016 09:10 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How does ABSENT table come into play in the question?
And what is ATYPE column of ABSENT11 table? How does it come into play in the question?
And what is STATUS column in STUDENT22 table? How does it come into play in the question?
And what are NAME, F_NAME, CLASS and SECTION columns? How do they come into play in the question?
What are the specification of each column of the result?
What is "Total days"? What is "Sundays"? What is "Absents"? What is "Present"?
Even if is obvious for you you MUST COMPLETELY specify the input and the result.

[Updated on: Fri, 01 January 2016 09:12]

Report message to a moderator

Re: calculate working days not beween date [message #646492 is a reply to message #646489] Sat, 02 January 2016 01:33 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
The main role plays by these tables. (Absent11,Student22)
*. AType is marked by 'PRESENT' OR 'ABSENT'.
*. STATUS means 'PRESENT' OR 'N_PRESENT'. It is used in report main query.IF Student Is PRESENT its data is shown on reports if N_Present then no data is shown.
*. The rest i have explain in the picture above
i want when user input or select Year from parameter form the report main query shows months, its total days, its Sunday. and the absent it will collect from absent11 through (astuid) which is equal to stuid of student table.
 The report main query is:
SELECT DISTINCT TO_CHAR(MON, 'MONTH') MON,
TO_CHAR(TRUNC(ABSENT1.ABDATE, 'YY'), 'YYYY') Y, COUNT(ABSENT1.ASTUID) ASD, 
STUDENT.STUID, STUDENT.CLASS||' - '||STUDENT.SECTION clss, 
STUDENT.STUDENTID, STUDENT.NAME, STUDENT.F_NAME, STUDENT.ROLLNO
FROM ABSENT1, STUDENT,MONTHS
WHERE (STUDENT.STATUS = 'PRESENT'
 AND STUDENT.CLASS = :CLS
 AND STUDENT.SECTION = :SEC
 AND TO_CHAR(TRUNC(ABSENT1.ABDATE, 'YY'), 'YYYY') = :YEA)
 AND (ABSENT1.ASTUID(+) = STUDENT.STUID)
AND TO_CHAR(MON,'MONTH')=TO_CHAR(ABDATE,'MONTH')
GROUP BY 
TO_CHAR(MON, 'MONTH'),
TO_CHAR(TRUNC(ABSENT1.ABDATE, 'YY'), 'YYYY'), STUDENT.STUID, STUDENT.CLASS||' - '||STUDENT.SECTION, 
STUDENT.STUDENTID, STUDENT.NAME, STUDENT.F_NAME, 
TO_CHAR(TRUNC(ABSENT1.ABDATE, 'MM'), 'MONTH'), TO_CHAR(TRUNC(ABSENT1.ABDATE, 'YY'), 'YYYY'), 
STUDENT.CLASS||' - '||STUDENT.SECTION, STUDENT.ROLLNO 

and how i can edit this query with report main query.
with 
      months as (
        select add_months(trunc(sysdate,'YEAR'),level-13) month
        from dual
        connect by level <= 36
      )
    select to_char(month,'YYYY') year,
           to_char(month,'Month') month,
           to_char(month,'Day') first_day,
          to_char(last_day(month),'Day DD') last_day,
          4+
          case 
            when to_char(last_day(month),'DD')
                 - decode(to_char(month,'D'),1,0,8-to_char(month,'D'))
                 >= 29
              then 1
            else 0
          end nb_sunday
   from months

[Updated on: Sat, 02 January 2016 01:36]

Report message to a moderator

Re: calculate working days not beween date [message #646494 is a reply to message #646492] Sat, 02 January 2016 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your result image shows 2 for "Absents" in March when your test case gives 3 absents if you count all students and only 1 if you count only student 1 as in your image.
So why 2?
Is the result for only one user or for all?

Quote:
how i can edit this query with report main query.


As I said above;

Re: calculate working days not beween date [message #646496 is a reply to message #646494] Sat, 02 January 2016 03:33 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
Sorry for misprint. i need result for all. i mean how i can alter the yur query with report query.

[Updated on: Sat, 02 January 2016 03:41]

Report message to a moderator

Re: calculate working days not beween date [message #646498 is a reply to message #646496] Sat, 02 January 2016 05:39 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
This is a college homework assignment, right?

In that case, I think your first job is to tidy up your relational structures. For example, using a column of type date for your primary/foreign keys is going to cost you marks. You should be using numeric surrogate keys.

Once that is done, your query is almost there. LF did the hard part, all you have to do is join it to a query against absent11, something like this:
orclz>
orclz> with mo_tot_sun as
  2  (SELECT TO_CHAR (datum, 'mm') month,
  3              COUNT (*) total_days,
  4              SUM (DECODE (TO_CHAR (datum, 'd'), 7, 1, 0)) sundays
  5         FROM
  6  (SELECT TRUNC (SYSDATE, 'yyyy') + LEVEL - 1 datum
  7                    FROM DUAL
  8              CONNECT BY LEVEL <=
  9                              ADD_MONTHS (TRUNC (SYSDATE, 'yyyy'), 12)
 10                            - TRUNC (SYSDATE, 'yyyy'))
 11   GROUP BY TO_CHAR (datum, 'mm')),
 12  a2015 as (select to_char(abdate,'mm') month,count(*) absents from absent11 group by to_char(abdate,'mm'))
 13  select month, total_days,sundays,nvl(absents,0) absents, nvl(total_days-sundays-absents,0) presents
 14  from mo_tot_sun left outer join a2015 using (month) order by month
 15  ;

MO TOTAL_DAYS    SUNDAYS    ABSENTS   PRESENTS
-- ---------- ---------- ---------- ----------
01         31          5          0          0
02         29          4          0          0
03         31          4          3         24
04         30          4         10         16
05         31          5          4         22
06         30          4          0          0
07         31          5          3         23
08         31          4          0          0
09         30          4          0          0
10         31          5          0          0
11         30          4          0          0
12         31          4          0          0

12 rows selected.

orclz>
but I'm not writing it all for you. You'll have to make modifications to group by each student ad correct the zeros.

[Updated on: Sat, 02 January 2016 05:40]

Report message to a moderator

Re: calculate working days not beween date [message #646536 is a reply to message #646498] Mon, 04 January 2016 02:33 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
But how i can use the with clause in reports 6i.
*. i want to pick year from user parameter form.
*. it will shows the month of the year which is selected.
*. And shows sundays of the moths of the selected year.

[Updated on: Mon, 04 January 2016 02:35]

Report message to a moderator

Re: calculate working days not beween date [message #646537 is a reply to message #646536] Mon, 04 January 2016 02:35 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
shahzad-ul-hasan wrote on Mon, 04 January 2016 08:33
But how i can use the with clause in reports 6i.
MC already told you, on Wednesday. Are you sure that you are actually cut out for this type of work?

[Updated on: Mon, 04 January 2016 02:36]

Report message to a moderator

Re: calculate working days not beween date [message #646538 is a reply to message #646537] Mon, 04 January 2016 02:39 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
i am using inline view but cannot succeed in building the main report query.
Re: calculate working days not beween date [message #646539 is a reply to message #646538] Mon, 04 January 2016 02:42 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
I don't believe you. If you had actually written any code, you would have posted it.
If you ever do write some code, please make sure that it is well formatted.

Re: calculate working days not beween date [message #646540 is a reply to message #646539] Mon, 04 January 2016 02:49 Go to previous messageGo to previous message
shahzad-ul-hasan
Messages: 508
Registered: August 2002
Senior Member
SELECT MONTH,TOTAL_DAYS,SUNDAYS,ABSENTS,PRESENTS,STUDENT.STUID, STUDENT.CLASS||' - '||STUDENT.SECTION clss, 
STUDENT.STUDENTID, STUDENT.NAME, STUDENT.F_NAME, STUDENT.ROLLNO FROM
 (SELECT TO_CHAR (datum, 'mm') month,
                COUNT (*) total_days,
                SUM (DECODE (TO_CHAR (datum, 'd'), 7, 1, 0)) sundays
           FROM
    (SELECT TRUNC (SYSDATE, 'yyyy') + LEVEL - 1 datum
                      FROM DUAL
                CONNECT BY LEVEL <=
                               ADD_MONTHS (TRUNC (SYSDATE, 'yyyy'), 12)
                             - TRUNC (SYSDATE, 'yyyy'))
    GROUP BY TO_CHAR (datum, 'mm')),
   a2015 as (select to_char(abdate,'mm') month,count(*) absents from absent11 group by to_char(abdate,'mm'))
   select month, total_days,sundays,nvl(absents,0) absents, nvl(total_days-sundays-absents,0) presents
   from mo_tot_sun left outer join a2015 using (month) order by month)
FROM ABSENT1, STUDENT
WHERE (STUDENT.STATUS = 'PRESENT'
 AND STUDENT.CLASS = :CLS
 AND STUDENT.SECTION = :SEC
 AND TO_CHAR(TRUNC(ABSENT1.ABDATE, 'YY'), 'YYYY') = :YEA)
 AND (ABSENT1.ASTUID(+) = STUDENT.STUID)
AND TO_CHAR(MON,'MONTH')=TO_CHAR(ABDATE,'MONTH')
GROUP BY 
TO_CHAR(TRUNC(ABSENT1.ABDATE, 'YY'), 'YYYY'), STUDENT.STUID, STUDENT.CLASS||' - '||STUDENT.SECTION, 
STUDENT.STUDENTID, STUDENT.NAME, STUDENT.F_NAME, 
TO_CHAR(TRUNC(ABSENT1.ABDATE, 'MM'), 'MONTH'), TO_CHAR(TRUNC(ABSENT1.ABDATE, 'YY'), 'YYYY'), 
STUDENT.CLASS||' - '||STUDENT.SECTION, STUDENT.ROLLNO 

[Updated on: Mon, 04 January 2016 02:52]

Report message to a moderator

Previous Topic: multi insert
Next Topic: need help in sql query
Goto Forum:
  


Current Time: Sat Feb 24 19:50:28 CST 2018

Total time taken to generate the page: 0.89409 seconds