Обсуждение: Dividing results from two tables with different time frames
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
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
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
==================================================================
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.
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
==================================================================