first day of week, last day of week -- stored procedure help?

[LMCRYER]

I have a stored procedure which works and provides data. I now want to add more to it, and I am having syntax problems.

The sp is provided with a start date and and end date viua parameters and then provides data grouped by week.

I am trying to add the following to the sp and any help would be much appreciated.

1. FIRST day of the week (usually Monday....but...)
-- if the employee's hire date was wednesday, it should show WEDNESDAY, not monday ....so the hire_date needs to be evaluated in here
2. Last day of the week (friday)
-- if the employee terminated mid-week, this should be their terminate date, otherwise the real last day of the week
3. Calculation = # of days in the week * 8
4. BenchmarkHrs -- if the employees real amt of hours worked is < the calculation (#4) then use their real hours, otherwise use the calculation (whichever is greater).

Can anyone help?

LMC

******************************************************

CREATE PROCEDURE [DBO].[OT_ANALYSIS_LMCTEST]
(@STARTDATE DATEtime,
@ENDDATE DATEtime)
AS

set datefirst 1
--ensures that Monday is the first day of the week


SELECT
TAB.CC,
TAB.EN,
SOPN.PO_OPERATION_CLASS_CODE,
PACT.EMPLOYEE_ID,
EMP.[NAME],
datediff(ww,@STARTDATE,@ENDDATE) as numweeks,
(datepart(week,(PACT.ACTIVITY_DATE))) as weeknumber ,
(datepart(yyyy,(PACT.ACTIVITY_DATE))) as yearnumber ,
SUM(PACT.ACT_RSRC_USED) AS TOT_HRS,
EMPOT.[HIRE DATE],
EMPOT.[REHIRE DATE],
EMPOT.[LAST DAY WORKED]

FROM

EMPLOYEE_MASTER.DBO.TAB_POOL TAB
inner join SOPN_ELK SOPN on TAB.bid_cat = SOPN.PO_OPERATION_CLASS_CODE


left outer join PACT_ELK PACT on
SOPN.MAJOR_SEQ_NBR = PACT.MAJ_SEQ_NBR and
SOPN.MINOR_SEQ_NBR = PACT.MIN_SEQ_NBR and
SOPN.ALTERNATE_SEQ_NBR = PACT.ALT_SEQ_NBR and
SOPN.ORD_NBR = PACT.ORDER_NBR


LEFT JOIN EMPLOYEE_MASTER.DBO.EMP EMP ON
PACT.EMPLOYEE_ID = EMP.[EMPL ID]

LEFT JOIN EMPLOYEE_MASTER.DBO.VW_EMPOT EMPOT ON
EMP.[EMPL ID] = EMPOT.EMPLID


WHERE

PACT.ACTIVITY_DATE >= (@STARTDATE) AND
PACT.ACTIVITY_DATE <= ( @ENDDATE) AND
SUBSTRING(PACT.CONTROL_ID,9,3) <> '392' AND
SUBSTRING (PACT.CONTROL_ID,9,3)<> '382' AND
ACT_RSRC_USED <> '0'



GROUP BY (datepart(yyyy,(PACT.ACTIVITY_DATE))) ,
(datepart(week,(PACT.ACTIVITY_DATE))) ,


TAB.CC,
TAB.EN,
SOPN.PO_OPERATION_CLASS_CODE,
PACT.EMPLOYEE_ID,
EMP.[NAME],
EMPOT.[HIRE DATE],
EMPOT.[REHIRE DATE],
EMPOT.[LAST DAY WORKED]
GO


LMC