Converting an Access query to SQL view 1

[snoopy80]

I have an Access query that I am having difficulty to convert it to SQL view. Here is the Access03 Query:

SELECT qryOrder.ID, qryOrder.PatientOwes, qryOrder.DOS, Now()-[dos] AS data, IIf([ALLOWED]=0,[totalbilled]-[priminsurancepaid]-[secinsurancepaid]-[patientpaid]-[adjustmentamount],[ALLOWED]-[priminsurancepaid]-[secinsurancepaid]-[patientpaid]-[adjustmentamount]) AS ar, qryOrder.HCFA, qryOrder.[Item#], qryOrder.DatePaid, tblPatient.DATEUC1, tblPatient.DATEUC1, tblPatient.DATEUC1, tblPatient.DATEUC1, tblPatient.DATEUC1, tblPatient.DATEUC1, tblPatient.DATEUC1, tblPatient.DATEUC1, tblPatient.DATEUC1
FROM qryOrder INNER JOIN tblPatient ON qryOrder.ID = tblPatient.ID
WHERE ((Not (qryOrder.DOS) Is Null And (qryOrder.DOS)<=Now()) AND ((IIf([ALLOWED]=0,[totalbilled]-[priminsurancepaid]-[secinsurancepaid]-[patientpaid]-[adjustmentamount],[ALLOWED]-[priminsurancepaid]-[secinsurancepaid]-[patientpaid]-[adjustmentamount]))>0));

Can someone help me to convert this to SQL 2005 view

 

[snoopy80]

Sorry accidently clicked submit.

Can someone help me to convert this to SQL view?
Thanks.

 

[gmmastros]

At a quick glance, I have 2 suggestions.

Change Now() to GetDate()

Replace IIF with Case/When:

IIf([ALLOWED]=0,[totalbilled]-[priminsurancepaid]-[secinsurancepaid]-[patientpaid]-[adjustmentamount],[ALLOWED]-[priminsurancepaid]-[secinsurancepaid]-[patientpaid]-[adjustmentamount]) AS ar,

Becomes....

[tt][blue]Case When [Allowed] = 0
Then [totalbilled]-[priminsurancepaid]-[secinsurancepaid]-[patientpaid]-[adjustmentamount]
Else [ALLOWED]-[priminsurancepaid]-[secinsurancepaid]-[patientpaid]-[adjustmentamount]
End As ar
[/blue][/tt]

Since most of this is the same, I would suggest....

[blue]Case When [ALLOWED]=0 Then [totalbilled] Else [Allowed] End [/blue]
	-[priminsurancepaid]-[secinsurancepaid]-[patientpaid]-[adjustmentamount]
    AS ar,

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[snoopy80]

Thanks George. It worked.
Really appreciate your fast response and great help.