Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YVuS3-0007Kf-Cp for pgsql-sql@arkaria.postgresql.org; Thu, 12 Mar 2015 04:10:27 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YVuS2-0002oB-NV for pgsql-sql@arkaria.postgresql.org; Thu, 12 Mar 2015 04:10:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YVuS1-0002o4-FQ for pgsql-sql@postgresql.org; Thu, 12 Mar 2015 04:10:25 +0000 Received: from mail-ie0-x22c.google.com ([2607:f8b0:4001:c03::22c]) by magus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YVuRw-0002E3-0K for pgsql-sql@postgresql.org; Thu, 12 Mar 2015 04:10:23 +0000 Received: by iecsl2 with SMTP id sl2so14505111iec.1 for ; Wed, 11 Mar 2015 21:10:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type; bh=o44QRYgaQSB4yZueypAQ5WsXQ2rRz4bWb6fSXfvlgSs=; b=If4g+i4eWJInonKXQiqoSQKnMBTzN4vA8RZwqnK7scaLBPV5EyM0K2ZgLk1li/a1GA uipSWqURNg+hZvpXZPYt+jzuqUrbc6tctMg1lADLjTzTNwuxHJDoXYKB9gh/glE223nn 0ediIP+bMjBxJ5ylYATFguqwGlb/NRu3UrnVkBT7WRxdBP0PkjuCAIlCbYHiS16SZFtM 5onnSx4MD7N65brpQFcXNr/QbK/sGO4y1OMpP65WEYYsfXQWDgNMFLyJSTsiiPgy5gDf L7J6+I2oNlg21op9GBlZo92PLIQ/kxTIHbhif7aU7loy34AikWLC9sGg2mAl4YAoeW4j eRKw== X-Received: by 10.182.88.136 with SMTP id bg8mr17494225obb.86.1426133415249; Wed, 11 Mar 2015 21:10:15 -0700 (PDT) Received: from [10.0.1.180] ([50.24.200.147]) by mx.google.com with ESMTPSA id q2sm4008150obq.14.2015.03.11.21.10.14 for (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 11 Mar 2015 21:10:14 -0700 (PDT) Message-ID: <550111A0.2070105@gmail.com> Date: Wed, 11 Mar 2015 23:10:08 -0500 From: Jason Aleski User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:31.0) Gecko/20100101 Thunderbird/31.5.0 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Find inconsistencies in data with date range References: <54FA1E61.9000201@gmail.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------020100070105050700030003" X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------020100070105050700030003 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit In case anyone else needs similar code, I was able to get this working. Below is the code that pulls the missing dates using a cursor and returns the information into a table. I'm sure there may be a way to make the code more efficient, but considering this will only get ran maybe once a quarter (for quarterly reports), it works for me. With 700+ stores, it takes about 30 minutes to fully run from a reporting server. I have a JAVA program that queries the function "SELECT * FROM eod_missing_dates();" Then sends all the missing dates to a RabbitMQ server to with a worker program to try to rebuild the missing eod summaries and if not, it will send a message to the store managers. Hopefully this code will help someone else! CREATE OR REPLACE FUNCTION eod_missing_dates() RETURNS TABLE(store_id uuid, location character varying, missing_ts timestamp with time zone) AS $BODY$ DECLARE location_cursor CURSOR FOR SELECT * FROM locations ORDER BY store_id; store_rec location%ROWTYPE; BEGIN CREATE TEMP TABLE dr_temptable(dr_ts, dr_dow) AS (SELECT t1.GenDate as gendate, extract(dow from GenDate) AS dayofweek FROM (SELECT date as GenDate FROM generate_series('1950-01-01'::date,CURRENT_TIMESTAMP::date,'1 day'::interval) date ) AS t1 WHERE extract(dow from GenDate) NOT IN (0,6)); OPEN location_cursor; LOOP FETCH location_cursor INTO store_rec; EXIT WHEN store_rec IS NULL; IF NOT FOUND THEN EXIT; END IF; RAISE INFO '%', 'Checking data/dates for ' || store_rec.location; RETURN QUERY SELECT store_rec.row_id as store_id, store_rec.location AS location, dr_ts AS missing_ts FROM dr_temptable AS t1 WHERE dr_ts NOT IN (SELECT t1.eod_ts FROM daily_salessummary AS t1 JOIN location AS t2 ON t1.store_id = t2.row_id WHERE t2.location = store_rec.location) AND dr_ts > (SELECT start_date FROM locations WHERE location=store_rec.location); END LOOP; CLOSE location_cursor; DROP TABLE dr_temptable; END; $BODY$ LANGUAGE plpgsql VOLATILE ; Jason Aleski / IT Specialist > > On 6 March 2015 at 22:38, 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') > > > _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 > > --------------020100070105050700030003 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit In case anyone else needs similar code, I was able to get this working.  Below is the code that pulls the missing dates using a cursor and returns the information into a table. I'm sure there may be a way to make the code more efficient, but considering this will only get ran maybe once a quarter (for quarterly reports), it works for me.  With 700+ stores, it takes about 30 minutes to fully run from a reporting server.  I have a JAVA program that queries the function "SELECT * FROM eod_missing_dates();"  Then sends all the missing dates to a RabbitMQ server to with a worker program to try to rebuild the missing eod summaries and if not, it will send a message to the store managers.  Hopefully this code will help someone else!


CREATE OR REPLACE FUNCTION eod_missing_dates()
  RETURNS TABLE(store_id uuid, location character varying, missing_ts timestamp with time zone) AS
$BODY$
DECLARE
  location_cursor CURSOR FOR SELECT * FROM locations ORDER BY store_id;
  store_rec location%ROWTYPE;
BEGIN

  CREATE TEMP TABLE dr_temptable(dr_ts, dr_dow) AS (SELECT t1.GenDate as gendate, extract(dow from GenDate) AS dayofweek
  FROM (SELECT date as GenDate
        FROM generate_series('1950-01-01'::date,CURRENT_TIMESTAMP::date,'1 day'::interval) date
       ) AS t1 
  WHERE extract(dow from GenDate) NOT IN (0,6));

  OPEN location_cursor;
  LOOP
    FETCH location_cursor INTO store_rec;
    EXIT WHEN store_rec IS NULL;

    IF NOT FOUND THEN
      EXIT;
    END IF;
   
    RAISE INFO '%', 'Checking data/dates for ' || store_rec.location;
    RETURN QUERY SELECT store_rec.row_id as store_id, store_rec.location AS location, dr_ts AS missing_ts FROM dr_temptable AS t1
                 WHERE dr_ts NOT IN (SELECT t1.eod_ts FROM daily_salessummary AS t1
                      JOIN location AS t2 ON t1.store_id = t2.row_id
                      WHERE t2.location = store_rec.location)                     
                      AND dr_ts > (SELECT start_date FROM locations WHERE location=store_rec.location);   
  END LOOP;
  CLOSE location_cursor; 
  DROP TABLE dr_temptable;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
;


Jason Aleski / IT Specialist


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


--------------020100070105050700030003--