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 anyoneSELECT period, h10 / (htotal - h60 - h70) * 1 AS utilizationFROM (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 htotalFROM 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.StonebreakerThe greatest obstacle to discovery is not ignorance - it is the illusion of knowledge. -Daniel Boorstin |
|
|
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. |
|
|
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 utilizationFROM ( 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 |
|
|
|
|
|