CODEWITHSUNDEEP
sql-server
Chrislaar123
Chrislaar123

Reputation: 321

Creating a value field by counting weeks values

I have a query that returns the following information 

ID  Employees  wk1   wk2   wk3   wk4 
1      10       2     7     4     5
2      15       5     5     5     5

I want to add another field to this query which works out Weeksemployees. It would do this by looking at the Employees column and counting how many wk's that figure could fulfill. Example is shown below;

ID  Employees WeeksEmployees  wk1   wk2   wk3   wk4 
1      10            2          2     7     4     5
2      15            3          5     5     5     5

If there's any way that I could achieve this then it would be massively helpful.

Upvotes: 0

Views: 61

Answers (3)

Midhun Siva
Midhun Siva

Reputation: 35

            CREATE TABLE SAMPLE1
            (
             ID         INT IDENTITY
            ,EMPLOYEES  INT
            ,WK1        INT
            ,WK2        INT
            ,WK3        INT
            ,WK4        INT
            )

            INSERT INTO SAMPLE1 VALUES(10,2,7,4,5)
            INSERT INTO SAMPLE1 VALUES(15,5,5,5,5)
            INSERT INTO SAMPLE1 VALUES(12,15,5,5,5)
            INSERT INTO SAMPLE1 VALUES(14,3,4,7,5)
            INSERT INTO SAMPLE1 VALUES(20,5,5,5,5)


            SELECT ID,EMPLOYEES,WK1,WK2,WK3,WK4
                   ,CASE
                       WHEN WK1>EMPLOYEES THEN 0 
                       WHEN WK1+WK2>EMPLOYEES THEN 1
                       WHEN WK1+WK2+WK3>EMPLOYEES THEN 2
                       WHEN WK1+WK2+WK3+WK4>EMPLOYEES THEN 3
                       ELSE 4
                    END AS WEEKSEMPLOYEES
            FROM SAMPLE1

Upvotes: 0

Tristan
Tristan

Reputation: 1024

This will calculate the figures as you asked for.

create table #demo (id int, employees int, wk1 int, wk2 int, wk3 int, wk4 int)
insert #demo values
(1,10,2,7,4,5),
(2,15,5,5,5,5)

select *, case
when wk1 + wk2 + wk3 + wk4 <= employees then 4
when wk1 + wk2 + wk3 <= employees then 3
when wk1 + wk2 <= employees then 2
when wk1 <= employees then 1 else 0 end as WeeksEmployees
from #demo

I can image you might want to be able to have a larger number of weeks and since you are in IT I assume you dont want to type it all out ;) If so please let me know the requirements and i'll see if there is a pivot or dynamic solution that fits.

Upvotes: 1

Millstone1998
Millstone1998

Reputation: 170

You could do something like the following, but if you are not always wanting to start at week 1 then you'll need to repeat exactly what your criteria are.

DECLARE @EmployeeWeekData TABLE
   (
     ID INT
   , Employees INT
   , wk1 INT
   , wk2 INT
   , wk3 INT
   , wk4 INT
   )
INSERT   INTO @EmployeeWeekData
VALUES   ( 1, 10, 2, 7, 4, 5 )
,        ( 2, 15, 5, 5, 5, 5 )

SELECT   *
       , CASE WHEN Employees - ( wk1 + wk2 + wk3 + wk4 ) >= 0 THEN 4
              WHEN Employees - ( wk1 + wk2 + wk3 ) >= 0 THEN 3
              WHEN Employees - ( wk1 + wk2 ) >= 0 THEN 2
              WHEN Employees - ( wk1 ) >= 0 THEN 1
              ELSE 0
         END AS WeeksFulfilled
FROM     @EmployeeWeekData ewd

Kind Regards, CarrieAnne

Upvotes: 1

Related Questions