Teee (8/2/2013)

---

Select

jc.intJobCardId

,b.vchBarcodeas [Barcode]

,i.dteIncidentDateas [IncidentDate]

,ISNULL(ISNULL(p.nvcDisplayName, p2.nvcDisplayName), '')as [ServiceProvider]

,dteAssignedDateas [AssignedDate]

,dteCompletedDateas [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 it.intItemId IS NOT NULL Else 'All' End --This is the part that's breaking the script

I'm actually passing bitIsAsset as a parameter( When its = 1 the the itemid must not be blank or null) else the parameter value must be All

I just don't know how to use IS NOT NULL in a case statement.

Thanks