February 20, 2012 at 10:34 am
Hi,
my job is to get this result set
Emp_name, Work_date, Shift_Description, Unit_description, Start Time (just time no date), End Time (Just time no date)
SO here is the code, it works, but the procedure is not executing. It says
Msg 241, Level 16, State 1, Procedure WEB_SO_GetEmployeeOrgSchedule2, Line 22
Conversion failed when converting datetime from character string.
Anyhow, here is the code:
USE [Version500_Model]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================
-- Author: Steven Liberman
-- Create Date: February 20, 2012
-- Description: Getting Employee Schedule for 1 week
-- select * from employee
-- select * from emp_schedule
-- select * from units
-- select * from shifts
-- WEB_SO_GetEmployeeOrgSchedule2 '3273','2011-01-01','2011-01-07'
-- ===================================
ALTER PROCEDURE WEB_SO_GetEmployeeOrgSchedule2
(
@ORG_LEVEL_ID INT,
@start_date DATETIME,
@end_date DATETIME
)
AS
BEGIN
SELECT em.emp_name, es.work_date, un.unit_description,
CONVERT(CHAR(5), es.shift_start_time, 108) as shift_start_time,
CONVERT(CHAR(5), es.shift_end_time, 108) as shift_end_time,
shi.shift_description
from
employee as em
join
emp_schedule as es
on
em.emp_id = es.emp_id
join
shifts as shi
on
es.shift_id = shi.shift_id
join
units as un
on
es.unit_id = un.unit_id
where
em.emp_organization_id = @ORG_LEVEL_ID
AND
es.work_date between '@start_date' and '@end_date'
END
Oh yea, I need to get a schedule for employee on a one week basis and order it by org_level_id.
February 20, 2012 at 10:40 am
Are you using SQL 2008 (as per the forum you posted in), or an earlier version?
If 2008, then there's a Time datatype that you should use instead of DateTime, and that should solve the problem.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 20, 2012 at 10:42 am
Replace
AND
es.work_date between '@start_date' and '@end_date'
with
AND
es.work_date between @start_date and @end_date
By putting the quotes, you made those literal string values, not parameters and SQL was trying to convert the string '@start_date' to datetime, which is clearly not going to work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2012 at 10:45 am
GilaMonster (2/20/2012)
Replace
ANDes.work_date between '@start_date' and '@end_date'
with
ANDes.work_date between @start_date and @end_date
By putting the quotes, you made those literal string values, not parameters and SQL was trying to convert the string '@start_date' to datetime, which is clearly not going to work.
Thank you so much, it works perfectly now.
February 20, 2012 at 1:20 pm
Now how do I get this one to execute: Its same thing, but different code.
It works, but not executable:
Msg 4104, Level 16, State 1, Procedure WEB_SO_GetEmployeeOrgSchedule, Line 100
The multi-part identifier "u.unit_description" could not be bound.
Msg 4104, Level 16, State 1, Procedure WEB_SO_GetEmployeeOrgSchedule, Line 100
The multi-part identifier "sh.shift_description" could not be bound.
USE [Version500_Model]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================
-- Author: Steven Liberman
-- Create Date: February 9, 2012
-- Description: Getting Employee Schedule for 1 week
-- select * from employee
-- select * from emp_schedule
-- select * from units
-- select * from shifts
-- exec WEB_SO_GetEmployeeOrgSchedule '2','2011-01-01','2011-01-28'
-- ===================================
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 = 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 e.emp_id
FROM employee as e
joinemp_schedule as s
one.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 week_num = ( DATEDIFF(DAY, @start_sked,
@date) / 7 + 1 ),
e.emp_name, work_date, u.unit_description, s.shift_id, sh.shift_description
from employee as e join emp_schedule as s on
e.emp_id = s.emp_id
join
units as u
on
s.unit_id = u.unit_id
join
shifts as 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 week_num = (DATEDIFF(DAY, @start_sked,
@date) / 7 + 1 ),
work_date = @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),
date = dt,
( SELECT week_num,
work_date,
u.unit_description,
s.shift_id,
sh.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 as e
join emp_schedule as s
on e.emp_id = s.emp_id
WHERE emp_organization_id = @org_level_id
FOR XML RAW('e'),
TYPE
END
February 20, 2012 at 1:30 pm
You have this code towards the bottom
SELECT week_num, work_date, u.unit_description, s.shift_id, sh.shift_description
FROM #GET_ORG_SKED
WHERE work_date = d.dt
There are columns aliased u, s and sh in the select, but the only table is #GET_ORG_SKED
p.s. that doesn't look particularly optimal, the loops to start with...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2012 at 1:40 pm
GilaMonster (2/20/2012)
You have this code towards the bottom
SELECT week_num, work_date, u.unit_description, s.shift_id, sh.shift_descriptionFROM #GET_ORG_SKED
WHERE work_date = d.dt
There are columns aliased u, s and sh in the select, but the only table is #GET_ORG_SKED
p.s. that doesn't look particularly optimal, the loops to start with...
What fix would you recommend to get this code working? Just asking.
February 20, 2012 at 1:56 pm
njdevils39 (2/20/2012)
GilaMonster (2/20/2012)
You have this code towards the bottom
SELECT week_num, work_date, u.unit_description, s.shift_id, sh.shift_descriptionFROM #GET_ORG_SKED
WHERE work_date = d.dt
There are columns aliased u, s and sh in the select, but the only table is #GET_ORG_SKED
p.s. that doesn't look particularly optimal, the loops to start with...
What fix would you recommend to get this code working? Just asking.
I think the recommendation would be to remove the aliases before the columns (assuming that the columns are in this temporary table) or to add the tables and aliases needed. Are you not familiar with aliases? With all this code I have to assume you know what aliases are.
Jared
CE - Microsoft
February 20, 2012 at 2:03 pm
SQLKnowItAll (2/20/2012)
njdevils39 (2/20/2012)
GilaMonster (2/20/2012)
You have this code towards the bottom
SELECT week_num, work_date, u.unit_description, s.shift_id, sh.shift_descriptionFROM #GET_ORG_SKED
WHERE work_date = d.dt
There are columns aliased u, s and sh in the select, but the only table is #GET_ORG_SKED
p.s. that doesn't look particularly optimal, the loops to start with...
What fix would you recommend to get this code working? Just asking.
I think the recommendation would be to remove the aliases before the columns (assuming that the columns are in this temporary table) or to add the tables and aliases needed. Are you not familiar with aliases? With all this code I have to assume you know what aliases are.
OK I deleted the aliases and it seems to work, bit I get this statement once executing it:
Msg 7119, Level 16, State 1, Procedure WEB_SO_GetEmployeeOrgSchedule, Line 100
Attempting to grow LOB beyond maximum allowed size of 2,147,483,647 bytes.
February 20, 2012 at 2:12 pm
Then there is something seriously wrong with the XML generation code (which piece I can't tell), it's generated a piece of XML over 2GB in size. I think you might want to break it down and maybe consider rewriting it, I highly doubt you are intending to generate that large a piece of XML.
Possibly the recursion in the CTE went too far, maybe it just needs to be limited a little more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2012 at 2:20 pm
GilaMonster (2/20/2012)
Then there is something seriously wrong with the XML generation code (which piece I can't tell), it's generated a piece of XML over 2GB in size. I think you might want to break it down and maybe consider rewriting it, I highly doubt you are intending to generate that large a piece of XML.Possibly the recursion in the CTE went too far, maybe it just needs to be limited a little more.
What kind of code would you recommend though, if you had any recommendations. I can try one.
February 20, 2012 at 2:24 pm
I recommend looking at it bit by bit and working out if it's doing what you want or not.
I haven't got the vaguest clue what that is supposed to be doing, and without a few hours to study it, I'm unlikely to get such a clue. I assume you wrote this code, so you have a much better understanding of what it should be doing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2012 at 2:25 pm
njdevils39 (2/20/2012)
GilaMonster (2/20/2012)
Then there is something seriously wrong with the XML generation code (which piece I can't tell), it's generated a piece of XML over 2GB in size. I think you might want to break it down and maybe consider rewriting it, I highly doubt you are intending to generate that large a piece of XML.Possibly the recursion in the CTE went too far, maybe it just needs to be limited a little more.
What kind of code would you recommend though, if you had any recommendations. I can try one.
I think Gail and others would recommend SQL 🙂 Ok, just kidding... What exactly are you asking? How to get rid of the loops? How to change the xml?
Can you give us DDL for the tables being used and then some sample data and expected output? That is the place to start. Also, I have to ask... Did you write this or are you trying to build on someone else's code? Maybe if you did not write this, it is best to start from scratch. If you did, then you should be able to help us with the questions we have.
Jared
CE - Microsoft
February 20, 2012 at 2:28 pm
SQLKnowItAll (2/20/2012)
I think Gail and others would recommend SQL 🙂
XML generation and parsing is one area I would actually recommend C# if at all possible. Probably not appropriate in this case though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2012 at 2:31 pm
GilaMonster (2/20/2012)
SQLKnowItAll (2/20/2012)
I think Gail and others would recommend SQL 🙂XML generation and parsing is one area I would actually recommend C# if at all possible. Probably not appropriate in this case though.
I tried removing the XML's and it is not even altering the procedure. It seems like its a memory thing now, where if I can crunch it to something it should work.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply