I have a query that generates a list of tasks that have a due and completed date.
Name Task Due Date Completed Date
Joe Task1 10/1/16 10/30/16
Joe Task1 10/1/17 1/1/18
Joe Task1 10/1/18 2/1/19
Joe Task2 10/1/16 1/1/18
The date range for the data is three fiscal years. I want to end up with a list like this of completed dates based on FY of scheduled date. There are multiple tasks but not everyone has every task, but everyone will have at least one task.
CFY - current fiscal year. There may be multiple instances of the tasks, but I would want the most recent/latest date for that FY.
Name CFY CFY-1 CFY-2
Joe
Task1 2/1/19 1/1/18 10/30/16
Task2 1/1/18
I am thinking crosstab query but I don't know how to do it! Many thanks.
Name Task Due Date Completed Date
Joe Task1 10/1/16 10/30/16
Joe Task1 10/1/17 1/1/18
Joe Task1 10/1/18 2/1/19
Joe Task2 10/1/16 1/1/18
The date range for the data is three fiscal years. I want to end up with a list like this of completed dates based on FY of scheduled date. There are multiple tasks but not everyone has every task, but everyone will have at least one task.
CFY - current fiscal year. There may be multiple instances of the tasks, but I would want the most recent/latest date for that FY.
Name CFY CFY-1 CFY-2
Joe
Task1 2/1/19 1/1/18 10/30/16
Task2 1/1/18
I am thinking crosstab query but I don't know how to do it! Many thanks.