Calculate stoptime with shift in DAX

[As9124141636]

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

[tamerj1 · Accepted solution]

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 ) )
)

[tamerj1 · Accepted solution]

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 ) )
)

[As9124141636]

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

[tamerj1]

@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.

[zfadaie · Accepted solution]

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 ) )

[FreemanZ]

hi @As9124141636 

can you explain the logic behind?