Message-ID: <40E3F24438264205B10395EFCEE492AD@Moon>
From: "Oliver d'Azevedo Christina" <oliveiros.cristina@asperger-talents.com>
To: "Rihad" <rihad@stream.az>,
	<pgsql-sql@postgresql.org>
References: <4FE35E88.6010805@stream.az>
Subject: Re: Need help building this query
Date: Thu, 21 Jun 2012 19:50:30 +0100
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=response
Content-Transfer-Encoding: 7bit

For matching triples (foo, bar, baz) the date in table B shouldnt always be 
after any date in table A, as table B contains complete operations?


Best,
Oliver

----- Original Message ----- 
From: "Rihad" <rihad@stream.az>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, June 21, 2012 6:48 PM
Subject: [SQL] Need help building this query


> Hi, folks. I currently need to join two tables that lack primary keys, and 
> columns used to distinguish each record can be duplicated. I need to build 
> statistics over the data in those tables. Consider this:
>
>
> TableA:
> row 1: foo: 123, bar: 456, baz: 789, amount: 10.99, date_of_op: date
> row 2: foo: 123, bar: 456, baz: 789, amount: 10.99, date_of_op: date
> row 3: foo: 123, bar: 456, baz: 789, amount: 10.99, date_of_op: date
>
> TableB:
> row 1: foo: 123, bar: 456, baz: 789, amount: 10.99, date_of_op: date
>
>
> Columns foo + bar + baz are used to distinguish a performed "operation":
> TableA.date_of_op isn't, because it can lag behind TableB.
>
> Not all different "operations" are in table B.
> Table B is just there so we know which "operations" are complete, so to 
> speak (happening under external means and not under any of my control).
>
> Now, for each operation (foo+bar+baz) in table A, only *one* row should be 
> matched in table B, because it only has one matching row there.
> The other two in TableA should be considered unmatched.
>
> Now the query should be able to get count(*) and sum(amount) every day for 
> that day, considering that matched and unmatched operations should be 
> counted separately. The report would look something like this:
>
> TableA.date_of_op  TableB.date_of_op
> 2012-06-21            [empty]                  [count(*) and sum(amount) 
> of all data in TableA for this day unmatched in TableB]
> 2012-06-21            2012-06-20            [count(*) and sum(amount) of 
> all data in TableA matched in TableB for the 20-th]
> 2012-06-21            2012-06-19            [count(*) and sum(amount) of 
> all data in TableA matched in TableB for the 19-th]
>
>
> Can this awkward thing be done in pure SQL, or I'd be better off using 
> programming for this?
>
> Thanks, I hope I could explain this.
>
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>