Replace a block of conditions

I have added another staff member, and this block of condtionals is begining to get unwieldy

case when
((dbo.tblInsDetails.[InspectorID] = 7 OR dbo.tblInsDetails.[InspectorID] = 13 OR dbo.tblInsDetails.[InspectorID] = 24 OR dbo.tblInsDetails.[InspectorID] = 37) Or
(dbo.tblInsDetails.[InspectorID2] = 7 OR dbo.tblInsDetails.[InspectorID2] = 13 OR dbo.tblInsDetails.[InspectorID2] = 24 OR dbo.tblInsDetails.[InspectorID2] = 37) or
(dbo.tblInsDetails.[InspectorID3] = 7 OR dbo.tblInsDetails.[InspectorID3] = 13 OR dbo.tblInsDetails.[InspectorID3] = 24 OR dbo.tblInsDetails.[InspectorID3] = 37) or
(dbo.tblInsDetails.[InspectorID4] = 7 OR dbo.tblInsDetails.[InspectorID4] = 13 OR dbo.tblInsDetails.[InspectorID4] = 24 OR dbo.tblInsDetails.[InspectorID4] = 37) or
(dbo.tblInsDetails.[InspectorID5] = 7 OR dbo.tblInsDetails.[InspectorID5] = 13 OR dbo.tblInsDetails.[InspectorID5] = 24 OR dbo.tblInsDetails.[InspectorID5] = 37)) then 1
      else 0
end) as SomeColumnIReturn

Now, it is easy enough for me to get these four dudes InspectorID's and zero into a table variable

-- a table variable to hold the values
Declare @Engineers table (
InspectorID int ,
InspectorName nvarchar(255),
[Role] nvarchar(50))

insert into @Engineers (
InspectorID,
InspectorName,
[Role])

Select InspectorID, InspectorName,[Role] from tblInspectors where [Role] = 'Eng' and [inactive] =0
Union
Select 0 as InspectorID,'' as InspectorName,'' as [Role]

What I am having trouble with is constructing the boolean to replace that big block of hard-coded ID comparisons
Pseudo-coded, I want
case when
(InspectorID in (select InspectorID from @Engineers) or
InspectorID2 in (select InspectorID from @Engineers) or
InspectorID3 in (select InspectorID from @Engineers) or
InspectorID4 in (select InspectorID from @Engineers) or
InspectorID5 in (select InspectorID from @Engineers) )
then 1
else 0
end
) as SomeColumnIReturn

It just doesn't play.
Ideas?
LVL 26
Nick67Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ephraim WangoyaCommented:

This is a little simpler as well

