Re: Find inconsistencies in data with date range

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(dot)aleski(at)gmail(dot)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
>
>