Removing saturday and sunday from ssrs 2008 calendar report

I used the following tutorial to create a calendar based report in SSRS. It is working but not exactly to what my requirements are,

My DBMS for scheduling ensures no tasks are added to Saturday's and Sunday's but I am not sure how to reflect that via this report the way it is currently built

Because of the way the query is built, right now tasks that aren't actually scheduled on a Saturday or Sunday are displaying as if they are because I haven't found  a way to filter out those days. I am hoping someone can tell me how to accomplish this.

http://rduclos.wordpress.com/2010/02/13/ssrs-2008-generate-calendars-based-on-a-date-range/

Thanks for any help!
J CAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Code below (also) works regardless of any date or language settings:

WHERE
    DATEDIFF(DAY, 0, d.[Date]) % 7 NOT IN (5, 6)
0
 
ValentinoVBI ConsultantCommented:
Ideally you'd have a Date dimension that contains a column to indicate which day of the week the record represents.  In that case you could just use a WHERE clause to filter out the weekend days.

Another option is to use either the DATEPART or DATENAME function.  Note that the result of DATEPART is influenced by the DATEFIRST setting, as following example indicates:

select DATEPART(dw, getdate())
select DATENAME(dw, getdate())

set datefirst 3;

select DATEPART(dw, getdate())
select DATENAME(dw, getdate())

Open in new window

0
 
Easwaran ParamasivamCommented:
Include below highlighted where clause to filter saturday and sunday. HTH.


SELECT
 DisplayOnCalendar = DENSE_RANK() OVER (ORDER BY d.Year, d.Month),
 d.Month,
 [Day] = DATEPART(DAY,d.[Date]),
 d.Year,
 [WeekDay] = DATEPART(WEEKDAY, d.[Date]),
 [Order] = DENSE_RANK() OVER (PARTITION BY d.Year, d.Month ORDER BY d.Date),
 d.Date,
 ebd.FullName
FROM
 Dates d
 LEFT JOIN EmployeeBirthDay ebd ON ebd.Month = DATEPART(MONTH,d.[Date]) AND ebd.Day = DATEPART(DAY,d.[Date])
WHERE ((DATEPART(dw, d.[Date]) + @@DATEFIRST) % 7) NOT IN (0, 1)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.