>>>>>>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
>>
>>I have an update
>>If I put the dates in the select statement like this I get results
>>
>> lcSQLCMD=[ SELECT arjobhd.custno, ];
>> +[ arjobhd.customer, ];
>> +[ ISNULL(ssslsman.salespn,'') as salespn, ];
>> +[ arjobhd.transtype, ];
>> +[ arjobtax.taxcode, ];
>> +[ IsNull(arjobhd.invno,0) as invno, ];
>> +[ arjob.ljob, ];
>> +[ bbjthead.id, ];
>> +[ ISNULL(SUM(arjob.price),0) as invoice, ];
>> +[ ISNULL(SUM(arjob.frtamt),0) as freight, ];
>> +[ CAST(00.00 AS decimal(10,2)) as tax, ];
>> +[ CAST(00.00 AS decimal(10,2)) as cost_lab, ];
>> +[ CAST(00.00 AS decimal(10,2)) as cost_mat, ];
>> +[ CAST(00.00 AS decimal(10,2)) as cost_fin, ];
>> +[ CAST(00.00 AS decimal(10,2)) as cost_pur, ];
>> +[ CAST(00.00 AS decimal(10,2)) as Totcost, ];
>> +[ CAST(00.00 AS decimal(10,2)) as TotInv, ];
>> +[ CAST(00.00 AS decimal(10,2)) as profper ];
>> +[ FROM arjobhd ];
>> +[ JOIN arjob on arjobhd.invno=arjob.invno ];
>> +[ JOIN arjobtax on arjobhd.invno=arjobtax.invno ];
>> +[ JOIN bbjthead on bbjthead.ljob=arjob.ljob ];
>> +[ 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 ) ];
>> +[ Group by arjobhd.custno, ];
>> +[ arjobhd.customer, ];
>> +[ ssslsman.salespn, ];
>> +[ arjobhd.transtype, ];
>> +[ arjobtax.taxcode, ];
>> +[ arjobhd.invno, ];
>> +[ arjob.ljob, ];
>> +[ bbjthead.id ];
>> +[ ORDER by arjob.ljob, arjobhd.invno ]
>>
>>
>>If I do this it fails
>>
>> lcSQLCMD=[ SELECT arjobhd.custno, ];
>> +[ arjobhd.customer, ];
>> +[ ISNULL(ssslsman.salespn,'') as salespn, ];
>> +[ arjobhd.transtype, ];
>> +[ arjobtax.taxcode, ];
>> +[ IsNull(arjobhd.invno,0) as invno, ];
>> +[ arjob.ljob, ];
>> +[ bbjthead.id, ];
>> +[ ISNULL(SUM(arjob.price),0) as invoice, ];
>> +[ ISNULL(SUM(arjob.frtamt),0) as freight, ];
>> +[ CAST(00.00 AS decimal(10,2)) as tax, ];
>> +[ CAST(00.00 AS decimal(10,2)) as cost_lab, ];
>> +[ CAST(00.00 AS decimal(10,2)) as cost_mat, ];
>> +[ CAST(00.00 AS decimal(10,2)) as cost_fin, ];
>> +[ CAST(00.00 AS decimal(10,2)) as cost_pur, ];
>> +[ CAST(00.00 AS decimal(10,2)) as Totcost, ];
>> +[ CAST(00.00 AS decimal(10,2)) as TotInv, ];
>> +[ CAST(00.00 AS decimal(10,2)) as profper ];
>> +[ FROM arjobhd ];
>> +[ JOIN arjob on arjobhd.invno=arjob.invno ];
>> +[ JOIN arjobtax on arjobhd.invno=arjobtax.invno ];
>> +[ JOIN bbjthead on bbjthead.ljob=arjob.ljob ];
>> +[ JOIN ssslsman on arjobhd.salespnno=ssslsman.salespnno ];
>> +[ WHERE ];
>> +[ ( bbjthead.closedate between ?ldstartdate and ?ldenddate ];
>> +[ AND arjobhd.invdate IS NOT NULL ];
>> +[ AND bbjthead.closedate >= arjobhd.invdate ) ];
>> +[ OR ];
>> +[ ( arjobhd.invdate between ?ldstartdate and ?ldenddate ];
>> +[ AND bbjthead.closedate IS NOT NULL ];
>> +[ AND invdate >= bbjthead.closedate ) ];
>> +[ Group by arjobhd.custno, ];
>> +[ arjobhd.customer, ];
>> +[ ssslsman.salespn, ];
>> +[ arjobhd.transtype, ];
>> +[ arjobtax.taxcode, ];
>> +[ arjobhd.invno, ];
>> +[ arjob.ljob, ];
>> +[ bbjthead.id ];
>> +[ ORDER by arjob.ljob, arjobhd.invno ]
>>
>>
>>ldStartDate={}
>>ldEndDate={}
>>ldStartDate=thisform.startdate.value
>>ldEndDate=thisform.enddate.value
>>
>>I use ThisForm.StartDate.Value={} to set the data type for the textbox in the form
>>
>>Same for EndDate
>>
>>ldStartDate={}
>>ldEndDate={}
>>ldStartDate=thisform.startdate.value
>>ldEndDate=thisform.enddate.value
>>
>>This has worked in the past but today it does not
>
>Can you run SQL Profiler and verify what is the exact command being sent to SQL Server in the second case?


exec sp_executesql N' SELECT arjobhd.custno, arjobhd.customer, ISNULL(ssslsman.salespn,'''') as salespn, arjobhd.transtype, arjobtax.taxcode, IsNull(arjobhd.invno,0) as invno, arjob.ljob, bbjthead.id, ISNULL(SUM(arjob.price),0) as invoice, ISNULL(SUM(arjob.frtamt),0) as freight, CAST(00.00 AS decimal(10,2)) as tax, CAST(00.00 AS decimal(10,2)) as cost_lab, CAST(00.00 AS decimal(10,2)) as cost_mat, CAST(00.00 AS decimal(10,2)) as cost_fin, CAST(00.00 AS decimal(10,2)) as cost_pur, CAST(00.00 AS decimal(10,2)) as Totcost, CAST(00.00 AS decimal(10,2)) as TotInv, CAST(00.00 AS decimal(10,2)) as profper FROM arjobhd JOIN arjob on arjobhd.invno=arjob.invno JOIN arjobtax on arjobhd.invno=arjobtax.invno JOIN bbjthead on bbjthead.ljob=arjob.ljob JOIN ssslsman on arjobhd.salespnno=ssslsman.salespnno WHERE ( bbjthead.closedate between @P1 and @P2 AND arjobhd.invdate IS NOT NULL AND bbjthead.closedate >= arjobhd.invdate ) OR ( arjobhd.invdate between @P3 and @P4 AND bbjthead.closedate IS NOT NULL AND invdate >= bbjthead.closedate ) Group by arjobhd.custno, arjobhd.customer, ssslsman.salespn, arjobhd.transtype, arjobtax.taxcode, arjobhd.invno, arjob.ljob, bbjthead.id ORDER by arjob.ljob, arjobhd.invno ',N'@P1 datetime,@P2 datetime,@P3 datetime,@P4 datetime','2010-06-01 00:00:00:000','2010-06-30 00:00:00:000','2010-06-01 00:00:00:000','2010-06-30 00:00:00:000'