Hi,
I'm still new at report writing, using cognos 10.2.
I am trying to run a report for all employees on sick leave. The columns I have are Employee number, sick start date, sick end date.
As employees sickness is logged in week blocks. I want to write a report where any absence where the end date of one entry = start date - day of another entry. So that rather than having multiple lines of data on the output for one employee, it gives me one line of data.
Example
Data currently produced
Employee Sick Start Date Sick End Date
0123456. 01/01/2020. 07/01/2020
0123456. 08/01/2020. 14/01/2020
0123456. 15/01/2020. 22/01/2020
0123456. 23/01/2020. 28/01/2020
What I want on the output
0123456. 01/01/2020. 28/01/2020
Any help on this would be greatly appreciated.
Quote from: ArunStillLearning on 25 Apr 2020 02:24:47 AM
Hi,
I'm still new at report writing, using cognos 10.2.
I am trying to run a report for all employees on sick leave. The columns I have are Employee number, sick start date, sick end date.
As employees sickness is logged in week blocks. I want to write a report where any absence where the end date of one entry = start date - day of another entry. So that rather than having multiple lines of data on the output for one employee, it gives me one line of data.
Example
Data currently produced
Employee Sick Start Date Sick End Date
0123456. 01/01/2020. 07/01/2020
0123456. 08/01/2020. 14/01/2020
0123456. 15/01/2020. 22/01/2020
0123456. 23/01/2020. 28/01/2020
What I want on the output
0123456. 01/01/2020. 28/01/2020
Any help on this would be greatly appreciated.
You can probably achieve what you need using the minimum and maximum functions.
For the first sick start date:
minimum([Sick Start Date] for [Employee]) For the last sick end date:
maximum([Sick End Date] for [Employee])sjdig
Thanks for replying sorry I have only just seen response. I managed to do it in end by joining two queries together. Took me a bit of time but got there in the end