May 6, 2018 at 11:07 am
Comments posted to this topic are about the item Friday the 13th
May 7, 2018 at 12:02 am
The value of
n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
can be simplified to
n = a.n * 10 + b.n
Which is faster, cleaner and less overhead on server
May 7, 2018 at 8:28 am
ibraheem.alkilanny - Monday, May 7, 2018 12:02 AMThe value ofn = ROW_NUMBER() OVER (ORDER BY (SELECT null))can be simplified to
n = a.n * 10 + b.nWhich is faster, cleaner and less overhead on server
Actually, it should be like this and start with zero.
SELECT n = a.n + (b.n *10)
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n) -- 10 rows
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)) b(n)
May 7, 2018 at 8:34 am
Luis Cazares - Monday, May 7, 2018 8:28 AMibraheem.alkilanny - Monday, May 7, 2018 12:02 AMThe value ofn = ROW_NUMBER() OVER (ORDER BY (SELECT null))can be simplified to
n = a.n * 10 + b.nWhich is faster, cleaner and less overhead on server
Actually, it should be like this and start with zero.
SELECT n = a.n + (b.n *10)
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n) -- 10 rows
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)) b(n)
It works on either way you want to phrase it.
SELECT n = a.n * 12 + b.n
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n) -- 10 rows
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) b(n)
ORDER BY 1
May 7, 2018 at 11:53 am
Couple of shorter solutions:
--Solution 1
SET DATEFIRST 1
DECLARE @d DATE = '2016-05-13 00:00:00.000'--GETDATE()
WHILE (@d < '2099/01/01')
BEGIN
IF DATEPART(dw,DATEFROMPARTS(YEAR(@d), MONTH(@d), 13)) = 5
BEGIN
SELECT @d AS Friday13
END
SET @d = DATEADD(m,1,@d)
END
--Solution 2
SELECT n+12
FROM
(SELECT DATEADD(M,ROW_NUMBER() OVER (ORDER BY id)-1,'20160101')n FROM sys.syscolumns)N WHERE DATEPART(dw, n)=7
--Solution 3
DECLARE @d1 DATETIME=42501
WHILE @d1<=73049
BEGIN
SET @d1+=7
IF(DAY(@d1)=13)
SELECT @d1
END
We had a running gag who can post Friday 13th T-SQL in a tweet (meaning max 140 chars). More on this blogpost: https://tomaztsql.wordpress.com/2016/05/13/friday-13th-with-t-sql-a-shortest-competition/
Best, Tomaž
Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog: https://tomaztsql.wordpress.com/
May 8, 2018 at 8:32 am
Luis Cazares - Monday, May 7, 2018 8:34 AMLuis Cazares - Monday, May 7, 2018 8:28 AMibraheem.alkilanny - Monday, May 7, 2018 12:02 AMThe value ofn = ROW_NUMBER() OVER (ORDER BY (SELECT null))can be simplified to
n = a.n * 10 + b.nWhich is faster, cleaner and less overhead on server
Actually, it should be like this and start with zero.
SELECT n = a.n + (b.n *10)
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n) -- 10 rows
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)) b(n)It works on either way you want to phrase it.
SELECT n = a.n * 12 + b.n
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n) -- 10 rows
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) b(n)
ORDER BY 1
Thanks guys, I would never ever had thought of doing it that way. But yeah, one never stops learning.
May 8, 2018 at 9:01 am
tomaz.kastrun - Monday, May 7, 2018 11:53 AMCouple of shorter solutions:
--Solution 1
SET DATEFIRST 1
DECLARE @d DATE = '2016-05-13 00:00:00.000'--GETDATE()
WHILE (@d < '2099/01/01')
BEGIN
IF DATEPART(dw,DATEFROMPARTS(YEAR(@d), MONTH(@d), 13)) = 5
BEGIN
SELECT @d AS Friday13
END
SET @d = DATEADD(m,1,@d)
END--Solution 2
SELECT n+12
FROM
(SELECT DATEADD(M,ROW_NUMBER() OVER (ORDER BY id)-1,'20160101')n FROM sys.syscolumns)N WHERE DATEPART(dw, n)=7--Solution 3
DECLARE @d1 DATETIME=42501
WHILE @d1<=73049
BEGIN
SET @d1+=7
IF(DAY(@d1)=13)
SELECT @d1
ENDWe had a running gag who can post Friday 13th T-SQL in a tweet (meaning max 140 chars). More on this blogpost: https://tomaztsql.wordpress.com/2016/05/13/friday-13th-with-t-sql-a-shortest-competition/
Best, Tomaž
All valid solutions, Tomaz. But the whole point was about how to do this without introducing RBAR ( Row By Agonizing Row) logic to it. Just a clean set based solution to it.
May 9, 2018 at 12:00 pm
A fun challenge! Here's a solution using a recursive cte:
DECLARE @FirstMonth DATE = GETDATE();
;WITH [cte] AS (
SELECT DATEFROMPARTS(DATEPART(YEAR,@FirstMonth), DATEPART(MONTH,@FirstMonth), 13) AS [d] -- the 13th for our first month
UNION ALL
SELECT DATEADD(MONTH,1,[d]) FROM [cte] -- a new row for every subsequent month
)
SELECT TOP(10) [d]
FROM [cte]
WHERE DATENAME(WEEKDAY,[d]) = 'Friday';
May 9, 2018 at 8:03 pm
sgriffin-681047 - Wednesday, May 9, 2018 12:00 PMA fun challenge! Here's a solution using a recursive cte:DECLARE @FirstMonth DATE = GETDATE();
;WITH [cte] AS (
SELECT DATEFROMPARTS(DATEPART(YEAR,@FirstMonth), DATEPART(MONTH,@FirstMonth), 13) AS [d] -- the 13th for our first month
UNION ALL
SELECT DATEADD(MONTH,1,[d]) FROM [cte] -- a new row for every subsequent month
)
SELECT TOP(10) [d]
FROM [cte]
WHERE DATENAME(WEEKDAY,[d]) = 'Friday';
I agree with Jan Van der Eecken above about set based solutions. Recursive CTEs (rCTE) qualify as RBAR when assigned the task of counting and can actually be beaten by a well formed WHILE loop in a transaction. Of course, set based "Pseudo Cursors" (like many of those posted already) blow both out of the water. And, no... I'm not prone to justifying the use of RBAR just because there's a low rowcount. The following article demonstrates the nature of rCTEs that increment and how bad they actually are even for low row counts.
Hidden RBAR: Counting with Recursive CTE's
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply