Help converting a nasty Query from Access

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help converting a nasty Query from Access

Author

Topic

jadixon
Starting Member

4 Posts

Posted - 2008-07-24 : 15:47:59

I have a query that works fine in access, but I am having a difficult time converting it to something SQL Server likes and that works. I have tried breaking into pieces, but I can't seem to get it to work.
If anyone can help converting this ugly mess, it will be greatly appreciated.

SELECT Query43_Complete_Labor.[2], Query43_Complete_Labor.[3], T13.P2A, Last(T13.P2D) AS LastOfP2D, Sum((nz([SumOf5]))+(nz([SumOf6]))+(nz([SumOf7]))+(nz([SumOf8]))+(nz([SumOf9]))+(nz([SumOf10]))+(nz([SumOf4]))) AS HrsWk, Last((nz([P2B]))+(nz([P2C]))) AS RegPayRate, IIf([LastOf18E] Is Null Or [LastOf18E]<=(nz(0)),IIf([HrsWk]=nz(0),[RegPayRate],(((IIf([LastOfP2D]=No And [P2E]<>4,IIf(((nz([HrsWk])))<=40,(nz([HrsWk])),IIf((nz([HrsWk]))>=50,(nz([HrsWk])),IIf((nz([HrsWk]))<=45,40,(((((nz([HrsWk])))-45)*2)+40)))),IIf([P2E]=4,(nz([HrsWk])),IIf([LastOfP2D]=Yes,IIf((nz([HrsWk]))<=40,(nz([HrsWk])),IIf(((nz([HrsWk]))-(nz([SumOfHolPotHrs])))<=40,(nz([HrsWk])),((((nz([HrsWk]))-(nz([SumOfHolPotHrs]))-40)*1.5)+40+IIf((nz([HrsWk]))-(nz([SumOfHolPotHrs]))>40,(nz([SumOfHolPotHrs])),0))))))))*[RegPayRate])/IIf([LastOfP2D]=No Or [LastOfP2D]=Yes And [HrsWk]=[SumOfHolPotHrs],(nz([HrsWk])),((nz([HrsWk])))))),[ORR]) AS RateThisWeek, T13.P2E, Query51.SumOfHolPotHrs, Query49.DateLastWageAdj, Last(IIf([18E]>0,[18E],0)) AS HA, iif(HrsWk > 0, ((IIf([HA]=nz(0),[RegPayRate],(((IIf([LastOfP2D]=No And [P2E]<>4,IIf(((nz([HA])))<=40,(nz([HA])),IIf((nz([HA]))>=50,(nz([HA])),IIf((nz([HA]))<=45,40,(((((nz([HA])))-45)*2)+40)))),IIf([P2E]=4,(nz([HA])),IIf([LastOfP2D]=Yes,IIf((nz([HA]))<=40,(nz([HA])),IIf(((nz([HA]))-(nz([SumOfHolPotHrs])))<=40,(nz([HA])),((((nz([HA]))-(nz([SumOfHolPotHrs]))-40)*1.5)+40+IIf((nz([HA]))-(nz([SumOfHolPotHrs]))>40,(nz([SumOfHolPotHrs])),0))))))))*[RegPayRate])/IIf([LastOfP2D]=No Or [LastOfP2D]=Yes And [HA]=[SumOfHolPotHrs],(nz([HA])),((nz([HA])))))))*(nz([LastOf18E])))/(nz([HrsWk])),0) AS ORR, Last(Query72.[18E]) AS LastOf18E, Last(T13.P2I) AS LastOfP2I, Last(([P2k])) AS MC
FROM Query72 RIGHT JOIN ((Query49 INNER JOIN (T13 INNER JOIN Query43_Complete_Labor ON T13.P1 = Query43_Complete_Labor.[2]) ON Query49.P1 = Query43_Complete_Labor.[2]) LEFT JOIN Query51 ON (Query43_Complete_Labor.[2] = Query51.[2]) AND (Query43_Complete_Labor.[3] = Query51.PotHolWE)) ON (Query72.[18B] = Query43_Complete_Labor.[2]) AND (Query72.[18C] = Query43_Complete_Labor.[3])
GROUP BY Query43_Complete_Labor.[2], Query43_Complete_Labor.[3], T13.P2A, T13.P2E, Query51.SumOfHolPotHrs, Query49.DateLastWageAdj
HAVING (((T13.P2A)=IIf([P2A]>[3],0,IIf([3]>=[DateLastWageAdj] And [P2A]<[DateLastWageAdj],0,[P2A]))) AND ((Last((nz([P2B]))+(nz([P2C]))))<>0))
ORDER BY Query43_Complete_Labor.[2], Query43_Complete_Labor.[3];

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-24 : 20:00:42

You may be better off looking at the query in the MS-Access query builder & trying to re-create it from there. If that doesn't work, as a first step getting indentation happening would be good! Sorry I don't have the time to go right into it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-25 : 03:22:27

nz([col]) should be ISNULL(col,0)
IIF(....) should be CASE WHEN condition then value1 else value2 END

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-25 : 04:23:40

You also have the function LAST, which is not supported on SQL Server.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources