Re: Find inconsistencies in data with date range - Mailing list pgsql-sql
From | s d |
---|---|
Subject | Re: Find inconsistencies in data with date range |
Date | |
Msg-id | CAKyoTgbVfWULPdG1-BX3FSfm=hT3aFRs=avM+kg=0ozhP_RCwg@mail.gmail.com Whole thread Raw |
In response to | Find inconsistencies in data with date range (Jason Aleski <jason.aleski@gmail.com>) |
Responses |
Re: Find inconsistencies in data with date range
|
List | pgsql-sql |
Hi,
Something like this?
It inserts error records into a table called locationrep.
create or replace function finderror() returns void as $$
declare
startd date;
daterec record;
begin
select into startd min(startdate) from location; --identify the earliest opening date
--iterating trough dates from that date until now
for daterec in select generate_series::date as repdate from generate_series(startd,now()::date,'1 day'::interval) loop
--insert ito the error table all the shops which not have an entry from the current date and opened before the said date
insert into locationrep select shop,daterec.repdate from location where startdate<=daterec.repdate and not exists(select 1 from daily_salessummary where shop=location.shop and reportdate=daterec.repdate);
end loop;
end;
$$ language plpgsql;
Regards,
Sándor Daku
On 6 March 2015 at 22:38, Jason Aleski <jason.aleski@gmail.com> 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')
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