>>>>>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 )
Try for now to change all LEFT JOIN to INNER JOIN. It's a bit complex to properly code your conditions and LEFT JOINs, so I suggest using INNER JOIN for now.
If it's not broken, fix it until it is.
My Blog