Message-ID: <54FA1E61.9000201@gmail.com>
Date: Fri, 06 Mar 2015 15:38:41 -0600
From: Jason Aleski <jason.aleski@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:31.0) Gecko/20100101 Thunderbird/31.5.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Find inconsistencies in data with date range
Content-Type: multipart/alternative;
 boundary="------------010503010901070701070607"
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

This is a multi-part message in MIME format.
--------------010503010901070701070607
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

I know I can do this Java, but I'd rather have this running as a Stored 
Procedure.  What I am wanting to do is identify and potentially correct 
the summary data for date inconsistencies.  We have policies/red flag 
reports in place to keep this from happening, but we are now cleaning up 
history.  The query below works on a per store basis, but I'd like to be 
able to run this for all stores in the location table.

I've looked at some procedure codes regarding looping, but everything I 
try to create seems to give me problems.  THe code I'm trying is also 
below.  Does anyone have any suggestions on how to accomplish this?


_Working Tables_
   locations - table contains store information, startup date, address, etc
   daily_salessummary - table holds daily sales summary by store 
(summary should be updated nightly).  eod_ts is End of Day Timestamp.

_Query_
WITH datelist AS(
   SELECT t1.GenDate as gendate, extract(dow from GenDate) AS dayofweek
   FROM (SELECT date as GenDate
         FROM 
generate_series('1985-01-01'::date,CURRENT_TIMESTAMP::date,'1 
day'::interval) date
        ) AS t1
)
SELECT gendate FROM datelist AS t1
WHERE gendate NOT IN (SELECT t1.eod_ts FROM daily_salessummary AS t1
                       JOIN locations AS t2 ON t1.location_id = t2.row_id
                       WHERE t2.locationCode = 'US_FL_TAMPA_141')

   AND gendate > (SELECT start_date FROM locations WHERE locationCode = 
'US_FL_TAMPA_141')


_Desired Output_ - could output to an exceptions table
StoreCode 'US_FL_TAMA_141' missing daily summary for 1998-01-01
StoreCode 'MX_OAXACA_SALINA_8344' missing daily summary for 2011-06-05


_ProcedureSQL_ (contains unknown errors)
DECLARE
   CURSOR location_table IS
     SELECT locationCode FROM locations;
   BEGIN
     FOR thisSymbol IN ticker_tables LOOP
       EXECUTE IMMEDIATE 'WITH datelist AS(
                                               SELECT t1.GenDate as 
gendate, extract(dow from GenDate) AS dayofweek
                                                 FROM (SELECT date as 
GenDate
                                                       FROM 
generate_series('1985-01-01'::date,CURRENT_TIMESTAMP::date,'1 
day'::interval) date
                                                      ) AS t1
                                               )
                                               SELECT gendate FROM 
datelist AS t1
                                               WHERE gendate NOT IN 
(SELECT t1.eod_ts FROM daily_salessummary AS t1
JOIN locations AS t2 ON t1.location_id = t2.row_id
WHERE t2.locationCode = '' || location_table.locationCode  || '')
                                              AND gendate > (SELECT 
start_date FROM locations WHERE locationCode = '' || 
location_table.locationCode  || '')';
     END LOOP;
   END;


-- 
Jason Aleski / IT Specialist


--------------010503010901070701070607
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=utf-8">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    I know I can do this Java, but I'd rather have this running as a
    Stored Procedure.  What I am wanting to do is identify and
    potentially correct the summary data for date inconsistencies.  We
    have policies/red flag reports in place to keep this from happening,
    but we are now cleaning up history.  The query below works on a per
    store basis, but I'd like to be able to run this for all stores in
    the location table.<br>
    <br>
    I've looked at some procedure codes regarding looping, but
    everything I try to create seems to give me problems.  THe code I'm
    trying is also below.  Does anyone have any suggestions on how to
    accomplish this?<br>
    <br>
    <br>
    <br>
    <u>Working Tables</u><br>
      locations - table contains store information, startup date,
    address, etc<br>
      daily_salessummary - table holds daily sales summary by store
    (summary should be updated nightly).  eod_ts is End of Day Timestamp.<br>
    <br>
    <u>Query</u><br>
    WITH datelist AS(<br>
      SELECT t1.GenDate as gendate, extract(dow from GenDate) AS
    dayofweek<br>
      FROM (SELECT date as GenDate<br>
            FROM
    generate_series('1985-01-01'::date,CURRENT_TIMESTAMP::date,'1
    day'::interval) date<br>
           ) AS t1  <br>
    )<br>
    SELECT gendate FROM datelist AS t1<br>
    WHERE gendate NOT IN (SELECT t1.eod_ts FROM daily_salessummary AS t1<br>
                          JOIN locations AS t2 ON t1.location_id =
    t2.row_id<br>
                          WHERE t2.locationCode = 'US_FL_TAMPA_141')<br>
                          <br>
      AND gendate &gt; (SELECT start_date FROM locations WHERE
    locationCode = 'US_FL_TAMPA_141')<br>
    <br>
    <br>
    <u>Desired Output</u> - could output to an exceptions table<br>
    StoreCode 'US_FL_TAMA_141' missing daily summary for 1998-01-01<br>
    StoreCode 'MX_OAXACA_SALINA_8344' missing daily summary for
    2011-06-05<br>
    <br>
    <br>
    <u>ProcedureSQL</u> (contains unknown errors)<br>
    DECLARE<br>
      CURSOR location_table IS<br>
        SELECT locationCode FROM locations;<br>
      BEGIN<br>
        FOR thisSymbol IN ticker_tables LOOP<br>
          EXECUTE IMMEDIATE 'WITH datelist AS(<br>
                                                  SELECT t1.GenDate as
    gendate, extract(dow from GenDate) AS dayofweek<br>
                                                    FROM (SELECT date as
    GenDate<br>
                                                          FROM
    generate_series('1985-01-01'::date,CURRENT_TIMESTAMP::date,'1
    day'::interval) date<br>
                                                         ) AS t1  <br>
                                                  )<br>
                                                  SELECT gendate FROM
    datelist AS t1<br>
                                                  WHERE gendate NOT IN
    (SELECT t1.eod_ts FROM daily_salessummary AS t1<br>
                                                                       
    JOIN locations AS t2 ON t1.location_id = t2.row_id<br>
                                                                       
    WHERE t2.locationCode = '' || location_table.locationCode  || '') 
                      <br>
                                                 AND gendate &gt;
    (SELECT start_date FROM locations WHERE locationCode = '' ||
    location_table.locationCode  || '')';<br>
        END LOOP;<br>
      END;<br>
    <br>
    <br>
    <br>
    <pre class="moz-signature" cols="72">-- 
Jason Aleski / IT Specialist</pre>
  </body>
</html>

--------------010503010901070701070607--