agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Jason Aleski <jason.aleski@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Find inconsistencies in data with date range
Date: Fri, 06 Mar 2015 15:38:41 -0600
Message-ID: <54FA1E61.9000201@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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 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
view thread (5+ messages) latest in thread
Message-ID: <54FA1E61.9000201@gmail.com>
Permalink: ../54FA1E61.9000201@gmail.com/
Also on: postgresql.org/message-id/54FA1E61.9000201@gmail.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: jason.aleski@gmail.com
Subject: Re: Find inconsistencies in data with date range
In-Reply-To: <54FA1E61.9000201@gmail.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