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

["George Jansen" <GJANSEN@aflcio.org>]

I'd have thought that the  commission was calculated per deal. What does
it mean to know that you have n salesmen earning m percent commission if
you don't consider what they sold?

But, given the scenario you show, why not

SELECT r.id, r.start_date, r.end_date, sum(a.commission)
FROM report_periods r, agents a
WHERE [periods overlap]
GROUP BY r.id, r.start_date, r.end_date

>>> JMDICKENSON@rainbow-media.com 10/19/2004 2:00:01 PM >>>
Thank you for your email. My first step was to get all qualifying
agents
within the reporting date range. I am able to get that list without
any
hassle, by isolating the scenarios. 

With these sample cases:
Agent           Eff_Date                End
Date            Commission (%)
Ag1             03/29/04        07/25/04                30
Ag2             04/29/04        06/25/04                20
Ag3             01/29/04        09/25/04                30
Ag4             01/29/04        05/25/04                10
Ag5             05/26/04        09/25/04                10

I have to slice the time ranges to get the following resultset:

Eff_Date                End Date                SUM of Commissions for
03/29/04        04/28/04                Ag1 + Ag3 + Ag4
04/29/04        05/25/04                Ag1 + Ag2 + Ag3 + Ag4
05/26/04                06/25/04        Ag1 + Ag2 + Ag3 + Ag5
06/26/04                07/25/04                Ag1 + Ag3 + Ag5

I need to do this, so as to determine whether the commissions fall
short of a 100% within the derived time slices for a given reporting
date range.

Again, thanks in advance.
Jessie

>>> GJANSEN@aflcio.org 10/19/2004 1:20:28 PM >>>
Shouldn't the clause be something like

(agent.eff_dt between rep.start and rep.end OR
 agent.end_dt between rep.start and rep.end OR
  rep.start between agent.eff_dt and agent.end_dt OR
  rep.end between agent.eff_dt and agent.end_dt)

?



>>> JMDICKENSON@rainbow-media.com 10/19/2004 12:26:51 PM >>>
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 

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

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

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