CASE Statement

Question

Post reply

CASE Statement

thunter 5669

SSC-Addicted

Points: 428
More actions
July 3, 2019 at 12:49 pm

#3659740

I am a SQL novice. Our SQL expert is unavailable and I need to make some small changes to a stored procedure, any help would be appreciated.

So I have a stored procedure that pulls the data into an Excel spreadsheet. Here is what the output looks like:
What I am trying to accomplish is when the % is 0 or the Incentive is blank, I want the Pay column to be a 0. Attached is the query for the stored procedure. At the end of the code, you will see the CASE statements for the columns. Thank you!!
Attachments:
You must be logged in to view attached files.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Andrey SSC Eights! Points: 840 More actions · Answer 1

Please, attach the query in TXT file .

Office files can be with viruses or macros .

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Andrey wrote:

Please, attach the query in TXT file . Office files can be with viruses or macros .

Better still, just paste it into a post (using the Insert/edit code sample button).

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

thunter 5669 SSC-Addicted Points: 428 More actions · Answer 3

USE [TMWSUITE]

GO

/****** Object: StoredProcedure [dbo].[wynne_incentive] Script Date: 7/2/2019 1:30:43 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[wynne_incentive] @datein1 varchar(10), @datein2 varchar(10)

as

with cte_drivers(id,nm,de,yr,py,st,se,ty,tr)

as

(select mpp_id, mpp_lastfirst,

(Case when mpp_hiredate > mpp_senioritydate

then mpp_hiredate

else mpp_senioritydate

End),

(Case when mpp_hiredate > mpp_senioritydate

then cast(datediff(DAY, mpp_hiredate, @datein1) / (365.23076923074) as int)

else cast(datediff(DAY, mpp_senioritydate, @datein1) / (365.23076923074) as int)

End),

SUM(p.pyd_amount),

mpp_status,

(Case when mpp_hiredate > mpp_senioritydate

then cast(datediff(DAY, mpp_hiredate, @datein1) as int)

Else cast(datediff(DAY, mpp_senioritydate, @datein1) as int)

End),

--cast(datediff(DAY, mpp_senioritydate, @datein1) / (365.23076923074) as int)

(select EMPLOYMENTTYPE from [WYN].dbo.upr00100

where EMPLOYID = mpp_id),

case

when mpp_status = 'OUT' then mpp_terminationdt

else null

end

from manpowerprofile m inner join paydetail p on m.mpp_id = p.asgn_id

and (select h.pyh_issuedate from payheader h where p.pyh_number = h.pyh_pyhnumber) between @datein1 and @datein2

and p.pyd_pretax = 'Y'

and pyt_itemcode not in('CVAC', 'CHOLID', 'CGRNT', 'CDISB', 'CDISAB', 'CINSPL', 'CRECRT')

where mpp_type1 = 'CD' --and mpp_status <> 'OUT'

--and cast(datediff(DAY, mpp_senioritydate, @datein1) / (365.23076923074) as int) >= 1

--and(select EMPLOYMENTTYPE from suite1.wyn.dbo.upr00100

--where EMPLOYID = mpp_id) <> 3

group by mpp_id, mpp_lastfirst,mpp_senioritydate, mpp_hiredate, mpp_status, mpp_terminationdt

)--select * from cte_drivers order by 1 exec wynne_incentive '01/01/19','03/31/19'

select id,nm,de,yr,

py,

case

when se < 1 or ty = 3 or yr = 0 --or st = 'OUT'

then 0

when yr >= 3

then round(py*.03,2)

else round(py*.02,2)

end,

case

when st = 'OUT' or se < 1 or ty = 3 or yr = 0 then 0

when yr >= 3

then .03

else .02

end,

case

when st = 'OUT' then 'N - Term'

when se < 1 then 'N - Date'

when ty = 3 then 'N - Part Time'

when yr = 0 then 'N - Years'

else 'Y' end,

tr

from cte_drivers

order by 1

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

Wow, you refused to format your code, even though I mentioned how to do it! For the benefit of others, here it is again.

USE TMWSUITE;
GO

/****** Object:  StoredProcedure [dbo].[wynne_incentive]    Script Date: 7/2/2019 1:30:43 PM ******/

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

ALTER PROC dbo.wynne_incentive
    @datein1 VARCHAR(10)
   ,@datein2 VARCHAR(10) AS;
