Help How To ? 7 Conditions For Evry Day Check For All Month

help how to ? 7 conditions for evry day check for all month

a condition "code block" for evry day in the week
like CASE inside CASE
and How to insert it to #Temp table all







Code Block
CREATE PROC YourProc
@StartDate datetime = NULL,
@EndDate datetime = NULL
AS
SET @StartDate = COALESCE(@StartDate,DATEADD(d,DATEDIFF(d,0,GETDATE()),0))--defaulting to todays date if not supplied
SET @EndDate=COALESCE(@EndDate,DATEADD(m,1,@StartDate))--defaults to 1 month from today

WHILE @StartDate <= @EndDate
BEGIN
SELECT CASE
WHEN DATENAME( dw,@StartDate)='Sunday'

---- for Sunday----for Sunday------------for Sunday----for Sunday------v_un

WHEN DATENAME( dw,@StartDate)='Sunday' AND
empid IN (SELECT empid FROM v_un WHERE (shift =45 )
THEN 1
WHEN DATENAME( dw,@StartDate)='Sunday' AND
empid IN (SELECT empid FROM v_un WHERE (shift =51 )
THEN 1
WHEN DATENAME( dw,@StartDate)='Sunday' AND
empid IN (SELECT empid FROM v_un WHERE (shift =11 )
THEN 2
WHEN DATENAME( dw,@StartDate)='Sunday' AND
empid IN (SELECT empid FROM v_un WHERE (shift =12)
THEN 2
WHEN DATENAME( dw,@StartDate)='Sunday' AND
empid IN (SELECT empid FROM v_un WHERE (shift =22 )
THEN 3
WHEN DATENAME( dw,@StartDate)='Sunday' AND
empid IN (SELECT empid FROM v_un WHERE (shift =23)
THEN 3
WHEN DATENAME( dw,@StartDate)='Sunday' AND
empid IN (SELECT empid FROM v_un WHERE (shift =34)
THEN 5
------------------------END for Sunday----for Sunday
SELECT CASE
WHEN DATENAME( dw,@StartDate)='monday'
---- for monday----for monday------------for monday----for monday----FROM v_1
WHEN DATENAME( dw,@StartDate)='monday' AND
empid IN (SELECT empid FROM v_1 WHERE (shift =45 )
THEN 1
WHEN DATENAME( dw,@StartDate)='monday' AND
empid IN (SELECT empid FROM v_1 WHERE (shift =51 )
THEN 1
WHEN DATENAME( dw,@StartDate)='monday' AND
empid IN (SELECT empid FROM v_1 WHERE (shift =11 )
THEN 2
WHEN DATENAME( dw,@StartDate)='Monday' AND
empid IN (SELECT empid FROM v_1 WHERE (shift =12)
THEN 2
WHEN DATENAME( dw,@StartDate)='monday' AND
empid IN (SELECT empid FROM v_1 WHERE (shift =22 )
THEN 3
WHEN DATENAME( dw,@StartDate)='monday' AND
empid IN (SELECT empid FROM v_1 WHERE (shift =23)
THEN 3
WHEN DATENAME( dw,@StartDate)='monday' AND
empid IN (SELECT empid FROM v_1 WHERE (shift =34)
THEN 5
--------------------------END for monday
SELECT CASE
WHEN DATENAME( dw,@StartDate)='Tuesday'

---- for Tuesdayy----for Tuesday----for Tuesday----for Tuesday----from V_2

WHEN DATENAME( dw,@StartDate)='Tuesday' AND
empid IN (SELECT empid FROM v_2 WHERE (shift =45 )
THEN 1
WHEN DATENAME( dw,@StartDate)='Tuesday' AND
empid IN (SELECT empid FROM v_2 WHERE (shift =51 )
THEN 1
WHEN DATENAME( dw,@StartDate)='Tuesday' AND
empid IN (SELECT empid FROM v_2 WHERE (shift =11 )
THEN 2
WHEN DATENAME( dw,@StartDate)='Tuesday' AND
empid IN (SELECT empid FROM v_2 WHERE (shift =12)
THEN 2
WHEN DATENAME( dw,@StartDate)='Tuesday' AND
empid IN (SELECT empid FROM v_2 WHERE (shift =22 )
THEN 3
WHEN DATENAME( dw,@StartDate)='Tuesday' AND
empid IN (SELECT empid FROM v_2 WHERE (shift =23)
THEN 3
WHEN DATENAME( dw,@StartDate)='Tuesday' AND
empid IN (SELECT empid FROM v_2 WHERE (shift =34)
THEN 5
................................................END for Tuesday
/////////
until
Saturday
.....
WHEN DATENAME( dw,@StartDate)='Saturday' AND
...
THEN ..
END
SET @StartDate=DATEADD(d,1,@StartDate)
END
GO





TNX