When the IIF() Function is Your Friend. Part 2.

Following on from my previous post, which you can read here, on the incredibly useful IIF() function, I have been working on an example of nested IIF() functions. This inserts an IIF() function within another, with another, within another ad-infinitum.

As an example let’s imagine that what I’m after is, the week that a specific date falls in, within a given year. The first week NOT starting on January 1st, but rather the first Monday of January. This can be thought as the difference between the date and the first Monday of January divided by seven. We must then take the value returned and round up (or take the ceiling).

As example we’ll generate a table with the following 11 fields…

1. CCTV Survey ID
2. Survey Date
3. Year of Survey
4. Day on 1st
5. Day on 2nd
6. Day on 3rd
7. Day on 4th
8. Day on 5th
9. Day on 6th
10. Day on 7th
11. Week Of the Year Starting On 1st Monday Of the Year

… to do this we of course use a SELECT statement. The first 10 fields are easy enough:

Initial SELECT Query

Initial SELECT Query

SELECT

oid AS ‘CCTV Survey ID’,

when_surveyed AS ‘Survey Date’,

YEARPART(when_surveyed) AS ‘Year of Survey’,

DAYNAME(’01/01/’ + YEARPART(when_surveyed)) AS ‘Day on 1st’,
DAYNAME(’02/01/’ + YEARPART(when_surveyed)) AS ‘Day on 2nd’,
DAYNAME(’03/01/’ + YEARPART(when_surveyed)) AS ‘Day on 3rd’,
DAYNAME(’04/01/’ + YEARPART(when_surveyed)) AS ‘Day on 4th’,
DAYNAME(’05/01/’ + YEARPART(when_surveyed)) AS ‘Day on 5th’,
DAYNAME(’06/01/’ + YEARPART(when_surveyed)) AS ‘Day on 6th’,
DAYNAME(’07/01/’ + YEARPART(when_surveyed)) AS ‘Day on 7th’

Now for the hard part, field 11, the calculation outlined earlier. For this we have to test whether January 1st is a Monday, if it is then process the calculation otherwise move on to test whether January 2nd is a Monday, if it is then process the calculation otherwise move on to test whether January 3rd, and so on until January 7th. The basis of the calculation will revolve around the following:

IIF(DAYNAME(’01/01/’ + YEARPART(when_surveyed)) = ‘Monday’, CEIL(DAYSDIFF(’01/01/’ + YEARPART(when_surveyed), when_surveyed)/7), NEXT DAY)

In the above where I have NEXT DAY a similar statement has to be entered for the test of the following day and so on until January 7th. So we get:

IIF(DAYNAME(’01/01/’ + YEARPART(when_surveyed)) = ‘Monday’, CEIL(DAYSDIFF(’01/01/’ + YEARPART(when_surveyed), when_surveyed)/7), IIF(DAYNAME(’02/01/’ + YEARPART(when_surveyed)) = ‘Monday’, CEIL(DAYSDIFF(’01/01/’ + YEARPART(when_surveyed), when_surveyed)/7), IIF(DAYNAME(’03/01/’ + YEARPART(when_surveyed)) = ‘Monday’, CEIL(DAYSDIFF(’01/01/’ + YEARPART(when_surveyed), when_surveyed)/7), IIF(DAYNAME(’04/01/’ + YEARPART(when_surveyed)) = ‘Monday’, CEIL(DAYSDIFF(’01/01/’ + YEARPART(when_surveyed), when_surveyed)/7), IIF(DAYNAME(’05/01/’ + YEARPART(when_surveyed)) = ‘Monday’, CEIL(DAYSDIFF(’01/01/’ + YEARPART(when_surveyed), when_surveyed)/7), IIF(DAYNAME(’06/01/’ + YEARPART(when_surveyed)) = ‘Monday’, CEIL(DAYSDIFF(’01/01/’ + YEARPART(when_surveyed), when_surveyed)/7), IIF(DAYNAME(’07/01/’ + YEARPART(when_surveyed)) = ‘Monday’, CEIL(DAYSDIFF(’01/01/’ + YEARPART(when_surveyed), when_surveyed)/7), NULL)))))))

If we neaten up the entire query to add column headers as well as some ordering we end up with:

SELECT

oid AS ‘CCTV Survey ID’,

when_surveyed AS ‘Survey Date’,

YEARPART(when_surveyed) AS ‘Year of Survey’,

DAYNAME(’01/01/’ + YEARPART(when_surveyed)) AS ‘Day on 1st’,
DAYNAME(’02/01/’ + YEARPART(when_surveyed)) AS ‘Day on 2nd’,
DAYNAME(’03/01/’ + YEARPART(when_surveyed)) AS ‘Day on 3rd’,
DAYNAME(’04/01/’ + YEARPART(when_surveyed)) AS ‘Day on 4th’,
DAYNAME(’05/01/’ + YEARPART(when_surveyed)) AS ‘Day on 5th’,
DAYNAME(’06/01/’ + YEARPART(when_surveyed)) AS ‘Day on 6th’,
DAYNAME(’07/01/’ + YEARPART(when_surveyed)) AS ‘Day on 7th’,

IIF(DAYNAME(’01/01/’ + YEARPART(when_surveyed)) = ‘Monday’, CEIL(DAYSDIFF(’01/01/’ + YEARPART(when_surveyed), when_surveyed)/7), IIF(DAYNAME(’02/01/’ + YEARPART(when_surveyed)) = ‘Monday’, CEIL(DAYSDIFF(’01/01/’ + YEARPART(when_surveyed), when_surveyed)/7), IIF(DAYNAME(’03/01/’ + YEARPART(when_surveyed)) = ‘Monday’, CEIL(DAYSDIFF(’01/01/’ + YEARPART(when_surveyed), when_surveyed)/7), IIF(DAYNAME(’04/01/’ + YEARPART(when_surveyed)) = ‘Monday’, CEIL(DAYSDIFF(’01/01/’ + YEARPART(when_surveyed), when_surveyed)/7), IIF(DAYNAME(’05/01/’ + YEARPART(when_surveyed)) = ‘Monday’, CEIL(DAYSDIFF(’01/01/’ + YEARPART(when_surveyed), when_surveyed)/7), IIF(DAYNAME(’06/01/’ + YEARPART(when_surveyed)) = ‘Monday’, CEIL(DAYSDIFF(’01/01/’ + YEARPART(when_surveyed), when_surveyed)/7), IIF(DAYNAME(’07/01/’ + YEARPART(when_surveyed)) = ‘Monday’, CEIL(DAYSDIFF(’01/01/’ + YEARPART(when_surveyed), when_surveyed)/7), NULL))))))) AS ‘Week Of the Year Starting On 1st Monday Of the Year’

ORDER BY oid ASC

This generates the following table:

SQL Generated Table

SQL Generated Table

Share this post!

    About Idris Nujjoo

    Idris is the Product Sector Leader for InfoNet and InfoMaster and has been working for Innovyze (and its earlier incarnations) since 2001 and specifically working on InfoNet and Asset Management for the past decade.
    This entry was posted in InfoNet. Bookmark the permalink.