Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
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.
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
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?
@yaman123 , refer if this blog can help
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
71 |