Hi Everyone,
Its been awhile since I posted and I am having a real brain buster 3 days straight and still no solution
I am trying to get values(which are based in seconds) for MTD the problem is some start and end dates will overlap
MTD (October)
ex; StartDate 9-22-2016 EndDate 10-03-2016
browsing countless pages I found this sample to calculate the time between overlapping days
max(0, min(EndDate1, EndDate2) - max(StartDate1, StartDate2))
problem is T-SQL max/min function only takes 1 parameter
so I created 2 UDF max and min to take 2 parameters
example MAX UDF
function dbo.UDFexample(@x datetime, @y datetime)
returns datetime
as
begin
return case when @x >@y then @y else @x end
end
function dbo.UDFexample(@x datetime, @y datetime)
returns datetime
as
begin
return case when @x >@y then @y else @x end
end
The UDF_FORMAT_TO_DATE function just converts the seconds to "HH:MM:SS" format
SELECT
EXP.[LNG_EQUIPMENT_ID] 'LNG_EQUIPMENT_ID'
,EXP.[STR_EQUIPMENT_ID] 'STR_EQUIPMENT_ID'
,EXP.STR_EQUIPMENT_LABEL 'EQUIPMENT_LABEL'
,DBO.UDF_FORMAT_TO_DATE(SUM(datediff(SECOND, DBO.MAT24965_MAX(0, DBO.MAT24965_MIN(GETDATE(), EXP.[STR_ENDTIME_UTC])) -
DBO.MAT24965_MAX(EXP.STR_STARTTIME_UTC, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)), '01-01-2016'))) AS MTD
,DBO.UDF_FORMAT_TO_DATE(SUM(datediff(SECOND, DBO.MAT24965_MAX(0, DBO.MAT24965_MIN(GETDATE(), EXP.[STR_ENDTIME_UTC])) -
DBO.MAT24965_MAX(EXP.STR_STARTTIME_UTC, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()),0)), '01-01-2016'))) AS YTD
,DBO.UDF_FORMAT_TO_DATE(SUM(datediff(SECOND, DBO.MAT24965_MAX(0, DBO.MAT24965_MIN('10-09-2016', EXP.[STR_ENDTIME_UTC])) -
DBO.MAT24965_MAX(EXP.STR_STARTTIME_UTC, '09-20-2016'), '01-01-2016')))
AS STARTTOENDTOTAL
FROM [dbo].[EXPtimes] EXP
WHERE
AND DATEADD(dd, DATEDIFF(dd, 0, EXP.STR_STARTTIME_UTC), 0) >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()),0)
AND DATEADD(dd, DATEDIFF(dd, 0, EXP.STR_ENDTIME_UTC), 0) <= GETDATE()
GROUP BY
EXP.LNG_EQUIPMENT_ID,
EXP.STR_EQUIPMENT_ID,
EXP.STR_EQUIPMENT_LABEL
I am getting an error
"the datedff function resulted in an overflow. the number of dateparts separating two/datetime instances is too large"
Please let me know if you need more detail
To whoever replies
Thanks in advance you will save me a ton of stress and hair
--
Digi