Thread: Dividing results from two tables with different time frames

Dividing results from two tables with different time frames

From

"Becky Hoff"

Date:

17 September 2006, 05:30:34

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I hope I can explain this clearly.  I have two queries I’m running in a report.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">The first one is:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">select   dr.store_id,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">                                                store.suffix,</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                store.sort_id,</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                year as data_year,</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                (dr.layaway_starting_balance</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                            + dr.layaway_net_change)</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                            as layaway_balance,</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                (dr.loan_starting_balance </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                            + dr.loan_net_change) </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                            as loan_balance,</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                dr.inventory_starting_balance</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                            + inventory_net_change</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                            as inventory,</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                (dr.loan_starting_number + dr.loan_number_change) as
number_loan,</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                (dr.loan_starting_balance +
dr.loan_net_change)</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                            /(dr.loan_starting_number +
dr.loan_number_change)as loan_balance_avg</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">                        from                 daily_runbalance dr</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                join store on (dr.store_id =
store.store_id)</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                        where                dr.date = '2006-06-30'</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                and dr.store_id = 4</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                and store.store_id = 4</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">The second is very long so I’ll just post the relevant pieces.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">select   dr.store_id, </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">                                                store.short_name,</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                store.sort_id,</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                ds.year as data_year,</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                </span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                sum(ds.pulled_loan_total) as
loan_pulls,</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                sum(ds.renew_loan_amount) as
loan_renewals,</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                                                               
</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                    </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">                        from daily_runbalance dr</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">                        </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">                        join daily_summary ds on (dr.store_id = ds.store_id and dr.date =
ds.date)</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                        join cash on (dr.store_id = cash.store_id and dr.date =
cash.date)</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                        join store on (dr.store_id = store.store_id)</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                        </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">                        where    dr.date between '2006-04-01' and '2006-06-30'</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                and dr.store_id = 4</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">                                                </span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">                        group by dr.store_id, store.sort_id, store.short_name,
ds.year</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">As you can see the two queries have different time frames.  The first one has one date, the second
onehas a range of dates.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">What I’m trying to accomplish is to get two percentages.  Both have one element from one table
dividedby an element in the other table.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">sum(ds.pulled_loan_total)/sum(dr.loan_starting_balance + dr.loan_net_change)*100 as
pulls_percent,</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">and</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">(sum(ds.renew_loan_amount)/sum(dr.loan_starting_balance + dr.loan_net_change))*100 as
renew_percent,</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">No matter which query I place them in it gives me the wrong data because of the time frames.  How
canI get the correct data?            </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Thanks  </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span
style="font-size:12.0pt;font-family:Arial">BeckyHoff</span></font><p class="MsoNormal"><font face="Arial"
size="3"><spanstyle="font-size:12.0pt; 
font-family:Arial">IT Specialist</span></font> <p class="MsoNormal"><font face="Times New Roman" size="3"><span
style="font-size:
12.0pt"> </span></font></div>

Re: Dividing results from two tables with different time

From

Ragnar

Date:

17 September 2006, 10:03:05

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

Re: Dividing results from two tables with different time frames

From

"Aaron Bono"

Date:

17 September 2006, 16:16:11

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
==================================================================

Re: Dividing results from two tables with different time frames

From

"Aaron Bono"

Date:

20 September 2006, 17:24:23

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.