7 conditions for evry day check for all month

• midan1

  SSCertifiable

  Points: 5100

  More actions

  December 29, 2007 at 5:23 pm

  #177815

  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 BlockCREATE 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 for all

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  December 29, 2007 at 5:56 pm

  #763535

  What is v_un, v_1, v_2, etc, etc? They're views aren't they? You should probably post at least 1 of those and some easy to load data... see the following URL...

  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• midan1

  SSCertifiable

  Points: 5100

  More actions

  December 30, 2007 at 1:08 pm

  #763567

  hi i am sorry

  Code Block this is the code for View v_un

  ----------------------------

  SELECT empid, shift

  FROM dbo.v_51

  UNION ALL

  SELECT empid, shift

  FROM dbo.v_11

  UNION ALL

  SELECT empid, shift

  FROM dbo.v_12

  UNION ALL

  SELECT empid, shift

  FROM dbo.v_22

  UNION ALL

  SELECT empid, shift

  FROM dbo.v_23

  UNION ALL

  SELECT empid, shift

  FROM dbo.v_34

  UNION ALL

  SELECT empid, shift

  FROM dbo.v_45

  ------------------------------

  this is View dbo.v_51

  ----------------------------------------------------

  SELECT empid, 51 AS shift

  FROM (SELECT empid

  FROM dbo.empbase

  WHERE (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5)

  GROUP BY empid

  UNION ALL

  SELECT empid

  FROM dbo.empbase AS empbase_1

  WHERE (basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)

  GROUP BY empid) AS derived

  GROUP BY empid

  HAVING (COUNT(1) > 1)

  ------------------------------------------------

  this is View dbo.v_11

  SELECT empid, 12 AS shift

  FROM (SELECT empid

  FROM dbo.empbase

  WHERE (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)

  GROUP BY empid

  UNION ALL

  SELECT empid

  FROM dbo.empbase AS empbase_1

  WHERE (basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)

  GROUP BY empid) AS derived

  GROUP BY empid

  HAVING (COUNT(1) > 1)

  ---------------------------------------------------------

  this is View dbo.v_12

  ------------------------------------------------

  SELECT empid, 12 AS shift

  FROM (SELECT empid

  FROM dbo.empbase

  WHERE (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)

  GROUP BY empid

  UNION ALL

  SELECT empid

  FROM dbo.empbase AS empbase_1

  WHERE (basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 2)

  GROUP BY empid) AS derived

  GROUP BY empid

  HAVING (COUNT(1) > 1)

  ---------------------------------------------------------

  ..........................................

  ............................

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  December 30, 2007 at 10:34 pm

  #763587

  You code calls on V-1... i see no example of V_1.

  Further... all of these views call on the table EmpBase... why do you need the views at all?

  Instea of trying to tell us what you think you want to do with the code, just tell us what you want to do in human terms and provide us with the table info and the sample data. Part of your problem is that your having problems with the code... stop trying to define the problem to us with bad code... tell us in English and without any reference to code, what the requriements are.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)