5 Replies Latest reply: Jul 8, 2014 7:31 AM by Etbin RSS

    Need help, Please advise.

    user13516599

      Hi All,

       

      I need some help in creating a sql, please advise.

      All I have is the frequency which can be DAILY or WEEKLY or MONTHLY and the WeekendSkipID which can be either Yes or No. so for eg

       

      Case 1. I want to fetch the data from a table A on a DAILY frequency and WeekendSkipID set to N.. that means.. fetch data everyday even on weekends. i.e. Friday will give Thursday rows and Saturday will give Friday rows from Table A.

       

      Case 2. I want to fetch the data from a table A on a DAILY frequency and WeekendSkipID set to Y.. that means.. fetch data everyday except Weekends. i.e. Friday will give Thursday rows and for Saturday/Sunday it will skip and on Monday it will give Friday rows from Table A.

       

      Similarly for Frequency WEEKLY 7 days data and WeekendSkipID set to N.. that means. fetch data on a WEEKLY basis(7 Days) even on weekends too.

       

      I just wanted to see if this can be easily done via sql.

       

      Thanks in Advance.

        • 1. Re: Need help, Please advise.
          proora

          Please give us sample of Data

          • 2. Re: Need help, Please advise.
            Frank Kulash

            Hi,

             

            Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements) for all the tables involved, so the people who want to help you can re-create the problem and test their ideas.  Also post the results you want from that data, and an explanation of how you get those results from that data.

            Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

            See the forum FAQ: https://forums.oracle.com/message/9362002

             

            As far as I can tell, you can do what you want using SQL.

            • 3. Re: Need help, Please advise.
              user13516599

              Apologies, I should have supplied sample data.

               

              User can select frequency which is DAILY, WEEKLY or MONTHLY. and WeekendSkipID which mean to skip the data of the weekened or not.

              Table A

               

              col1 col2 ... last_updated_timestamp

              A     B                7/3 Thursday

              c      d                7/4 Friday

              e      f                  7/4

              e1     f1                  7/5

              e2     f2                  7/6

              e3     f4                  7/7

              e4     f5                  7/8

              e5     f6                  7/9

              e6    f7                7/10

              e7     f8                  7/11

               

               

              so if USER select DAILY and WeekendSkipID N then and if I am running the query on 7/4 then fetch me 7/3 row and so on.

              But if USER select DAILY and WeekendSkipID Y ( I will skip the query execution on saturday/sunday) then if I am running the query on 7/7(monday) then get row from 7/4 and if running the query on 7/8 then get me data for 7/7.

               

              is it possible to do this with a sql ? Please advise, any help is appreciated.

              • 4. Re: Need help, Please advise.
                Frank Kulash

                Hi

                user13516599 wrote:

                 

                Apologies, I should have supplied sample data....

                 

                Right; you always need to supply CREATE TABLE and INSERT statements for the sample data.

                Post the exact results you want from the given data.  In this case, the results depend on which of the 3 inputs you get, so show all 3 results.  Make it clear which results are for which input.

                 

                What is weekendskipid?  Is that another run-time parameter?  If so, you probably don't need to post results for all 6 combinations; start with just 3 (one DAILY, one WEEKLY, one MONTHLY) but not all for the same value of weekendskipid.

                • 5. Re: Need help, Please advise.
                  Etbin

                  Something to play with

                   

                  with

                  table_a as

                  (select /*+ materialize */

                          date_,data_

                     from (select trunc(sysdate,'month') + level - 1 date_,

                                  dbms_random.string('l',dbms_random.value(10,20)) data_

                             from dual

                           connect by level <= 10

                           union all

                           select trunc(sysdate,'month') + trunc(dbms_random.value(0,10)) date_,

                                  dbms_random.string('l',dbms_random.value(10,20)) data_

                             from dual

                           connect by level <= 20

                          )

                  )

                  select date_,

                         data_,

                         :frequency frequency,

                         :skipweekend skip_weekend,

                         to_date(:the_date,'yyyymmdd') date_requested,

                         to_char(date_,'dy','nls_date_language = ''american''') day_abbrev

                    from table_a

                  where date_ = to_date(:the_date,'yyyymmdd') -

                                 case when:frequency = 'daily'

                                      then case when :skipweekend = 'n'

                                                then 0

                                                when :skipweekend = 'y'

                                                then case when to_char(to_date(:the_date,'yyyymmdd'),'dy','nls_date_language = ''american''') = 'sat'

                                                          then 1

                                                          when to_char(to_date(:the_date,'yyyymmdd'),'dy','nls_date_language = ''american''') = 'sun'

                                                          then 2

                                                          else 0

                                                     end

                                           end

                                      else 0

                                 end

                   

                  or in case you're looking for nls independent solution

                   

                  select date_,

                         data_,

                         :frequency frequency,

                         :skipweekend skip_weekend,

                         to_date(:the_date,'yyyymmdd') date_requested

                    from table_a

                  where date_ = to_date(:the_date,'yyyymmdd') -

                                 case when:frequency = 'daily'

                                      then case when :skipweekend = 'n'

                                                then 0

                                                when :skipweekend = 'y'

                                                then case when to_date(:the_date,'yyyymmdd') = trunc(to_date(:the_date,'yyyymmdd'),'iw') + 5

                                                          then 1

                                                          when to_date(:the_date,'yyyymmdd') = trunc(to_date(:the_date,'yyyymmdd'),'iw') + 6

                                                          then 2

                                                          else 0

                                                     end

                                           end

                                      else 0

                                 end


                  Regards

                   

                  Etbin