CODEWITHSUNDEEP
sql-servert-sqldatetimestored-procedures
hunt
hunt

Reputation: 321

How to get value based on working shift

I have 3 main shift

08:00~16:00 is S1
16:00~24:00 is S2
00:00~08:00 is S3

I have to get all data based on the 3 shift above, right now i'm creating a stored procedure to retrieve all the data based on the 3 shift.

    ALTER PROCEDURE sp_shift
(
    @V2_WORK_DATE DATETIME,
    @V3_SHIFT_NO VARCHAR(2)
)

AS
    DECLARE @shift VARCHAR(5);

        select t3.scn,t3.vsl_name, t1.qty, t1.opr_type, t1.hatch_num,t1.opr_st_dt_tm,t1.opr_ed_dt_tm,t1.wght,t1.vol,t2.description,t3.disc_load,

        case when @V3_SHIFT_NO = 'S1' THEN DATEPART(HH,@V2_WORK_DATE)> = 8 AND DATEPART(HH, @V2_WORK_DATE) <16
             when @V3_SHIFT_NO = 'S2' THEN DATEPART(HH,@V2_WORK_DATE)> = 16 AND DATEPART(HH, @V2_WORK_DATE) <24
             when @V3_SHIFT_NO = 'S1' THEN DATEPART(HH,@V2_WORK_DATE)> = 8 AND DATEPART(HH, @V2_WORK_DATE) <16 end as @shift
        from ccostsitem t1
        inner join ccoscargotype t2 on t2.code = t1.cg_type_id
        inner join ccostallysheet t3 on t3.id = t1.master_id
        where @shift = @V3_SHIFT_NO

Below is how i exec the sp with the param

EXEC sp_shift '2016-12-30 08:00:00.000','S1'

the problem with this stored procedure is the case when shows error near end prompt that 'incorrect syntax near end'

Upvotes: 1

Views: 68

Answers (3)

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4211

Don't use @ sign:

from:

end as @shift

to:

end as shift

Upvotes: 0

Wendy
Wendy

Reputation: 660

WHEN is the condition to be evaluated, THEN is the conclusion.

SELECT *
FROM (SELECT t3.scn
   , t3.vsl_name
   , t1.qty
   , t1.opr_type
   , t1.hatch_num
   , t1.opr_st_dt_tm
   , t1.opr_ed_dt_tm
   , t1.wght 
   , t1.vol
   , t2.description
   , t3.disc_load
   , CASE WHEN DATEPART(HH,@V2_WORK_DATE)> = 8 AND DATEPART(HH, @V2_WORK_DATE) <16 THEN 'S1'
         WHEN DATEPART(HH,@V2_WORK_DATE)> = 16 AND DATEPART(HH, @V2_WORK_DATE) <24  THEN 'S2'
         WHEN DATEPART(HH,@V2_WORK_DATE)> = 8 AND DATEPART(HH, @V2_WORK_DATE) <16 THEN 'S3' END AS Shift
    FROM ccostsitem t1
    INNER JOIN ccoscargotype t2 on t2.code = t1.cg_type_id
    INNER JOIN ccostallysheet t3 on t3.id = t1.master_id
    ) a
 WHERE Shift = @V3_SHIFT_NO

Upvotes: 1

Jacob H
Jacob H

Reputation: 2515

You want to SET your variable = to the CASE statement, not using AS. AS is an alias command and expects a column (in this case). This should work:

@shift = case when @V3_SHIFT_NO = 'S1' THEN DATEPART(HH,@V2_WORK_DATE)> = 8 AND DATEPART(HH, @V2_WORK_DATE) <16
              when @V3_SHIFT_NO = 'S2' THEN DATEPART(HH,@V2_WORK_DATE)> = 16 AND DATEPART(HH, @V2_WORK_DATE) <24
              when @V3_SHIFT_NO = 'S1' THEN DATEPART(HH,@V2_WORK_DATE)> = 8 AND DATEPART(HH, @V2_WORK_DATE) <16 end

edit: Actually, on second review, your query may need some additional help, but hopefully it gets you going in the right direction.

Upvotes: 0

Related Questions