You can also play Dark Warriors on
Back to post list
Back a step


Admin Nate
Posts: 5,727
Status: King

Karma: +2,355
[+1] [-1]

Offline

 Subject: Queries
SELECT X.*, Y.WeeklyHours AS CURRENTHOURS FROM

(SELECT FirstName + ' ' + LastName AS EmployeeName, CountryName, SUM(hoursWorked) AS WeeklyHours,
datepart(week, convert(smalldatetime, (convert(varchar(2), w.WH_Month) + '/' + convert(varchar(2), w.WH_Day)
+ '/' + convert(varchar(4), w.WH_Year)))) AS WeekNum
FROM Employees e, Countries c, WorkHours w
WHERE e.EmpID = w.EmpID
AND e.CountryID = c.CountryID
GROUP BY LastName, FirstName, CountryName, datepart(week, convert(smalldatetime, (convert(varchar(2), w.WH_Month) + '/' + convert(varchar(2), w.WH_Day)
+ '/' + convert(varchar(4), w.WH_Year)))) ) X

LEFT OUTER JOIN

(SELECT FirstName + ' ' + LastName AS EmployeeName, CountryName, SUM(hoursWorked) AS WeeklyHours, datepart(week, convert(smalldatetime, (convert(varchar(2), w.WH_Month) + '/' + convert(varchar(2), w.WH_Day)
+ '/' + convert(varchar(4), w.WH_Year)))) as WeekNum
FROM Employees e, Countries c, WorkHours w
WHERE e.EmpID = w.EmpID
AND e.CountryID = c.CountryID
AND datepart(week, convert(smalldatetime, (convert(varchar(2), w.WH_Month) + '/' + convert(varchar(2), w.WH_Day)
+ '/' + convert(varchar(4), w.WH_Year)))) = DATEPART(WEEK, GETDATE())
GROUP BY LastName, FirstName, CountryName, datepart(week, convert(smalldatetime, (convert(varchar(2), w.WH_Month) + '/' + convert(varchar(2), w.WH_Day)
+ '/' + convert(varchar(4), w.WH_Year)))) ) Y
ON X.EmployeeName = Y.EmployeeName
AND X.CountryName = Y.CountryName
AND X.WeekNum = Y.WeekNum
Time Posted: December 4 2009 02:57 pm EST
Last updated: December 5 2009 08:09 am EST


Replies:

Add reply:
Subject:
Body: