stored procedure go on and fill it automatic from

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

stored procedure go on and fill it automatic from

Author

Topic

midan1
Starting Member

39 Posts

Posted - 2007-12-24 : 14:15:08

help
1) how can i show all in same field (for evry day i get new field) in the stored procedure

2) it possible to go on and fill it automatic from this point (from first to end of the month)
i need to build stored procedure for all the month
-------------------------------------



DECLARE @yeara [varchar](4) 
DECLARE @month1 [varchar](2) 
DECLARE @day1 [varchar](2) 
DECLARE @day2 [varchar](2) 
DECLARE @day3 [varchar](2) 
DECLARE @mydate1 [datetime] 
DECLARE @mydate2 [datetime] 
set @yeara ='2007' 
SET @month1 ='12' 
Set @day1 ='1'
Set @day2 ='2' 
set @mydate1 = CONVERT([datetime] ,@day1 + '/'+ @month1 + '/' + @yearA ,103) 
set @mydate2 = CONVERT([datetime] ,@day2 + '/'+ @month1 + '/' + @yearA ,103) 

select
------------------first day of the month
[new date1]=@mydate1,WEEK_DAY=DATEPART(WEEKDAY, @mydate1 ),[new_shift_day1] = 
CASE 

WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 51 ))) 
THEN
1 
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 11 ))) 
THEN 2 
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 12 ))) 
THEN 2 
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 22 ))) 
THEN 3 
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 23 ))) 
THEN 3 
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 33 ))) 
THEN 4 
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 34 ))) 
THEN 5 
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 45 ))) 
THEN 1 
END, 
------------------second day of the month
[new date2]=@mydate2,WEEK_DAY=DATEPART(WEEKDAY, @mydate2 ),[new_shift_day2]= 
CASE 

WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 51 ))) 
THEN
2
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 11 ))) 
THEN 2 
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 12 ))) 
THEN 3 
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 22 ))) 
THEN 3 
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 23 ))) 
THEN 4 
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 33 ))) 
THEN 5 
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 34 ))) 
THEN 1 
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 45 ))) 
THEN 1 

END, 
SilokE.empid, SilokE.Fname FROM SilokE

TNX

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-25 : 02:27:22

Can you explain it in more detail? what do you mean by show all in same field?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-25 : 14:15:11

Don't bother.
This user has a habit of NOT providing additional information

See
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94363

E 12°55'05.25"
N 56°04'39.16"

ilan
Starting Member

21 Posts

Posted - 2007-12-26 : 02:49:43

i am apologize it is my copy and paste
i explain
what i mean is if i have stored procedure with 7 conditions
for evry day in the week one condition
like this i have 7 code blocks for Sunday,Monday , .....Saturday

CASE WHEN
Sunday
--------
WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 51 )))
THEN
1
END,

CASE WHEN
Monday
--------
WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 11 )))
THEN
2
END,
.......
CASE WHEN
Saturday
..............
------

now i need to use it in my stored procedure for all the month

  

CASE WHEN
"first day of the month" 

CASE WHEN
"Sunday"--- use code blocks for Sunday
CASE WHEN
"Monday"--- use code blocks for Monday
CASE WHEN
Tuesday "--- use code blocks for Tuesday
CASE WHEN
Tuesday "--- use code blocks for Tuesday
CASE WHEN
Wednesday "--- use code blocks for Wednesday
CASE WHEN
Thursday "--- use code blocks for Thursday
CASE WHEN
Friday "--- use code blocks for Friday
CASE WHEN
Saturday "--- use code blocks for Saturday

CASE WHEN
"second day of the month" 

CASE WHEN
"Sunday"--- use code blocks for Sunday
CASE WHEN
"Monday"--- use code blocks for Monday
CASE WHEN
Tuesday "--- use code blocks for Tuesday
CASE WHEN
Tuesday "--- use code blocks for Tuesday
CASE WHEN
Wednesday "--- use code blocks for Wednesday
CASE WHEN
Thursday "--- use code blocks for Thursday
CASE WHEN
Friday "--- use code blocks for Friday
CASE WHEN
Saturday "--- use code blocks for Satur

like this until the last day of the month

TNX

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-26 : 05:11:35

Guess this will give you with a stub to get desired result:-

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' AND  
                empid IN (SELECT empid FROM v_un WHERE (shift =51 )
           THEN 1
           WHEN DATENAME( dw,@StartDate)='Monday' AND  
                empid IN (SELECT empid FROM v_un WHERE (shift =11 )
           THEN 2
............

           WHEN DATENAME( dw,@StartDate)='Saturday' AND  
               ...
           THEN ..
       END       

SET @StartDate=DATEADD(d,1,@StartDate)
END

GO

ilan
Starting Member

21 Posts

Posted - 2007-12-26 : 06:43:09

tnx for your help

can replece ?

this line


WHEN DATENAME( dw,@StartDate)='Sunday' AND

with this line



WHEN DATEPART( dw,@StartDate)='1' AND

tnx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-26 : 06:49:43

Yup. you can use any of the two. DATEPART to get integer values or DATENAME to get character string.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-26 : 12:06:23

Beware that DATEPART function is depending on SET DATEFIRST setting, whereas DATENAME is not.

E 12°55'05.25"
N 56°04'39.16"

midan1
Starting Member

39 Posts

Posted - 2007-12-26 : 16:35:45

dear friends tnx for the help
i am appraiser it
i need you can you help ?
my problem is that i need

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



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 ---- 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 ---- 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 ---- 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources