pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDividing results from two tables with different time frames
4+ messages / 3 participants
[nested] [flat]
* Dividing results from two tables with different time frames
@ 2006-09-15 15:34 Becky Hoff <bhoff@standoninc.com>
2006-09-17 10:05 ` Re: Dividing results from two tables with different time Ragnar <gnari@hive.is>
2006-09-17 16:16 ` Re: Dividing results from two tables with different time frames Aaron Bono <postgresql@aranya.com>
0 siblings, 2 replies; 4+ messages in thread
From: Becky Hoff @ 2006-09-15 15:34 UTC (permalink / raw)
To: pgsql-sql
I hope I can explain this clearly. I have two queries I'm running in a
report.
The first one is:
select dr.store_id,
store.suffix,
store.sort_id,
year as data_year,
(dr.layaway_starting_balance
+
dr.layaway_net_change)
as
layaway_balance,
(dr.loan_starting_balance
+
dr.loan_net_change)
as
loan_balance,
dr.inventory_starting_balance
+
inventory_net_change
as
inventory,
(dr.loan_starting_number
+ dr.loan_number_change) as number_loan,
(dr.loan_starting_balance + dr.loan_net_change)
/(dr.loan_starting_number + dr.loan_number_change) as loan_balance_avg
from daily_runbalance dr
join store on
(dr.store_id = store.store_id)
where dr.date = '2006-06-30'
and dr.store_id = 4
and store.store_id = 4
The second is very long so I'll just post the relevant pieces.
select dr.store_id,
store.short_name,
store.sort_id,
ds.year as data_year,
sum(ds.pulled_loan_total) as loan_pulls,
sum(ds.renew_loan_amount) as loan_renewals,
from daily_runbalance dr
join daily_summary ds on (dr.store_id =
ds.store_id and dr.date = ds.date)
join cash on (dr.store_id = cash.store_id and
dr.date = cash.date)
join store on (dr.store_id = store.store_id)
where dr.date between '2006-04-01' and
'2006-06-30'
and dr.store_id = 4
group by dr.store_id, store.sort_id,
store.short_name, ds.year
As you can see the two queries have different time frames. The first
one has one date, the second one has a range of dates.
What I'm trying to accomplish is to get two percentages. Both have one
element from one table divided by an element in the other table.
sum(ds.pulled_loan_total)/sum(dr.loan_starting_balance +
dr.loan_net_change)*100 as pulls_percent,
and
(sum(ds.renew_loan_amount)/sum(dr.loan_starting_balance +
dr.loan_net_change))*100 as renew_percent,
No matter which query I place them in it gives me the wrong data because
of the time frames. How can I get the correct data?
Thanks
Becky Hoff
IT Specialist
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Dividing results from two tables with different time
2006-09-15 15:34 Dividing results from two tables with different time frames Becky Hoff <bhoff@standoninc.com>
@ 2006-09-17 10:05 ` Ragnar <gnari@hive.is>
1 sibling, 0 replies; 4+ messages in thread
From: Ragnar @ 2006-09-17 10:05 UTC (permalink / raw)
To: Becky Hoff <bhoff@standoninc.com>; +Cc: pgsql-sql
On fös, 2006-09-15 at 10:34 -0500, Becky Hoff wrote:
> I hope I can explain this clearly.
Not clear enough for me.
> I have two queries Iÿm running in a report.
> The first one is:
[snip]
> No matter which query I place them in it gives me the wrong data
> because of the time frames. How can I get the correct
> data?
It is not clear what you consider correct data.
I suggest you devise a simple test case, with just
the minimum number of tables and columns needed
to explain your problem, show us a small data set
and describe your wanted output.
For example, avoid joins in your test case, unless
they are central to your problem
gnari
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Dividing results from two tables with different time frames
2006-09-15 15:34 Dividing results from two tables with different time frames Becky Hoff <bhoff@standoninc.com>
@ 2006-09-17 16:16 ` Aaron Bono <postgresql@aranya.com>
1 sibling, 0 replies; 4+ messages in thread
From: Aaron Bono @ 2006-09-17 16:16 UTC (permalink / raw)
To: Becky Hoff <bhoff@standoninc.com>; +Cc: pgsql-sql
On 9/15/06, Becky Hoff <bhoff@standoninc.com> wrote:
>
> What I'm trying to accomplish is to get two percentages. Both have one
> element from one table divided by an element in the other table.
>
> sum(ds.pulled_loan_total)/sum(dr.loan_starting_balance +
> dr.loan_net_change)*100 as pulls_percent,
>
> and
>
> (sum(ds.renew_loan_amount)/sum(dr.loan_starting_balance +
> dr.loan_net_change))*100 as renew_percent,
>
> No matter which query I place them in it gives me the wrong data because
> of the time frames. How can I get the correct data?
>
What time frames do you want? Are you looking for daily, monthly or
something else?
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Dividing results from two tables with different time frames
@ 2006-09-20 17:24 Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 4+ messages in thread
From: Aaron Bono @ 2006-09-20 17:24 UTC (permalink / raw)
To: Becky Hoff <bhoff@standoninc.com>; +Cc: pgsql-sql
On 9/18/06, Becky Hoff <bhoff@standoninc.com> wrote:
>
> The sum(dr.loan_starting_balance + dr.loan_net_change) part of the
> calculation have a single date, the last day in a date range chosen by the
> user
>
>
>
> Sum(ds.pulled_loan_total) and sum(ds.renew_loan_amount) both have a range
> of dates chosen by the user
>
>
>
> What I need it the result of the second expression divided by the result
> of the first expression.
>
Can you provide some data examples. Show a sample of the data you have to
query, what results you are getting and what results you are really wanting
so we can see where your queries are not giving you what you need.
By your post it sounds like you just need to join your two queries and then
use the formula:
Sum(ds.pulled_loan_total) and sum(ds.renew_loan_amount) / sum(
dr.loan_starting_balance + dr.loan_net_change)
but if it were this simple you probably would have it figured out by now.
Data examples help us help you much better.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-09-20 17:24 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-15 15:34 Dividing results from two tables with different time frames Becky Hoff <bhoff@standoninc.com>
2006-09-17 10:05 ` Re: Dividing results from two tables with different time Ragnar <gnari@hive.is>
2006-09-17 16:16 ` Aaron Bono <postgresql@aranya.com>
2006-09-20 17:24 Re: Dividing results from two tables with different time frames Aaron Bono <postgresql@aranya.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox