Date: Fri, 6 Mar 2015 20:00:07 -0700 (MST)
From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-sql@postgresql.org
Message-ID: <1425697207404-5840891.post@n5.nabble.com>
In-Reply-To: <54FA1E61.9000201@gmail.com>
References: <54FA1E61.9000201@gmail.com>
Subject: Re: Find inconsistencies in data with date range
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Jason Aleksi wrote
> 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?

I would build a master table of stores and dates and then write a query to
update a third field from null to the number of records found for the given
combination.  When all the nulls are gone you can scan for zeros to figure
out what combinations are missing data.  If you have a matching index the
queries should execute reasonably efficiently and you either call it from a
function in the database or externally on one or more threads depending on
where you expect to encounter the processing bottleneck.  You can process
more than one day or store at a time if so desired but there will likely be
a point of diminishing returns depending on the volume of data.  I would
probably do all days for one store in a given year at a time.

David J.


--
View this message in context: http://postgresql.nabble.com/Find-inconsistencies-in-data-with-date-range-tp5840865p5840891.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql