kapil_kk (8/2/2013)
...
Try this now-
Selectjc.intJobCardId
,b.vchBarcode as [Barcode]
,i.dteIncidentDate as [IncidentDate]
,ISNULL(ISNULL(p.nvcDisplayName, p2.nvcDisplayName), '') as [ServiceProvider]
,dteAssignedDate as [AssignedDate]
,dteCompletedDate as [CompletedDate]
,DATEDIFF(Hour, dteAssignedDate,dteCompletedDate) as [TimeToComplete]
,ISNULL(lb.TotalHours, 0) as [TotalHours]
,lb.TotalHours/DATEDIFF(Hour, dteAssignedDate,dteCompletedDate) as [Efficiency]
,'' as [Productivity]
,l.vchLocation
,typ.vchType
From dtlIncident i
Inner Join dtlJobCard jc on i.uidId = jc.uidIncidentId
Left Join (Select intJobCardId,
SUM(decHoursWorked + decOvertime1HoursWorked +decOvertime2HoursWorked + decOvertime3HoursWorked ) as TotalHours
From dtlLabour
Group By intJobCardId) lb on jc.intJobCardId = lb.intJobCardId
Left Join dtlParty p on jc.uidServiceProviderOrganisationId = p.uidPartyId
Left Join dtlParty p2 on jc.uidServiceProviderPersonId = p2.uidPartyId
Inner Join dtlBarcode b with (nolock) on i.uidBarcodeId = b.uidId
Left Join dtlItem it on jc.intItemId = it.intItemId
Where i.dteIncidentDate between @dteFromDate and @dteToDate
and it.bitIsAsset = Case @bitAsset When 1 Then (SELECT it.intItemId FROM dtlItem it WHERE it.intItemId IS NOT NULL) Else 'All' End
If this works, I think it's by accident and not by design. It shouldn't be necessary to read the dtlItem table again, and the new subquery could return more than one result which would generate an error. I think this is what you are looking for:
WHERE i.dteIncidentDate BETWEEN @dteFromDate AND @dteToDate
AND (
(@bitAsset = 1 AND it.bitIsAsset = it.intItemId AND it.intItemId IS NOT NULL)
OR
(@bitAsset <> 1 AND it.bitIsAsset = 'All')
)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden