DataProject SQL Server Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am translating an Access Database into a DataProject using SQL Server as
the database. I have a query with the following code

SELECT Sum(IIf([Date]<[Date1],[Amount],0)) AS OpeningBalance, Sum(IIf([Date]
Between [Date1] And [Date2] And [PaymRec]="Receipt",[Amount],0)) AS Receipts,
Sum(IIf([Date] Between [Date1] And [Date2] And
[PaymRec]="Payment",[Amount],0)) AS Payments,
Sum(IIf([Date]<=[Date2],[Amount],0)) AS ClosingBalance
FROM CashBook;

Esentially what it does is return OpenningBalance, Receipts Payments and
ClosingBalance for a CashBook table.

Does anyone have any suggestions as to the best way to build a strored
procedure that can produce the same results.
 
The newsgroup for ADP is m.p.access.adp.sqlserver (but I must warn you that
at this moment and since two days, there is a lot of pollution from a well
known troll).

On SQL-Server, use the Case statement to replace the IIF() function and use
the single quote ' as the string delimiter instead of ". (With the correct
option, you can keep the double quote as the string delimiter on SQL-Server
but this will be a moving target if you come to work on different
servers/databases.) Use N' as the prefix if these strings are of type
Unicode (nchar, nvarchar and ntext instead of char, varchar and text):

SELECT Sum (Case When [Date] < [Date1] Then [Amount] Else 0 End) as
OpeningBalance,
Sum (Case When ([Date] Between [Date1] And [Date2] And [PaymRec] =
'Receipt') Then [Amount] Else 0 End) AS Receipts, ...
 
Back
Top