HR Query Help - headcount by function

[yaman123]

Hi, 

I am creating a report for our HR Dept and i am having issues with a query i would like to display in a graph format. 

I have the below dataset i have created in Oracle SQL. You can see if there is a change to either the costctr, site or grade, the valid from and valid to dates are changed and a new line is added for each change.  E.g Employee A or Employee G

I would like to write a case statement to show what function each employee is in e.g if costctr value is 145 and site is not A then function 'central' etc. But because the report will be run by choosing a month from a date table, how would i write this so the correct data is displayed per month and each employee will display the correct function? 

Any help would be greatly appreciated! 

EMP_NO

INTERNAL_DISPLAY_NAME

DATE_OF_EMPLOYMENT

DATE_OF_LEAVING

PROPERTY_CODE1

COSTCTR_VALUE

COSTCTR_FROM

COSTCTR_TO

PROPERTY_CODE2

SITE_VALUE

SITE_FROM

SITE_TO

PROPERTY_CODE3

GRADE_VALUE

GRADE_FROM

GRADE_TO

300520

Employee A

01/10/2000

12/31/9999

COSTCTR

270

03/09/2019

31/12/9999

SITE

A

03/09/2019

31/12/9999

GRADE

L5

03/09/2019

31/03/2020

300520

Employee A

01/10/2000

12/31/9999

COSTCTR

270

03/09/2019

31/12/9999

SITE

A

03/09/2019

31/12/9999

GRADE

L4

01/04/2020

31/12/9999

500264

Employee B

14/02/1995

12/31/9999

COSTCTR

101

03/09/2019

31/12/9999

SITE

B

03/09/2019

31/12/9999

GRADE

3

03/09/2019

31/12/9999

500265

Employee C

18/08/2008

12/31/9999

COSTCTR

145

03/09/2019

31/12/9999

SITE

C

03/09/2019

31/12/9999

GRADE

L6a

03/09/2019

31/12/9999

500267

Employee D

27/05/2008

12/31/9999

COSTCTR

145

03/09/2019

31/12/9999

SITE

C

03/09/2019

31/12/9999

GRADE

L6

03/09/2019

31/12/9999

500316

Employee E

19/07/2010

12/31/9999

COSTCTR

145

03/09/2019

31/12/9999

SITE

B

03/09/2019

31/12/9999

GRADE

1

03/09/2019

05/01/2020

500316

Employee E

19/07/2010

12/31/9999

COSTCTR

145

03/09/2019

31/12/9999

SITE

B

03/09/2019

31/12/9999

GRADE

L6a

06/01/2020

31/12/9999

500317

Employee F

06/04/2009

12/31/9999

COSTCTR

101

03/09/2019

31/12/9999

SITE

B

03/09/2019

31/12/9999

GRADE

L6

03/09/2019

31/12/9999

510173

Employee G

28/02/2018

12/31/9999

COSTCTR

101

03/09/2019

31/12/9999

SITE

C1

03/09/2019

02/08/2020

GRADE

4

03/09/2019

02/08/2020

510173

Employee G

28/02/2018

12/31/9999

COSTCTR

101

03/09/2019

31/12/9999

SITE

C

03/08/2020

31/12/9999

GRADE

4

03/09/2019

02/08/2020

510173

Employee G

28/02/2018

12/31/9999

COSTCTR

101

03/09/2019

31/12/9999

SITE

C1

03/09/2019

02/08/2020

GRADE

L6a

03/08/2020

31/12/9999

510173

Employee G

28/02/2018

12/31/9999

COSTCTR

101

03/09/2019

31/12/9999

SITE

C

03/08/2020

31/12/9999

GRADE

L6a

03/08/2020

31/12/9999

[v-lionel-msft]

Hi @amitchandak ,

Please show me your expected output table.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Create the function column in the table. I think that would be the fastest in terms of query speed.

What also would be convenient is having a seqnr in your table (each row a unique number)

Next you need to know how tou want the report to act.

Do you want to report per day ? Or want to report per month ? So if the user selects a month he/she get the value which is valid on the first or last day of the month

If you know what you want you can create a table which stores:

Calender date - SeqNr

Now if you have the requirement of reporting daily you link your calender on calenderdate

if on last day of month you add a column to each calendar date with lastday and link on lastdayofmonth etc

[yaman123]

Hi Remyo, 

Thanks for getting back to my query. 

The report will be run monthly, so the last date of the month will be used. 

In terms of creating a function column, is this created in SQL or Power BI? 

How would i add seq numbers to a table? How would this work with the date selection? 

[amitchandak]

@yaman123 , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...