SQLTeam.com | Weblogs | Forums

Splitting overlapping dates to calculate MTD,YTD


#1

Hi Everyone,

Its been awhile since I posted and I am having a real brain buster :cry: 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

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 :grin:

--
Digi


#2

I don't really understand what you are trying to do.

Testable data (CREATE TABLE and INSERT statements) with multiple sample rows and expected results would be much more useful than a text description of what you want.


#3

Hi Scott,

Thank you soo much for replying

sample table + data

CREATE TABLE OVERLAP
(
EQUIPMENT VARCHAR(20)
,STARTTIME DATETIME
,ENDTIME DATETIME
,DURATION BIGINT

)
INSERT INTO @OVERLAP(EQUIPMENT,STARTTIME, ENDTIME, DURATION) VALUES
('Equip1', '08-01-2016 18:28:40.000', '9-25-2016 14:10:29.733', 1331734),
('Equip1', '9-26-2016 02:32:56.243', :'10-04-2016 04:05:44.047', 1245896),
('Equip2', '10-01-2016 09:54:13.813', '10-10-2016 17:10:01.567', 25468),
('Equip3', '9-29-2016 00:10:13.713', 10-15-2016 04:55:10.000', 785412),
('Equip4', '7-20-2016 18:35:02.567', '10-11-2016 09:12:28.690'. 1332548)

desired result for MTD of october for example
would split Equip1's time 10-1-2016 -10-04-2016 to be included in the MTD:


#4

Here is how you can 'create' your own min/max function across columns:

       min_value = (Select min(Value)
                      From (
                    Values (column1)
                         , (column2)
                         , (column3)
                         , (column4)
                           ) As min(Value))

       max_value = (Select max(Value)
                      From (
                    Values (column1)
                         , (column2)
                         , (column3)
                         , (column4)
                           ) As max(Value))

Using this - you can avoid the UDF altogether which will be a performance killer. Just set the column values to the specific columns from the table that you want compared to return the min/max value.

You also need to review your WHERE clause. If you are trying to insure the dates you have are within a specific range then don't use the functions on the columns - it should just be:

AND EXP.STR_STARTTIME_UTC >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE(), 0)
AND EXP.STR_ENDTIME_UTC <= GETDATE()

If you don't want data up to the current moment in time - change the GETDATE() to CAST(GETDATE() AS DATE) and the less than or equal to just less than:

AND EXP.STR_ENDTIME_UTC < CAST(GETDATE() AS DATE)

This will be more consistent and give you everything up to yesterday without including today's data which will change depending on what time you collect the data.


#5

I modified the data slightly so that a date went past Oct, just to verify that the cut-off date logic was working. I left the result as total seconds duration to allow for easier testing, since after testing converting to a different duration format (days, hrs, mins, secs) should be trivial.

TRUNCATE TABLE OVERLAP
INSERT INTO OVERLAP(EQUIPMENT,STARTTIME, ENDTIME, DURATION) VALUES
('Equip1', '08-01-2016 18:28:40.000', '9-25-2016 14:10:29.733', 1331734),
('Equip1', '9-26-2016 02:32:56.243', '10-04-2016 04:05:44.047', 1245896),
('Equip2', '10-01-2016 09:54:13.813', '10-10-2016 17:10:01.567', 25468),
('Equip3', '9-29-2016 00:10:13.713', '10-15-2016 04:55:10.000', 785412),
('Equip4', '7-20-2016 18:35:02.567', '11-11-2016 09:12:28.690', 1332548)

DECLARE @Start_Date datetime
DECLARE @End_Date datetime

SET @Start_Date = '20160901' --include Sep to see the diff between YTD and MTD
SET @End_Date = '20161031'

SELECT EQUIPMENT,
    SUM(DATEDIFF(SECOND, CASE WHEN STARTTIME < Start_Date THEN Start_Date ELSE STARTTIME END,
        CASE WHEN ENDTIME > End_Date THEN End_Date ELSE ENDTIME END)) AS YTD_Duration_Secs,
    SUM(CASE WHEN ENDTIME < Current_Month_Start THEN 0 ELSE 
        DATEDIFF(SECOND, CASE WHEN STARTTIME < Start_Date THEN Start_Date ELSE STARTTIME END,
        CASE WHEN ENDTIME > End_Date THEN End_Date ELSE ENDTIME END) END) AS MTD_Duration_Secs
FROM OVERLAP
CROSS JOIN (
    /*strip times from @Start_Date and @End_Date, to be sure, and add 1 day to @End_Date to allow <*/
    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @Start_Date), 0) AS Start_Date, 
        DATEADD(DAY, DATEDIFF(DAY, 0, @End_Date) + 1, 0) AS End_Date,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS Current_Month_Start
) AS assign_alias_names_to_control_values
WHERE STARTTIME <= End_Date AND ENDTIME >= Start_Date
GROUP BY EQUIPMENT

