Are you referring to this function?

https://www.sqltopia.com/algorithms/date-and-time/get-the-nth-weekday-of-any-interval/

I can't see that it should take 4 seconds to run over a million date interval?

As Jeff states, there are so many things that prevent you from doing a simple ALTER TABLE ALTER COLUMN.

I have recently put my sp_AlterColumn on GitHub. I have used...

My goal was to demonstrate you don't need two cross apply.

SELECT          SUBSTRING(CONVERT(CHAR(9), DATEADD(MONTH, DATEDIFF(MONTH, 0, t.thedate), 0), 6),...

• This reply was modified 4 years ago by  SwePeso.

WITH cteDates(this, limit)
AS (
        SELECT  MIN(Startdate),
             ...

I use Google...

https://weblogs.sqlteam.com/peterl/2009/07/12/how-to-calculate-the-number-of-weekdays-for-any-given/

-- swePeso
SELECTAccountNumber,
STRING_AGG(Value, ',') WITHIN GROUP (ORDER BY Value)
FROM#TestData
GROUP BYAccountNumber;

Be very careful with LAG here. If you for some reason don't have data for the previous measure period, you will get the measure period before that.

That's why why need...

The bahaviour is decoumented here

https://msdn.microsoft.com/en-us/library/ms190309.aspx

-- SwePeso

WITH cteData

AS (

SELECTDESPATCH_ID,

SAMPLE_ID,

SUBSTRING(SAMPLE_ID, PATINDEX('%[0-9]%', SAMPLE_ID), LEN(SAMPLE_ID)) - ROW_NUMBER() OVER (PARTITION BY DESPATCH_ID ORDER BY SAMPLE_ID) AS grp

FROMdbo.SAMPLE_TABLE

)

SELECTDESPATCH_ID,

MIN(SAMPLE_ID) AS FromID,

MAX(SAMPLE_ID) AS ToID

FROMcteData

GROUP BYDESPATCH_ID,

grp

ORDER BYDESPATCH_ID,

grp;

-- SwePeso - Preaggregation/filtering step

SELECTSetID,

COUNT(*) AS cnt,

MIN(ItemCode) AS mn,

MAX(ItemCode) AS mx,

CHECKSUM_AGG(CHECKSUM(ItemCode)) AS chk

INTO#Temp

FROMdbo.SetsItems WITH (NOLOCK)

GROUP BYSetID;

-- SwePeso - projection step

SELECTROW_NUMBER() OVER (ORDER BY w.chk) AS UniqueSetID,

STUFF(f.Data, 1, 2, N'') AS...

This is a simple case of Relational Division, of which I am particular fond of.

The result isSetIDSetID

110

211

78

79

89By using this piece of codeSELECTkc.SetID,

nc.SetID

FROM(

SELECTSetID,

COUNT(*) AS cnt

FROMdbo.SetsItems

GROUP BYSetID

) AS kc

INNER JOIN(

SELECTSetID,

COUNT(*) AS cnt

FROMdbo.SetsItems

GROUP...

Dankort too.

Thank you for taking the time (pun intended) to test the different solutions.

DECLARE@Sample TABLE

(

s1 DATETIME,

f1 DATETIME,

s2 DATETIME,

f2 DATETIME

);

INSERT@Sample

(

s1,

f1,

s2,

f2

)

VALUES('2014-08-13 07:00', '2014-08-13 12:00', '2014-08-13 10:00', '2014-08-13 19:00'),

('2014-08-13 07:00', '2014-08-13 12:00', '2014-08-13 09:00', '2014-08-13 10:00'),

('2014-08-13 07:00', '2014-08-13 12:00', '2014-08-13 13:00', '2014-08-13 19:00');

-- SwePeso

SELECTs.*,

x.*

FROM@Sample AS s

CROSS...

Did you wrap the code as an inline table-valued function?

CREATE FUNCTION dbo.OverlapHours

(

@s1 DATETIME,

@f1 DATETIME,

@s2 DATETIME,

@f2 DATETIME

)

RETURNS TABLE

AS

RETURN(

SELECTCASE

WHEN MAX(FromTime) < MIN(ToTime) THEN DATEDIFF(HOUR, MAX(FromTime), MIN(ToTime))

ELSE 0

END AS [Hours]

FROM(

VALUES(@s1, @f1),

(@s2, @f2)

) AS...