Hi, softcom-ga:
The short answer is that the conditional expression IIF is not part of
Transact-SQL (the flavor of SQL in MS SQL Server). It is kind of a
Visual Basic construct that is incorporated into MS Access for
convenience.
The primary conditional expression syntax in Transact-SQL is the CASE
construct. There are some specialized ones that are helpful in
dealing with Null values (IsNull, NullIf, and COALESCE), but I think
you will find the CASE statement easy to understand and apply for your
purpose. Most of what I say here should work with SQL Server versions
going back a long way, with the exception of my final comment about
user-defined functions.
There are two forms of the CASE expression; see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp
The forms have slightly confusing names. In the first form, called
"simple CASE function", is not the best for your purpose, but for the
sake of completeness (and future reference), I will point it out. One
places an expression after the CASE keyword:
CASE testing_value
WHEN compare_value1 THEN result_value1
WHEN compare_value2 THEN result_value2
...
ELSE fall_through_alternative_value
END
and the "testing" value given there is compared serially to the values
provided after the WHEN keywords until the first match is found. The
"result" value after the corresponding THEN keyword is the outcome.
If no matching WHEN value is found, the value provided after the ELSE
keyword is the outcome.
For your purpose the second form, the "searched CASE function", will
be best. Here we omit any value expression after the CASE keyword and
instead place Boolean expressions after each WHEN keyword. Let me
illustrate this by converting your first "IIF" conditional into this
form:
CASE
WHEN [PermitDate] >= "11/1/2002"
And [PermitDate] < "12/1/2002"
THEN 1
ELSE 0
END
There is one other change I see that needs to be made to your SQL in
order for it to be palatable to SQL Server. The current date and time
is returned by:
GetDate( )
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_4z51.asp
instead of Now( ) as in MS Access.
There's another change I would make for the sake of legibility.
Although SQL Server can work with the OR logic in your complex WHERE
clause, a more readable way to expression these disjunctions would be
to use the IN syntax.
With all these changes (and taking the liberty of regularizing the
capitalization of PermitDate, perhaps an Access quirk?), I would
convert your Access SQL SELECT statement as follows:
SELECT Permits.Earea, Permits.Structure, Permits.ClassWork,
Sum(
CASE
WHEN [PermitDate] >= "11/1/2002"
And [PermitDate] < "12/1/2002"
THEN 1 ELSE 0
END ) AS Thismonth,
Sum(
CASE
WHEN [PermitDate] >= "10/1/2002"
And [PermitDate] < "11/1/2002"
THEN 1 ELSE 0
END ) AS previousmonth,
Sum(
CASE
WHEN [PermitDate] >= "1/1/2002"
And [PermitDate] <= GetDate()
THEN 1 ELSE 0
END ) AS YTD,
Sum(
CASE
WHEN [PermitDate] >= "1/1/2001"
And [PermitDate] <= "11/7/2001"
THEN 1 ELSE 0
END ) AS LYTD,
Sum(
CASE
WHEN [PermitDate] >= "11/1/2002"
And [PermitDate] < "12/1/2002"
THEN [ProjectCost] ELSE 0
END ) AS Thismonthdollars,
Sum(
CASE
WHEN [PermitDate] >= "10/1/2002"
And [PermitDate] < "11/1/2002"
THEN [ProjectCost] ELSE 0
END ) AS Lastmonthdollars,
Sum(
CASE
WHEN [PermitDate] >= "1/1/2002"
And [PermitDate] < GetDate()
THEN [ProjectCost] ELSE 0
END ) AS YTDdollars,
Sum(
CASE
WHEN [PermitDate] >= "1/1/2001"
And [PermitDate] < "11/7/2001"
THEN [ProjectCost] ELSE 0
END ) AS LastYTDdollars
FROM Permits
WHERE Permits.PermitDate Between "1/1/2001" And "12/1/2002"
AND Permits.Earea IN (
'A','C','D','D1','D2','D3','D4','D5','D6','D7',
'D8','D9','E','E1','F','F1','F2','F3','H','H1',
'H2','H3','H4','H5','H6','H7','H8')
GROUP BY Permits.Earea, Permits.Structure, Permits.ClassWork
ORDER BY Permits.Earea, Permits.Structure, Permits.ClassWork
Let me mention one other idea that might be helpful. In SQL Server
2000 you can incorporate user-defined functions into your SELECT
statements. Because all of your conditional logic here depends on a
comparison:
this_date >= date_begin AND this_date < date_final
we could shorten the syntax a bit by introducing a user-defined
function:
MyDateInRange( this_date, date_begin, date_final )
which returns 1 if the condition is met and 0 otherwise. For some of
your selected values, this return value could be used as is, while it
would be a multiplier for the dollar amounts, e.g. times ProjectCost
in the last four values returned.
Please advise me of the SQL to create your Permits table if you wish
me to test this for syntax, etc. I'd be happy to clarify any points
that you have further questions about.
regards, mathtalk-ga |
