February 20, 2012 at 2:47 pm
you can't just remove the XML's. Let's start at understanding what the outcome is supposed to be. Can you give us an example? If I was your boss and I said, what does this stored procedure do? What is the answer? That will help us a lot.
EDIT:Also, what is this supposed to do in words?
set @start_sked = DATEADD(DAY, -DATEPART(w, @start_date) + 1, @start_date)
I'm missing the relationship between days and weeks here. Ugh it has been a long day...
Jared
CE - Microsoft
February 20, 2012 at 3:02 pm
For anyone interested... I made this a bit easier to copy and paste. Hope I didn't delete any characters (except the aliases where they were not referenced).
USE [Version500_Model]
GO
ALTER PROCEDURE WEB_SO_GetEmployeeOrgSchedule
(
@ORG_LEVEL_ID INT,
@start_date SMALLDATETIME,
@end_date SMALLDATETIME
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @start_sked SMALLDATETIME;
DECLARE @end_sked SMALLDATETIME;
DECLARE @weeks int;
DECLARE @date SMALLDATETIME;
CREATE TABLE #GET_ORG_SKED
(
week_num int,
emp_name VARCHAR(255),
work_date SMALLDATETIME,
unit_description VARCHAR(255),
shift_id INT,
shift_description VARCHAR(255)
)
set @start_sked = '2012-01-01'
set @end_sked = '2012-01-29';
set @date = @start_sked ;
WHILE @date <= @end_sked
BEGIN
IF EXISTS (SELECT e.emp_id
FROM employee e
INNER JOIN emp_schedule s
ON e.emp_id = s.emp_id
WHERE emp_organization_id = @org_level_id
AND work_date = @date )
BEGIN
INSERT INTO #GET_ORG_SKED(week_num, emp_name, work_date, unit_description, shift_id, shift_description)
SELECT (DATEDIFF(DAY, @start_sked, @date) / 7 + 1 ), e.emp_name, work_date, u.unit_description, s.shift_id, sh.shift_description
from employee e
INNER join emp_schedule s
on e.emp_id = s.emp_id
INNER join units u
on s.unit_id = u.unit_id
INNER join shifts sh
on s.shift_id = sh.shift_id
where emp_organization_id = @org_level_id
AND work_date = @date)
END
ELSE
BEGIN
INSERT INTO #GET_ORG_SKED (week_num, work_date)
SELECT (DATEDIFF(DAY, @start_sked, @date) / 7 + 1 ), @date
from employee
where emp_organization_id = @org_level_id)
END
SET @date = DATEADD(DAY, 1, @date)
END
SET @weeks = ( DATEDIFF(DAY, @start_sked, @end_sked) + 1 ) / 7 ;
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 e.emp_id, e.emp_name, 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), [DATEADD] = dt,]
(SELECT week_num, work_date, unit_description, shift_id, shift_description
FROM #GET_ORG_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 employee e
INNER join emp_schedule s
on e.emp_id = s.emp_id
WHERE emp_organization_id = @org_level_id
FOR XML RAW('e'), TYPE
END
EDIT: I hard coded dates to make this easier for the time being.
Jared
CE - Microsoft
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply