SP Help for breaking up emp_names into birth month

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

General SQL Server Forums

New to SQL Server Programming

SP Help for breaking up emp_names into birth month

Author

Topic

steven.liberman1
Starting Member

15 Posts

Posted - 2011-04-28 : 12:36:26

I need to write a stored procedure where the Output would be in XML and it would give a birth_month of an employee date of birth and then list all the employee names born within that month.

Here is an example of the shift schedule for 28 days in XML, but I need something similar with the months and names Remember the emp_dob column is in SMALLDATETIME declaration.


<e emp_id="1" start_sked="2011-03-27T00:00:00" end_sked="2011-04-23T00:00:00">
  <w wk="1">
    <s work_date="03/27" date="2011-03-27T00:00:00">
      <shift work_date="1" />
    </s>
    <s work_date="03/28" date="2011-03-28T00:00:00">
      <shift work_date="1" />
    </s>
    <s work_date="03/29" date="2011-03-29T00:00:00">
      <shift work_date="1" />
    </s>
    <s work_date="03/30" date="2011-03-30T00:00:00">
      <shift work_date="1" />
    </s>
    <s work_date="03/31" date="2011-03-31T00:00:00">
      <shift work_date="1" />
    </s>
    <s work_date="04/01" date="2011-04-01T00:00:00">
      <shift work_date="1" />
    </s>
    <s work_date="04/02" date="2011-04-02T00:00:00">
      <shift work_date="1" />
    </s>
  </w>
  <w wk="2">
    <s work_date="04/03" date="2011-04-03T00:00:00">
      <shift work_date="2" />
    </s>
    <s work_date="04/04" date="2011-04-04T00:00:00">
      <shift work_date="2" />
    </s>
    <s work_date="04/05" date="2011-04-05T00:00:00">
      <shift work_date="2" />
    </s>
    <s work_date="04/06" date="2011-04-06T00:00:00">
      <shift work_date="2" />
    </s>
    <s work_date="04/07" date="2011-04-07T00:00:00">
      <shift work_date="2" />
    </s>
    <s work_date="04/08" date="2011-04-08T00:00:00">
      <shift work_date="2" />
    </s>
    <s work_date="04/09" date="2011-04-09T00:00:00">
      <shift work_date="2" />
    </s>
  </w>
  <w wk="3">
    <s work_date="04/10" date="2011-04-10T00:00:00">
      <shift work_date="3" />
    </s>
    <s work_date="04/11" date="2011-04-11T00:00:00">
      <shift work_date="3" />
    </s>
    <s work_date="04/12" date="2011-04-12T00:00:00">
      <shift work_date="3" />
    </s>
    <s work_date="04/13" date="2011-04-13T00:00:00">
      <shift work_date="3" />
    </s>
    <s work_date="04/14" date="2011-04-14T00:00:00">
      <shift work_date="3" />
    </s>
    <s work_date="04/15" date="2011-04-15T00:00:00">
      <shift work_date="3" />
    </s>
    <s work_date="04/16" date="2011-04-16T00:00:00">
      <shift work_date="3" />
    </s>
  </w>
  <w wk="4">
    <s work_date="04/17" date="2011-04-17T00:00:00">
      <shift work_date="4" />
    </s>
    <s work_date="04/18" date="2011-04-18T00:00:00">
      <shift work_date="4" />
    </s>
    <s work_date="04/19" date="2011-04-19T00:00:00">
      <shift work_date="4" />
    </s>
    <s work_date="04/20" date="2011-04-20T00:00:00">
      <shift work_date="4" />
    </s>
    <s work_date="04/21" date="2011-04-21T00:00:00">
      <shift work_date="4" />
    </s>
    <s work_date="04/22" date="2011-04-22T00:00:00">
      <shift work_date="4" />
    </s>
    <s work_date="04/23" date="2011-04-23T00:00:00">
      <shift work_date="4" />
    </s>
  </w>
</e>

Here is that respective stored proc


ALTER PROCEDURE [dbo].[WEB_SO_GetShifts_steven](
@emp_id int,
@start_date SMALLDATETIME,
@end_date SMALLDATETIME = null 
)

AS

BEGIN

SET NOCOUNT ON;
DECLARE @start_sked SMALLDATETIME;
DECLARE @end_sked SMALLDATETIME;
DECLARE @weeks int;
DECLARE @date SMALLDATETIME;

CREATE TABLE #GET_SKED
(week_num int,
work_date SMALLDATETIME,
shift_id int,
shift_description varchar(255)
)

set @start_sked = DATEADD(DAY, -DATEPART(w, @start_date) + 1,
                                   @start_date)

set @end_sked = DATEADD(DAY, 27, @start_sked) ;

set @date = @start_sked ;

Print @start_sked ;

Print @end_sked ;

