>>>>From the quick glance there is nothing bad in the SP.
>>>>
>>>>1. What is your SQL Server version and do you have latest SP installed (SP3 for 2005)
>>>>
>>>>2. Did you try to probably split this query into 2 steps - step 1 - just select, no group by in CTE and step 2 - group by
>>>>
>>>>3. Did you close and re-open SSMS?
>>>>
>>>>4. Try passing dates in ISO format '20100601' '20100630'
>>
>>I cut one of the OR conditions out the SP and it ran. Problem must be that SQL 2005 does not like the OR in the WHERE clause
>
>Try moving both criteria (both OR) into JOIN conditions. Having them in WHERE transforms your LEFT JOIN into Inner JOIN at present.
>
>Check http://beyondrelational.com/blogs/naomi/archive/2010/03/22/why-left-join-doesn-t-bring-all-records-from-the-left-table.aspx
could you show me what you mean?


FROM arjobhd
LEFT JOIN arjob on arjobhd.invno=arjob.invno
LEFT JOIN arjobtax on arjobhd.invno=arjobtax.invno
LEFT JOIN bbjthead on bbjthead.ljob=arjob.ljob
LEFT JOIN ssslsman on arjobhd.salespnno=ssslsman.salespnno
WHERE
( bbjthead.closedate between '06/01/2010' and '06/30/2010'
AND arjobhd.invdate IS NOT NULL
AND bbjthead.closedate >= arjobhd.invdate )
OR
( arjobhd.invdate between '06/01/2010' and '06/30/2010'
AND bbjthead.closedate IS NOT NULL
AND invdate >= bbjthead.closedate )