Hi
I would like to write on a table called ctcontract, this table contains all of our contract infomation. The table conatins a start date and an end date of all contracts and also the number of employees on each contract. What I would like to do is do a sum of the number of employees between a start date and an end date but with a twist of suming the number of employess each month between the start and end.
For example if I want to know the sum of employees between 01/12/07 and 31/12/08 I can use the following
Select CPCompanyid,NoEmployees as noemp from CTContracts where Fromdate
<= '200081231' and EndDate >= '20071201' and CTcontracts.LKContractType
ID in (0,8) Order by CPCompanyid,ctcontractid.
But what I would like to do is loop round each month between those dates for example 1/12/07 - to 31/12/07 and then 01/01/08 - 31/01/08 and so on until I get a sum of all the employees each month until Dec 08. This will give me a true representation of the number of employees per month for a whole year.
I hope I have explained clearly if not please let me know?
Start Free Trial
