|
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
|
- [SQL] Dividing results from two tables with different time fram... Becky Hoff
