Using iif to mimic CASE for days of week

Question

I've hit a little snag with one of my queries. I'm throwing together a simple chart to plot a number of reports being submitted by day of week.

My query to start was :

  SELECT Weekday(incidentdate) AS dayOfWeek
, Count(*) AS NumberOfIncidents
   FROM Incident
    GROUP BY Weekday(incidentdate);

This works fine and returns what I want, something like

The problem is, I want the number returned by the weekday function to read the corresponding day of week, like case when 1 then 'sunday' and so forth. Since Access doesn;t have the SQL server equivalent that returns it as the word for the weekday, I have to work around.

Problem is, it's not coming out the way I want. So I wrote it using iif since I can't use CASE. The problem is, since each iif statement is treated like a column selection (the way I'm writing it), my data comes out unusable, like this

  SELECT 
 iif(weekday(incidentdate) =1,'Sunday'),
 iif(weekday(incidentdate) =2,'Monday')
 'so forth
, Count(*) AS NumberOfIncidents
  FROM tblIncident
   GROUP BY Weekday(incidentdate);



  Expr1000  Expr1001    count   

  Sunday              20
           Monday      106
                      120
                      186
                      182
                      164
                       24

Of course, I want my weekdays to be in the same column as the original query. Halp pls

Create a lookup table, which matches each of the 7 numeric values to text names, and join on it. — MatBailie, Jul 17 '13 at 20:04

score 4 · Accepted Answer · edited Jul 17 '13 at 22:22

4

Use the WeekdayName() function.

SELECT
    WeekdayName(Weekday(incidentdate)) AS dayOfWeek,
    Count(*) AS NumberOfIncidents
FROM Incident
GROUP BY WeekdayName(Weekday(incidentdate));

edited Jul 17 '13 at 22:22

Andy G

19,232
5
47
69

answered Jul 17 '13 at 20:10

HansUp

95,961
11
77
135

............-_- I don't know why they don't list that on the weekday documentation page – Scotch Jul 17 '13 at 20:14

score 2 · Answer 2 · edited Jul 17 '13 at 20:19

As BWS Suggested, Switch was what I wanted. Here's what I ended up writing

SELECT 
 switch(
 Weekday(incidentdate) = 1, 'Sunday',
 Weekday(incidentdate) = 2,'Monday',
 Weekday(incidentdate) = 3,'Tuesday',
 Weekday(incidentdate) = 4,'Wednesday',
 Weekday(incidentdate) = 5,'Thursday',
 Weekday(incidentdate) = 6,'Friday',
 Weekday(incidentdate) = 7,'Saturday'
 ) as DayOfWeek
 , Count(*) AS NumberOfIncidents
FROM tblIncident
GROUP BY Weekday(incidentdate);

Posting this here so there's actual code for future readers

Edit: WeekdayName(weekday(yourdate)) as HansUp said it probably a little easier :)

Good, clean example of a Switch statement. Definitely usable for other CASE scenarios. — William M-B, Jul 17 '13 at 20:20

score 1 · Answer 3 · edited May 23 '17 at 12:29

1

check this previous post:

What is the equivalent of Select Case in Access SQL?

edited May 23 '17 at 12:29

Community

1
1

answered Jul 17 '13 at 20:06

BWS

3,786
18
25

`Switch` Worked. Thanks – Scotch Jul 17 '13 at 20:11

score 0 · Answer 4 · answered Jul 17 '13 at 20:11

0

Why not just create a 7 row table with day number & day name then just join to it?

answered Jul 17 '13 at 20:11

LoztInSpace

5,584
1
15
27

Using iif to mimic CASE for days of week

4 Answers4