case when 
((dbo.tblInsDetails.[InspectorID] IN (7, 13, 24, 37) Or
 (dbo.tblInsDetails.[InspectorID2] IN (7, 13, 24, 37) or
 (dbo.tblInsDetails.[InspectorID3] IN (7, 13, 24, 37) Or
 (dbo.tblInsDetails.[InspectorID4] IN (7, 13, 24, 37) Or
 (dbo.tblInsDetails.[InspectorID5] IN (7, 13, 24, 37)) then 1

Open in new window

0
knightEknightCommented:
you can simplify this by using IN like this:

((dbo.tblInsDetails.[InspectorID] in (7,13,24,37)
  Or  ...
0
Nick67Author Commented:
That still means if, lets say 37 goes inactive and 42 is hired to replace him that I will need to hand edit this procedure.
New hires get added to the table, and people that leave get marked inacative.

I'd like to alter this procedure once, and then not have to hand-edit every time someone is hired or quits
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Ephraim WangoyaCommented:

Then you should pass the IDs as a delimited parameter to your stored procedure.
In you SP pass the parameter into a temporary table and use the values from there
0
knightEknightCommented:
You can add these values to a simple table like this:

  insert into mySimpleTable select 7
  insert into mySimpleTable select 13
  insert into mySimpleTable select 24
  insert into mySimpleTable select 37

then in your query do this:

((dbo.tblInsDetails.[InspectorID] in (select distinct myCol from mySimpleTable)
  Or  ...

that way when you need people come & go you can just adjust this table.
0
Nick67Author Commented:
I could pass the ID's as a parameters to the sproc.
That still means getting the ID's and passing them in -- and I have no trouble getting them into a table variable as it is
This part works just fine

-- a table variable to hold the values
Declare @Engineers table (
InspectorID int ,
InspectorName nvarchar(255),
[Role] nvarchar(50))

insert into @Engineers (
InspectorID,
InspectorName,
[Role])

Select InspectorID, InspectorName,[Role] from tblInspectors where [Role] = 'Eng' and [inactive] =0
Union
Select 0 as InspectorID,'' as InspectorName,'' as [Role]

If I Issue

Select InspectorID from@ Engineers,
it returns 7,13,24,37 and 0 just like I want.

The case boolean goes BANG!
Trying to substitute
<dbo.tblInsDetails.[InspectorID] IN (7, 13, 24, 37) And>
for
<(dbo.tblInsDetails.[InspectorID] = 7 OR dbo.tblInsDetails.[InspectorID] = 13 OR dbo.tblInsDetails.[InspectorID] = 24 OR dbo.tblInsDetails.[InspectorID] = 37) and>

does not result in good syntax that I can use to alter the sproc


for
0
Nick67Author Commented:
Ah, my bad.
Bracketing...needful, but painful.

Now can I replace
<dbo.tblInsDetails.[InspectorID] IN (7, 13, 24, 37) > 
with
dbo.tblInsDetails.[InspectorID] IN (Select InspectorID from @Engineers)

0
Nick67Author Commented:
Thank you both.
I had tried similar syntax to what you both proposed, but using a (Select yadda from @TableVariable)
It didn't play.
It was ultimately a bracketing issue.
Futzy things.  Brackets.
Thanks for confirming that the simple IN syntax would work.
I'll split the points.

Here's the final sproc.
When you look at it, you'll see why I am not so eager to revisit it every time personnel changes.
That comparison block gets used 5 times.
It isn't hard to mess that up, syntax-wise
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Nick67
-- Create date: 8-Jan-09
-- Description:	Sum Eng Revenue by Month
-- =============================================
Alter PROCEDURE [dbo].[spEngRevenueByMonth] 
	-- Add the parameters for the stored procedure here
	@StartDate smalldatetime = getdate,
	@EndDate smalldatetime = getdate
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

--another large c*nty sp
--what I ultimately need is a listing of invoiced JobIDs that are only done by engineers OR
--invoiced Jobs that have been submitted for cert all in a given time bracket OR
--invoiced jobs that are not never going to be certed that invove an engineer.
--any jobs that have the override checkbox set (ie, NeverInvoice jobs, jobs that will never cert ect.)

-- a table variable to hold the values while they get tagged with YTD or LastWeek
Declare @Engineers table (
InspectorID int ,
InspectorName nvarchar(255),
[Role] nvarchar(50))

insert into @Engineers (
InspectorID,
InspectorName,
[Role])

Select InspectorID, InspectorName,[Role] from tblInspectors where [Role] = 'Eng' and [inactive] =0
Union
Select 0 as InspectorID,'' as InspectorName,'' as [Role]	

SELECT  Distinct   dbo.tblInsDetails.JobID, tblClients_1.[Client Name] AS Owner, dbo.tblClients.[Client Name] AS Consignee, dbo.tblInsDetails.Date, dbo.tblInsDetails.WO#, 
                      dbo.tblInsDetails.EngPO, dbo.tblInsTypes.InsTypeName, dbo.tblGeneralEquipType.GeneralEquipType,  dbo.tblCertResults.GeneralEquipTypeID, dbo.tblEquipmentType.EquipmentType, 
                      dbo.tblCertResults.EquipmentTypeID, dbo.tblCertResults.Item, dbo.tblCertStatus.DateSubmitted , dbo.tblInsDetails.InvoiceNumber, dbo.tblInsDetails.InvoiceDate, dbo.tblCertResults.Capacity, 
                      dbo.tblInvoiceSummary.EngCertification, dbo.tblInvoiceSummary.EngServices, dbo.tblInvoiceSummary.SubTotal, dbo.tblInvoiceSummary.GST, 
                      dbo.tblInvoiceSummary.Total,(
case 
	when
	(dbo.tblInsDetails.[InspectorID] IN (Select InspectorID from @Engineers) And
	(dbo.tblInsDetails.[InspectorID2] IN (Select InspectorID from @Engineers)) and
	(dbo.tblInsDetails.[InspectorID3] IN (Select InspectorID from @Engineers)) and
	(dbo.tblInsDetails.[InspectorID4] IN (Select InspectorID from @Engineers)) and
	(dbo.tblInsDetails.[InspectorID5] IN (Select InspectorID from @Engineers)))  then 1
	else 0 
end) as onlyEngineers,(
case
	when tblCertStatus.DateSubmitted Is Not Null then 1
	ELSE 0
END ) as isCert,(
case when 
((dbo.tblInsDetails.[InspectorID] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) Or
(dbo.tblInsDetails.[InspectorID2] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID3] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID4] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID5] IN (Select InspectorID from @Engineers Where InspectorID <> 0))) And 
dbo.tblInsTypes.InsTypeName not like '%Eng Cert%'  And 
dbo.tblInsTypes.InsTypeName not like '%CAODC%' then 1
	else 0 
end) as InvolvesEngineers,(
case when 
((dbo.tblInsDetails.[InspectorID] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) Or
(dbo.tblInsDetails.[InspectorID2] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID3] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID4] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID5] IN (Select InspectorID from @Engineers Where InspectorID <> 0))) then 1
	else 0 
end) as JointInspection,(
case when
dbo.tblLoadtests.jobid is not null then 1
	else 0
end) as OurLoadTest

FROM         dbo.tblEquipmentType INNER JOIN
                      dbo.tblCertResults ON dbo.tblEquipmentType.EquipmentTypeID = dbo.tblCertResults.EquipmentTypeID INNER JOIN
                      dbo.tblGeneralEquipType ON dbo.tblCertResults.GeneralEquipTypeID = dbo.tblGeneralEquipType.GeneralEquipTypeID INNER JOIN
                      dbo.tblInsDetails ON dbo.tblCertResults.JobID = dbo.tblInsDetails.JobID INNER JOIN                      
                      dbo.tblBilling ON dbo.tblInsDetails.JobID = dbo.tblBilling.JobID INNER JOIN
                      dbo.tblInsTypes ON dbo.tblInsDetails.InsTypeID = dbo.tblInsTypes.InsTypeID INNER JOIN
                      
                      dbo.tblClients AS tblClients_1 ON dbo.tblInsDetails.OwnerID = tblClients_1.[Client ID] INNER JOIN
                      dbo.tblClients ON dbo.tblInsDetails.ConsigneeID = dbo.tblClients.[Client ID] left Join
					  dbo.tblCertStatus ON dbo.tblCertResults.ResultsID = dbo.tblCertStatus.ResultsID LEFT JOIN 
					  dbo.tblLoadTests ON dbo.tblInsDetails.JobID = dbo.tblLoadTests.JobID left Join
					  dbo.tblInvoiceSummary ON dbo.tblInsDetails.JobID = dbo.tblInvoiceSummary.JobID
where dbo.tblInsDetails.JobID in(

select dbo.tblInsDetails.JobID
--,InspectorID,InspectorID2,InspectorID3,InspectorID4,InspectorID5
from dbo.tblInsDetails
where dbo.tblInsDetails.InvoiceDate Between cast(@StartDate as datetime) And cast(@EndDate as datetime)  and (
(dbo.tblInsDetails.[InspectorID]  IN (Select InspectorID from @Engineers Where InspectorID <> 0)) and
(dbo.tblInsDetails.[InspectorID2] IN (Select InspectorID from @Engineers)) and
(dbo.tblInsDetails.[InspectorID3] IN (Select InspectorID from @Engineers)) and
(dbo.tblInsDetails.[InspectorID4] IN (Select InspectorID from @Engineers)) and
(dbo.tblInsDetails.[InspectorID5] IN (Select InspectorID from @Engineers)))

--I also need Jobs that will require that are invoiced and have been submitted for cert in that same period.
--but they can't be ONLY engineers

Union All

SELECT tblInsDetails.JobID
--, tblInsDetails.[WO#], tblInsDetails.InvoiceDate, tblCertStatus.DateSubmitted
FROM (tblCertStatus INNER JOIN tblCertResults ON tblCertStatus.ResultsID = tblCertResults.ResultsID) INNER JOIN tblInsDetails ON tblCertResults.JobID = tblInsDetails.JobID
WHERE (((tblCertStatus.DateSubmitted) Between cast(@StartDate as datetime) And cast(@EndDate as datetime)) AND ((tblCertStatus.DateSubmitted) Is Not Null))


union all

--need non-cert jobs that involve an engineer
select dbo.tblInsDetails.JobID
--,InspectorID,InspectorID2,InspectorID3,InspectorID4,InspectorID5
from dbo.tblInsDetails inner join dbo.tblInstypes on dbo.tblInsDetails.InsTypeID = dbo.tblInsTypes.InsTypeID
where dbo.tblInsDetails.InvoiceDate Between cast(@StartDate as datetime) And cast(@EndDate as datetime) and (
(dbo.tblInsDetails.[InspectorID] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) Or
(dbo.tblInsDetails.[InspectorID2] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID3] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID4] IN (Select InspectorID from @Engineers Where InspectorID <> 0)) or
(dbo.tblInsDetails.[InspectorID5] IN (Select InspectorID from @Engineers Where InspectorID <> 0))) And 
dbo.tblInsTypes.InsTypeName not like '%Eng Cert%'  And 
dbo.tblInsTypes.InsTypeName not like '%CAODC%'


union all

select dbo.tblInsDetails.JobID from dbo.tblInsDetails where dbo.tblInsDetails.IncludeInEngRevenue = 1

)
and not dbo.tblInsDetails.InvoiceNumber is null
and not dbo.tblInsDetails.JobID in (select dbo.tblInsDetails.JobID from dbo.tblInsDetails inner join dbo.tblEngRevenuePaid on dbo.tblInsDetails.JobID = dbo.tblEngRevenuePaid.JobID
where dbo.tblEngRevenuePaid.StartDate < cast(@StartDate as datetime) 
)

Order by dbo.tblInsDetails.InvoiceDate

END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Author Commented:
My comment shows the final code that was put into production and how the question was ultimately sloved
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.