Date Split - SQL Help

[rashika]

Hi All,
This is a slight deviation from what my colleague taskchat had posted.
---------------------------------------------------------------------
Please help me with this query.

In table A , i have the following columns and records.

Inserted Date, Start_Date , End_Date, Qty
9/1/02, 10/01/02, 10/31/02, 30
9/2/02, 10/10/02, 10/20/02, 10
9/3/02, 10/1/02, 10/1/02 , 5
9/4/02, 10/1/02, 10/3/02, 7

The user can only insert a startdate, end date and a qty
he cannot update/delete records but can insert records if he changes
his mind on the qty
the latest records overrides any previous records.
So he came in on 9/1 and entered for the whole month
then he kept changing his mind.
So
Now in the web app, i have to display the above dates in the following
manner.

10/01/02 - 10/03/02 - 7
10/04/02 - 10/09/02 - 30
10/10/02 - 10/20/02 - 10
10/21/02 - 10/31/02 - 30

Performancewise, please suggest a stored procedure/sql to get data from the above table and display the data in the above manner.
-------------------------------------------------------------------

[chrisrlong]

Originally posted by rashika
Hi All,
This is a slight deviation from what my colleague taskchat had posted.

What is the deviation? And if it is only slight, then all you need is a slight modification to the solution I already provided. And how would we end up with a 'slight' deviation anyway? Are you guys working on the same problem with the same tables or not?

I already gave you an extremely efficient solution. If there is a specific problem with that solution, please let me know exactly what it is.

- Chris

[taskchat]

you've given a wise solution,but we feel tough to implement that. The problem is most of OLAP functions work in oracle 9i,we use 8i.
Please nvm abt the previous post.
Thanks for all efficient solutions.

[pando]

strange, I use those function quite often and I am using 8.1.7 mostly

they are called analytic functions and not OLAP btw

[chrisrlong]

Originally posted by taskchat
you've given a wise solution,but we feel tough to implement that. The problem is most of OLAP functions work in oracle 9i,we use 8i.
Please nvm abt the previous post.
Thanks for all efficient solutions.

Analytical functions, as Pando noted, do, indeed, work in 8i. However, you must use dynamic SQL to use them. See this thread for details:

http://www.dbasupport.com/forums/sho...threadid=31261

Simply posting the exact same question in a new thread was not the proper recourse, BTW. A response to the existing thread specifying your problem with using the analytical functions would have been more appropriate. You actually had posted that you hit a problem, but you never responded to say whether or not you had gotten past it.

Now, I use the Enterprise Edition solely, so I suppose that it is possible that 'lesser' versions do not include analytical functions - hopefully somebody else here can let you know about that.


So, if it turns out that you actually cannot use the analytical functions (if they are not supported in your version), then we will need a new solution. But let's make absolutely sure that we need a new solution before going down that road (at least, before I take another shot at it).

- Chris

[taskchat]

Pando, I might be wrong, as i got this error "feature not enabled: OLAP window function". I thought those are OLAP(online analytical processing)functions.
Chris, I already've dynamic sql. i thought static sql will improve the performance thatz why i approached the forum.
nvm,i am going to use dynamic sql only....
thanks guys

[ales]

The message "feature not enabled: OLAP window function" shows that you use the Standard Edition. You must have EE in order to use analytical functions.

[chrisrlong]

Originally posted by taskchat

Chris, I already've dynamic sql. i thought static sql will improve the performance thatz why i approached the forum.

There is virtually no difference between the performance of dynamic vs. statis SQL. This should not even be a consideration.

Originally posted by ales
The message "feature not enabled: OLAP window function" shows that you use the Standard Edition. You must have EE in order to use analytical functions.

So it appears we don't have the analytical functions at our disposal. That is unfortunate. Here are the solutions without analytical functions. The INSERT/UPDATE simply uses sub-SELECTs to get the 'winning' row for each day:

UPDATE
   Results_T   R
SET
   (
   MainTableID    ,
   QTY            ,
   LastUserID     ,
   LastTimestamp
   )   =
   (
   SELECT
      MT.MainTableID    ,
      MT.QTY            ,
      MT.LastUserID     ,
      MT.LastTimestamp
   FROM
      MainTable_T       MT   ,
      DateDimension_T   DD
   WHERE
      MT.Submission_ID   >       :Submission_ID   AND
      DD.SQL_DT         BETWEEN  MT.SDate
                        AND      MT.EDate         AND
      DD.SQL_DATE       =        R.Date           AND
      MT.TimeStamp      =
         (
         SELECT
            MAX(MT2.TimeStamp)
         FROM
            MainTable_T       MT2,
            DateDimension_T   DD2
         WHERE
            DD2.SQL_DT        =        DD.SQL_DATE      AND
            DD2.SQL_DT        BETWEEN  MT2.SDate
                              AND      MT2.EDate        
         )
   )
WHERE
   EXISTS
      (
      SELECT
         1
      FROM
         MainTable_T MT2
      WHERE
         MT2.Submission_ID >       :Submission_ID   AND
         R.DATE            BETWEEN  MT2.SDate
                           AND      MT2.EDate        
      )


INSERT INTO
   Results_T   R
SELECT
   MainTableID    ,
   QTY            ,
   LastUserID     ,
   LastTimestamp  
