>The point of the query is just one section of a program that gathers Puurchase Order Cost, Labor Cost, Materials Cost and Stock costs for jobs that were invoiced in a date range.
>
>This is the work around that I would not have found without your and looking at Profiler.
>That gave me the info needed. A Select statement rather than and EXEC statement
>The Select did not use parameters where the EXEC did.
>
>I would be nice to know the bug but I need to get this done. The customer is limping along without it.
>I would guess if it is a bug 2005 will not get the fix
>
>Thanks again for your help brain storming on this one.
>
>This works
>
> 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 ]
>
> cdStartDate= "'"+DTOC(ldStartDate)+"'"
> cdEndDate= "'"+DTOC(ldEndDate)+"'"
>
> lcSQLCMD=lcSQLCMD+"( bbjthead.closedate between "+cdStartDate+" and " +cdEndDate + " AND arjobhd.invdate IS NOT NULL AND bbjthead.closedate >= arjobhd.invdate )"
> lcSQLCMD=lcSQLCMD+" OR ( arjobhd.invdate between "+cdStartDate+" and " +cdEndDate + " AND bbjthead.closedate IS NOT NULL AND invdate >= bbjthead.closedate ) "
> lcSQLCMD=lcSQLCMD+" 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')

Ok, as long as you got it resolved using explicit values, it's fine.

I also asked my friend to look at this query, so he may jump into this discussion a bit later.