Hours calculation per day for an employee

[#1]

My table looks like the following:

Employee_id	Date	Hours
1	1/1/2016	3
1	1/1/2016	4
1	1/3/2016	5
1	1/4/2016	6
2	1/1/2016	2
2	1/2/2016	3
2	1/3/2016	4
2	1/4/2016	5

I want to find the employees total worked hours for each day..I have used the following expression,

     Load Employee_id,Sum(Hours) as Worked_Hours Group By Employee_id

Its grouping all the hours of each employee for every date but my requirement is to show the result for a employee's total hours worked per day.Based on this I want to generate a chart by below calculation,

    if(Hours<4),'Available',if(Hours>4 and Hours<7),'Partially Occupied',if(Hours>8),'Fully Occupied',Unknown).

I don't want to use date picker functionality. How could I get this?

[varshavig12]

Exp1:

Label:  Hrs

aggr(sum(Hours),Date,Employee_id)

Exp2:

if(Hrs<4,'Available',

if(Hrs>4 and Hrs<7,'Partially Occupied',

if(Hrs>8,'Fully Occupied','Unknown')))

[varshavig12]

Or in script:

a:

LOAD * INLINE [

    Employee_id, Date, Hours

    1, 1/1/2016, 3

    1, 1/1/2016, 4

    1, 1/3/2016, 5

    1, 1/4/2016, 6

    2, 1/1/2016, 2

    2, 1/2/2016, 3

    2, 1/3/2016, 4

    2, 1/4/2016, 5

];

NoConcatenate

Load Employee_id, Date,

sum(Hours) as Hrs

Resident a

Group by Date,Employee_id;

drop table a;

[jonathandienst]

Load the data like this:

Load

  Employee_id,

  Date,

  Worked_Hours,

  If(Worked_Hours < 4, 'Available',

  If(Worked_Hours < 7, 'Partially Occupied', 'Fully Occupied')) as WorkState

;

Load

  Employee_id,

  Date,

  Sum(Hours) as Worked_Hours

From ....

  Group By Employee_id, Date

;

Now use WorkState as a dimension.

(if the dates are strings, then load the dates like so to convert to proper date values:

Load

  ...

  Date(Date#(Date, 'M/d/yyyy')) as Date,

  ...

[surendraj]

Hi,

  while making the aggregations we don't apply on the same base table.instead,take a resident table to  make aggregation and groupby on both date and employee id fields.by considering the grouping it will add the sum of hours.

try it,hope it works.

--surendra