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

On 03/06/2015 01:38 PM, Jason Aleski wrote:
> 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')


First in above and in variation below I would probably do some alias 
renaming. I pretty sure t1 means different things throughout the query, 
but is hard to follow exactly what.

>
>
> _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;

I do not use cursors enough in plpgsql to be sure, but I think the above 
definition is incorrect:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-cursors.html

To reduce the moving parts I would write the function without the cursor 
and just hardwire the location information to start with to get a 
working sample.

>
>
>
> --
> Jason Aleski / IT Specialist
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com