Skip to main content
To homepage To homepage
Solved

splitting up time

K
Starter

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

Best answer by nmo

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:

 

View original
Did this topic help you find an answer to your question?

Christian Hauggaard
Community Manager
Forum|alt.badge.img+5

Hi @Kristoffer 

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

fwagner
K
2 people like this
  • fwagner
    fwagner
  • K
    Kristoffer
K
Starter

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
Contributor
Forum|alt.badge.img+1
  • nmo
  • Contributor
  • August 14, 2023

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:

 

Christian Hauggaard
K
2 people like this
  • Christian Hauggaard
    Christian Hauggaard
  • K
    Kristoffer
rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • rory.smith
  • TimeXtender Xpert
  • August 14, 2023

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.

 

K
1 person likes this
  • K
    Kristoffer
Christian Hauggaard
Community Manager
Forum|alt.badge.img+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
Starter

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


Most helpful members this week

Terms and ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings