Cognos how to use the prompt dates in queries
Hi
I have a report that uses date prompts to select data from contracts. (cognos8)
Basically you enter a start date and an end date and I have created queries that select data between those dates.
What I need to do though is count the number of emplyees on a contract for each month between the dates entered. for example if I enter the start prompt as 01/12/07 and the end prompt as 31/12/08 I want the count of employees from 01/12/07 to 31/12/07 and then the 01/01/08 to the 31/01/08 and then the same for Feb, March, April etc all the way to Dec 08 (end prompt date) and then add them all together at the end! Any ideas welcome!!
I have a report that uses date prompts to select data from contracts. (cognos8)
Basically you enter a start date and an end date and I have created queries that select data between those dates.
What I need to do though is count the number of emplyees on a contract for each month between the dates entered. for example if I enter the start prompt as 01/12/07 and the end prompt as 31/12/08 I want the count of employees from 01/12/07 to 31/12/07 and then the 01/01/08 to the 31/01/08 and then the same for Feb, March, April etc all the way to Dec 08 (end prompt date) and then add them all together at the end! Any ideas welcome!!
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Cheers!
ASKER
That sounds like what I need.
Are you able to send me any example of the "extract" function?
This is the select statement I can use to extract the infomation for just one month
Select CPCompanyid,NoEmployees as noemp from CTContracts where Fromdate
<= '20080430' and EndDate >= '20080401' and CTcontracts.LKContractType
The bit I am struggling with is using this statement in conjunction with the start and end date prompts and moving the dates forward to meet the time scales I need.