Category Archives: sql

Silver Pockets Full –

Yesterday, I came across Chet Justice’s blog post on the phenomenon of “Silver Pockets Full”. Interesting!!!
It claims that – a month with 5 Fridays and 5 Saturdays and 5 Sundays – occurs once in every 823 years.

So, the problem statement was to find out the correctness of this supposition. And, also to find out the next months which satisfies this criteria.

I believe, it can be done through any language (technical, of course) be it – SQL or Java or PERL or Python or etc. I prefer SQL and got a query ready.

Let’s try it out.

with t1 as
(
	select trunc(sysdate,'yyyy')+level-1 lvl
	from dual
	connect by level <= 50000
)
---
, t2 as
(
	select 
		to_char(lvl,'yyyymm') mx, 
		sum(decode(to_char(lvl,'dy'),'sun',1,'sat',1,'fri',1,0)) count_x
	from t1
	group by to_char(lvl,'yyyymm')
)
---
select to_char(to_date(mx,'yyyymm'),'yyyy-Month') yyyy_Month
from t2
where count_x = 15
order by mx;

Output:

YYYY_Month
------------
2014-August
2015-May
2016-January
2016-July
2017-December
2019-March
2020-May
2021-January
2021-October
2022-July
2023-December
2024-March
2025-August
2026-May
2027-January
2027-October
2028-December

... rest removed for brevity

138 rows selected