Converting Access query to SQL Server problem

[glgcag]

I have a query in Access that I need to convert to a stored procedure in SQL Server. This has a lot of Access-specific SQL so any help making it work with SQL Server is appreciated!

FYI- the query is doing the following:
-The first select determines clientkeys where the expenses have been posted, but they have not been assigned to a statement.
-The second select determines clientkeys where expense payments have been posted, but not assigned to a statement.
-The third select is subtracting the sum of expense payments from the sum of expenses, for each client, to determine if there is a balance.
-I then cycle through this recordset (usually no more than a couple of hundred records) and insert a new statement into tExpenseStatements with the clientkey and balance and then print the statement to send to the client.

SELECT DISTINCT tExpenses.ClientKey FROM tExpenses
 WHERE (((tExpenses.ClientKey) Is Not Null) 
AND ((nz([tExpenses].[StatementNumber],0))=0) AND ((tExpenses.PostDate) Is Not Null))
ORDER BY tExpenses.ClientKey

UNION 

SELECT DISTINCT tExpensePayments.ClientKey FROM tExpensePayments 
WHERE (((tExpensePayments.ClientKey) Is Not Null) 
AND ((nz([tExpensePayments].[StatementNumber],0))=0) 
AND ((tExpensePayments.PostDate) Is Not Null)) 
ORDER BY tExpensePayments.ClientKey

UNION

SELECT DISTINCT tClients.ClientNumber As ClientKey FROM tClients 
WHERE (((nz((SELECT SUM([Amount]) FROM tExpenses WHERE ClientKey=[ClientNumber]),0)
-nz((SELECT SUM([PaymentAmount]) FROM tExpensePayments WHERE ClientKey=[ClientNumber]),0))<>0));

 

[gmmastros]

At a quick glance, I see 2 problems, but there may be more.

NZ does not exist, use Coalesce instead.

Also... You cannot sort individual parts of a union query. You can only put the sort at the end.

-George

"the screen with the little boxes in the window." - Moron

 

[gmmastros]

[COLOR=blue]SELECT[/color] 	[COLOR=#FF00FF]DISTINCT[/color] 
		tExpenses.ClientKey 
[COLOR=blue]FROM[/color] 	tExpenses
[COLOR=blue]WHERE[/color] 	(((tExpenses.ClientKey) [COLOR=blue]Is[/color] Not Null)
		AND (([COLOR=#FF00FF]isnull[/color]([tExpenses].[StatementNumber],0))=0) AND ((tExpenses.PostDate) [COLOR=blue]Is[/color] Not Null))
[COLOR=green]--ORDER BY tExpenses.ClientKey
[/color]
UNION

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DISTINCT[/color] tExpensePayments.ClientKey [COLOR=blue]FROM[/color] tExpensePayments
[COLOR=blue]WHERE[/color] (((tExpensePayments.ClientKey) [COLOR=blue]Is[/color] Not Null)
AND (([COLOR=#FF00FF]isnull[/color]([tExpensePayments].[StatementNumber],0))=0)
AND ((tExpensePayments.PostDate) [COLOR=blue]Is[/color] Not Null))
[COLOR=green]--ORDER BY tExpensePayments.ClientKey
[/color]
UNION

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DISTINCT[/color] tClients.ClientNumber [COLOR=blue]As[/color] ClientKey [COLOR=blue]FROM[/color] tClients
[COLOR=blue]WHERE[/color] ((([COLOR=#FF00FF]isnull[/color](([COLOR=blue]SELECT[/color] SUM([Amount]) [COLOR=blue]FROM[/color] tExpenses [COLOR=blue]WHERE[/color] ClientKey=[ClientNumber]),0)
-[COLOR=#FF00FF]isnull[/color](([COLOR=blue]SELECT[/color] SUM([PaymentAmount]) [COLOR=blue]FROM[/color] tExpensePayments [COLOR=blue]WHERE[/color] ClientKey=[ClientNumber]),0))<>0));

-George

"the screen with the little boxes in the window." - Moron

 

[danvlas]

If [ClientNumber] is a parameter, you need to use @ClientNumber declared as a parameter of a stored proc or in-line function...


HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html