Home » SQL & PL/SQL » SQL & PL/SQL » Help in SQL (oracle 11g)
icon9.gif  Help in SQL [message #584283] Sat, 11 May 2013 00:03 Go to next message
rocky_007
Messages: 32
Registered: March 2012
Location: India
Member
Dear Gurus,

I want help from you in my query.I have created a test table for the same.

CREATE TABLE CHECK_DATES
(
  FROM_DATE  DATE,
  FLAG_FROM  CHAR(1 BYTE),
  TO_DATE    DATE,
  FLAG_TO    CHAR(1 BYTE)
)
TABLESPACE TBS_PAYROLL
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
SET DEFINE OFF;
Insert into CHECK_DATES
   (FROM_DATE, FLAG_FROM, TO_DATE, FLAG_TO)
 Values
   (TO_DATE('05/02/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Y', TO_DATE('05/02/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Y');
Insert into CHECK_DATES
   (FROM_DATE, FLAG_FROM, TO_DATE, FLAG_TO)
 Values
   (TO_DATE('05/04/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N', TO_DATE('05/06/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Y');
Insert into CHECK_DATES
   (FROM_DATE, FLAG_FROM, TO_DATE, FLAG_TO)
 Values
   (TO_DATE('05/08/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N', TO_DATE('05/09/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N');
Insert into CHECK_DATES
   (FROM_DATE, FLAG_FROM, TO_DATE, FLAG_TO)
 Values
   (TO_DATE('05/10/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Y', TO_DATE('05/11/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N');
COMMIT;



This is my test table which stores range of leave dates of employee (from_date and to_date). the Flag denotes that whether the day is a full day or half day. (N-half day, Y-full) It has 4 scenarios:
1. Employee wants to fill one full day leave for 2nd may then he will fill 2nd may in from_date and to_date and set the flag=Y to both from_flag and to_flag.
2. Employee wants to fill half day leave for 10th may then he will fill 10th may in from_date and to_date and set the flag=Y in any from_flag or to_flag and should contain 'N' in any of the two.
3. Employee wants to fill two half day leave for 8th and 9th may then he will fill 8th may in from_date and 9th May in to_date and set the flag='N' in from_flag as well as to_flag.
4. Employee wants to fill one half day leave for 4th may and two full day for 5th and 6th may then he will fill 4th may in from_date and set the from_flag='N' and 6th May in to_date and set the to_flag='Y'

You will find the same in test table.

Now, the problem is I want to show this data, datewise. that is my data should look like:
 date           days_count
5/2/2013        1
5/4/2013        0.5
5/5/2013        1
5/6/2013        1
5/8/2013        0.5
5/9/2013        0.5
5/10/2013       1
5/11/2013       0.5


I have tried this query but the result is not correct please help me in modifying this query:
SELECT from_date,FLAG_FROM, TO_DATE,FLAG_TO,
       CASE
          WHEN (flag_from = 'N' AND flag_to = 'Y')
           OR (flag_from = 'Y' AND flag_to = 'N')
             THEN 0.5
          ELSE 1
       END days_count, F_DAY
  FROM check_dates cd,
       (SELECT   TO_DATE (TO_CHAR ('01/' || TO_CHAR (:p_from_date, 'mm/yyyy')),
                          'dd/mm/yyyy'
                         )
               + ROWNUM
               - 1 f_day
          FROM all_objects
         WHERE ROWNUM <=
                    :TO_DATE
                  + 1
                  - TO_DATE (TO_CHAR ('01/' || TO_CHAR (SYSDATE, 'mm/yyyy')),
                             'dd/mm/yyyy'
                            )) month_days
 WHERE f_day BETWEEN cd.from_date AND cd.TO_DATE



Regards.
Re: Help in SQL [message #584285 is a reply to message #584283] Sat, 11 May 2013 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your model is wrong:

1/ "he will fill 4th may in from_date and set the from_flag='N' and 6th May in to_date and set the to_flag='Y'"
If you saw this in the table data how do you know it is 0.5 day on 4th and full day on 5th and 6th, or 0.5 day on 4th and 5th and full day on 6th?

2/ How do you interpret when from_date and to_date are same one but flags are different?

Regards
Michel

[Updated on: Sat, 11 May 2013 01:00]

Report message to a moderator

Re: Help in SQL [message #584288 is a reply to message #584285] Sat, 11 May 2013 02:12 Go to previous messageGo to next message
rocky_007
Messages: 32
Registered: March 2012
Location: India
Member
Hi and thanks for replying Michel, that is the problem actually I have been provided with this model.
Please suggest, isn't there any way to do this??? Even by pl/sql???
Re: Help in SQL [message #584290 is a reply to message #584288] Sat, 11 May 2013 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't answer my questions without these answers no code is possible.

Regards
Michel
Re: Help in SQL [message #584293 is a reply to message #584290] Sat, 11 May 2013 03:47 Go to previous messageGo to next message
rocky_007
Messages: 32
Registered: March 2012
Location: India
Member
Michel, here are your answers:
Quote:
If you saw this in the table data how do you know it is 0.5 day on 4th and full day on 5th and 6th, or 0.5 day on 4th and 5th and full day on 6th?


If anyone wants to fill the range of dates, then half day is allowed only on the first day of leave and the last day. The days falling in between these days are considered as 1 full leave. So, the user will fill 4th may as from date and check on the from_flag indicating that he will come half day to office and will be on half day leave. So table data shows 4th May in from_date and flag is set 'N' so it is half day, and in to_date its 6th may so it is clear that 5th may is a full day leave and then to_flag is 'Y' which means it is a full day leave.


Quote:
2/ How do you interpret when from_date and to_date are same one but flags are different?


The system checks whether employee has entered same date in both the fields, if yes then he/she can set any one flag to 'N' which means its a half day. For the case in single day, both the flags are not allowed to be 'N'.

Regards.
Re: Help in SQL [message #584296 is a reply to message #584293] Sat, 11 May 2013 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then flags can also be NULL, so how to interpret a range of date with one flag to NULL and the other to 'Y' or 'N'?
Note that there is no constraint on your table, so you cannot guarantee your rules. The best option to explain the different forbidden cases is to create these constraints. Think about them and give the constraints in SQL syntax.

Regards
Michel
Re: Help in SQL [message #584297 is a reply to message #584296] Sat, 11 May 2013 04:46 Go to previous messageGo to next message
rocky_007
Messages: 32
Registered: March 2012
Location: India
Member
I am sorry, I forgot to add that. Please run these scripts
ALTER TABLE check_dates
MODIFY flag_from CONSTRAINT not_null NOT NULL;

ALTER TABLE check_dates
MODIFY flag_to CONSTRAINT not_null1 NOT NULL;

ALTER TABLE check_dates
ADD PRIMARY KEY (from_date, flag_from);


In my real tables the user has to set these flags. There are some other constraints too
Re: Help in SQL [message #584300 is a reply to message #584297] Sat, 11 May 2013 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
There are some other constraints too


Post them if they are relevant to the question.

Quote:
For the case in single day, both the flags are not allowed to be 'N'.


This is not covered by the constraint you gave.
As a flag can't be NULL then I go back to my question: "How do you interpret when from_date and to_date are same one but flags are different?".

Note that the SQL part of the problem is the easiest one. Once the specifications and model cover all cases then it take a minute or 2 to write the SQL query.

Regards
Michel



Re: Help in SQL [message #584301 is a reply to message #584300] Sat, 11 May 2013 06:11 Go to previous messageGo to next message
rocky_007
Messages: 32
Registered: March 2012
Location: India
Member
Michel, I thank you for taking pain in my problem.

There are no additional constraints related to this. Both the flags are not allowed to be 'N' and this is handled in the form which captures the data. The form makes sure all the things are right.

When from_date and to_date are same then both the flags are considered. if any one of them in 'N' then it means it is a half day otherwise full day.

Regards.
Re: Help in SQL [message #584303 is a reply to message #584301] Sat, 11 May 2013 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from check_dates order by 1;
FROM_DATE   F TO_DATE     F
----------- - ----------- -
02-MAY-2013 Y 02-MAY-2013 Y
04-MAY-2013 N 06-MAY-2013 Y
08-MAY-2013 N 09-MAY-2013 N
10-MAY-2013 Y 11-MAY-2013 N

SQL> select column_value "date",
  2         case when    (   column_value = from_date 
  3                      and (  (from_date = to_date and least(flag_from, flag_to) = 'N')
  4                          or flag_from='N' ) )
  5                   or ( column_value != from_date and flag_to = 'N' )
  6                then 0.5
  7              else 1
  8         end "days_count"
  9  from check_dates,
 10       table (cast(multiset(select from_date+level-1 
 11                            from dual connect by level <= to_date-from_date+1)
 12                   as sys.odciDateList))
 13  order by 1
 14  /
date        days_count
----------- ----------
02-MAY-2013          1
04-MAY-2013         .5
05-MAY-2013          1
06-MAY-2013          1
08-MAY-2013         .5
09-MAY-2013         .5
10-MAY-2013          1
11-MAY-2013         .5

[Updated on: Sat, 11 May 2013 08:59]

Report message to a moderator

Re: Help in SQL [message #584322 is a reply to message #584303] Sun, 12 May 2013 23:23 Go to previous messageGo to next message
rocky_007
Messages: 32
Registered: March 2012
Location: India
Member
YOU ARE A GENIUS... Nod

I really wish I could think and code like you.

Thank you Michel.

Regards.
Re: Help in SQL [message #584373 is a reply to message #584322] Mon, 13 May 2013 07:36 Go to previous messageGo to next message
pointers
Messages: 353
Registered: May 2008
Senior Member
Hi Micheal,

Its pretty known (atleast to me) given a question undestandable, you will be able to write sql very well.

I have seen in your sql queries this logic quite a lot of times i.e. TABLE(CAST(TABLE ...)

1. Could you please share if you have any specific good know how on this tip (i hope not from oracle documentaion Smile )

2. How to refer a column in the main query if there is second column is involved in the collection part (table function)

e.g. The below is just for illustartion and practsing TABLE(CAST(MULTISET.. ).

CREATE TABLE t_date (start_date date, end_date date);

INSERT INTO t_date VALUES (TO_DATE('06/12/2012','mm/dd/yyyy'),TO_DATE('06/14/2012','mm/dd/yyyy'));
INSERT INTO t_date VALUES (TO_DATE('06/15/2012','mm/dd/yyyy'),TO_DATE('06/16/2012','mm/dd/yyyy'));
INSERT INTO t_date VALUES (TO_DATE('06/18/2012','mm/dd/yyyy'),TO_DATE('06/18/2012','mm/dd/yyyy'));
INSERT INTO t_date VALUES (TO_DATE('06/20/2012','mm/dd/yyyy'),TO_DATE('06/24/2012','mm/dd/yyyy'));

I know the below works ---

select column_value as date_values
FROM t_date
    ,TABLE (CAST (MULTISET(SELECT  start_date+level-1
                            FROM dual
                                connect by level <= end_date-start_date+1
                        ) AS SYS.odcidatelist
                )
          )
/

How to refer a column explicitly but not using COLUMN_VALUE
The below throws error.

select t.bb
FROM t_date
    ,TABLE (CAST (MULTISET(SELECT  start_date+level bb,'b' -- added 'b' deliberatly
                            FROM dual
                                connect by level <= end_date-start_date+1
                        ) AS SYS.odcidatelist
                )
          ) t
/


Thank you in advance.

Regards,
Pointers
Re: Help in SQL [message #584377 is a reply to message #584373] Mon, 13 May 2013 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
1. Could you please share if you have any specific good know how on this tip (i hope not from oracle documentaion Smile )


This is a row generator.

Regards
Michel
Re: Help in SQL [message #584399 is a reply to message #584373] Mon, 13 May 2013 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The below throws error.


Of course as you cast an array of 2 columns, one date and one string, into an array of one column of datatype date.

SQL> create or replace type rectype as object (
  2    dt date,
  3    str varchar2(10)
  4  )
  5  /

Type created.

SQL> create or replace type tabtype as table of rectype
  2  /

Type created.

SQL> select * from table(cast(multiset(select sysdate+level-1, 'b' from dual connect by level<=3)as tabtype));
DT                  STR
------------------- ----------
13/05/2013 20:24:39 b
14/05/2013 20:24:39 b
15/05/2013 20:24:39 b

You have your answer... Smile

Regards
Michel
Re: Help in SQL [message #584570 is a reply to message #584399] Wed, 15 May 2013 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Pointers,

Any feedback?

Regards
Michel
Re: Help in SQL [message #584578 is a reply to message #584570] Wed, 15 May 2013 14:22 Go to previous message
pointers
Messages: 353
Registered: May 2008
Senior Member
@Micheal:
First of all, I dint see this post (there was one part responded by you, the other was not), but I know you would point this to my other post where you mentioned I dint respond to feedback. But yes, I dint see this post.

I would go through your query and would share feedback.

Thank you very much for your passion to help.

Regards,
Pointers
Previous Topic: DDL Trigger not working
Next Topic: substr of clob
Goto Forum:
  


Current Time: Thu Nov 27 18:35:00 CST 2014

Total time taken to generate the page: 0.20099 seconds