Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL PUZZLE | The Last Friday of the Month Puzzle | Advanced SQL

In this puzzle you have to read the input date from a variable and provide us the last friday of the month using SQL. For more details please see the sample input and expected output.

Sample Input

(No column name)
2010-07-10 00:00:00.000

Expected Output

Last-Friday of Month
2010-07-30 00:00:00.000

Script – DDL and INSERT Sample Data

--

DECLARE @StartDate AS DATETIME ='10-July-2010'
DECLARE @DayName AS VARCHAR(20) = 'Friday'

--

SOLUTION – 1

--

DECLARE @StartDate AS DATETIME ='10-July-2010'
DECLARE @DayName AS VARCHAR(20) = 'Friday'
SELECT DATEADD(d, -1 * ((DATEPART(dw,EOMONTH(@StartDate)) + 1) % 7), EOMONTH(@StartDate)) [Last-Friday of Month]

--

Output – 1

--

Last-Friday of Month
--------------------
2010-07-30

(1 row affected)


--

SOLUTION – 2

--

DECLARE @StartDate AS DATETIME ='10-July-2010'
DECLARE @DayName AS VARCHAR(20) = 'Friday'
;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number)  Number
    from
    SingleDigits as d1,
    SingleDigits as d2 
)
SELECT TOP 1 DATEADD(d,Number-1,@StartDate) [Last-Friday of Month] FROM Series 
WHERE DATEADD(d,Number-1,@StartDate) <= EOMONTH(@StartDate)
AND DATENAME(dw, DATEADD(d,Number-1,@StartDate)) = @DayName
ORDER BY Number DESC

--

Output – 2

--

Last-Friday of Month
-----------------------
2010-07-30 00:00:00.000

(1 row affected)


--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com