WITH cte_drivers (id, nm, de, yr, py, st, se, ty, tr)
AS (SELECT mpp_id
          ,mpp_lastfirst
          ,(CASE
                WHEN mpp_hiredate > mpp_senioritydate THEN
                    mpp_hiredate
                ELSE
                    mpp_senioritydate
            END
           )
          ,(CASE
                WHEN mpp_hiredate > mpp_senioritydate THEN
                    CAST(DATEDIFF(DAY, mpp_hiredate, @datein1) / (365.23076923074) AS INT)
                ELSE
                    CAST(DATEDIFF(DAY, mpp_senioritydate, @datein1) / (365.23076923074) AS INT)
            END
           )
          ,SUM(p.pyd_amount)
          ,mpp_status
          ,(CASE
                WHEN mpp_hiredate > mpp_senioritydate THEN
                    CAST(DATEDIFF(DAY, mpp_hiredate, @datein1) AS INT)
                ELSE
                    CAST(DATEDIFF(DAY, mpp_senioritydate, @datein1)
          AS
          INT)
            END
           )
          --cast(datediff(DAY, mpp_senioritydate, @datein1) / (365.23076923074) as int)
          ,(
               SELECT EMPLOYMENTTYPE FROM WYN.dbo.upr00100 WHERE EMPLOYID = mpp_id
           )
          ,CASE
               WHEN mpp_status = 'OUT' THEN
                   mpp_terminationdt
               ELSE
                   NULL
           END
    FROM manpowerprofile m
        INNER JOIN paydetail p
            ON m.mpp_id = p.asgn_id
               AND
               (
                   SELECT h.pyh_issuedate
                   FROM payheader h
                   WHERE p.pyh_number = h.pyh_pyhnumber
               )
               BETWEEN @datein1 AND @datein2
               AND p.pyd_pretax = 'Y'
               AND pyt_itemcode NOT IN ('CVAC', 'CHOLID', 'CGRNT', 'CDISB', 'CDISAB', 'CINSPL', 'CRECRT')
    WHERE mpp_type1 = 'CD' --and mpp_status <> 'OUT'

    --and cast(datediff(DAY, mpp_senioritydate, @datein1) / (365.23076923074) as int) >= 1

    --and(select EMPLOYMENTTYPE from suite1.wyn.dbo.upr00100

    --where EMPLOYID = mpp_id) <> 3
    GROUP BY mpp_id
            ,mpp_lastfirst
            ,mpp_senioritydate
            ,mpp_hiredate
            ,mpp_status
            ,mpp_terminationdt) --select * from cte_drivers order by 1 exec wynne_incentive '01/01/19','03/31/19'
SELECT id
      ,nm
      ,de
      ,yr
      ,py
      ,CASE
           WHEN se < 1
                OR ty = 3
                OR yr = 0 --or st = 'OUT'
      THEN
               0
           WHEN yr >= 3 THEN
               ROUND(py * .03, 2)
           ELSE
               ROUND(py * .02, 2)
       END
      ,CASE
           WHEN st = 'OUT'
                OR se < 1
                OR ty = 3
                OR yr = 0 THEN
               0
           WHEN yr >= 3 THEN
               .03
           ELSE
               .02
       END
      ,CASE
           WHEN st = 'OUT' THEN
               'N - Term'
           WHEN se < 1 THEN
               'N - Date'
           WHEN ty = 3 THEN
               'N - Part Time'
           WHEN yr = 0 THEN
               'N - Years'
           ELSE
               'Y'
       END
      ,tr
FROM cte_drivers
ORDER BY 1;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

thunter 5669 SSC-Addicted Points: 428 More actions · Answer 5

thunter 5669

SSC-Addicted

Points: 428

July 3, 2019 at 1:55 pm

#3659760

Sorry I missed that step, my apologies.

Andrey SSC Eights! Points: 840 More actions · Answer 6

try this one:

SELECT id
,nm
,de
,yr
--,py   /*pay old ?*/
,CASE  /* pay new ?*/
WHEN st = 'OUT'
OR se < 1
OR ty = 3
OR yr = 0
THEN 0
ELSE py
END [py]
,CASE  /*incentive ?*/
WHEN se < 1
OR ty = 3
OR yr = 0 --or st = 'OUT'
THEN 0
WHEN yr >= 3
THEN round(py * .03, 2)
ELSE round(py * .02, 2)
END
,CASE  /* % ?*/
WHEN st = 'OUT'
OR se < 1
OR ty = 3
OR yr = 0
THEN 0
WHEN yr >= 3
THEN .03
ELSE .02
END
,CASE   /*eligible?*/
WHEN st = 'OUT'
THEN 'N - Term'
WHEN se < 1
THEN 'N - Date'
WHEN ty = 3
THEN 'N - Part Time'
WHEN yr = 0
THEN 'N - Years'
ELSE 'Y'
END
,tr
FROM cte_drivers
ORDER BY 1

thunter 5669 SSC-Addicted Points: 428 More actions · Answer 7

I tried and get error

Msg 102, Level 15, State 1, Procedure wynne_incentive, Line 45 [Batch Start Line 7]
Incorrect syntax near ';'.

thunter 5669 SSC-Addicted Points: 428 More actions · Answer 8

I figured out the syntax errors, now let me run it and see if it works. Thank you!!!

thunter 5669 SSC-Addicted Points: 428 More actions · Answer 9

Looking good. However, I need the Pay column to show zero instead of null, so it can total at the bottom.

Andrey SSC Eights! Points: 840 More actions · Answer 10

thunter 5669 wrote:

Looking good. However, I need the Pay column to show zero instead of null, so it can total at the bottom.

let's go step-by-step:

does the query deliver expected result without NULLs but with zeroes?
if the query is fine, then it's Excel which shows '-' instead of '0' and it's a formatting issue (conditional?)

thunter 5669 SSC-Addicted Points: 428 More actions · Answer 11

Yes, I fixed the Excel formatting and now it looks fine. Thank you for all your help, I appreciate it.

CASE Statement

Attachments: