Hi Mark,

I think it can be done with a query. The idea is to create a select for each possible type of cow and UNION them. Here's pseudo code for one type CatAan = 110.

SELECT d1.DierID, d1.Aanvoer, d1.Afvoer, 
  CAST(Afvoer-Aanvoer+1 AS int) AS DierDagen, 110 AS CatAan, 110 AS CatAf
FROM ( SELECT DierID,
		CASE WHEN DATEADD(Month, 2, GebDatum) < Aanvoer THEN Aanvoer
			ELSE DATEADD(Month, 2, GebDatum) END AS Aanvoer,
		CASE WHEN (DATEADD(Month, 3, GebDatum)-1) > Afvoer THEN Afvoer
			ELSE (DATEADD(Month, 3, GebDatum)-1) END AS Afvoer
	FROM DierTable
	WHERE Where CatAan <> CatAf And DierDagen > 0
		AND  ( DATEADD(Month, 2, GebDatum) BETWEEN Aanvoer And Afvoer)
             OR (DATEADD(Month, 3, GebDatum)-1) BETWEEN Aanvoer And Afvoer) ) d1

Where,
DATEADD(Month, 2, GebDatum) is a beginning date for type 110
(DATEADD(Month, 3, GebDatum)-1) is an end date for type 110
Don't forget to add records with matching Aanvoer And Afvoer.

SELECT DierID, Aanvoer, Afvoer, DierDagen, CatAan, CatAf
	FROM DierTable
	WHERE Where CatAan = CatAf And DierDagen > 0

>Hi Michael,
>
>The SHOW_STATISTICS shows the statistics are updated last on July 1 2002. That is not very recent, but on this test machine very little changes occur. I did the UPDATE STATISTICS and wil have to wait and see if that changed anything.
>
>I'm afraid all the ugly code is neccesary for now. I think it should be able to work set-oriented, but I haven't figured it out yet. Maybe you have a good idea if I explain what the code is meant to do? I'll take the sp_AanwezigheidCategorie first (added below again). This proc selects records wich represent cows into table #tmpTable. This table no has the ID of the animal (DierID), the date of birth (GebDatum), the date of arrival (Aanvoer), the date of departure (Afvoer), number of days present (DierDagen) and finally two codes (CatAan and CatAf). These codes represent the type of cow (calf, 1 year old, older cow etc.) the animal was at arrivel (CatAan) or departure (CatAf).
>What I need to calculate is the number of days the animal was present as every code. The records that have equal CatAan and CatAf hold this number in DierDagen. But the records with different CatAan and CatAf can't be calculated yet. I can calculate when a animal moves from one code to the other by using its birthday. In this proc I select all records of the animals with different CatAan and CatAf codes. Then I calculate when the animal moves from the startcode (CatAan) to the next code. Now I update the original record by replacing CatAf with CatAan. I insert a new record with the code the animal move to as CatAan and the previous CatAf as CatAf. The dates move to. If the animal only moved to an other code this one is ready now, but if the animal moved through more categories the record wil again be selected in the following round and will go though this process again.
>
>At the end all records have equal CatAan and CatAf so I can calculate the average number of animals of every Cat by summing the days and deviding that by the length of the period.
>
>Does this make some sense now? I'v seen people do wonders with Set-Oriented sql, but this one never made it.
>