Counting mondays - 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

General SQL Server Forums

New to SQL Server Programming

Counting mondays

Author

Topic

ejnot
Starting Member

8 Posts

Posted - 2012-06-21 : 09:17:53

Hi,
I am trying to count how many mondays there are between today and just another date.
I have a calendar where I want this to be an attribute like this:

Week_No Date Day_No_In_Week count_Mondays
52 20120101 7 3
1 20120102 1 2
1 20120103 2 2
1 20120104 3 2
1 20120105 4 2
1 20120106 5 2
1 20120107 6 2
1 20120108 7 2
2 20120109 1 1
2 20120110 2 1
2 20120111 3 1
2 20120112 4 1
2 20120113 5 1
2 20120114 6 1
2 20120115 7 1
3 20120116 1 0 today!
3 20120117 2 0
3 20120118 3 0
3 20120119 4 0
3 20120120 5 0
3 20120121 6 0
3 20120122 7 0
4 20120123 1 1
4 20120124 2 1
4 20120125 3 1
4 20120126 4 1
4 20120127 5 1
4 20120128 6 1
4 20120129 7 1
5 20120130 1 2

Let's say that 16.1.2012 is today. and I want to know how many modays there are between this date and any other date I have in my calendar.

My SQL is like this:
select d.[Date], WF
from Calendar d
join (
select count(Day_No_In_Week) as WF from Calendar d1
where [Day_No_In_Week] = '1'
and Date between d.Date and (select Date from DIM_TIME where [Date] = ,getdate(),112))
) WFL
on d.Date = WFL.Date
where d.Date between '20120101' and '20120130'
group by d.[Date]

which does not work, because the subquery cannot read the main table.

Can anyone help me to solve this? I would be very happy! :)

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-06-21 : 09:38:34

you need to create a UDF for this.
here is the code:
CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)
SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END
RETURN (@rtDayofWeek)
END
GO

then write your sql statement tto count monday
eg:select count(dbo.udf_DayOfWeek(birthdate)) as dayweek from HumanResources.Employee where dbo.udf_DayOfWeek(birthdate)='Monday';

ejnot
Starting Member

8 Posts

Posted - 2012-06-21 : 09:40:46

Thank you, I'll try it out.

ejnot
Starting Member

8 Posts

Posted - 2012-06-22 : 09:09:49

Hi,
your SQL did not give me what I was looking for, but it gave me an idea of what to do.

Here is my answer:

CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate date )

RETURNS varchar(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)
DECLARE @today VARCHAR(10)

select @rtDayofWeek = count([Day_No_In_Week])
from Calendar
where [Date] between getdate() and @today
and [Day_No_In_Week] = 7

RETURN (@rtDayofWeek)
END

--Then I can write this statement:
select Date, dbo.udf_DayOfWeek(Date) as dayweek
from Calendar
where Date between '20120101' and getdate()
order by Date

ejnot
Starting Member

8 Posts

Posted - 2012-06-22 : 09:13:17

with minor corrections:

quote:
Originally posted by ejnot

Hi,
your SQL did not give me what I was looking for, but it gave me an idea of what to do.

Here is my answer:

CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate date )

RETURNS varchar(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)

select @rtDayofWeek = count(datepart(d,date))
from Calendar
where [Date] between getdate() and @today
and datepart(d,date) = 7

RETURN (@rtDayofWeek)
END

--Then I can write this statement:
select Date, dbo.udf_DayOfWeek(Date) as dayweek
from Calendar
where Date between '20120101' and getdate()
order by Date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources