>>>>>>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.


I tried it and it failed in the Foxpro 9 form

ldStartDate={}
ldEndDate={}
ldStartDate=thisform.startdate.value
ldEndDate=thisform.enddate.value

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 ]


SQLEXEC(gnConnect,lcSQLCMD,'jobcost')

SELECT jobcost
brow