NO OF SATS AND SUNDAYS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

NO OF SATS AND SUNDAYS

Author

Topic

DBA007
Posting Yak Master

145 Posts

Posted - 2009-02-04 : 04:33:22

DEAR GURUS

CAN ANY ONE TELL ME HOW TO GET NUMBER OF SATURDAYS AND SUNDAYS IN A YEAR USING A QUERY OR A STOREDPROCEDURE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 09:29:58

[code]
DECLARE @year int
SET @year=2008--sample value
;With Date_CTE (DateVal)
AS
(
SELECT DATEADD(yy,@year-1900,0)
UNION ALL
SELECT DATEADD(dd,1,DateVal)
FROM Date_CTE
WHERE DATEADD(dd,1,DateVal)<DATEADD(yy,@year-1899,0)
)

SELECT SUM(CASE WHEN DATENAME(dw,DateVal) IN ('Saturday','Sunday') THEN 1 ELSE 0 END) AS WeekendCount,
SUM(CASE WHEN DATENAME(dw,DateVal) ='Saturday' THEN 1 ELSE 0 END) AS SatCount,
SUM(CASE WHEN DATENAME(dw,DateVal) ='Sunday' THEN 1 ELSE 0 END) AS SunCount
FROM Date_CTE

OPTION (MAXRECURSION 0)
[/code]

DBA007
Posting Yak Master

145 Posts

Posted - 2009-02-05 : 06:30:28

Dear Visakh Thanks for the Script.

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-06 : 08:11:45

No recursive CTE necessary:


CREATE PROCEDURE [dbo].[usp_GetWeekendDaysInYear]
(
	@year	int
)
AS
DECLARE	@sats	tinyint,
		@suns	tinyint;

SET DATEFIRST 7;  -- Sunday
SELECT @sats = 		
		CASE
			WHEN DATEPART(yyyy, DATEADD(wk, 52, DATEADD(dd, 7 - DATEPART(dw, DATEADD(yy, @year - DATEPART(yy, 0), 0)), DATEADD(yy, @year - DATEPART(yy, 0), 0)))) = @year THEN 53
			ELSE 52
		END
SET DATEFIRST 1;  -- Moday
SELECT @suns = 		CASE
			WHEN DATEPART(yyyy, DATEADD(wk, 52, DATEADD(dd, 7 - DATEPART(dw, DATEADD(yy, @year - DATEPART(yy, 0), 0)), DATEADD(yy, @year - DATEPART(yy, 0), 0)))) = @year THEN 53
			ELSE 52
		END

SELECT	@year AS [Year],
		@sats AS [Saturdays],
		@suns AS [Sundays];
GO 

EXEC [dbo].[usp_GetWeekendDaysInYear] @year = 2009
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources