April 19, 2011 at 2:14 pm
MY parameters:
@emp_id
@work_date
Table: emp_schedule
How would I write a Stored Procedure where the output will be in XML format?
My assignment: take the work_date and for the next 4 weeks release his days in each week and withing each day his/her shift.
The result has to be in XML format.
like
<Week ="1">
<day = "0">
<shift = "***">
</shift>
</day>
</week>
April 19, 2011 at 2:22 pm
please provide the table structure and some sample data.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 19, 2011 at 2:51 pm
emp_id = 1
work_date = 2011-04-01
shift_start_time = "2011-04-01 07:00:00"
shift_end_time = "2011-04-01 15:00:00"
The table includes the dates he is scheduled to work on for the next 4 weeks from 2011-04-01
So all data between "2011-04-01" and "2011-04-28"
<Week = "1">
<day ="0">
<shift start =>
<shift finish =>
April 19, 2011 at 3:31 pm
Here's a couple of options/samples for you:
with emp_schedule as (
Select shift_start_time = '2011-04-01 07:00:00'
,shift_end_time = '2011-04-01 15:00:00'
,work_date = '2011-04-01'
,emp_id = 1
)
Select emp_id as "@Employee",DATEPART(wk,work_date) as "@WorkWeek"
,(Select DATEPART(dd,work_date) as "@Day"
,(Select shift_end_time as "@EndShift"
,shift_start_time as "@StartShift"
From emp_schedule
Group by emp_id,shift_start_time,shift_end_time
FOR XML PATH ('Shift'), type)
From emp_schedule
Group by emp_id,work_date
FOR XML PATH ('WorkDay'), type)
From emp_schedule
Group By emp_id,DATEPART(wk,work_date)
FOR XML PATH ('WorkWeek'), type
;
with emp_schedule as (
Select shift_start_time = '2011-04-01 07:00:00'
,shift_end_time = '2011-04-01 15:00:00'
,work_date = '2011-04-01'
,emp_id = 1
)
Select emp_id as "Employee",DATEPART(wk,work_date) as "WorkWeek"
,(Select DATEPART(dd,work_date) as "Day"
,(Select shift_end_time as "EndShift"
,shift_start_time as "StartShift"
From emp_schedule
Group by emp_id,shift_start_time,shift_end_time
FOR XML PATH ('Shift'), type)
From emp_schedule
Group by emp_id,work_date
FOR XML PATH ('WorkDay'), type)
From emp_schedule
Group By emp_id,DATEPART(wk,work_date)
FOR XML PATH ('WorkWeek'), type
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 20, 2011 at 10:00 am
This is how the result should be
<e emp_id="518" emp_name="Avila, Bobbie" start_sched="2011-04-17T00:00:00" end_sched="2011-05-14T00:00:00">
<w wk="1">
<s work_date="04/17" date="2011-04-17T00:00:00">
<shift job_code="38" job_department_id="31" week_no="1" />
</s>
<s work_date="04/18" date="2011-04-18T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />
</s>
<s work_date="04/19" date="2011-04-19T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />
</s>
<s work_date="04/20" date="2011-04-20T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />
</s>
<s work_date="04/21" date="2011-04-21T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />
</s>
<s work_date="04/22" date="2011-04-22T00:00:00">
<shift job_code="38" job_department_id="31" week_no="1" />
</s>
<s work_date="04/23" date="2011-04-23T00:00:00">
<shift job_code="38" job_department_id="31" week_no="1" />
</s>
</w>
<w wk="2">
<s work_date="04/24" date="2011-04-24T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />
</s>
<s work_date="04/25" date="2011-04-25T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />
</s>
<s work_date="04/26" date="2011-04-26T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />
</s>
<s work_date="04/27" date="2011-04-27T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />
</s>
<s work_date="04/28" date="2011-04-28T00:00:00">
<shift job_code="38" job_department_id="31" week_no="2" />
</s>
<s work_date="04/29" date="2011-04-29T00:00:00">
<shift job_code="38" job_department_id="31" week_no="2" />
</s>
<s work_date="04/30" date="2011-04-30T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />
</s>
</w>
<w wk="3">
<s work_date="05/01" date="2011-05-01T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />
</s>
<s work_date="05/02" date="2011-05-02T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />
</s>
<s work_date="05/03" date="2011-05-03T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />
</s>
<s work_date="05/04" date="2011-05-04T00:00:00">
<shift job_code="38" job_department_id="31" week_no="3" />
</s>
<s work_date="05/05" date="2011-05-05T00:00:00">
<shift job_code="38" job_department_id="31" week_no="3" />
</s>
<s work_date="05/06" date="2011-05-06T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />
</s>
<s work_date="05/07" date="2011-05-07T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />
</s>
</w>
<w wk="4">
<s work_date="05/08" date="2011-05-08T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />
</s>
<s work_date="05/09" date="2011-05-09T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />
</s>
<s work_date="05/10" date="2011-05-10T00:00:00">
<shift job_code="38" job_department_id="31" week_no="4" />
</s>
<s work_date="05/11" date="2011-05-11T00:00:00">
<shift job_code="38" job_department_id="31" week_no="4" />
</s>
<s work_date="05/12" date="2011-05-12T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />
</s>
<s work_date="05/13" date="2011-05-13T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />
</s>
<s work_date="05/14" date="2011-05-14T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />
</s>
</w>
</e>
April 20, 2011 at 10:13 am
You didn't have all of that info included in your first posts.
I provided two sample queries to help you figure out what needs to be done. Add in the additional fields and query from the table as needed to get the data you need.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 20, 2011 at 10:20 am
One of the guys told me I need to create a table with
emp_id,
emp_name,
job_description,
shift_id,
shift_start_time,
shift_end_time
but I have to parse the data into this format with w wk and day, can you please point me in the right direction so I can write the code, because it is somewhat confusing to me.
<e emp_id="518" emp_name="Avila, Bobbie" start_sched="2011-04-17T00:00:00" end_sched="2011-05-14T00:00:00">
<w wk="1">
<s work_date="04/17" date="2011-04-17T00:00:00">
<shift job_code="38" job_department_id="31" week_no="1" />
</s>
<s work_date="04/18" date="2011-04-18T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />
</s>
<s work_date="04/19" date="2011-04-19T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />
</s>
<s work_date="04/20" date="2011-04-20T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />
</s>
<s work_date="04/21" date="2011-04-21T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="1" />
</s>
<s work_date="04/22" date="2011-04-22T00:00:00">
<shift job_code="38" job_department_id="31" week_no="1" />
</s>
<s work_date="04/23" date="2011-04-23T00:00:00">
<shift job_code="38" job_department_id="31" week_no="1" />
</s>
</w>
<w wk="2">
<s work_date="04/24" date="2011-04-24T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />
</s>
<s work_date="04/25" date="2011-04-25T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />
</s>
<s work_date="04/26" date="2011-04-26T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />
</s>
<s work_date="04/27" date="2011-04-27T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />
</s>
<s work_date="04/28" date="2011-04-28T00:00:00">
<shift job_code="38" job_department_id="31" week_no="2" />
</s>
<s work_date="04/29" date="2011-04-29T00:00:00">
<shift job_code="38" job_department_id="31" week_no="2" />
</s>
<s work_date="04/30" date="2011-04-30T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="2" />
</s>
</w>
<w wk="3">
<s work_date="05/01" date="2011-05-01T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />
</s>
<s work_date="05/02" date="2011-05-02T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />
</s>
<s work_date="05/03" date="2011-05-03T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />
</s>
<s work_date="05/04" date="2011-05-04T00:00:00">
<shift job_code="38" job_department_id="31" week_no="3" />
</s>
<s work_date="05/05" date="2011-05-05T00:00:00">
<shift job_code="38" job_department_id="31" week_no="3" />
</s>
<s work_date="05/06" date="2011-05-06T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />
</s>
<s work_date="05/07" date="2011-05-07T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="3" />
</s>
</w>
<w wk="4">
<s work_date="05/08" date="2011-05-08T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />
</s>
<s work_date="05/09" date="2011-05-09T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />
</s>
<s work_date="05/10" date="2011-05-10T00:00:00">
<shift job_code="38" job_department_id="31" week_no="4" />
</s>
<s work_date="05/11" date="2011-05-11T00:00:00">
<shift job_code="38" job_department_id="31" week_no="4" />
</s>
<s work_date="05/12" date="2011-05-12T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />
</s>
<s work_date="05/13" date="2011-05-13T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />
</s>
<s work_date="05/14" date="2011-05-14T00:00:00">
<shift shift_id="16" shift_description="3p-11p" unit_id="71" unit_code="7" work_status=" " job_code="38" job_department_id="31" week_no="4" />
</s>
</w>
</e>
Previous ItemNext Item
April 20, 2011 at 10:40 am
you should be able to follow this and add in the pieces that you need.
with emp_schedule as (
Select shift_start_time = '2011-04-01 07:00:00'
,shift_end_time = '2011-04-01 15:00:00'
,work_date = '2011-04-01'
,emp_id = 1
)
Select emp_id as "@emp_id",DATEPART(wk,work_date) as "@WorkWeek"
,(Select DATEPART(dd,work_date) as "@Day"
,(Select work_date as "@work_date"
,(Select shift_end_time as "@shift_end_time"
,shift_start_time as "@shift_start_time"
From emp_schedule
Group by emp_id,shift_start_time,shift_end_time
FOR XML PATH ('shift'), type)
From emp_schedule
Group by emp_id,work_date
FOR XML PATH ('s'), type)
From emp_schedule
Group by emp_id,work_date
FOR XML PATH ('w'), type)
From emp_schedule
Group By emp_id,DATEPART(wk,work_date)
FOR XML PATH ('e'), type
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 20, 2011 at 10:53 am
In the first select statement, I need to split calender into 28 days. with wk 1,2,3,4 and days from 1-7 or 0-6.
How do I make sure that the work_date is between April 1 and 28. And how do I split it into weeks and days.
April 20, 2011 at 12:34 pm
with emp_schedule as (
Select shift_start_time = '2011-04-01 07:00:00'
,shift_end_time = '2011-04-01 15:00:00'
,work_date = '2011-04-01'
,emp_id = 1
)
-How would I make the start time and end time be on work date between the 1st and the 28th
-I also want week 1 to be the 1st through the 7th, week 2 8-14, 3 --> 15-21, 4 --> 22-28
April 20, 2011 at 2:09 pm
Also How would I create a time table with all the values you have given me in your query.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply