Find inconsistencies in data with date range - Mailing list pgsql-sql

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
identifyand potentially correct the summary data for date inconsistencies.  We have policies/red flag reports in place
tokeep this from happening, but we are now cleaning up history.  The query below works on a per store basis, but I'd
liketo be able to run this for all stores in the location table.<br /><br /> I've looked at some procedure codes
regardinglooping, but everything I try to create seems to give me problems.  THe code I'm trying is also below.  Does
anyonehave any suggestions on how to accomplish this?<br /><br /><br /><br /><u>Working Tables</u><br />   locations -
tablecontains store information, startup date, address, etc<br />   daily_salessummary - table holds daily sales
summaryby store (summary should be updated nightly).  eod_ts is End of Day Timestamp.<br /><br /><u>Query</u><br />
WITHdatelist AS(<br />   SELECT t1.GenDate as gendate, extract(dow from GenDate) AS dayofweek<br />   FROM (SELECT date
asGenDate<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_salessummaryAS 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 >
(SELECTstart_date FROM locations WHERE locationCode = 'US_FL_TAMPA_141')<br /><br /><br /><u>Desired Output</u> - could
outputto 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
datelistAS t1<br />                                               WHERE gendate NOT IN (SELECT t1.eod_ts FROM
daily_salessummaryAS t1<br />                                                                     JOIN locations AS t2
ONt1.location_id = t2.row_id<br />                                                                     WHERE
t2.locationCode= '' || location_table.locationCode  || '')                    <br />
                                            AND gendate > (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>