Creating Counter that adjusts based on key action/category

[sergi117]

Hey I'm trying to build a counter which counts based on actions.
So I have two tables
Table 1


name...Hire_Date...Effective_date...action
John...1/1/2015...1/1/2015..action1
John...1/1/2015...1/7/2015..action2
John...1/1/2015...1/6/2015..action3
John...1/1/2015...1/14/2015..action4


Table 2
Date Beg...Date End
1/1/2015...1/7/2015
1/8/2015...1/14/2015


Essentially I need to count all periods from table 2 where the hire date belongs in, but if action 2 occurs within the range then don't count that range. I had an initial sum(iif to get the total where hire date fits in but I'm having trouble in setting up a counter of sorts which knows to exclude the range if the effective date of action 2 occurs within that range.


The example here means that the total count for John should be 1 since action 2 on 1/7/2015 zeroes out the count by being in the range, but action 4 being outside of it still counting.

[CJ_London]

totally confused - please clarify

I need to count all periods from table 2 where the hire date belongs in, but if action 2 occurs within the range then don't count that range

The example here means that the total count for John should be 1 since action 2 on 1/7/2015 zeroes out the count by being in the range

I'm seeing 3 actions for John within range 1/1/2015...1/7/2015

John...1/1/2015...1/1/2015..action1
John...1/1/2015...1/7/2015..action2
John...1/1/2015...1/6/2015..action3

So 'if action 2 occurs within the range then don't count that range' will return 0 because action2 is within the range

or perhaps you mean just ignore action2 - which means you should return 2

and if 1 is the right answer - on what basis are you deciding which one to include

or do you mean (action1)+1 (action2)-1 (action3)+1=1-1+1=1

or perhaps with a more complex scenario (action1)+1 (action1)+1 (action2)-1 (action3)+1=1+1-1+1=2

or alternatively (action1)+1 (action1)+1 (action2)-2 (action3)+1=1+1-2+1=1

[sergi117]

Ok so the idea is action 2 would set the range it is in to be 0. So normally the hire date occurs within the two ranges of table 2 which would be a count of 2. However, since action 2's effective date is 1/7/2015, it occurs in the first range meaning despite the hire date being within the range, the count there would be 0. So the only range where hire date fits and isn't canceled by action 2 being in it, is the second record.

Think essentially:
name...hire
John...1/1/2015 (fits in both ranges) so the number is 2, but if the effective date of action 2 occurs within one of the date ranges then exclude that from the count. Therefore only 1. It's less about each count for each separate action, but just counting the number of ranges that hire date fits in, and excluding a range if the date corresponding to action 2 is also in it.

[CJ_London]

Sorry I do not follow. You keep saying there are 2, I see 3. Using the example, explain the rational step by step as examples, not as sentences

this is your range

Date Beg...Date End
1/1/2015...1/7/2015

which of these do you not consider to be in range?

John...1/1/2015...1/1/2015..action1
John...1/1/2015...1/7/2015..action2
John...1/1/2015...1/6/2015..action3

might be helpful if you explain what the real business is and what you are trying to achieve with this exercise. and clarify what the actions are

[sergi117]

this is my range (ignore action 3 for now just to get idea across):

Date Beg...Date End
1/1/2015...1/7/2015

I need to see how many of this range does my hire date fit in

name...hire...effective_date...action
John...1/1/2015...1/1/2015...action 1
John...1/1/2015...1/7/2015...action 2

Despite there being two records, the same hire date applies to the same person.
His hire date fits into the range so I should get 1.

[sergi117]

However if the action is "action 2" and the effective date fits in the range then instead of counting as 1 it should count as 0 since "action 2 should cancel the count"

name...hire...effective_date...action
John...1/1/2015...1/1/2015...action 1
John...1/1/2015...1/7/2015...action 2

Date Beg...Date End
1/1/2015...1/7/2015

(1/7/2015 fits here as well) cancel count of this record (make this count 0)

[sergi117]

example 1 (excluding action 2):
name...hire...effective_date...action
John...1/1/2015...1/1/2015...action 1

Date Beg...Date End...count
1/1/2015...1/7/2015...1

example 2 (Including action 2)
name...hire...effective_date...action
John...1/1/2015...1/1/2015...action 1
John...1/1/2015...1/7/2015...action 2

Date Beg...Date End...count
1/1/2015...1/7/2015...0

[sergi117]

So even if there are 3 or 6 or however many other instances that are in the range, if there is a single action 2 which occurs within the range, then the count for that range is 0.

[CJ_London]

this is my range (ignore action 3 for now just to get idea across):

Sorry, last chance or I'm going to have to drop out of this thread. Your explanations are too vague and you are not providing any background I can relate to - why ignore action3 to get the idea across, why not ignore action1 instead? What is the rationale for 'offsetting' action2 against action1 rather than action3? I appreciate it is a count but if the order was action1, action3, action2 you would have a different answer.

without knowing the meaning of action1, 2, 3 - is it just a counter or does it have some other meaning? What if there was an action4 and action5 within the same period? The relevance of the dates - do all records all start at the beginning of a week and can last no more that a week? or can an action period starts before the beginning of the range and/or end after the end of a range? And if so, what is the rule to be applied?

best I can suggest is a group by query, linking table1 to table2 on HireDate=dateBeg and a sum of a calculated value

Sum(iif(action='action2',-1,1))