April 28, 2011 at 8:39 am
I need a stored procedure with Output in XML format where I would give a month of employee date of birth
emp_dob column is in SMALLDATETIME
So, it should be like this
<m>
<month_birth = "January">
<emp_name = "?????" />
<emp_name = "?????" />
<emp_name = "?????" />
<emp_name = "?????" />
</m>
<m>
<month_birth = "February">
<emp_name = "?????" />
<emp_name = "?????" />
</m>
HERE IS WHAT I HAVE SO FAR, but I know something is not right
ALTER PROCEDURE WEB_SO_MONTHBIRTH
(
@emp_dob SMALLDATETIME
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @emp_name varchar(255) ;
CREATE TABLE #GET_EMP_MON_BIRTH
(
emp_dob SMALLDATETIME,
emp_name varchar(255)
)
SET @emp_dob = month(emp_dob);
SET emp_name
END
April 28, 2011 at 8:45 am
what are you trying to do with the following lines of code?
CREATE TABLE #GET_EMP_MON_BIRTH
(
emp_dob SMALLDATETIME,
emp_name varchar(255)
)
SET @emp_dob = month(emp_dob);
SET emp_name
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 28, 2011 at 8:49 am
Here is the XML Of each employee shift schedule
<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 the stored procedure for that
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
So, I need something similar to that, but instead of emp_id its emp_dob, but showing only the month.
April 28, 2011 at 10:04 am
How would I get the month breakdown
remember emp_dob column is in SMALLDATETIEM Declaration.
April 28, 2011 at 10:53 am
I'm not quite clear what you are doing here.
Can you provide a short sample of test data and the source? Is this is in a table or in XML for the source and what is the output?
I think that you are reading from a table and outputting XML, but it's not quite clear.
April 28, 2011 at 10:57 am
select emp_name, emp_dob from employee
emp_dob is listed for example '1974-01-03 00:00:00' OR '1974-01-03'
So, I would need something like my first post, where it would break all emp_names into the respective birth months.
April 28, 2011 at 12:14 pm
Is DATENAME not something you want?
You're not really describing this well, either in your first post or this last one.
April 28, 2011 at 12:40 pm
How does this work for you?
-- See how you start off by actually creating a table and then
-- inserting the data into it? Your doing this makes it a lot easier
-- for all of us volunteers to help you. So, help us help you.
-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/
-- for more details on how to do all of this.
DECLARE @test-2 TABLE
(emp_name VARCHAR(50),
emp_dob SMALLDATETIME) ;
INSERT INTO @test-2
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' ;
-- parameter for the stored procedure.
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-2 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-2 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;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 28, 2011 at 1:00 pm
How would I insert the names of the employees and their date of births from the employee table into the declared test table.
DECLARE @test-2 TABLE
(emp_name VARCHAR(50),
emp_dob SMALLDATETIME) ;
INSERT INTO @test-2
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' ;
April 28, 2011 at 1:45 pm
Seriously?
njdevils39 (4/28/2011)
How would I insert the names of the employees and their date of births from the employee table into the declared test table.
DECLARE @test-2 TABLE
(emp_name VARCHAR(50),
emp_dob SMALLDATETIME) ;
INSERT INTO @test-2
SELECT Name, DOB FROM employees
@waynes: You write some great code.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 28, 2011 at 1:51 pm
Thank You ToddASD, now I have to study the fucntionality of everything inside the Stored Proc, but Thank you all very much.
April 28, 2011 at 2:07 pm
ColdCoffee (4/28/2011)
toddasd (4/28/2011)
@waynes: You write some great code.I totally concur 🙂 he is awesome, like how The Miz in WWE says, Wayne is awesome 🙂
lol...love it. You just worked WWE into a SQL discussion.
ColdCoffee - "what do you think of 1NF?"
intimidatedSSCnoob "well, I..."
ColdCoffee - "IT DOESN'T MATTER WHAT YOU THINK!!!"
April 28, 2011 at 2:20 pm
calvo (4/28/2011)
ColdCoffee (4/28/2011)
toddasd (4/28/2011)
@waynes: You write some great code.I totally concur 🙂 he is awesome, like how The Miz in WWE says, Wayne is awesome 🙂
lol...love it. You just worked WWE into a SQL discussion.
ColdCoffee - "what do you think of 1NF?"
intimidatedSSCnoob "well, I..."
ColdCoffee - "IT DOESN'T MATTER WHAT YOU THINK!!!"
I'm very proud to say I don't watch WWE (...anymore).
But I will follow every answer I give now with "You can't see me!"
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 28, 2011 at 2:56 pm
calvo (4/28/2011)
ColdCoffee (4/28/2011)
toddasd (4/28/2011)
@waynes: You write some great code.I totally concur 🙂 he is awesome, like how The Miz in WWE says, Wayne is awesome 🙂
lol...love it. You just worked WWE into a SQL discussion.
ColdCoffee - "what do you think of 1NF?"
intimidatedSSCnoob "well, I..."
ColdCoffee - "IT DOESN'T MATTER WHAT YOU THINK!!!"
Give me a Hell YEAH!! :-P:w00t:
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply