topic Re: Calculate stoptime with shift in DAX in DAX Commands and Tips

Calculate stoptime with shift in DAX

As9124141636 — Wed, 15 Feb 2023 12:54:51 GMT

I have a table with 5 columns "InputTable", based on "Guideline Table" i want to generate new table named "ResultTable" with 3 columns.

Re: Calculate stoptime with shift in DAX

FreemanZ — Wed, 15 Feb 2023 13:49:05 GMT

hi As9124141636

can you explain the logic behind?

Re: Calculate stoptime with shift in DAX

tamerj1 — Thu, 16 Feb 2023 07:29:18 GMT

Hi As9124141636
Please refer to attached sample file with the proposed solution. You need to test the performance from your end. Perhaps simpler solutions are possible, however, for now this seems to work.

ResultTable = GROUPBY ( GENERATE ( SELECTCOLUMNS ( CROSSJOIN ( CALENDAR ( MIN ( InputTable[StartDate] ), MAX ( InputTable[FinishDate] ) ), GuidlineTable ), "Date", [Date], "Shift", [Shift], "From", [From] + [Date], "To", [To] + [Date] ), VAR T1 = FILTER ( InputTable, [StartDate] + [StartTime] < [To] && [FinishDate] + [FinishTime] > [From] ) RETURN SELECTCOLUMNS ( T1, "Start", [StartDate] + [StartTime], "Finish", [FinishDate] + [FinishTime] ) ), [Date], [Shift], "StopTime", SUMX ( CURRENTGROUP ( ), DATEDIFF ( MAX ( [From], [Start] ), MIN ( [To], [Finish] ), HOUR ) ) )

Re: Calculate stoptime with shift in DAX

As9124141636 — Fri, 17 Feb 2023 10:21:57 GMT

thanks for your exact solution. is it possible to do with TSQL in ssms?

Re: Calculate stoptime with shift in DAX

tamerj1 — Fri, 17 Feb 2023 10:31:14 GMT

As9124141636
Most probably yes but I'm no expert in TSQL. You can place a question in the desktop forum, I'm pretty sure many heroes can do beautiful TSQL over there.

Re: Calculate stoptime with shift in DAX

zfadaie — Sun, 19 Feb 2023 11:06:13 GMT

TSQL Solution:

Drop Table IF Exists #ResultTable

Create table #ResultTable ([Date] date)
Declare @StartResult datetime ,@EndResult datetime

select @StartResult = MIN(StartDate), @EndResult =Max(FinishDate)
from [dbo].[InputTable]

-- Create CALENDAR ( MIN ( InputTable[StartDate] ), MAX ( InputTable[FinishDate] )
While (@StartResult <> @EndResult)
Begin
insert into #ResultTable
select @StartResult
Set @StartResult = @StartResult+1
End
insert into #ResultTable
select @EndResult

--"Date", [Date],
-- "Shift", [Shift],
-- "From", [From] + [Date],
-- "To", [To] + [Date]
GO

With cte as (

select *,case when Shift=2 then DATEADD(day,1,[TOs]) else [Tos] End as [TO]

from (
select Date,Shift, CONVERT(DATETIME, CONVERT(CHAR(8), Date, 112)
+ ' ' + CONVERT(CHAR(8),[From], 108)) as [From] ,
CONVERT(DATETIME, CONVERT(CHAR(8), Date, 112)
+ ' ' + CONVERT(CHAR(8),[TO], 108)) as [TOs]
from #ResultTable cross join [dbo].[GuidlineTable] )T

)
-- VAR T1 = FILTER ( InputTable, [StartDate] + [StartTime] < [To] && [FinishDate] + [FinishTime] > [From] )
,cte2 as
(

select CONVERT(DATETIME, StartDate
+ ' ' + CONVERT(CHAR(8),StartTime, 108)) as Start ,
CONVERT(DATETIME,FinishDate
+ ' ' + CONVERT(CHAR(8),FinishTime, 108)) as Finish
from InputTable

)

select Date , Shift ,Sum(DATEDIFF(Hour,S,E) )as StopTime
from (
select

*, case when Start > = [From] Then Start else [From] End As S
,case when Finish <= [TO] then Finish else [TO] End as E
from (
select *

from cte cross join cte2
where Start < [TO] and Finish > [From]
)T)B

group by date ,Shift
order by date,Shift

-- "StopTime", SUMX ( CURRENTGROUP ( ), DATEDIFF ( MAX ( [From], [Start] ), MIN ( [To], [Finish] ), HOUR ) )