6 Replies Latest reply: Oct 3, 2013 8:18 AM by 1011589 RSS

    NO SALES DATES

    1011589

      I HAVE TO TABLES financialyr_mst and invoice_hdr. i want no sales date for particular financial year.

       

      invoice table----------

      NAME                            Null?     Type

      ------------------------------- --------- -----

      INV_CNTRL_NO                    NOT NULL  NUMBER(10,0)

      FINYR_CODE                                VARCHAR2(6)

      PERIOD_CODE                               NUMBER(3,0)

      INV_TYPE                                  VARCHAR2(10)

      INV_NO                                    NUMBER(10,0)

      INV_DT                                    DATE

      CUST_CODE                                 VARCHAR2(10)

      ORD_CNTRL_NO                              NUMBER(10,0)

      INV_VALUE                                 NUMBER(12,2)

      /

      financial yr table---------------

       

      NAME                            Null?     Type

      ------------------------------- --------- -----

      FINYR_CODE                      NOT NULL  VARCHAR2(6)

      START_DATE                                DATE

      END_DATE                                  DATE

       

      ------------------

       

      i am trying these query

       

      select (start_date+level-1) fin_dt from finyr_mst

      where finyr_code =13

      connect by level<=(end_date-start_date)+1

      minus

      select inv_dt from  inv_hdr.

       

      --------------------

      but that query returninig thousand of records.i want only 365 records???????

        • 1. Re: NO SALES DATES
          Karthick_Arp

          Looking for this?

           

          with finyr_mast_13

          as

          (

          select (start_date+level-1) fin_dt

             from finyr_mst

            where finyr_code = 13

          connect by level <= (end_date-start_date)+1

          )

          select fin_dt

            from finyr_mast_13 a

            left

            join inv_hdr b

              on a.fin_dt = b.fnv_dt

          where b.fnv_dt is null;

           

          I am not very clear on what you want. May be you need to explain it better.

          • 2. Re: NO SALES DATES
            1011589

            i want no sales date.....

            • 3. Re: NO SALES DATES
              Karthick_Arp

              The above query does not work?

              • 4. Re: NO SALES DATES
                Frank Kulash

                Hi,

                 

                I think you want something like this:

                 

                WITH days_wanted AS

                (

                    SELECT  finyr_code

                    ,       start_date + 1 - LEVEL AS a_date

                    FROM    finyr_mst

                --  START WITH   finyr_code   IN (13)   -- if wanted

                    CONNECT BY   finyr_code         = PRIOR finyr_code

                            AND  LEVEL             <= end_date + 1 - start_date

                            AND  PRIOR SYS_GUID ()  IS NOT NULL

                )

                SELECT    d.*

                FROM              days_wanted  d

                LEFT OUTER JOIN   inv_hdr      i  PARTITION BY (i.finyr_code)

                                                  ON  i.inv_dt  = d.a_date

                WHERE     i.inv_dt   IS NULL

                ORDER BY  d.finyr_code

                ,         d.a_date

                ;

                I hope this answers your question.
                If not, post  a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
                Explain, using specific examples, how you get those results from that data.   For example, do any of the columns in inv_hdr (other than inv_dt) play any role in this problem, particularly finyr_code?  If not, don't post them.

                Will you ever want to do this query for 2 (or more) values of finyr_mst.finyr_code at the same time?  Make sure your sample data and results show what you want.
                Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

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

                • 5. Re: NO SALES DATES
                  1011589

                  Sir,

                  its coming too many records?

                  //

                  select a.fin_dt

                  from

                  (select (start_date+level-1) fin_dt

                     from finyr_mst

                    where finyr_code = 13

                  connect by level <= (end_date-start_date)+1

                  ) a, inv_hdr b

                  where  (a.fin_dt-b.inv_dt)>0

                  • 6. Re: NO SALES DATES
                    1011589

                    i got right answer.thanks for support..romba  nandri

                     

                     

                     

                     

                    select a.start_date+level-1 no_sale_date from

                     

                     

                    (select start_date start_date from finyr_mst where finyr_code=13) a

                     

                     

                    connect by level<=(select end_date-start_date from finyr_mst where finyr_code=13)+1

                    minus

                    select inv_dt from inv_hdr  where finyr_code=13