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
 General SQL Server Forums
 New to SQL Server Programming
 convert ms access query to ms sql format

Author  Topic 

ovikat
Starting Member

1 Post

Posted - 2010-01-22 : 08:58:57
Hi, Can anyone help convert the query below to ms sql format....
i use to implement the query against an access database but the database have been migrated to SQL 2005.
PLease help convert the to be conpatible with SQL 2005. Thanks a million anyone


SELECT period, h10 / (htotal - h60 - h70) * 1 AS utilization
FROM (SELECT format(wkending, '(mm)-mmm-YYYY') AS period,
SUM(switch(extraid = '10', hoursworked, extraid <> '10', 0)) AS h10,
SUM(switch(extraid = '50', hoursworked, extraid <> '50', 0)) AS h50,
SUM(switch(extraid = '15', hoursworked, extraid <> '15', 0)) AS h15,
SUM(switch(extraid = '20', hoursworked, extraid <> '20', 0)) AS h20,
SUM(switch(extraid = '30', hoursworked, extraid <> '30', 0)) AS h30,
SUM(switch(extraid = '40', hoursworked, extraid <> '40', 0)) AS h40,
SUM(switch(extraid = '60', hoursworked, extraid <> '60', 0)) AS h60,
SUM(switch(extraid = '70', hoursworked, extraid <> '70', 0)) AS h70,
SUM(hoursworked) AS htotal
FROM misquery
WHERE wkending >= 06 / 03 / 09 AND nationality IN ('N') AND
deptemp NOT IN ('fa', 'adm', 'hr', 'mgt', 'BD', 'IARM', 'NCB', 'IT', 'SCR', 'QA/QC', 'HSE')
GROUP BY format(wkending, '(mm)-mmm-YYYY')) AS x[/SIZE]

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2010-01-22 : 09:26:10
Research 'derived table', that should give you the answer.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-01-22 : 09:27:42
The only changes you need to make are the format() statement, look up CONVERT() and the first clkause in your WHERE.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-01-22 : 10:34:53
What does the switch statement do? That isn't a part of SQL Server, but is probably similar to a case statement. Also you'll need single quotes around the dates.

Jim


SELECT period, h10 / (htotal - h60 - h70) * 1 AS utilization
FROM
( SELECT
CONVERT(varchar(10),wkending, 101) AS period,
SUM(switch(extraid = '10', hoursworked, extraid <> '10', 0)) AS h10,
SUM(switch(extraid = '50', hoursworked, extraid <> '50', 0)) AS h50,
SUM(switch(extraid = '15', hoursworked, extraid <> '15', 0)) AS h15,
SUM(switch(extraid = '20', hoursworked, extraid <> '20', 0)) AS h20,
SUM(switch(extraid = '30', hoursworked, extraid <> '30', 0)) AS h30,
SUM(switch(extraid = '40', hoursworked, extraid <> '40', 0)) AS h40,
SUM(switch(extraid = '60', hoursworked, extraid <> '60', 0)) AS h60,
SUM(switch(extraid = '70', hoursworked, extraid <> '70', 0)) AS h70,
SUM(hoursworked) AS htotal
FROM misquery
WHERE wkending >= '06/03/09' AND nationality IN ('N') AND
deptemp NOT IN ('fa', 'adm', 'hr', 'mgt', 'BD', 'IARM', 'NCB', 'IT', 'SCR', 'QA/QC', 'HSE')
GROUP BY convert(varchar(10),wkending, 101
) AS x[/SIZE]



Everyday I learn something that somebody else already knew
Go to Top of Page
   

- Advertisement -