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
 Query Question

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.
Go to Top of Page

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 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 -