Re: Find inconsistencies in data with date range

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(dot)klaver(at)aklaver(dot)com