CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012sql-server-2008-r2
user1326379
user1326379

Reputation: 170

Multiple records in one row

I have a table EmpLunch like below:

EmployeeId    BusinessDate    PunchIn   Lunch1Start   Lunch1End Lunch2Start Lunch2End   PunchOut
101            10/12/2017     9:00 AM   1:00 PM       1:30 PM                           5:00 PM
101            10/13/2017     9:00 AM   1:00 PM       1:28 PM                           5:00 PM
101            10/14/2017     9:00 AM   1:00 PM       1:28 PM    3:00 PM    3:28 PM     5:00 PM
101            10/15/2017     9:00 AM                                                   5:00 PM

I need to populate the data in one additional column ErrorCode based on following logic:

Error Code =
1 = Lunch 1 Not Taken
2 = Lunch 1 Less Than 30 Minutes
3 = Lunch 1 Started 300 Minutes Past IN Punch
4 = Lunch 2 Not Taken
5 = Lunch 2 Less Than 30 Minutes

The resultant table sould be like:

EmployeeId    BusinessDate    PunchIn   Lunch1Start   Lunch1End Lunch2Start Lunch2End   PunchOut    ErrorCode
101            10/12/2017     9:00 AM   1:00 PM       1:30 PM                           5:00 PM     4
101            10/13/2017     9:00 AM   1:00 PM       1:28 PM                           5:00 PM     2, 4
101            10/14/2017     9:00 AM   1:00 PM       1:28 PM    3:00 PM    3:28 PM     5:00 PM     2, 5
101            10/15/2017     9:00 AM                                                   5:00 PM     1

I have written a simple code like below:

Select *, 
CASE WHEN Lunch1Start IS NULL THEN '1'
WHEN DATEDIFF(MINUTE, Lunch1Start, Lunch1End) < 30.0 THEN '2'
WHEN DATEDIFF(MINUTE, LunchEnd1, PunchIn) < 300.0 THEN '3'
WHEN Lunch2Start IS NULL THEN '4'
WHEN DATEDIFF(MINUTE, Lunch2End, LunchI2Start) < 30.0 THEN '5'
END LunchError
From EmpLunch

The above query is not giving multiple records in a cell (like record for 10/13 and 10/14 in above resultant table). My query is giving out just the first value in the column. Please suggest a way to achieve the goal (comma separated multiple values in column).

Upvotes: 2

Views: 140

Answers (3)

Lee
Lee

Reputation: 189

Mine works:

    DROP TABLE IF EXISTS category --SQL 2016/17
    go
    CREATE TABLE Category
    (id int IDENTITY (1,1) NOT NULL Primary Key
    ,Punchin time
    ,lunch1Start time 
    ,lunch1End time
    ,lunch2Start time
    ,lunch2End time
    ,Punchout time
    ,LunchError as CASE when lunch1start is null then '1' WHEN DATEDIFF(MINUTE,lunch1start,lunch1end) < 30 THEN '2' 
        WHEN DATEDIFF(MINUTE,lunch1end, punchin) < 300 THEN '3'  
        WHEN lunch2start is null then '4' end
        +   ', '+  CASE WHEN lunch2start  is null then '4' WHEN DATEDIFF(MINUTE,lunch2start,lunch2end) < 30 THEN '5' 
        WHEN DATEDIFF(MINUTE,lunch2start,lunch2end) < 300 THEN '3' 
        END  

    )

    INSERT category(punchin, lunch1Start, lunch1End, Punchout) 
    VALUES ('9:00 am','1:00 pm', '1:30 pm', '5:00 pm')

    INSERT category(punchin, lunch1Start, lunch1End, Punchout) 
    VALUES('9:00 am','1:00 pm', '1:28 pm', '5:00 pm')

    INSERT category(punchin, lunch1Start, lunch1End, lunch2Start, lunch2End, Punchout) 
    VALUES ('9:00 am','1:00 pm', '1:28 pm', '3:00 pm', '3:28','5:00 pm')

    INSERT category(punchin,  Punchout) 
    VALUES ('9:00 am','5:00 pm')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can get the separate values using separate case expressions concatenated together. Here is one method:

select el.*,
       stuff( (case when Lunch1Start is null then ', 1' else '' end) +
              (case when datediff(minute, Lunch1Start, Lunch1End) < 30 then ', 2' else '' end) +
              (case when datediff(minute, LunchEnd1, PunchIn) < 300 then ', 3' else '' end) +
              (case when Lunch2Start IS NULL then ', 4' else '' end) +
              (case when datediff(minute, Lunch2End, LunchI2Start) < 30 then ', 5' else '' end), 1, 2, '')
from EmpLunch el;

The stuff() removes the separators. Also note that this version returns an empty string -- rather than NULL -- if there are no errors. If you want NULL, then use nullif():

select el.*,
       nullif(stuff( (case when Lunch1Start is null then ', 1' else '' end) +
                     (case when datediff(minute, Lunch1Start, Lunch1End) < 30 then ', 2' else '' end) +
                     (case when datediff(minute, LunchEnd1, PunchIn) < 300 then ', 3' else '' end) +
                     (case when Lunch2Start IS NULL then ', 4' else '' end) +
                     (case when datediff(minute, Lunch2End, LunchI2Start) < 30 then ', 5' else '' end), 1, 2, ''), '')
from EmpLunch el;

Note that datediff() returns an integer. It also may not do exactly what you want, because it counts "unit boundaries" between values. I would use this logic instead:

select el.*,
       stuff( (case when Lunch1Start is null then ', 1' else '' end) +
              (case when Lunch1Start < dateadd(minute, -30, Lunch1End) then ', 2' else '' end) +
              (case when LunchEnd1 < dateadd(minute, -300) < 300 then ', 3' else '' end) +
              (case when Lunch2Start IS NULL then ', 4' else '' end) +
              (case when Lunch2End < dateadd(minute, -30, LunchI2Start) then ', 5' else '' end), 1, 2, '')
from EmpLunch el;

Upvotes: 3

uzi
uzi

Reputation: 4146

You just need to concat result of three differenct cases. First for error codes 1 and 2, second for error code 3 and third for codes 4 and 5. Like this:

Select 
    *,  STUFF(CASE WHEN Lunch1Start IS NULL THEN ', 1' WHEN DATEDIFF(MINUTE, Lunch1Start, Lunch1End) < 30.0 THEN ', 2' ELSE '' END
    + CASE WHEN DATEDIFF(MINUTE, LunchEnd1, PunchIn) < 300.0 THEN ', 3' ELSE '' END
    + CASE WHEN Lunch2Start IS NULL THEN ', 4' WHEN DATEDIFF(MINUTE, Lunch2End, LunchI2Start) < 30.0 THEN ', 5' ELSE '' END,1,2,'') LunchError
From EmpLunch

Upvotes: 3

Related Questions