Home » SQL & PL/SQL » SQL & PL/SQL » Days Query (11G)
Days Query [message #685131] Wed, 27 October 2021 16:55 Go to next message
glmjoy
Messages: 187
Registered: September 2011
Location: KR
Senior Member
Thanks in Advance to All

My Query is that I want to find date working date before or after OFF or AL Day

for e.g 17-OCT-21 is SUNDAY and 23-OCT-2021 is SATURDAY and both Stat is OFF so I want Date 16-OCT-21 and 22-OCT-2021 and after OFF day or AL Absent 25-OCT-21 i want date 26-OCT-2021

I had tried this query but didn't get single date

1 Select emp_code,att_Date,stat,to_char(att_Date, 'fmday'),
2 case
3 when to_char(att_Date, 'fmday') = 'friday' then
4 att_Date+ 3
5 when to_char(att_Date, 'fmday') = 'saturday' then
6 att_Date + 2
7 else
8 att_Date + 1
9 end next_day
10* from attend

SQL> /

MP_CO ATT_DATE STAT TO_CHAR(A NEXT_DAY
----- --------- ---- --------- ---------
60213 15-OCT-21 P friday 18-OCT-21
60213 16-OCT-21 P saturday 18-OCT-21
60213 17-OCT-21 OFF sunday 18-OCT-21
60213 21-OCT-21 P thursday 22-OCT-21
60213 22-OCT-21 P friday 25-OCT-21
60213 23-OCT-21 OFF saturday 25-OCT-21
60213 24-OCT-21 OFF sunday 25-OCT-21
60213 25-OCT-21 AL monday 26-OCT-21
60213 26-OCT-21 P tuesday 27-OCT-21
60213 18-OCT-21 P monday 19-OCT-21
60213 19-OCT-21 P tuesday 20-OCT-21
60213 19-OCT-21 P tuesday 20-OCT-21

12 rows selected.

1 CREATE TABLE ATTEND
2 (
3 EMP_CODE VARCHAR2(6) NOT NULL,
4 ATT_DATE DATE ,
5 TIME_IN VARCHAR2(30 BYTE),
6 TIME_OUT VARCHAR2(30 BYTE),
7* STAT VARCHAR2(4))
SQL> /

Table created.



SQL> Insert into ATTEND
2 (EMP_CODE, ATT_DATE, TIME_IN,
3 TIME_OUT, STAT)
4 Values
5 ('160213', TO_DATE('10/15/2021 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '09:02:00',
6 '17:15:00', 'P')
7 /

1 row created.

SQL>
SQL> Insert into ATTEND
2 (EMP_CODE, ATT_DATE, TIME_IN,
3 TIME_OUT, STAT)
4 Values
5 ('160213', TO_DATE('10/16/2021 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '09:10:00',
6 '17:25:00', 'P')
7 /

1 row created.
/

SQL>
SQL> Insert into ATTEND
2 (EMP_CODE, ATT_DATE, TIME_IN,
3 TIME_OUT, STAT)
4 Values
5 ('160213', TO_DATE('10/17/2021 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL,
6 NULL, 'OFF')
7 /


SQL>
SQL> Insert into ATTEND
2 (EMP_CODE, ATT_DATE, TIME_IN,
3 TIME_OUT, STAT)
4 Values
5 ('160213', TO_DATE('10/18/2021 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '09:02:00',
6 '17:15:00', 'P')
7 /

1 row created.


SQL>
SQL> Insert into ATTEND
2 (EMP_CODE, ATT_DATE, TIME_IN,
3 TIME_OUT, STAT)
4 Values
5 ('160213', TO_DATE('10/19/2021 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '09:02:00',
6 '17:15:00', 'P')
7 /

1 row created.
/
SQL>
SQL> Insert into ATTEND
2 (EMP_CODE, ATT_DATE, TIME_IN,
3 TIME_OUT, STAT)
4 Values
5 ('160213', TO_DATE('10/19/2021 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '09:02:00',
6 '17:15:00', 'P')
7 /

1 row created.
/

SQL>
SQL> Insert into ATTEND
2 (EMP_CODE, ATT_DATE, TIME_IN,
3 TIME_OUT, STAT)
4 Values
5 ('160213', TO_DATE('10/21/2021 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '09:43:00',
6 '17:05:00', 'P')
7 /

1 row created.

SQL>
SQL> Insert into ATTEND
2 (EMP_CODE, ATT_DATE, TIME_IN,
3 TIME_OUT, STAT)
4 Values
5 ('160213', TO_DATE('10/22/2021 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '09:01:00',
6 '17:05:00', 'P')
7 /

1 row created.

SQL>
SQL> Insert into ATTEND
2 (EMP_CODE, ATT_DATE, TIME_IN,
3 TIME_OUT, STAT)
4 Values
5 ('160213', TO_DATE('10/23/2021 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), null,
6 null, 'OFF')
7 /

1 row created.

SQL>
SQL> Insert into ATTEND
2 (EMP_CODE, ATT_DATE, TIME_IN,
3 TIME_OUT, STAT)
4 Values
5 ('160213', TO_DATE('10/24/2021 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL,
6 NULL, 'OFF')
7 /

1 row created.

SQL> Insert into ATTEND
2 (EMP_CODE, ATT_DATE, TIME_IN,
3 TIME_OUT, STAT)
4 Values
5 ('160213', TO_DATE('10/25/2021 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), null,
6 null, 'AL')
7 /

1 row created.

SQL> Insert into ATTEND
2 (EMP_CODE, ATT_DATE, TIME_IN,
3 TIME_OUT, STAT)
4 Values
5 ('160213', TO_DATE('10/26/2021 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '09:03:00',
6 '17:15:00', 'P')
7 /

1 row created.



Re: Days Query [message #685134 is a reply to message #685131] Thu, 28 October 2021 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Sat, 04 September 2021 16:33
Michel Cadot wrote on Wed, 28 July 2021 10:38

Please format your post as explained in How to use [code] tags and make your code easier to read, and align the column in result.

John Watson wrote on Sat, 04 September 2021 09:50
You have been asked, more than once, to follow the Forum rules: Format your posts correctly using [code] tags for code, and use type casting functions such as TO_DATE when working with dates and strings.

It us extremely rude of you to ignore these requests.

Your answer:

glmjoy wrote on Sat, 04 September 2021 10:05
sorry for that

You are so sorry that you ignore it in your next topic.

And post a test case, test it before but do NOT post the execution, just the statements, we can't copy and paste an execution.


In the end:


EdStevens wrote on Sat, 04 September 2021 16:45
You are trying to insert a character string ('01-JAN-2021') into a column that is defined as a DATE datatype. This will force an implied TO_DATE operation to convert that string into the internal, binary structure of a DATE. This may or may not succeed, depending on the controlling setting of NLS_DATE_FORMAT. Better to leave nothing to chance and explcitly use TO_DATE:

insert into TBAL values('121028',TO_DATE('01-JAN-2021',DD-MON-YYYY'),'01',2021);

Your answer:

glmjoy wrote on Sat, 04 September 2021 20:46
Thanks for the correction

but you ignore it.

Re: Days Query [message #685141 is a reply to message #685131] Thu, 28 October 2021 09:37 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
keepling TIME_IN and TIME_OUT is a very serious design flaw. These should be DATE types. DATE is somewhat mis-named, as it is actually a date and time, down to the second. And with that, you don't need a separate column for ATT_DATE.

CREATE TABLE ATTEND
(
EMP_CODE VARCHAR2(6) NOT NULL,
TIME_IN DATE,
TIME_OUT DATE,
STAT VARCHAR2(4))
;

Insert into ATTEND
(EMP_CODE,
TIME_IN,
TIME_OUT,
STAT)
Values
('160213',
TO_DATE('10/15/2021 09:02:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('10/15/2021 17:15:00' 'MM/DD/YYYY HH24:MI:SS'),
'P'
)
;
Re: Days Query [message #685199 is a reply to message #685141] Thu, 04 November 2021 11:02 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
CREATE TABLE ATTEND
(
EMP_CODE VARCHAR2(6) NOT NULL,
TIME_IN DATE,
TIME_OUT DATE,
STAT VARCHAR2(4))
;

Insert into ATTEND
(EMP_CODE,
TIME_IN,
TIME_OUT,
STAT)
Values
('160213',
TO_DATE('10/15/2021 09:02:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('10/15/2021 17:15:00' 'MM/DD/YYYY HH24:MI:SS'),
'P'
)
;
Re: Days Query [message #685203 is a reply to message #685199] Thu, 04 November 2021 11:23 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So shahzad-ul-hasan = glmjoy which wouldn't surprise me as their behaviors are the same one.

Previous Topic: How to get monthly average sum
Next Topic: Select value from multiple dates
Goto Forum:
  


Current Time: Thu Mar 28 12:23:24 CDT 2024