FROM
   MainTable_T       MT   ,
   DateDimension_T   DD
WHERE
   MT.Submission_ID   >       :Submission_ID   AND
   DD.SQL_DT         BETWEEN  MT.SDate
                     AND      MT.EDate         AND
   MT.TimeStamp      =
      (
      SELECT
         MAX(MT2.TimeStamp)
      FROM
         MainTable_T       MT2,
         DateDimension_T   DD2
      WHERE
         DD2.SQL_DT        =        DD.SQL_DATE      AND
         DD2.SQL_DT        BETWEEN  MT2.SDate
                           AND      MT2.EDate  
      )                                        AND
   NOT EXISTS   (
      SELECT
         1
      FROM
         Results_T   R2
      WHERE
         R2.Date   =   DD.SQL_DT
      )

Note that there are some other code changes as well - I didn't make these tables so I'm still not positive these are perfect - but they're fairly obvious, so any issues should be minor.

Here, then is the PL/SQL solution to build the resultset. Without analytical functions, the SQL-only solution would be very expensive and complex, so the PL/SQL solution becomes the solution of choice.

CREATE OR REPLACE PACKAGE CRL_PKG_HandleRanges
AS

   TYPE T_DATE_T     IS TABLE OF DATE     INDEX BY BINARY_INTEGER;
   TYPE T_NUMBER_T   IS TABLE OF NUMBER   INDEX BY BINARY_INTEGER;

   PROCEDURE CRL_sp_DisplayNewRanges
      (
      p_Start_Dt  IN    DATE       ,
      p_End_Dt    IN    DATE       ,
      p_BDate_T   OUT   T_DATE_T   ,
      p_EDate_T   OUT   T_DATE_T   ,
      p_Qty_T     OUT   T_NUMBER_T
      )   ;
END;
/   


CREATE OR REPLACE PACKAGE BODY CRL_PKG_HandleRanges
AS


PROCEDURE CRL_sp_DisplayNewRanges
   (
   p_Start_Dt  IN    DATE       ,
   p_End_Dt    IN    DATE       ,
   p_BDate_T   OUT   T_DATE_T   ,
   p_EDate_T   OUT   T_DATE_T   ,
   p_Qty_T     OUT   T_NUMBER_T
   )
AS

   CURSOR
      l_Cur
   IS
      SELECT
         *
      FROM
         Result_T
      WHERE
         Target_Dt   BETWEEN  p_Start_Dt
                     AND      p_End_Dt
      ORDER BY
         Target_DT
   ;

   l_OldResult_R   RESULT_T%ROWTYPE   ;

   l_BDate         DATE               ;
   l_EDate         DATE               ;
   l_Qty           NUMBER             ;

   l_Record_Ct     INTEGER   :=   0   ;
   
BEGIN

   -- --------------------------------------------------
   -- Loop through all the records to build the resulset
   -- --------------------------------------------------

   FOR
      l_Result_R
   IN
      l_CUR
   LOOP

         -- See if we are starting a new range
      IF ( l_OldResult_R.Target_Dt   IS   NULL                    )   OR
         ( l_OldResult_R.Target_Dt   <>   l_Result_R.Target_Dt - 1)   OR
         ( l_OldResult_R.QTY         <>   l_Result_R.Qty          )   
      THEN

            -- See if there was an old range to close
         IF ( l_OldResult_R.Target_Dt   IS   NOT NULL   )   THEN

               -- Store the End of the previous range
            l_EDate      :=   l_OldResult_R.Target_Dt   ;

               -- Increment the record count
            l_Record_Ct  :=   l_Record_Ct   +   1       ;

               -- Add the previous range to the resultset
            p_BDate_T   (l_Record_Ct)   :=   l_BDate   ;
            p_EDate_T   (l_Record_Ct)   :=   l_EDate   ;
            p_Qty_T     (l_Record_Ct)   :=   l_Qty     ;

            DBMS_OUTPUT.Put_Line 
               ( 
               l_BDate   ||   '   ' ||
               l_EDate   ||   '   ' ||
               l_Qty
               )   ;

         END IF;

            -- Start the new range
         l_BDate   :=   l_Result_R.Target_Dt   ;
         l_Qty     :=   l_Result_R.Qty         ;

      END IF;

      l_OldResult_R   :=   l_Result_R;

   END LOOP;


   -- ----------------------------
   -- Don't forget the last record
   -- ----------------------------

      -- Store the End of the previous range
   l_EDate      :=   l_OldResult_R.Target_Dt   ;

      -- Increment the record count
   l_Record_Ct  :=   l_Record_Ct   +   1       ;

      -- Add the previous range to the resultset
   p_BDate_T   (l_Record_Ct)   :=   l_BDate   ;
   p_EDate_T   (l_Record_Ct)   :=   l_EDate   ;
   p_Qty_T     (l_Record_Ct)   :=   l_Qty     ;

   DBMS_OUTPUT.Put_Line 
      ( 
      l_BDate   ||   '   ' ||
      l_EDate   ||   '   ' ||
      l_Qty
      )   ;

END CRL_sp_DisplayNewRanges;

END CRL_PKG_HandleRanges;

Note that this assumes that you are using an ODBC driver that can convert table arrays into resultsets. If you don't understand how that is done, someone here should be able to help you.

Let me know how this goes,

- Chris