Re: [SQL] need help

[Jaime Casanova]

On Thu, Feb 21, 2013 at 3:20 PM, denero team  wrote:
> Hi,
>
> Thanks for replying me. yes you are right at some level for my case.
> but its not what I want. I am explaining you a case by example.
>
[...]
>
> Now I really don't know how to do this.
>
> can you advise me more ?
>

I'm not really sure if you even know what you want, because the
examples you showed were just a:
select * from prodct_move where datetime < $given_date

but from the description you gave before i understood another thing,
so this is my only attempt to get an answer from thin air for you:

SELECT distinct on (p.name) p.name, l.name, datetime
FROM location l
INNER JOIN product_move m ON m.destination_location = l.id
INNER JOIN product p ON m.product_id = p.id
WHERE
m.datetime < '2012-12-31'
ORDER BY p.name, datetime DESC

--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157


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

Re: [SQL] Summing & Grouping in a Hierarchical Structure

[Alexander Gataric]

I would use the recursive CTE to gather the hierarchical portion of the data
you need and then join that CTE to another table or CTE with the other data
you need. I had a situation like this at my job were organization info was
in a hierarchal table and I needed to join it to two other tables. I created
a CTE with the combined data from the non-hierarchical tables and left
joined it to the recursive CTE.

 

If you're having trouble with this, I suggest looking into CTEs and the
different types of joins.

 

 

 

From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
On Behalf Of Don Parris
Sent: Thursday, February 21, 2013 4:38 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Summing & Grouping in a Hierarchical Structure

 

Hi Alexander,

I appreciate you taking time to reply to my post.  I like the idea of the
WITH RECURSIVE query, but...  The two examples in the link you offered are
not so helpful to me.  For example, the initial WITH query shown uses a
single table, and I wander how that might apply in my case, where the
relevant information is actually found in two tables, one of them a
recursive table.

The second example, which applies the WITH RECURSIVE clause, is even less
so.  I wonder if there is a good tutorial somewhere on this that shows some
other examples?  That might help me catch on a little better.  I'll search
for that today.

 

On Thu, Feb 14, 2013 at 11:30 PM, Alexander Gataric  wrote:

I would try a recursive
  query to
determine the category structure and aggregate as you go. I had a similar
problem with a hierarchical structure for an organization structure. Another
thing you might try is to create a separate CTE for each category and then
aggregate the individual CTEs.

 

 

From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
On Behalf Of Don Parris
Sent: Thursday, February 14, 2013 7:58 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Summing & Grouping in a Hierarchical Structure

 

Hi all,

I posted to this list some time ago about working with a hierarchical
category structure.   I had great difficulty with my problem and gave up for
a time.  I recently returned to it and resolved a big part of it.  I have
one step left to go, but at least I have solved this part.

Here is the original thread (or one of them):
http://www.postgresql.org/message-id/CAJ-7yonw4_qDCp-ZNYwEkR2jdLKeL8nfGc+-TL
LSW=rmo1v...@mail.gmail.com


 

Here is my recent blog post about how I managed to show my expenses summed
and grouped by a mid-level category:
http://dcparris.net/2013/02/13/hierarchical-categories-rdbms/


Specifically, I wanted to sum and group expenses according to categories,
not just at the bottom tier, but at higher tiers, so as to show more
summarized information.  A CEO primarily wants to know the sum total for all
the business units, yet have the ability to drill down to more detailed
levels if something is unusually high or low.  In my case, I could see the
details, but not the summary.  Well now I can summarize by what I refer to
as the 2nd-level categories.

Anyway, I hope this helps someone, as I have come to appreciate - and I mean
really appreciate - the challenge of working with hierarchical structures in
a 2-dimensional RDBMS.  If anyone sees something I should explain better or
in more depth, please let me know.

 

Regards,

Don

-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate

http://dcparris.net/  

GPG Key ID: F5E179BE




-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate

http://dcparris.net/  

GPG Key ID: F5E179BE

Re: [SQL] need help

