Re: Find inconsistencies in data with date range

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