Have a look at the attached file for better understanding of the scenarios.
There is a table box and a pivot table (format is there). My desired layout will be on the pivot table where it hasa simple Year, Week and number of bus with special condition, this can be done by using Count(Bus)
as you can see from the table box, these are the buses that are being deploy on 2012 Week 2. Actual time of boarding is the time they left the paritcular bus_terminal. For first row, HAN LINN departed bus terminl call PPI1 at jan 10 2012.
In cases where bus have to move to another terminal due to shortage of buses over the other terminal, it will be call berthing sequence 2. Example will be bus TATA MACHAN, it first berth was at PPI1 and move to KI interchange which shows 2.
Therefore, I would want to count the number of buses that has move from terminal to another terminal.
The next condition is, if the bus move from within terminal saying SAN CALYPS where its berthing shift 1 and 2 is the same TPI, it should exclude from the counting of buses. This goes to TATA HADIAH where it shifted from PPI1 to PPI2 as PPI represent the same whole bus interchange.
I would want to count bus such as JINYUNHE where it move from KI to PPI1, these are count as one bus that really deploy to other terminal.
Is there anyway I can do in a single expression?
Correct result will be 3 which is JINYUNHE, TATA LAMBANG and TATA MACHAN
