Teee (8/2/2013)
Selectjc.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
Try this now-
Select
jc.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
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/