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
 SQL Query Help Please Desperate!!!!

Author  Topic 

sandeebee123
Starting Member

2 Posts

Posted - 2013-08-05 : 11:34:02
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:53:19
Dup of this? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=187340
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-05 : 12:08:21
[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 SupWinID
FROM dbo.Employees AS e
INNER 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.Supervisor
WHERE 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.WindowsID
ORDER BY e.Supervisor,
wh.[Full Name],
wh.[Date Worked];[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -