RE: [sqr-users] Help on Time Slicing.

[Bob Stone <bstone@fastenal.com>]

I emailed Mr. Dickerson outside the SQR group (as to not innondate the gruop
as I was getting clarifications and questions answered) and we came to 2
conclusions about it.  First, the 5 scenarios can be summed up in 3
statements for the where clause: 

date eff (begin)
date end
date a (first)
date b (last)

AND ( (eff >= a and eff <= b) OR 
      (end >= a and end <= b) OR 
      (eff <= a and end >= last) )

 ** either the begin or the end falls inbetween the period (between a,b), or
it spans a,b.  

Then we realized it would make more sense to split the 4 month period into 1
month parts (looking at his SQL as one month chunks) and then do that SQL 4
times, keeping track of "totals" as he went (or whatever he needed to know
about the 4 month chunk).  Changing it from 4 month info sliced into 1 month
chunks  to 1 months chunks added up into a 4 month period. 

For example, think about a problem w/out using time.  At my place of
business, we sell things at stores.  A group of stores are in a specific
district, then a few districts make up a region, the all the regions make up
our company.  It doesn't make sense to grab the total sales for a specific
region, then try to work our way backwards to find the total at any store in
the region.  We should look at sales for store1, store2 and store3, then add
them together to get district totals.  Then we add together the group of
distict totals to find it for the region...etc.  The same bottom up aproach
should work for a time sliced report.  Start with a small time increment and
work your way up to the bigger one.

He has since emailed me back saying he completed his section with the bottom
up approach.


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of Don
Mellen
Sent: Thursday, October 21, 2004 10:58 AM
To: This list is for discussion about the SQR database reporting
language from Hyperion Solutions.
Subject: Re: [sqr-users] Help on Time Slicing.


Sounds like it would be easier to break this apart by procedures.  A
single sql approach, while interesting and challenging, might be hard to
maintain. Using SQR certainly allows you to break this up into logical
sections easily.  Something like....

create-array name=check_dates_array size=100
   field=start_of_period:date
   field=end_of_period:date

!* Select contracts active in date range
begin-select
(stuff)

   clear-array check_dates_array

   !* Put first value in
   if &contract_start > $rpt_start
      put &contract_start into check_dates_array(0) start_of_period
   else
      put $rpt_start into check_dates_array(0) start_of_period
   end-if

   if &contract_end < $rpt_end
      put &contract_end into check_dates_array(0) end_of_period
   else
      put $rpt_end into check_dates_array(0) end_of_period
   end-if

   !* Select from agents on this contract and 
   !* fill the array with "change" dates if any.
   !* Adjust these dates into the array, so there's
   !* no period overlap
   do fill_check_dates_array

   !* Loop through the array and for each row
   !* perform a select that gets the sum of commisions
   !* for any one day in that row, if <> 100%, then print info.
   do check_the_array_dates
   
from table
where (other stuff)
end-select


Of course, your mileage may vary.
HTH,
Don


On Tue, 19 Oct 2004, Jessie Dickenson wrote:

> I have the following requirement & since I have exhausted all ideas, I
> am turning to this group for help. This is predominantly a SQL issue.
> 
> I need to run a report for a certain date range: say, for example:
> 03/29/04 to 07/25/04
> 
> The requirement sounds simple:
> List all deals that have agents with commissions less than 100% during
> this time frame.
> 
> Business Scenario:
> 1. A deal can have multiple agents.
> 2. Each agent can be assigned less than 100% commission.
> 
> So, let's say a deal has 5 agents, covering 5 typical scenarios:
> 
> Scenario 1: Agent's effective dates are for the reporting date range -
> So, agent.eff_date = 03/29/04 and agent.end_date = 07/25/04, with a
> commission of 30%.
> 
> Scenario 2:  Agent's effective dates fall within the reporting date
> range - So, agent.eff_date = 04/29/04 and agent.end_date = 06/25/04,
> with a commission of 20%.
> 
> Scenario 3:  Agent's effective dates covers the reporting date range -
> So, agent.eff_date = 01/29/04 and agent.end_date = 09/25/04, with a
> commission of 30%.
> 
> Scenario 4: Agent's effective dates partially covers the reporting date
> range around the Start date of the run - So, agent.eff_date = 01/29/04
> and agent.end_date = 05/25/04, with a commission of 10%.
> 
> Scenario 5: Agent's effective dates partially covers the reporting date
> range around the End date of the run - So, agent.eff_date = 05/26/04 and
> agent.end_date = 09/25/04, with a commission of 10%.
> 
> I need to time slice the effective dates of the 5 agents, so that for
> each time slice, I can sum up the percentage commission & list those
> deals with not-100% commissions for any of these time slices.
> 
> If any of you has any ideas to throw in, I would greatly appreciate
> your help. 
> 
> I am thinking that a procedure is needed here. But if someone has ideas
> for a simple SQL, I gladly look forward to your ideas.
> 
> Thanks in advance.
> Jessie

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users