September 9, 2008 at 9:29 am
SELECT CASE WHEN h.cloned_wrksht_indcr IS NULL THEN AVG(ABS(CAST(dbo.fn_workdays(h.job_execution_dt,l.creatn_dt) AS MONEY)))
ELSE AVG(ABS(CAST(dbo.fn_workdays(h.job_execution_dt,h.mdfctn_dt) AS MONEY))) END
FROM order_mgmt.dbo.task_wrksht_labor l WITH(NOLOCK)
JOIN order_mgmt.dbo.task_wrksht_hdr h WITH(NOLOCK) ON l.wrksht_hdr_id=h.wrksht_hdr_id
JOIN order_mgmt.dbo.task_job_hdr j WITH(NOLOCK) ON j.job_hdr_id = h.job_hdr_id
WHERE CASE WHEN h.cloned_wrksht_indcr IS NULL THEN h.creatn_dt
ELSE h.mdfctn_dt END BETWEEN @wb_date AND @we_date
AND ISNULL(h.cloned_wrksht_indcr, 'n') <> 'y'
AND LEFT(j.mastr_sls_order_no,2) = @branch
so im trying to implement the above...and i keep getting an error saying cloned_wrksht_indcr is not in an agg or group by clause. .but i dont want to group by cloned_wrksht_indcr!
merely want one set of calculations if its null...and another set of calculations if not...
any help??
September 9, 2008 at 11:04 am
I don't know if it would make a difference, but it seems like maybe you could eliminate the CASE in your select by using ISNULL?
AVG(ABS(CAST(dbo.fn_workdays(h.job_execution_dt,ISNULL(h.mdfctn_dt,l.creatn_dt) AS MONEY)))
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 9, 2008 at 11:47 am
hey greg...i like your idea, except that it would check for a null between those specific dates. what i intended to do was perform a certain calculation based on the condition of another field.
i also successfully implemented it by moving the case from outside to inside the fn_workingdays.
so that it will compare job_execution_dt to either creatn or mdfctn dates depending on cloned_wrksht_indcr.
thank you very much for your feedback greg. that was a very prompt response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply