Transact SQL Question

jjc9809
jjc9809 used Ask the Experts™
on
I have written a Select Statement in Transact SQl that uses a Case Statement.  The column created is Expr1.  I need to then convert and cast Expr1 as Integer.  How can I write the TSQL Statement to convert the results of Expr1 to an Integer?

My TSQL:

SELECT     TOP (100)
                      PERCENT CASE WHEN ResolutionTime = '>30' THEN '45' WHEN ResolutionTime = '1-2Hr' THEN '90' WHEN ResolutionTime = '<30' THEN '15' WHEN ResolutionTime
                       = '2-4Hr' THEN '180' WHEN ResolutionTime = '4-6Hr' THEN '300' WHEN ResolutionTime = '<1 Hour' THEN '30' WHEN ResolutionTime = '<1Hr' THEN '30'
                       WHEN ResolutionTime = '1-2 Hour' THEN '90' WHEN ResolutionTime = '2-4 Hour' THEN '180' WHEN ResolutionTime = '4-8 Hour' THEN '360' WHEN ResolutionTime
                       = '4-8Hr' THEN '360' WHEN ResolutionTime = '>8 Hour' THEN '540' END AS Expr1, ID, Issue, Resolution, ResolutionTime, Time, Technician,
                      WorkOrder#, DateTimeAdded, Status
FROM         dbo.MainCallIssue
ORDER BY ResolutionTime

The Results in EXPR1 needs to classified as Integer so as to be used in a CRystal Report.

jjc9809
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
remove the Quotes


SELECT TOP ( 100 ) PERCENT
        CASE WHEN ResolutionTime = '>30' THEN 45
             WHEN ResolutionTime = '1-2Hr' THEN 90
             WHEN ResolutionTime = '<30' THEN 15
             WHEN ResolutionTime = '2-4Hr' THEN 180
             WHEN ResolutionTime = '4-6Hr' THEN 300
             WHEN ResolutionTime = '<1 Hour' THEN 30
             WHEN ResolutionTime = '<1Hr' THEN 30
             WHEN ResolutionTime = '1-2 Hour' THEN 90
             WHEN ResolutionTime = '2-4 Hour' THEN 180
             WHEN ResolutionTime = '4-8 Hour' THEN 360
             WHEN ResolutionTime = '4-8Hr' THEN 360
             WHEN ResolutionTime = '>8 Hour' THEN 540
        END AS Expr1, ID, Issue, Resolution, ResolutionTime, Time, Technician,
        WorkOrder#, DateTimeAdded, Status
FROM    dbo.MainCallIssue
ORDER BY ResolutionTime

Commented:
wrap the entire case statement in a convert:




SELECT     TOP (100)
                      PERCENT convert(int,CASE WHEN ResolutionTime = '>30' THEN '45' WHEN ResolutionTime = '1-2Hr' THEN '90' WHEN ResolutionTime = '<30' THEN '15' WHEN ResolutionTime
                       = '2-4Hr' THEN '180' WHEN ResolutionTime = '4-6Hr' THEN '300' WHEN ResolutionTime = '<1 Hour' THEN '30' WHEN ResolutionTime = '<1Hr' THEN '30'
                       WHEN ResolutionTime = '1-2 Hour' THEN '90' WHEN ResolutionTime = '2-4 Hour' THEN '180' WHEN ResolutionTime = '4-8 Hour' THEN '360' WHEN ResolutionTime
                       = '4-8Hr' THEN '360' WHEN ResolutionTime = '>8 Hour' THEN '540' END) AS Expr1, ID, Issue, Resolution, ResolutionTime, Time, Technician,
                      WorkOrder#, DateTimeAdded, Status
FROM         dbo.MainCallIssue
ORDER BY ResolutionTime

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial