如何设置view中的字段类型

Question

CREATE VIEW v_legalyear AS
 (
SELECT A.emplid, [legal_day] = 
CASE    WHEN A.working_year >= 0 AND A.working_year < 10 THEN 
CASE WHEN datediff(yy,B.hire_dt, CONVERT(datetime, '12/31/' + RIGHT(ltrim(str(year(getdate()))), 2), 1))= 0  THEN
CASE WHEN (day(B.hire_dt)- 15)<=0 THEN
(12-month(B.hire_dt)+1)*5/12
else
(12-month(B.hire_dt)+1)*5/12
end
else 
5
end
 WHEN A.working_year >= 10 AND A.working_year < 20 THEN 
10
 WHEN A.working_year > 20 THEN
 15 
END 
 FROM eofficesuz.dbo.SUZEOFFICE_EXISTING_EMPL AS A LEFT OUTER JOIN
      eofficesuz.dbo.EOFFICE_EXISTING_EMPL AS B ON a.emplid = b.emplid
)
我发现legal_day这个字段是按整形存的，如何使它按float存

Answer 1

CREATE VIEW v_legalyear AS
 (
SELECT A.emplid, [legal_day] = 
    CASE    WHEN A.working_year >= 0 AND A.working_year < 10 THEN 
        CASE WHEN datediff(yy,B.hire_dt, CONVERT(datetime, '12/31/' + RIGHT(ltrim(str(year(getdate()))), 2), 1))= 0  THEN
            CASE WHEN (day(B.hire_dt)- 15)<=0 THEN
                (12-month(B.hire_dt)+1)*5/12*1.0
            else
                (12-month(B.hire_dt)+1)*5/12*1.0        
            end        
        else 
            5.00
        end
     WHEN A.working_year >= 10 AND A.working_year < 20 THEN 
        10.00
     WHEN A.working_year > 20 THEN
         15.00
    END 
 FROM eofficesuz.dbo.SUZEOFFICE_EXISTING_EMPL AS A LEFT OUTER JOIN
      eofficesuz.dbo.EOFFICE_EXISTING_EMPL AS B ON a.emplid = b.emplid
)

Answer 2

--try
CREATE VIEW v_legalyear AS
 (
SELECT A.emplid, [legal_day] = cast(
    CASE    WHEN A.working_year >= 0 AND A.working_year < 10 THEN 
        CASE WHEN datediff(yy,B.hire_dt, CONVERT(datetime, '12/31/' + RIGHT(ltrim(str(year(getdate()))), 2), 1))= 0  THEN
            CASE WHEN (day(B.hire_dt)- 15)<=0 THEN
                (12-month(B.hire_dt)+1)*5/12
            else
                (12-month(B.hire_dt)+1)*5/12        
            end        
        else 
            5
        end
     WHEN A.working_year >= 10 AND A.working_year < 20 THEN 
        10
     WHEN A.working_year > 20 THEN
         15 
    END  as float)
 FROM eofficesuz.dbo.SUZEOFFICE_EXISTING_EMPL AS A LEFT OUTER JOIN
      eofficesuz.dbo.EOFFICE_EXISTING_EMPL AS B ON a.emplid = b.emplid
)

Answer 3

CREATE VIEW v_legalyear AS
 (
SELECT A.emplid, [legal_day] = 
    CASE    WHEN A.working_year >= 0 AND A.working_year < 10 THEN 
        CASE WHEN datediff(yy,B.hire_dt, CONVERT(datetime, '12/31/' + RIGHT(ltrim(str(year(getdate()))), 2), 1))= 0  THEN
            CASE WHEN (day(B.hire_dt)- 15)<=0 THEN
                (12-month(B.hire_dt)+1)*5/12*1.0
            else
                (12-month(B.hire_dt)+1)*5/12*1.0        
            end        
        else 
            5*1.0
        end
     WHEN A.working_year >= 10 AND A.working_year < 20 THEN 
        10*1.0
     WHEN A.working_year > 20 THEN
         15*1.0
    END 
 FROM eofficesuz.dbo.SUZEOFFICE_EXISTING_EMPL AS A LEFT OUTER JOIN
      eofficesuz.dbo.EOFFICE_EXISTING_EMPL AS B ON a.emplid = b.emplid
)

Answer 4

CREATE VIEW v_legalyear AS
 (
SELECT A.emplid, [legal_day] = 
    CASE    WHEN A.working_year >= 0 AND A.working_year < 10 THEN 
        CASE WHEN datediff(yy,B.hire_dt, CONVERT(datetime, '12/31/' + RIGHT(ltrim(str(year(getdate()))), 2), 1))= 0  THEN
            CASE WHEN (day(B.hire_dt)- 15)<=0 THEN
                (12-month(B.hire_dt)+1)*5/12.0
            else
                (12-month(B.hire_dt)+1)*5/12.0        
            end        
        else 
            5.0
        end
     WHEN A.working_year >= 10 AND A.working_year < 20 THEN 
        10.0
     WHEN A.working_year > 20 THEN
         15 .0
    END 
 FROM eofficesuz.dbo.SUZEOFFICE_EXISTING_EMPL AS A LEFT OUTER JOIN
      eofficesuz.dbo.EOFFICE_EXISTING_EMPL AS B ON a.emplid = b.emplid
)