SQL to Find number of saturday and sunday in the current month
SELECT
TO_CHAR(c_date,'DAY') day,
COUNT(*)
FROM
(
SELECT trunc(SYSDATE,'MONTH') + ( level - 1 ) c_date
FROM dual
CONNECT BY level <= last_day(trunc(SYSDATE) ) - trunc(SYSDATE,'MONTH') + 1
)
WHERE
TO_CHAR(c_date,'DY') IN ('SAT','SUN')
GROUP BY
TO_CHAR(c_date,'DAY');
WITH c_date as (SELECT trunc(SYSDATE,'MONTH') + ( level - 1 ) d
FROM dual
CONNECT BY level <= last_day(trunc(SYSDATE) ) - trunc(SYSDATE,'MONTH') + 1)
SELECT TO_CHAR(c_date.d,'DAY') day,COUNT(*)
FROM c_date
WHERE
TO_CHAR(c_date.d,'DY') IN ('SAT','SUN')
GROUP BY
TO_CHAR(c_date.d,'DAY');
SELECT
TO_CHAR(c_date,'DAY') day,
COUNT(*)
FROM
(
SELECT trunc(SYSDATE,'MONTH') + ( level - 1 ) c_date
FROM dual
CONNECT BY level <= last_day(trunc(SYSDATE) ) - trunc(SYSDATE,'MONTH') + 1
)
WHERE
TO_CHAR(c_date,'DY') IN ('SAT','SUN')
GROUP BY
TO_CHAR(c_date,'DAY');
WITH c_date as (SELECT trunc(SYSDATE,'MONTH') + ( level - 1 ) d
FROM dual
CONNECT BY level <= last_day(trunc(SYSDATE) ) - trunc(SYSDATE,'MONTH') + 1)
SELECT TO_CHAR(c_date.d,'DAY') day,COUNT(*)
FROM c_date
WHERE
TO_CHAR(c_date.d,'DY') IN ('SAT','SUN')
GROUP BY
TO_CHAR(c_date.d,'DAY');
-- Query to find the number of saturday and sunday between two given dates
SELECT
TO_CHAR(c_date,'DAY') day,
COUNT(*)
FROM
(SELECT trunc(to_date('01/01/2019','DD/MM/YYYY')) + ( level - 1 ) c_date
FROM dual
CONNECT BY level <= trunc(to_date('31/12/2019','DD/MM/YYYY')) - trunc(to_date('01/01/2019','DD/MM/YYYY')) + 1
)
WHERE TO_CHAR(c_date,'DY') IN ('SAT','SUN')
GROUP BY
TO_CHAR(c_date,'DAY');
Great Siva...Many Thanks..this is very helpful to SQL guys.
ReplyDeleteAwesome delivery of the concepts
ReplyDeleteWell Explanation Shiva..
ReplyDeleteOr we can also use
ReplyDeleteSELECT
TO_CHAR(c_date,'DAY') day,
COUNT(*)
FROM
(
SELECT trunc(SYSDATE,'MONTH') + ( level - 1 ) c_date
FROM dual
CONNECT BY level <= extract(day from last_day(trunc(SYSDATE))
)
WHERE
TO_CHAR(c_date,'DY') IN ('SAT','SUN')
GROUP BY
TO_CHAR(c_date,'DAY');
Your are really helping guys like us who are working on SQL, PLSQL..keep it up..thank you
ReplyDeleteThank you
ReplyDeleteNicely explained sir
ReplyDeleteThanks a lot Siva, this series is very helpful for me!!
ReplyDeleteif you want to just check the occurrence of any particular day in given date ..
ReplyDeleteselect sum(decode(to_char(trunc(sysdate,'mm')+level-1,'day'),'saturday ',1)) days from dual connect by level<=to_char(last_day(sysdate),'dd');
great post and the best pte training institute in ernakulam
ReplyDeleteeach and every tutorial is amazing! you are doing GREAT. Thank You.
ReplyDelete