• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 44
  • Last Modified:

Switch statements in access in SQL Server

Hi Im trying to transorm some access database, reports queries etc into SQL Server.

I'm having issues with some of the queries where they are group by and counts using switch statements in access

see below.....how would i mimic this in sql server?

SELECT tblStaff.Full_Name, tblCompany.Managed_Account_Agent, tblStaff.Manager, tblStaff.Email, Count(tblCompany.Managed_Account_Agent) AS [TOTAL MAs], Count(Switch([REMOVE_FROM_LEADCHECKING_PLATFORM] Is Null,[Managed_Account_Agent])) AS [TOTAL F2C MAs], Count(Switch([REMOVE_FROM_LEADCHECKING_PLATFORM] Is Null And [Actual_Number_Of_Employees]>1 And [Mobile_Provider]>"" And [PMOBHAND]>0 And [EXP_Date] Is Not Null,1)) AS [Mobile_Profile F2C], ([Mobile_Profile F2C]/[TOTAL F2C MAs]) AS [Mobile_Profile F2C %]
FROM tblCompany INNER JOIN tblStaff ON tblCompany.Managed_Account_Agent = tblStaff.SystemName
GROUP BY tblStaff.Full_Name, tblCompany.Managed_Account_Agent, tblStaff.Manager, tblStaff.Email
HAVING (((tblStaff.Manager)<>"leaver"));
0
Neil Woods
Asked:
Neil Woods
  • 2
3 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Group By is the same in either.

Switch can generally be changed over to CASE statements for SQL Server, but your SWITCH statements don't look like they're necessary. A SWITCH is intended to iterate a list of checks and values, and return the FIRST value for the Check that evaluates to True.

Your statements, like:

Switch([REMOVE_FROM_LEADCHECKING_PLATFORM] Is Null,[Managed_Account_Agent])

Seems only to check whether that field is NULL, and then return a value in Managed_Account_Agent, and you the COUNT on that value. If you could perhaps let us know what you're trying to accomplish we could help you to rewrite this to be more "SQL Server Ready".
0
 
ste5anSenior DeveloperCommented:
hmm, something like this:

SELECT   S.Full_Name ,
         C.Managed_Account_Agent ,
         S.Manager ,
         S.Email ,
         COUNT(*) AS [TOTAL MAs] ,
         SUM(IIF([REMOVE_FROM_LEADCHECKING_PLATFORM] IS NULL, 1, 0)) AS [TOTAL F2C MAs] ,
         SUM(IIF(
                 [REMOVE_FROM_LEADCHECKING_PLATFORM] IS NULL
                 AND [Actual_Number_Of_Employees] > 1
                 AND [Mobile_Provider] > ''
                 AND [PMOBHAND] > 0
                 AND [EXP_Date] IS NOT NULL ,
                 1 ,
                 0)) AS [Mobile_Profile F2C]
FROM     tblCompany C
         INNER JOIN tblStaff S ON C.Managed_Account_Agent = S.SystemName
GROUP BY S.Full_Name ,
         C.Managed_Account_Agent ,
         S.Manager ,
         S.Email
HAVING   S.Manager != 'leaver';

Open in new window


When using an older version than SQL Server 2012 you need CASE..END instead IIF.

p.s. always qualify all columns with table aliases. It's unclear where some of your columns come from.
0
 
Dale FyeCommented:
To replace the Switch statement, I would normally use a CASE statement in SQL Server

But I would argue that your Access query should not be using SWITCH but maybe the NZ function or an IIF statement.  Switch is designed to give you multiple options in a single statement.  In the following statement the SWITCH function will return the value from the first critieria which returns a true response.

X = SWITCH(varTest < 3, 1 varTest < 9, 2, varTest < 20, 3, True, 4)

In your case, you simply evaluating a single criteria, so you could just as easily use:
SUM(IIF([somefield] IS NULL, 1, 0))

Open in new window

IN SQL Server you would replace the IIF with a case statement:
SUM(CASE WHEN [SomeField] IS NULL THEN 1 ELSE 0 END))

Open in new window


Also, you have a HAVING clause in your query, when you should have a WHERE clause.  It is easy to do this in Access because when you create an aggregate query and place the criteria under a column where you use a Group By clause, Access automatically places this in a HAVING clause.  However, placing that in a HAVING clause means that access will process all records in the group by and then exclude the records that don't match the HAVING clause.  If you change that up (add another column to the query grid, select WHERE instead of GroupBy, and add your criteria, Access will first identify those records which meet the WHERE criteria, and then perform the aggregation against only those records.  This can be much quicker.

Finally, although Access lets you refer to computed fields in the same query they are created, SQL Server doesn't let you do this, so I would create a subquery to compute those two values, and then an outer query to compute the percentage.

So your sql might look like:

SELECT T.*,
CASE WHEN T.[Total F2C MAs] IS NOT NULL THEN T.[Mobile_Profile F2C]/T.[TOTAL F2C MAs] AS [Mobile_Profile F2C %]
FROM (
SELECT tblStaff.Full_Name, tblCompany.Managed_Account_Agent, tblStaff.Manager, tblStaff.Email,
Count(tblCompany.Managed_Account_Agent) AS [TOTAL MAs],
SUM(CASE WHEN [REMOVE_FROM_LEADCHECKING_PLATFORM] Is Null THEN 1 ELSE 0 END)) AS [TOTAL F2C MAs],
SUM(CASE WHEN ([REMOVE_FROM_LEADCHECKING_PLATFORM] Is Null) And
                                ([Actual_Number_Of_Employees]>1) And
                                ([Mobile_Provider]>'') And
                                ([PMOBHAND]>0) And
                                ([EXP_Date] Is Not Null) THEN 1 ELSE 0 END) AS [Mobile_Profile F2C],
FROM tblCompany INNER JOIN tblStaff ON tblCompany.Managed_Account_Agent = tblStaff.SystemName
WHERE (tblStaff.Manager<>'leaver')
GROUP BY tblStaff.Full_Name, tblCompany.Managed_Account_Agent, tblStaff.Manager, tblStaff.Email
) as T

Hope this helps
Dale
0
 
Dale FyeCommented:
Recommend generally splitting points between Dale and Ste5an, as they both actually rewrote the SQL and made the changes appropriate to this query, while Scott provided more generalized solution.  Recommend awarding Dale a few extra points as he [I] recognized that the user should be using a WHERE clause rather then a HAVING clause, to speed up processing.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now