#6

Thank you truly amazing !


#7

You're welcome! I just don't have time to create the sample data myself (boring too!), but writing the query is fun.


#8

Hi Scott,

I just tested your code I just need to implement a few more changes

MTD needs to be sort of static that displays the current month only with no ability to change to the parameters
YTD static as well

is it possible to just use
@start_date and @end_date as another column so that the user can pull which ever values between them

with a MTD figure ..
Equip1 has a starttime '2016-09-29 23:03:49:360' endtime '2016-10-04 10:24:26:297'
and a duration of '386437' seconds

how would you be able to split the 10-01-2016 to 10-04-2016 from the September so that we could get the MTD..

im soo confused :sweat:


#9

Just set the @Start_Date and @End_Date so that they will always be Jan 1 and the end of the current month:

SET @Start_Date = DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
SET @End_Date = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))

#10

I ran the start and end dates from 11-1-2016 to 11-3-2016
it seems that some rows are pulling correct date but others seem to be pulling YTD values

any thoughts on this ? :worried:


#11

I don't know what you mean. What you've shown means nothing to me -- how could I tell your MTD data from your YTD data? I don't see your original data.

My best guess is that the particular equipment or whatever didn't exist until that month, so the MTD and YTD are the same.


#12

your right sorry about that Scott that has nothing to do with the data I provided

would it be possible to add WTD really having trouble doing this

DECLARE @Start_Date DATETIME
DECLARE @End_Date DATETIME
DECLARE @WEnd_Date DATETIME

SET @Start_Date = DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
SET @End_Date = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))
SET @WEnd_Date = DATEADD(DAY, -1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()) + 1, 0))

SELECT
OEE.[LNG_EQUIPMENT_ID] 'LNG_EQUIPMENT_ID'
,OEE.[STR_EQUIPMENT_ID] 'STR_EQUIPMENT_ID'
,OEE.STR_EQUIPMENT_LABEL 'EQUIPMENT_LABEL'

	,SUM(CASE WHEN OEE.STR_STARTTIME_UTC >= DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, '11-1-2016'), 0))
	AND OEE.STR_ENDTIME_UTC <= DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, '11-3-2016')+1,0)) THEN OEE.DURATION ELSE 0 END) AS STARTTOENDTOTAL

	,SUM(DATEDIFF(SECOND,	CASE WHEN OEE.STR_STARTTIME_UTC < Start_Date THEN Start_Date ELSE OEE.STR_STARTTIME_UTC END,
	CASE WHEN OEE.STR_ENDTIME_UTC > End_Date THEN End_Date ELSE OEE.STR_ENDTIME_UTC END)) AS YTD

	,SUM(	CASE WHEN OEE.STR_ENDTIME_UTC < Current_Month THEN 0 ELSE
	DATEDIFF(SECOND, CASE WHEN OEE.STR_STARTTIME_UTC < Start_Date THEN Start_Date ELSE OEE.STR_STARTTIME_UTC END,
	CASE WHEN OEE.STR_ENDTIME_UTC > End_Date THEN End_Date ELSE OEE.STR_ENDTIME_UTC END) END) AS MTD
	
	,SUM( CASE WHEN OEE.STR_ENDTIME_UTC < Current_Week THEN 0 ELSE	--NEW ADDITION *WTD*				
	DATEDIFF(SECOND, CASE WHEN OEE.STR_STARTTIME_UTC < Start_Date THEN Start_Date ELSE OEE.STR_STARTTIME_UTC END,
	CASE WHEN OEE.STR_ENDTIME_UTC > End_Date THEN End_Date ELSE OEE.STR_ENDTIME_UTC END) END) AS WTD	

FROM dbo.OVERLAP OEE
CROSS JOIN (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @Start_Date), 0) AS Start_Date,
DATEADD(DAY, DATEDIFF(DAY, 0, @End_Date) + 1, 0) AS End_Date,
DATEADD(DAY, DATEDIFF(DAY, 0, @WEnd_Date) + 1, 0) AS WEnd_Date,
DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0) AS Current_Week, --NEW ADDITION
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS Current_Month,
DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()),0) AS Current_Year
) AS Control_Values

WHERE
OEE.STR_REASON1 = 'Running'
AND OEE.STR_LINE_LABEL = 'Production Line'
AND OEE.STR_STARTTIME_UTC <= End_Date AND OEE.STR_ENDTIME_UTC >= Start_Date

GROUP BY
OEE.LNG_EQUIPMENT_ID,
OEE.STR_EQUIPMENT_ID,
OEE.STR_EQUIPMENT_LABEL