Forum Stats

  • 3,770,911 Users
  • 2,253,180 Discussions
  • 7,875,653 Comments

Discussions

How generate from date and to date list?

Chandler Bing
Chandler Bing Member Posts: 179 Bronze Badge

Hi Experts,

I have 2 date field that will be enter by user i.e from date and to date and after enter the date user will click on save button .

On click on save button a report will be show according to below format

1st – 8th of month, 9th – 15th of Month, 16th -24th of Month, 25th – 30th/31st of Month.

for example: if user enter the 01-09-2021 and 31-10-2021 then list generate as below:

From Date To Date

01-09-2021 08-09-2021

09-09-2021 15-09-2021

16-09-2021 24-09-2021

25-09-2021 30-09-2021

01-10-2021 08-10-2021

09-10-2021 15-10-2021

16-10-2021 24-10-2021

25-10-2021 30-10-2021

and if user enter the date between 01-09-2021 and 27-09-2021 then format will be :

01-09-2021 08-09-2021

09-09-2021 15-09-2021

16-09-2021 24-09-2021

25-10-2021 27-10-2021

i need help how to achieve this through plsql .



i know maybe this is not the right platform to ask this type of question but i have no choice because i m stuck with this for last few days. if any one know how to achieve this its really appreciate.

«1

Answers

  • Chandler Bing
    Chandler Bing Member Posts: 179 Bronze Badge

    @fac586 pls any suggestions?

  • StewStrykerNH
    StewStrykerNH Member Posts: 84 Blue Ribbon
    edited Sep 28, 2021 6:13PM

    I solved this need a few years back by creating a pipeline function that you can call from a SQL query. It's not pretty or include any cool techniques, but it gets the job done.

    Oddly, this is the second time I've shared it in the past week, but never shared before!


    -- Compile these to get a table function that returns a stream of dates

    CREATE OR REPLACE TYPE date_obj_type AS OBJECT

      (the_date DATE);


    CREATE OR REPLACE TYPE date_stream_type AS TABLE OF date_obj_type;


    -- Pipelined function returns stream of dates in provided range - MM/DD/YYYY

    CREATE OR REPLACE FUNCTION date_range_stream(start_date_in IN DATE,

                           end_date_in  IN DATE) RETURN date_stream_type 

                           DETERMINISTIC

      AUTHID DEFINER

      PIPELINED IS

      /*

        Parameters:   start_date_in - First date to return (truncated)

                end_date_in  - Last date to return, inclusive

       

        Results:    date

       

        Author:     Stew Stryker

       

        Usage:     SELECT to_date(text01, 'MM/DD/YYYY') AS a_date

                 FROM TABLE(date_range_stream('01-MAR-2009', SYSDATE))

       

                Returns a rows from starting date to current

      */

      cur_date DATE := TRUNC(start_date_in);

      date_row date_obj_type := date_obj_type(NULL);

    BEGIN

      WHILE cur_date <= TRUNC(end_date_in)

      LOOP

        date_row.the_date := cur_date;

        PIPE ROW(date_row);

        cur_date := cur_date + 1;

      END LOOP;


      RETURN;


    EXCEPTION

      WHEN no_data_found THEN

        RETURN;

      WHEN OTHERS THEN

        dbms_output.put_line('EXCEPTION IN date_range_stream - ' || SQLCODE || ': ' ||

                   SQLERRM);

        RETURN;

    END date_range_stream;

    /

    -- Date Stream to show results

    SELECT the_date

     FROM TABLE(date_range_stream(to_date('27-aug-2021', 'dd-mon-yyyy'), to_date('10-sep-2021', 'dd-mon-yyyy')));

    Scott Wesley
  • Scott Wesley
    Scott Wesley Member Posts: 6,127 Gold Crown

    Who needs PL/SQL? ;p

    with data as (select date '2021-09-01' df, date '2021-10-31' dt from dual)   
    select df+rownum-1 dts
    from data
    connect by level <= dt-df+1 -- days_between
    
    StewStrykerNH
  • Chandler Bing
    Chandler Bing Member Posts: 179 Bronze Badge

    Hi @StewStrykerNH @Scott Wesley ,

    Thanks for your reply , i think i didnt explain my requirement well because both your answers dosent get the data that i want.

    Your both query data return in on one column and continous of data like below image

    but i dont want this as i explain in my post i want format like below

    if user enter from date as '01-09-2021' and to_date like '30-10-2021'


    hope i now explain well.

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,149 Red Diamond

    hope i now explain well.

    Not really. Do not post screenshots of textual information, such as code, sample data, or error messages. Screenshots are not accessible to visually impaired members of the community, cannot be indexed by search engines, or copied and pasted to assist in developing a solution.

    You also need to explain why the data is required in this format and the rules used to produce it. Why do you need 8 day periods apparently starting on arbitrary days of the week rather than 7 day weeks starting on Mondays?

  • Chandler Bing
    Chandler Bing Member Posts: 179 Bronze Badge

    Hi @fac586 ,

    I have to post this screenshots to explain briefly as you see that i already explain in post without any attachment but ok i understand that next time i will be consider this.

    and why i need this format, in my application there is billing schedule option in which user can select the billing period like monthly,daily,weekly,fortnighlty and then user enter the from date and to date and click on generate button so billing schedule will be generate acording to entered data.

    so i need one another option in billing period that is standard , in standard billing period date that will generate as i explain before. i need this because this billing schedule generate in company .I already do that monthly ,daily, weekly, fortnighlty but i dont understand how to generate this schedule.

  • StewStrykerNH
    StewStrykerNH Member Posts: 84 Blue Ribbon

    @Scott Wesley Thanks so much for this!

    I've been telling myself for years that I needed to learn how to use CONNECT BY for more things, but I never think of stuff like this! I actually opened a browser page for a tutorial just yesterday morning; I guess I should study it! :-)

    Scott Wesley
  • StewStrykerNH
    StewStrykerNH Member Posts: 84 Blue Ribbon
    edited Sep 29, 2021 2:10PM

    I thought your difficulty was with generating a date stream, not with grouping the dates for your specific report format. To the original requirement, if it was me, I'd combine:

    1. the SQL power of CONNECT BY that Scott suggested, with
    2. the procedural capabilities of a pipeline function, to return both date columns and skip by 8 days.

    But I'm sure someone else has a cleaner method!

    The rest is up to you.

    p.s. BTW, how are you and Monica doing? ;-)

    Scott Wesley
  • Chandler Bing
    Chandler Bing Member Posts: 179 Bronze Badge

    yes, you are right difficulty was with generating a date stream and i dint find the any logic for this but i am trying.


    BTW, i am great and who cares how she is . ;-)

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,149 Red Diamond

    and why i need this format, in my application there is billing schedule option in which user can select the billing period like monthly,daily,weekly,fortnighlty and then user enter the from date and to date and click on generate button so billing schedule will be generate acording to entered data.

    so i need one another option in billing period that is standard , in standard billing period date that will generate as i explain before. i need this because this billing schedule generate in company .I already do that monthly ,daily, weekly, fortnighlty but i dont understand how to generate this schedule.

    This is how the question should have been originally posted, along with the code already developed and what is still missing—a precise definition of how this "standard" billing schedule is to be generated.

    How have you implemented the monthly/daily/weekly/fortnightly schedules? And why and how is the "standard" one significantly different?

    StewStrykerNH