To homepage To homepage
Solved

splitting up time

  • 14 August 2023
  • 6 replies
  • 50 views
K
Rank

I have a table with dates of employees when they start and end work each day. Some days the start of work day is not the same as the end of the work day. Example : employee 2 start work 07-08-2023 end work 09-08-2023. This means that the employee is sick in the period. I have a condition that set this employees work time to 7.5 hours. I have to split up the dates where start_date <> end_date. So that i in the interval can add 7.5 hours to the employee each day he was sick. How can i do this?

 

This is a screenshot where the dates are different. Sorry about the language “mekaniker” is the employee and “Antaltimer_Frokost” means being sick

 

The end result i want is to get 7.5 for each day, so at the first i would like a row for each sicks day . So for the last employee “95012” there would be three rows with each of the day the employee has been sick. Hope its clear what i want

icon

Best answer by nmo 14 August 2023, 12:56

View original

6 replies

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @Kristoffer 

Perhaps you could take (date_end - date_start)*7.5 ?

K
Rank

Forgot to say that i need a row for each of the days, i have tried to make a self lookup but i cant really get it to work

N
Rank

Hi Kristoffer

If you create a date table you can build the logic in a custom view such as this:

Of course assuming you are able to convert your numeric date values to a date/datetime format

output:

 

rory.smith
Rank
Userlevel 5
Badge +7

Hi,

that means you need to “blow up” the table, i.e. add records for each day. See: https://legacysupport.timextender.com/hc/en-us/articles/115001394043-Exploding-tables-using-views .So you should build a Custom View that joins your data to a calender table and uses Window Functions to fill the 7.5 hours for sick days between the start and end date.See: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16 for the documentation for Window Functions, Stackoverflow has many good examples.

 

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @Kristoffer 

As proposed by @nmo, you can use a custom view similar to below

If one of these answers solves your issue, then please help us by marking a best answer

K
Rank

Hi @Christian Hauggaard i will mark a anwser, right now i am tryin each of the ideas out. But thank you for all the help 

Reply


Terms and ConditionsCookie settings