0

I had some issue while populating some data as per my requirements and that is as follows..

Table data:

---------------------------------------------
EMPID  PROJ_TYPE  HOURS  ST_DATE    ED_DATE
---------------------------------------------
1      DEV        10     2013-1-1   2013-5-1
1      DEV        34     2013-5-2   2013-8-1
1      DEV        23     2013-8-2   2013-10-1
1      TEST       12     2014-1-1   2014-3-1
1      TEST       25     2014-3-2   2014-6-1

Here I want to fetch the employee data with hours, So as simple I wrote

SELECT EMPID,
       PROJ_TYPE, 
       SUM(CASE WHEN PROJ_TYPE='DEV' THEN HOURS ELSE 0 END) DEV, 
       SUM(CASE WHEN PROJ_TYPE='TEST' THEN HOURS ELSE 0 END) TEST
FROM TABLE1
GROUP BY EMPID, PROJ_TYPE

Hence it is returning two rows as expected but here the twist I want only one row per employee. If employee have multiple PROJ_TYPE then it should combine both project values and return only one row.

Finally my result set should look like:

----------------------------
EMPID  PROJ     DEV  TEST
----------------------------
1     DEV/TEST  67   37

Hope I am clear please comment if any doubt on my question.

  • please add a dbfiddle so we can try our code: dbfiddle.uk – DDS May 24 '18 at 14:46
1

A pivot will work for this

Test Data:

Declare @Test Table
(
 EMPID          Int
,PROJ_TYPE      NVarchar(4)
,HOURS          Int
,ST_DATE        Date
,ED_DATE        Date
);
Insert Into @Test
(EMPID, PROJ_TYPE, HOURS, ST_DATE, ED_DATE)
Values ('1', 'DEV','10','2013-1-1','2013-5-1')
        ,('1', 'DEV','34','2013-5-2','2013-8-1')
        ,('1', 'DEV','23','2013-8-2','2013-10-1')
        ,('1', 'TEST','12','2014-1-1','2014-3-1')
        ,('1', 'TEST','25','2014-3-2','2014-6-1')
        ,('2', 'DEV','7','2013-5-2','2013-8-1')
        ,('2', 'DEV','14','2013-8-2','2013-10-1')
        ,('2', 'TEST','12','2014-1-1','2014-3-1')
        ,('2', 'TEST','19','2014-3-2','2014-6-1')
        ,('3', 'DEV','34','2013-5-2','2013-8-1')
        ,('3', 'DEV','8','2013-8-2','2013-10-1')
        ,('3', 'TEST','12','2014-1-1','2014-3-1')
        ,('3', 'TEST','7','2014-3-2','2014-6-1')
        ,('3', 'DEV','23','2013-8-2','2013-10-1')
        ,('3', 'TEST','18','2014-1-1','2014-3-1')
        ,('4', 'TEST','25','2014-3-2','2014-6-1');

Query:

Select      EMPID
            ,IsNull(DEV,0) As DEV
            ,IsNull(TEST,0) As TEST
From        (
            Select      EMPID
                        ,PROJ_TYPE
                        ,HOURS
            From        @Test
            ) sq
    Pivot   (
            Sum(HOURS)
            For PROJ_TYPE In ([DEV],[TEST])
            ) piv;

SQL Fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=6d9a0ceb9dbf13e8af351822733c4607

0

Just don't group by project type (nor select it without a grouping function)

SELECT EMPID,         
       TOTAL_HOURS = SUM(HOURS), -- Just for cross-check
       PROJ = CASE WHEN SELECT (COUNT (DISTINCT PROJ_TYPE) 
                        FROM TABLE1 E 
                        WHERE E.EMPID = EE.EMPID 
                        GROUP BY E.EMPID) > 1 
                   THEN 'DEV/TEST' 
                   ELSE SELECT (DISTINCT PROJ_TYPE 
                                FROM TABLE1 TE 
                                WHERE TE.EMPID = EE.EMPID 
                                GROUP BY E.EMPID) 
              END,
     SUM(CASE WHEN PROJ_TYPE='DEV' THEN HOURS ELSE 0 END) AS DEV, 
     SUM(CASE WHEN PROJ_TYPE='TEST' THEN HOURS ELSE 0 END) AS TEST
FROM TABLE1 EE
GROUP BY EMPID

It's not elegant but should work

0

Maybe this:

    with cte as (select EMPID,PROJ_TYPE, SUM(CASE WHEN PROJ_TYPE='DEV' THEN HOURS ELSE 0 END) DEV, 
    SUM(CASE WHEN PROJ_TYPE='TEST' THEN HOURS ELSE 0 END) TEST
    FROM TABLE1
    GROUP BY EMPID, PROJ_TYPE)

    select t1.empID, isnull(t1.proj,'') + '/' + isnull(t2.proj,'') as proj, t1.hours, t2.hours)
    from cte t1
    full outer join cte t2 on t1.empID = t2.empID
    where isnull(t1.proj,'Dev') = 'Dev'
    and isnull(t2.proj, 'Test') = 'Test'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.