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.
Author |
Topic |
sandeebee123
Starting Member
2 Posts |
Posted - 2013-08-05 : 11:37:40
|
I am trying to run an Access query that someone completed in SQL. Below is the query: SELECT Employees.Supervisor, [Work Hours].[Full Name], [Work Hours].[Date Worked], [Work Codes].[Productivity Type], [Work Codes].WorkCode, Sum([Work Hours].Hours) AS Hours, [Work Hours].Description, [Work Hours].ManualCount, [Employees-Active].WindowsID AS SupWinID FROM (Employees INNER JOIN ([Work Hours] LEFT JOIN [Work Codes] ON [Work Hours].[Work Code]=[Work Codes].ID) ON Employees.[Full Name]=[Work Hours].[Full Name]) INNER JOIN [Employees-Active] ON Employees.Supervisor=[Employees-Active].[Employee Name] WHERE (((Employees.EmpType)="RS")) GROUP BY Employees.Supervisor, [Work Hours].[Full Name], [Work Hours].[Date Worked], [Work Codes].[Productivity Type], [Work Codes].WorkCode, [Work Hours].Description, [Work Hours].ManualCount, [Employees-Active].WindowsID HAVING ((([Work Hours].[Date Worked]) Between (Date()-Weekday(Date()))-6 And Date()-Weekday(Date()))) ORDER BY Employees.Supervisor, [Work Hours].[Full Name], [Work Hours].[Date Worked]; I am getting the following error: Compile error. in query expression '((([Work.Hours].[Date Worked]) Between (date()-Weekday(Date()))-6 And Date ()-Weekday(Date()))'. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-05 : 11:46:20
|
quote: Originally posted by sandeebee123 I am trying to run an Access query that someone completed in SQL. Below is the query: SELECT Employees.Supervisor, [Work Hours].[Full Name], [Work Hours].[Date Worked], [Work Codes].[Productivity Type], [Work Codes].WorkCode, Sum([Work Hours].Hours) AS Hours, [Work Hours].Description, [Work Hours].ManualCount, [Employees-Active].WindowsID AS SupWinID FROM (Employees INNER JOIN ([Work Hours] LEFT JOIN [Work Codes] ON [Work Hours].[Work Code]=[Work Codes].ID) ON Employees.[Full Name]=[Work Hours].[Full Name]) INNER JOIN [Employees-Active] ON Employees.Supervisor=[Employees-Active].[Employee Name] WHERE (((Employees.EmpType)="RS")) GROUP BY Employees.Supervisor, [Work Hours].[Full Name], [Work Hours].[Date Worked], [Work Codes].[Productivity Type], [Work Codes].WorkCode, [Work Hours].Description, [Work Hours].ManualCount, [Employees-Active].WindowsID HAVING ((([Work Hours].[Date Worked]) Between (Date()-Weekday(Date()))-6 And Date()-Weekday(Date()))) ORDER BY Employees.Supervisor, [Work Hours].[Full Name], [Work Hours].[Date Worked]; I am getting the following error: Compile error. in query expression '((([Work.Hours].[Date Worked]) Between (date()-Weekday(Date()))-6 And Date ()-Weekday(Date()))'.
That DATE function you are using is not a built in SQL Server function. Try replacing each instance of Date() with CAST(GETDATE() AS DATE) if you are on SQL 2008 or later.If you are on an earlier version of SQL, instead use: DATEADD(day,DATEDIFF(day,0,GETDATE()),0) to replace each instance of Date() function.Edit: Weekday is not a built in function either. So unless you have a user defined function for Weekday, you would need to replace that as well. Initially I thought it was trying to determine whether a given day is a weekday or not. But that doesn't seem like it.You have to understand what the logic of those Date() and Weekday(Date()) functions are and do appropriate replacements. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-05 : 12:08:49
|
[code]SELECT e.Supervisor, wh.[Full Name], wh.[Date Worked], wc.[Productivity Type], wc.WorkCode, SUM(wh.[Hours]) AS [Hours], wh.[Description], wh.ManualCount, ea.WindowsID AS SupWinIDFROM dbo.Employees AS eINNER JOIN dbo.[Work Hours] AS wh ON wh.[Full Name] = w.[Full Name]LEFT JOIN dbo.[Work Codes] AS wc ON wc.ID = wh.[Work Code]INNER JOIN dbo.[Employees-Active] AS ea ON ea.[Employee Name] = e.SupervisorWHERE e.EmpType = 'RS' AND wh.[Date Worked] >= DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()) / 7 * 7, -8) AND wh.[Date Worked] < DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()) / 7 * 7, -1)GROUP BY e.Supervisor, wh.[Full Name], wh.[Date Worked], wc.[Productivity Type], wc.WorkCode, wh.[Description], wh.ManualCount, ea.WindowsIDORDER BY e.Supervisor, wh.[Full Name], wh.[Date Worked];[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
|