How to replace Cursor in SQL Server?

SQL statement needed VB.net to create a variety of totals

• I have an SQL Server Database that I need to create totals, but my SQL statement won't work. The table has a number of different areas that need to be grouped and then I need counts for the current month, previous month, year to date, and previous year to date. I also need to be able to sum up fees paid for the same periods. The following statement works in Access, but not on SQL Server. I did replace the # with '. SELECT Permits.Earea, Permits.Structure, Permits.ClassWork, Sum(IIf(([PermitDate]>=#11/1/2002#) And ([permitdate]<#12/1/2002#),1,0)) AS Thismonth, Sum(IIf(([PermitDate]>=#10/1/2002#) And ([permitdate]<#11/1/2002#),1,0)) AS previousmonth, Sum(IIf(([PermitDate]>=#1/1/2002#) And ([permitdate]<=Now()),1,0)) AS YTD, Sum(IIf(([PermitDate]>=#1/1/2001#) And ([permitdate]<=#11/7/2001#),1,0)) AS LYTD, Sum(IIf(([PermitDate]>=#11/1/2002#) And ([permitdate]<#12/1/2002#),[ProjectCost],0)) AS Thismonthdollars, Sum(IIf(([PermitDate]>=#10/1/2002#) And ([permitdate]<#11/1/2002#),[ProjectCost],0)) AS Lastmonthdollars, Sum(IIf(([PermitDate]>=#1/1/2002#) And ([permitdate]<Now()),[ProjectCost],0)) AS YTDdollars, Sum(IIf(([PermitDate]>=#1/1/2001#) And ([permitdate]<#11/7/2001#),[ProjectCost],0)) AS LastYTDdollars FROM Permits WHERE (((Permits.PermitDate) Between #1/1/2001# And #12/1/2002#) AND ((Permits.Earea)='A' Or (Permits.Earea)='C' Or (Permits.Earea)='D' Or (Permits.Earea)='D1' Or (Permits.Earea)='D2' Or (Permits.Earea)='D3' Or (Permits.Earea)='D4' Or (Permits.Earea)='D5' Or (Permits.Earea)='D6' Or (Permits.Earea)='D7' Or (Permits.Earea)='D8' Or (Permits.Earea)='D9' Or (Permits.Earea)='E' Or (Permits.Earea)='E1' Or (Permits.Earea)='F' Or (Permits.Earea)='F1' Or (Permits.Earea)='F2' Or (Permits.Earea)='F3' Or (Permits.Earea)='H' Or (Permits.Earea)='H1' Or (Permits.Earea)='H2' Or (Permits.Earea)='H3' Or (Permits.Earea)='H4' Or (Permits.Earea)='H5' Or (Permits.Earea)='H6' Or (Permits.Earea)='H7' Or (Permits.Earea)='H8')) GROUP BY Permits.Earea, Permits.Structure, Permits.ClassWork ORDER BY Permits.Earea, Permits.Structure, Permits.ClassWork Is this the right way to do this or is there a better way?

• Answer:

  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