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

[Don Mellen <donm@ontko.com>]

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
> 

-----------------------------------------------------------------------
Donald Mellen  | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/
donm@ontko.com |  "In the beginning, there was nothing, which exploded"



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