0

I would like to exclude the weekend in year 2016, but I need the weekend in year 2015 for my calculations. I did used this expression in my query:

((DATEPART(dw, IntervalDate) + @@DATEFIRST) % 7) NOT IN (0,1)

But this excludes in every year the weekend. How can I only exclude Saturday, Sunday in 2016?

I use SSRS, and I have been thinking to exclude the weekends for the year 2016 in the filter option, but it takes a lot of time and maybe it is possible to do it in the query.

M.JAY
  • 183
  • 2
  • 11
  • Which DBMS are you using? –  Jun 23 '16 at 10:13
  • That's not a DBMS, that's a SQL client tool. But as the only DBMS it can connect to is SQL Server it's safe to assume that you are using SQL Server –  Jun 23 '16 at 10:21
  • 1
    One approach is to add a [calendar table](http://www.sqlservercentral.com/blogs/dwainsql/2014/03/30/calendar-tables-in-t-sql/). Within this table you could add a bit field to control which days are/are not in scope of your reports. – David Rushton Jun 23 '16 at 10:32

3 Answers3

0

Try this:

(DATEPART(year, IntervalDate) <> 2016) OR ((DATEPART(dw, IntervalDate) + @@DATEFIRST) % 7) NOT IN (0,1)
Robert Kock
  • 5,795
  • 1
  • 12
  • 20
0

Presumably you mean you want to restrict your result set by excluding weekends in 2016?

This code will do that for you:

WHERE YEAR(IntervalDate) <> 2016 OR (YEAR(IntervalDate) = 2016 AND DATEPART(dw,IntervalDate) > 1 AND DATEPART(dw,IntervalDate) < 7)

(you can you use YEAR or DATEPART)

But you must first understand which day of the week corresponds to 1. Take a look at the SET DATEFIRST command.

BIDeveloper
  • 2,628
  • 4
  • 36
  • 51
  • Thank You, but If I use this expression then a failure appears: `Timeout`. – M.JAY Jun 28 '16 at 08:47
  • I used a chart in SSRS: ` IntervalDateWeek >= getdate() - 350` But if I use it for a short term the report displays also an error. – M.JAY Jun 28 '16 at 08:53
  • @M.JAY the SQL I offered will solve your problem If you are getting a timeout then this is because the SQL query is taking too long to return data. Ignore SSRS for now, and run your SQL on SQL Server and check it is correct. – BIDeveloper Jun 28 '16 at 08:57
0

A calendar table greatly simplifies queries like this one. A big advantage is that correct queries are obviously correct.

select *
from calendar
where (year_of_date = 2015)
   or (year_of_date = 2016 and day_of_week not in ('Sat', 'Sun'))
order by cal_date;

I've posted DDL for a calendar table in PostgreSQL.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • I can't see how that makes things easier. You're doing the same where clause (basically) as the other two answers but you've introduced the complexity (and therefore also a performance hit) of a further table. Am I missing something? – BIDeveloper Jun 23 '16 at 11:51
  • 1
    @BIDeveloper: a) A WHERE clause based on a calendar table is so much more readable that even financial auditors can see that it's *obviously* right. b) A join on a calendar table is usually faster--about twice as fast on my system--because of indexes on its columns. – Mike Sherrill 'Cat Recall' Jun 24 '16 at 11:07
  • So I haven't missed anything then :-) – BIDeveloper Jun 24 '16 at 11:21