agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: Jason Aleski <jason.aleski@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Find inconsistencies in data with date range
Date: Fri, 06 Mar 2015 14:12:43 -0800
Message-ID: <54FA265B.5080305@aklaver.com> (raw)
In-Reply-To: <54FA1E61.9000201@gmail.com>
References: <54FA1E61.9000201@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (5+ messages) latest in thread
Message-ID: <54FA265B.5080305@aklaver.com>
Permalink: ../54FA265B.5080305@aklaver.com/
Also on: postgresql.org/message-id/54FA265B.5080305@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, jason.aleski@gmail.com
Subject: Re: Find inconsistencies in data with date range
In-Reply-To: <54FA265B.5080305@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox