Of course there are a lot more records. Basically I'm retrieving the sum of Reg_Hrs and Trav_Hrs for a particular Period_End_Date. My query (so far) looks like this:

SELECT Emp_Code, Period_End_Date, SUM(Reg_Hrs) AS Tot_Reg_Hours, SUM(Trav_Hrs) AS Tot_Trav_Hrs, SUM(Reg_Hrs) + SUM(Trav_Hrs) AS Tot_HrsFROM Time_Card_TableWHERE Period_End_Date = '03/26/11'GROUP BY Emp_Code, Period_End_Date

The problem is that I also have to return the overtime hours. You'd think it would be an easy matter of using a case to find out if the total hours is greater than 80 then return the total hours minus 80.

Nope.

I have to figure out the over time per week (based on the Work_Date field. I tried this:

SUM( CASE WHEN Work_Date BETWEEN DATEADD(day, -13, Period_End_Date) AND DATEADD(day, -7, Period_End_Date) THEN CASE WHEN (RegHrs + TravHrs) > 80 THEN (RegHrs + TravHrs) - 80 ELSE 0 END END) AS OT_Week1,SUM( CASE WHEN Work_Date BETWEEN DATEADD(day, -6, Period_End_Date) AND Period_End_Date THEN CASE WHEN (RegHrs + TravHrs) > 80 THEN (RegHrs + TravHrs) - 80 ELSE 0 END END) AS OT_Week2,

SELECT Emp_Code ,Period_End_Date ,SUM(Reg_Hrs) AS Tot_Reg_Hours ,SUM(Trav_Hrs) AS Tot_Trav_Hrs ,SUM(Reg_Hrs) + SUM(Trav_Hrs) AS Tot_Hrs ,SUM(overtimehours) AS OT_HrsFROM ( SELECT Emp_Code ,Period_End_Date , CASE WHEN SUM(Reg_Hrs) > 40 THEN SUM(Reg_Hrs)-40 ELSE 0 END as overtimehours , SUM(Reg_Hrs) AS Reg_Hrs , SUM(Trav_Hrs) AS Trav_Hrs , DATEPART(week,work_date) as weeknumber FROM Time_Card_Table GROUP BY Emp_Code ,Period_End_Date ,DATEPART(week,work_date)) wGROUP BY Emp_Code ,Period_End_Date

WITH ot AS ( SELECT Emp_Code ,Period_End_Date , CASE WHEN SUM(Reg_Hrs) > 40 THEN SUM(Reg_Hrs)-40 ELSE 0 END as overtimehours , SUM(Reg_Hrs) AS Reg_Hrs , SUM(Trav_Hrs) AS Trav_Hrs , DATEPART(week,work_date) as weeknumber , DATEPART(week,work_date)-DATEPART(week,DATEADD(month,-1,period_End_Date)) AS weeknum FROM Time_Card_Table GROUP BY Emp_Code ,Period_End_Date ,DATEPART(week,work_date))SELECT Emp_Code ,Period_End_Date ,SUM(Reg_Hrs) AS Tot_Reg_Hours ,SUM(Trav_Hrs) AS Tot_Trav_Hrs ,SUM(Reg_Hrs) + SUM(Trav_Hrs) AS Tot_Hrs ,sum(CASE WHEN weeknum = 1 THEN overtimehours ELSE 0 END) AS OT_Week1 ,sum(CASE WHEN weeknum = 2 THEN overtimehours ELSE 0 END) AS OT_Week2 ,sum(CASE WHEN weeknum = 3 THEN overtimehours ELSE 0 END) AS OT_Week3 ,sum(CASE WHEN weeknum = 4 THEN overtimehours ELSE 0 END) AS OT_Week4 ,sum(CASE WHEN weeknum = 5 THEN overtimehours ELSE 0 END) AS OT_Week5FROM ot GROUP BY Emp_Code, Period_End_Date

This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.