[Oliver d'Azevedo Cristina]

Sorry, why do you need the joins?

Best,
Oliver

Enviado via iPhone

Em 21/02/2013, às 09:28 PM, Russell Keane  escreveu:

>>> Now I really don't know how to do this.
>>> 
>>> can you advise me more ?
>>> 
>>> 
>>> Thanks,
>>> 
>>> Dhaval
>> 
>> 
>> I think these are the sqls you are looking for:
>> 
>> SELECT pm.id as move_id, p.id as product_id, l.id as location_id
>> FROM product_move pm inner join product p on pm.product_id = p.id inner join 
>> location l on pm.destination_location = l.id
>> and datetime BETWEEN '2010-1-01' AND '2012-12-31'
> 
> 
> Sorry, that should have been:
> 
> For your 2 examples:
> 
> SELECT pm.id as move_id, p.id as product_id, l.id as location_id
> FROM product_move pm inner join product p on pm.product_id = p.id inner join 
> location l on pm.destination_location = l.id
> and datetime < '2012-11-30'
> 
> SELECT pm.id as move_id, p.id as product_id, l.id as location_id
> FROM product_move pm inner join product p on pm.product_id = p.id inner join 
> location l on pm.destination_location = l.id
> and datetime < '2012-12-31'
> 
> I'm not what the use of the 'from' date is in your examples.
> Do you need to know the final destination of the product in that time period?
> Or every destination location of the product in that time period?
> 
> Regards,
> 
> Russell Keane
> INPS
> 
> Follow us on twitter | visit www.inps.co.uk
> 
> 
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql


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

Re: [SQL] need help

[Russell Keane]

> > Now I really don't know how to do this.
> > 
> > can you advise me more ?
> > 
> > 
> > Thanks,
> > 
> > Dhaval
>
>
> I think these are the sqls you are looking for:
>
> SELECT pm.id as move_id, p.id as product_id, l.id as location_id
> FROM product_move pm inner join product p on pm.product_id = p.id inner join 
> location l on pm.destination_location = l.id
> and datetime BETWEEN '2010-1-01' AND '2012-12-31'


Sorry, that should have been:

For your 2 examples:

SELECT pm.id as move_id, p.id as product_id, l.id as location_id
FROM product_move pm inner join product p on pm.product_id = p.id inner join 
location l on pm.destination_location = l.id
and datetime < '2012-11-30'

SELECT pm.id as move_id, p.id as product_id, l.id as location_id
FROM product_move pm inner join product p on pm.product_id = p.id inner join 
location l on pm.destination_location = l.id
and datetime < '2012-12-31'

I'm not what the use of the 'from' date is in your examples.
Do you need to know the final destination of the product in that time period?
Or every destination location of the product in that time period?

Regards,

Russell Keane
INPS

Follow us on twitter | visit www.inps.co.uk



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

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

Re: [SQL] need help

[Russell Keane]

> Consider following are data in each table
> 
> Location :
> id , name, code
> 1, stock, stock
> 2, customer, customer
> 3, asset, asset
> 
> Product :
> id, name, code, location
> 1, product1, p1, 1
> 2, product2, p2, 3
> 
> 
> Product_Move :
> id, product_id, source_location, destination_location, datetime 1, 1, 
> Null, 1, 2012-10-15 10:00:00 2, 2, Null, 1, 2012-10-15 10:05:00 3, 2, 
> 1, 3,  2012-12-01 09:00:00
> 
> Please review all data , you can see, current location of product1
> (p1) is 1 (stock) and current location of product2 (p2) is 3 (asset).
> 
> now i want to find location of all products for given period
> 
> for example : 2012-11-01 to 2012-11-30, then i need result should be 
> like below move_id, product_id, location_id 1, 1, 1 2, 2, 1
> 
> another example : 2012-11-01 to 2012-12-31 move_id, product_id, 
> location_id 1, 1, 1 2, 2, 1 3, 2, 3
> 
> Now I really don't know how to do this.
> 
> can you advise me more ?
> 
> 
> Thanks,
> 
> Dhaval 


I think these are the sqls you are looking for:

SELECT pm.id as move_id, p.id as product_id, l.id as location_id
FROM product_move pm inner join product p on pm.product_id = p.id inner join 
location l on pm.destination_location = l.id
and datetime BETWEEN '2010-1-01' AND '2012-12-31'


Regards,

Russell Keane
INPS

Follow us on twitter | visit www.inps.co.uk



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

Re: [SQL] need help

[Oliver d'Azevedo Cristina]

SELECT move_id, product_id,destination_location as location_id
FROM product_move
Where datetime BETWEEN $first
AND $last

Have you tried something like this?

Best,
Oliver

Enviado via iPhone

Em 21/02/2013, às 08:20 PM, denero team  escreveu:

> Hi,
> 
> Thanks for replying me. yes you are right at some level for my case.
> but its not what I want. I am explaining you a case by example.
> 
> Consider following are data in each table
> 
> Location :
> id , name, code
> 1, stock, stock
> 2, customer, customer
> 3, asset, asset
> 
> Product :
> id, name, code, location
> 1, product1, p1, 1
> 2, product2, p2, 3
> 
> 
> Product_Move :
> id, product_id, source_location, destination_location, datetime
> 1, 1, Null, 1, 2012-10-15 10:00:00
> 2, 2, Null, 1, 2012-10-15 10:05:00
> 3, 2, 1, 3,  2012-12-01 09:00:00
> 
> Please review all data , you can see, current location of product1
> (p1) is 1 (stock) and current location of product2 (p2) is 3 (asset).
> 
> now i want to find location of all products for given period
> 
> for example : 2012-11-01 to 2012-11-30, then i need result should be like 
> below
> move_id, product_id, location_id
> 1, 1, 1
> 2, 2, 1
> 
> another example : 2012-11-01 to 2012-12-31
> move_id, product_id, location_id
> 1, 1, 1
> 2, 2, 1
> 3, 2, 3
> 
> Now I really don't know how to do this.
> 
> can you advise me more ?
> 
> 
> Thanks,
> 
> Dhaval
> 
> 
> On Fri, Feb 22, 2013 at 1:26 AM, Carlos Chapi
>  wrote:
>> Hello,
>> 
>> Maybe this query can help you
>> 
>> SELECT p.name, l.name
>> FROM location l
>> INNER JOIN product_move m ON m.source_location = location.id
>> INNER JOIN product p ON m.product_id = p.id
>> WHERE p.id = $product_id
>> AND m.datetime < $given_date
>> ORDER BY datetime DESC LIMIT 1
>> 
>> It will return the name of the product and the location for a given id and
>> date.
>> 
>> 
>> 2013/2/21 denero team 
>>> 
>>> Hi All,
>>> 
>>> I need some help for my problem.
>>> Problem :
>>> I have following tables
>>> 1. Location :
>>>id, name, code
>>> 2. Product
>>>id, name, code, location ( ref to location table)
>>> 2. Product_Move
>>>id, product_id ( ref to product table), source_location (ref to
>>> location table) , destination_location ( ref to location table) ,
>>> datetime ( date when move is created)
>>> 
>>> now i want to know for given period of dates, where is the product
>>> actually.
>>> 
>>> can anyone help me ??
>>> 
>>> Thanks,
>>> 
>>> Dhaval
>>> 
>>> 
>>> --
>>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-sql
>> 
>> 
> 
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql


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

Re: [SQL] need help

[denero team]

Hi,

Thanks for replying me. yes you are right at some level for my case.
but its not what I want. I am explaining you a case by example.

Consider following are data in each table

Location :
id , name, code
1, stock, stock
2, customer, customer
3, asset, asset

Product :
id, name, code, location
1, product1, p1, 1
2, product2, p2, 3


Product_Move :
id, product_id, source_location, destination_location, datetime
1, 1, Null, 1, 2012-10-15 10:00:00
2, 2, Null, 1, 2012-10-15 10:05:00
3, 2, 1, 3,  2012-12-01 09:00:00

Please review all data , you can see, current location of product1
(p1) is 1 (stock) and current location of product2 (p2) is 3 (asset).

now i want to find location of all products for given period

for example : 2012-11-01 to 2012-11-30, then i need result should be like below
move_id, product_id, location_id
1, 1, 1
2, 2, 1

another example : 2012-11-01 to 2012-12-31
move_id, product_id, location_id
1, 1, 1
2, 2, 1
3, 2, 3

Now I really don't know how to do this.

can you advise me more ?


Thanks,

Dhaval


On Fri, Feb 22, 2013 at 1:26 AM, Carlos Chapi
 wrote:
> Hello,
>
> Maybe this query can help you
>
> SELECT p.name, l.name
> FROM location l
> INNER JOIN product_move m ON m.source_location = location.id
> INNER JOIN product p ON m.product_id = p.id
> WHERE p.id = $product_id
> AND m.datetime < $given_date
> ORDER BY datetime DESC LIMIT 1
>
> It will return the name of the product and the location for a given id and
> date.
>
>
> 2013/2/21 denero team 
>>
>> Hi All,
>>
>> I need some help for my problem.
>> Problem :
>> I have following tables
>> 1. Location :
>> id, name, code
>> 2. Product
>> id, name, code, location ( ref to location table)
>> 2. Product_Move
>> id, product_id ( ref to product table), source_location (ref to
>> location table) , destination_location ( ref to location table) ,
>> datetime ( date when move is created)
>>
>> now i want to know for given period of dates, where is the product
>> actually.
>>
>> can anyone help me ??
>>
>> Thanks,
>>
>> Dhaval
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
>


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

Re: [SQL] need help

[Carlos Chapi]

Hello,

Maybe this query can help you

SELECT p.name, l.name
FROM location l
INNER JOIN product_move m ON m.source_location = location.id
INNER JOIN product p ON m.product_id = p.id
WHERE p.id = $product_id
AND m.datetime < $given_date
ORDER BY datetime DESC LIMIT 1

It will return the name of the product and the location for a given id and
date.


2013/2/21 denero team 

> Hi All,
>
> I need some help for my problem.
> Problem :
> I have following tables
> 1. Location :
> id, name, code
> 2. Product
> id, name, code, location ( ref to location table)
> 2. Product_Move
> id, product_id ( ref to product table), source_location (ref to
> location table) , destination_location ( ref to location table) ,
> datetime ( date when move is created)
>
> now i want to know for given period of dates, where is the product
> actually.
>
> can anyone help me ??
>
> Thanks,
>
> Dhaval
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

[SQL] need help

[denero team]

Hi All,

I need some help for my problem.
Problem :
I have following tables
1. Location :
id, name, code
2. Product
id, name, code, location ( ref to location table)
2. Product_Move
id, product_id ( ref to product table), source_location (ref to
location table) , destination_location ( ref to location table) ,
datetime ( date when move is created)

now i want to know for given period of dates, where is the product actually.

can anyone help me ??

Thanks,

Dhaval


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

Re: [SQL] Summing & Grouping in a Hierarchical Structure

[Don Parris]

Hi Mike,

No Excel here.  This is a strictly libre systems environment.  I believe
LibreOffice Calc has a similar tool though.  And your suggestion might be a
great one.


On Mon, Feb 18, 2013 at 10:24 AM, Relyea, Mike wrote:

> > From: pgsql-sql-ow...@postgresql.org [mailto:
> pgsql-sql-ow...@postgresql.org] On Behalf Of Don Parris
> > Sent: Thursday, February 14, 2013 8:58 PM
> > To: pgsql-sql@postgresql.org
> > Subject: [SQL] Summing & Grouping in a Hierarchical Structure
> >
> > Hi all,
> > I posted to this list some time ago about working with a hierarchical
> category structure.   I had great difficulty with my problem and gave up
> for a time.
> > I recently returned to it and resolved a big part of it.  I have one
> step left to go, but at least I have solved this part.
> >
> > Here is the original thread (or one of them):
> >
> http://www.postgresql.org/message-id/CAJ-7yonw4_qDCp-ZNYwEkR2jdLKeL8nfGc+-TLLSW=rmo1v...@mail.gmail.com
> >
> >
> > Here is my recent blog post about how I managed to show my expenses
> summed and grouped by a mid-level category:
> > http://dcparris.net/2013/02/13/hierarchical-categories-rdbms/
> >
> >
> > Specifically, I wanted to sum and group expenses according to
> categories, not just at the bottom tier, but at higher tiers, so as to show
> more summarized information.
> > A CEO primarily wants to know the sum total for all the business units,
> yet have the ability to drill down to more detailed levels if something is
> unusually high or low.
> > In my case, I could see the details, but not the summary.  Well now I
> can summarize by what I refer to as the 2nd-level categories.
> > Anyway, I hope this helps someone, as I have come to appreciate - and I
> mean really appreciate - the challenge of working with hierarchical
> structures in a 2-dimensional RDBMS.
> > If anyone sees something I should explain better or in more depth,
> please let me know.
> >
> > Regards,
> > Don
> > --
> > D.C. Parris, FMP, Linux+, ESL Certificate
> > Minister, Security/FM Coordinator, Free Software Advocate
> > http://dcparris.net/
> > GPG Key ID: F5E179BE
>
> My two cents would be to actually use a  different tool for the job of
> presenting this data.  I'd have used a pivot table in Microsoft Excel.  Not
> sure what your environment or requirements are but pivot tables are widely
> used in business, easy to share, can be formatted, and give the user the
> ability to drill down and navigate to the data they want to see.
> I'd set up a query to pull the raw data you need with all of the
> categories and associated data you need.  Then bring that data to Excel to
> present and summarize it.
>
> Mike
>



-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/

GPG Key ID: F5E179BE

Re: [SQL] Summing & Grouping in a Hierarchical Structure

[Don Parris]

Hi Alexander,

I appreciate you taking time to reply to my post.  I like the idea of the
WITH RECURSIVE query, but...  The two examples in the link you offered are
not so helpful to me.  For example, the initial WITH query shown uses a
single table, and I wander how that might apply in my case, where the
relevant information is actually found in two tables, one of them a
recursive table.

The second example, which applies the WITH RECURSIVE clause, is even less
so.  I wonder if there is a good tutorial somewhere on this that shows some
other examples?  That might help me catch on a little better.  I'll search
for that today.


On Thu, Feb 14, 2013 at 11:30 PM, Alexander Gataric  wrote:

> I would try a 
> recursivequery 
> to determine the category structure and aggregate as you go. I had a
> similar problem with a hierarchical structure for an organization
> structure. Another thing you might try is to create a separate CTE for each
> category and then aggregate the individual CTEs.
>
> ** **
>
> ** **
>
> *From:* pgsql-sql-ow...@postgresql.org [mailto:
> pgsql-sql-ow...@postgresql.org] *On Behalf Of *Don Parris
> *Sent:* Thursday, February 14, 2013 7:58 PM
> *To:* pgsql-sql@postgresql.org
> *Subject:* [SQL] Summing & Grouping in a Hierarchical Structure
>
> ** **
>
> Hi all,
>
> I posted to this list some time ago about working with a hierarchical
> category structure.   I had great difficulty with my problem and gave up
> for a time.  I recently returned to it and resolved a big part of it.  I
> have one step left to go, but at least I have solved this part.
>
> Here is the original thread (or one of them):
>
> http://www.postgresql.org/message-id/CAJ-7yonw4_qDCp-ZNYwEkR2jdLKeL8nfGc+-TLLSW=rmo1v...@mail.gmail.com
> 
>
> ** **
>
> Here is my recent blog post about how I managed to show my expenses summed
> and grouped by a mid-level category:
> http://dcparris.net/2013/02/13/hierarchical-categories-rdbms/
>
>
> Specifically, I wanted to sum and group expenses according to categories,
> not just at the bottom tier, but at higher tiers, so as to show more
> summarized information.  A CEO primarily wants to know the sum total for
> all the business units, yet have the ability to drill down to more detailed
> levels if something is unusually high or low.  In my case, I could see the
> details, but not the summary.  Well now I can summarize by what I refer to
> as the 2nd-level categories.
>
> Anyway, I hope this helps someone, as I have come to appreciate - and I
> mean really appreciate - the challenge of working with hierarchical
> structures in a 2-dimensional RDBMS.  If anyone sees something I should
> explain better or in more depth, please let me know.
>
> ** **
>
> Regards,
>
> Don
>
> --
> D.C. Parris, FMP, Linux+, ESL Certificate
> Minister, Security/FM Coordinator, Free Software Advocate
>
> http://dcparris.net/ 
>
> GPG Key ID: F5E179BE
>



-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/

GPG Key ID: F5E179BE