WHILE @date <= @end_sked
            BEGIN
				IF EXISTS ( SELECT  emp_id
                            FROM    emp_schedule
                            WHERE   emp_id = @emp_id
                                    AND work_date = @date ) 
                                    
					BEGIN
					
					INSERT INTO #GET_SKED
					(week_num, work_date, shift_id, shift_description)
					(SELECT week_num = ( DATEDIFF(DAY, @start_sked,
                                                             @date) / 7 + 1 ),
                                         work_date, es.shift_id, s.shift_description
                                         from emp_schedule es
                                         join shifts s on es.shift_id = s.shift_id
                                         where emp_id = @emp_id
                                         and
                                         work_date = @date)
                    END
                   ELSE
					BEGIN
					
					INSERT INTO #GET_SKED
					(week_num, work_date)
					(SELECT week_num = (DATEDIFF(DAY, @start_sked,
                                                             @date) / 7 + 1 ),
                     work_date = @date
                     from employee
                     where emp_id = @emp_id)
					END
					
					SET @date = DATEADD(DAY, 1, @date)
            END        
            
  SET @weeks = ( DATEDIFF(DAY, @start_sked, @end_sked) + 1 ) / 7 ;

;
        WITH    weeks ( wk )
                  AS ( SELECT   1
                       UNION ALL
                       SELECT   wk + 1
                       FROM     weeks
                       WHERE    wk < @weeks
                     ) ,
                weekdays ( wkd )
                  AS ( SELECT   1
                       UNION ALL
                       SELECT   wkd + 1
                       FROM     weekdays
                       WHERE    wkd < 7
                     ) ,
                dates ( dt, week_num )
                  AS ( SELECT   @start_sked,
                                DATEDIFF(DAY, @start_sked, @start_sked) / 7
                                + 1
                       UNION ALL
                       SELECT   DATEADD(DAY, 1, dt),
                                DATEDIFF(DAY, @start_sked,
                                         DATEADD(DAY, 1, dt)) / 7 + 1
                       FROM     dates
                       WHERE    dt < @end_sked
                     )
            SELECT  emp_id,
                    start_sked = @start_sked,  end_sked = @end_sked,                    
                    ( SELECT    wk,
                                ( SELECT    work_date = SUBSTRING(CONVERT(VARCHAR(10), dt, 101), 1, LEN(CONVERT(VARCHAR(10), dt, 101)) - 5),
                                            date = dt,
                                            ( SELECT    week_num
														work_date,
														shift_id,
                                                        shift_description
                                              FROM      #GET_SKED
                                              WHERE     work_date = d.dt
                                            FOR
                                              XML RAW('shift'),
                                                  TYPE
                                            )
                                  FROM      dates d
                                  WHERE     week_num = w.wk
                                FOR
                                  XML RAW('s'),
                                      TYPE
                                )
                      FROM      weeks w
                    FOR
                      XML AUTO,
                          TYPE
                    )
            FROM    dbo.employee
            WHERE   emp_id = @emp_id
        FOR     XML RAW('e'),
                    TYPE
                 

    END

steven.liberman1
Starting Member

15 Posts

Posted - 2011-04-28 : 15:32:44

This is my code, but I need the test Table to select the names of the employee table and their date of birth's


ALTER PROCEDURE WEB_SO_MONTHBIRTH 


AS

BEGIN

DECLARE @test TABLE
        (emp_name VARCHAR(50),
         emp_dob SMALLDATETIME) ;
INSERT  INTO @test
SELECT  'Me', '19600404'          UNION ALL
SELECT  'You', '19610420'         UNION ALL
SELECT  'The Guru', '19800423'    UNION ALL
SELECT  'Bozo', '19721218'        UNION ALL
SELECT  'Bozo-child', '19821115'  UNION ALL
SELECT  'Me-Child', '19810105'    UNION ALL
SELECT  'You-Child', '19800229'   UNION ALL
SELECT  'You-Spouse', '19580315'  UNION ALL
SELECT  'Guru-Spouse', '19780529' UNION ALL
SELECT  'Guru-Child', '20030615'  UNION ALL
SELECT  'Me-Child2', '19980711'   UNION ALL
SELECT  'You-Child2', '19820827'  UNION ALL
SELECT  'Guru-Child2', '20050927' UNION ALL
SELECT  'Guru-Child3', '20071005' ;


DECLARE @emp_dob SMALLDATETIME ;
SET @emp_dob = GETDATE() ;

-- if you need to restrict the results for the passed in month:
--DECLARE @MonthStart DATETIME,
--        @MonthEnd DATETIME;
--SET @MonthStart = DATEADD(MONTH, DATEDIFF(MONTH, 0, @emp_dob), 0);
--SET @MonthEnd   = DATEADD(MONTH, 1, @MonthStart);
        
;WITH cte1 AS
(
-- get the distinct months being used
SELECT DISTINCT 
       sDOB = DATENAME(MONTH, emp_dob)
  FROM @test t1
 -- to restrict for the specified month:
 --WHERE emp_dob >= @MonthStart
 --  AND emp_dob < @MonthEnd
), cte2 AS
(
-- add the month of birth to the xml string. Convert it all to XML.
SELECT m = CONVERT(XML, '<month_birth>' + sDOB + '</month_birth>' + ds.emp_name)
       ,sDOB
  FROM cte1
  -- get the employees that have a birthdate in the month being tested
  -- build a string in XML format 
        CROSS APPLY (SELECT emp_name = (
                                   SELECT '<emp_name>' + emp_name + '</emp_name>'
                                   FROM   @test t2
                                   WHERE  DATENAME(MONTH, emp_dob) = cte1.sDOB
                                   ORDER BY emp_name
                                     FOR XML PATH(''),TYPE).value('.', 'varchar(max)')) ds
)
SELECT m
  FROM cte2
 ORDER BY CONVERT(datetime, sDOB + ' 2005')
   FOR XML PATH(''),TYPE;
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources