March 26, 2012 at 2:10 pm
OK here is the XML FILE AND MY STORED PROCEDURE WHICH sort off works, just need to clean up the return values
<Rates>
<Rate date_changed="Nov 10 2011 7:28AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="0" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<Rate date_changed="Nov 10 2011 7:19AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="40" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<Rate date_changed="Nov 10 2011 6:38AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="0" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<Rate date_changed="Nov 10 2011 6:33AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="40" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>
ALTER PROCEDURE [dbo].[WEB_HR_GetRateHistory]
(
@ORG_LEVEL_ID INT,
@EMP_ID INT
)
AS
BEGIN
declare @xmlDoc varchar(max);
DECLARE @x XML;
CREATE TABLE #RATE_HISTORY
(EMP_NAME VARCHAR(255), EMP_ORGANIZATION VARCHAR(255), EMP_DEPARTMENT VARCHAR(255), EMP_DT_TERMINATION DATETIME, DATE_CHANGED DATETIME,
FROM_PAY_RATE INT, TO_PAY_RATE INT)
INSERT INTO #RATE_HISTORY
(EMP_NAME , EMP_ORGANIZATION , EMP_DEPARTMENT , EMP_DT_TERMINATION, DATE_CHANGED, FROM_PAY_RATE
, TO_PAY_RATE)
SELECT e.emp_name,
e.emp_organization,
e.emp_department,
e.emp_dt_termination,
DATE_CHANGED = (select max(convert(varchar(10), x.r.value('@date_changed','DATETIME'), 101)) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)),
FROM_PAY_RATE = (select min(x.r.value('@rate','DECIMAL(10,5)')) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)),
TO_PAY_RATE = (select max(x.r.value('@rate','DECIMAL(10,5)')) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r))
FROM employee e
JOIN employee_other eo ON e.emp_id = eo.emp_id
where e.emp_id = @emp_id and (emp_dt_termination is NULL) and e.emp_organization_id = @ORG_LEVEL_ID
select * from #RATE_HISTORY
END
So, I need for that single employee with that XML Field to be like this below:
ITS Giving me 0 as FROM PAY_RATE and 15209 in TO_PAY_RATE and the wrong date.
DATE_CHANGED = 'Nov 10 2011'
FROM_PAY_RATE = '1730.77'
TO_PAY_RATE = '1720.78'
March 26, 2012 at 2:12 pm
How about the DDL (CREATE TABLE statement(s)) for the base table(s) in the stored procedure, sample data for the base table(s) (as a series of INSERT INTO statements), and the expected results based on the sample data.
Not much we can do without this additional information. If you really want help, help us help you.
March 26, 2012 at 2:16 pm
BROKEN RECORD!!! You continue to ignore the most basic and simple request. Are you a troll looking to be annoying or are you seriously unable to read and comprehend basic english? Given that you type in totally non broken english I assume you are either a native speaker or at least extremely fluent.
The insert statement you keep posting is where you have a problem. Nobody can help because you refuse to post ddl and sample data.
If you just post those two things you will get an answer. It will be tested, fast and reliable. If you continue to ignore the request you will receive exactly what you posted...nothing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 26, 2012 at 2:22 pm
Just to add, it doesn't even need to be the entire table(s). You can reduce the table(s) to the columns used in stored procedure and the key (index) columns. The sample data needs to be just that sample data. Something the represents the problem domain without being real data, as long as part of it also relates to the XML data you have been providing.
March 26, 2012 at 3:28 pm
Lynn Pettis (3/26/2012)
Just to add, it doesn't even need to be the entire table(s). You can reduce the table(s) to the columns used in stored procedure and the key (index) columns. The sample data needs to be just that sample data. Something the represents the problem domain without being real data, as long as part of it also relates to the XML data you have been providing.
No the stored procedure works perfectly as I used the CROSS APPLY function for the XML.
It just is not returning the right values in those columns I have mentioned. It just needs to return those values I mentioned in the input parameters.
March 26, 2012 at 3:31 pm
njdevils39 (3/26/2012)
Lynn Pettis (3/26/2012)
Just to add, it doesn't even need to be the entire table(s). You can reduce the table(s) to the columns used in stored procedure and the key (index) columns. The sample data needs to be just that sample data. Something the represents the problem domain without being real data, as long as part of it also relates to the XML data you have been providing.No the stored procedure works perfectly as I used the CROSS APPLY function for the XML.
It just is not returning the right values in those columns I have mentioned. It just needs to return those values I mentioned in the input parameters.
If I run the following code:
INSERT INTO #RATE_HISTORY
(EMP_NAME , EMP_ORGANIZATION , EMP_DEPARTMENT , EMP_DT_TERMINATION, DATE_CHANGED, FROM_PAY_RATE
, TO_PAY_RATE)
SELECT e.emp_name,
e.emp_organization,
e.emp_department,
e.emp_dt_termination,
DATE_CHANGED = (select max(convert(varchar(10), x.r.value('@date_changed','DATETIME'), 101)) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)),
FROM_PAY_RATE = (select min(x.r.value('@rate','DECIMAL(10,5)')) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)),
TO_PAY_RATE = (select max(x.r.value('@rate','DECIMAL(10,5)')) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r))
FROM employee e
JOIN employee_other eo ON e.emp_id = eo.emp_id
where e.emp_id = @emp_id and (emp_dt_termination is NULL) and e.emp_organization_id = @ORG_LEVEL_ID
I get nothing. I don't have the tables employee or employee_other, nor do I have any data for those tables. How are we supposed to help you if we can't even run your code?
March 26, 2012 at 3:47 pm
im amazed at your patience sean. im sitting here laughing. ill save you at least one time.
We need the DDL and sample data. im supprised you are still getting responses as i would have give up after 3 or 4 times asking for the same information. just read the links and post the requested information. you will have an answer with in 15 min of doing so. (ok maby not 15 min but much faster than 70 posts)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 26, 2012 at 3:59 pm
capn.hector (3/26/2012)
im amazed at your patience sean. im sitting here laughing. ill save you at least one time.We need the DDL and sample data. im supprised you are still getting responses as i would have give up after 3 or 4 times asking for the same information. just read the links and post the requested information. you will have an answer with in 15 min of doing so. (ok maby not 15 min but much faster than 70 posts)
DDL:
CREATE TABLE EMPLOYEE
(
EMP_NAME VARCHAR(MAX),
EMP_ORGANIZATION VARCHAR(MAX),
EMP_DEPARTMENT VARCHAR(MAX),
EMP_DT_TERMINATION DATETTIME
)
CREATE TABLE EMPLOYEE_OTHER
(
RATE_HISTORY XML
)
Sample Data:
Insert into Employee
(EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT, EMP_DT_TERMINATION)
(SELECT EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT, EMP_DT_TERMINATION FROM
EMPLOYEE)
INSERT INTO EMPLOYEE_OTHER
(RATE_HISTORY)
(SELECT RATE_HISTORY FROM EMPLOYEE_OTHER)
DO NOT KNOW IF THIS HELPS.
March 26, 2012 at 4:01 pm
Yeah I first requested ddl and sample data for employee and employee_other in post #5 of this thread. I am now writing post #68 and still asking for the same thing. For whatever reason the OP is absolutely refusing to post the most basic thing. I know that njdevils is probably so frustrated that he/she can't see straight. I am sure they can't possibly understand why it is so difficult not to just tell them what they are asking because we are the sql gurus right? I (and several of my other threadizens) have repeatedly asked over and over and over for something would take the OP about 10 minutes to put together. However we are 2 1/2 weeks into this and the information requested more than 60 posts ago has still not been provided. I appreciate the feedback about my patience level. At this point it goes way beyond patience. It is almost now a study how absolutely stubborn and pig headed an individual can be about not providing details to a problem they have requested to have help on. This thread may end up serving as the epitome of obstinate refusal to provide enough information to solve a problem.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 26, 2012 at 4:03 pm
njdevils39 (3/26/2012)
capn.hector (3/26/2012)
im amazed at your patience sean. im sitting here laughing. ill save you at least one time.We need the DDL and sample data. im supprised you are still getting responses as i would have give up after 3 or 4 times asking for the same information. just read the links and post the requested information. you will have an answer with in 15 min of doing so. (ok maby not 15 min but much faster than 70 posts)
DDL:
CREATE TABLE EMPLOYEE
(
EMP_NAME VARCHAR(MAX),
EMP_ORGANIZATION VARCHAR(MAX),
EMP_DEPARTMENT VARCHAR(MAX),
EMP_DT_TERMINATION DATETTIME
)
CREATE TABLE EMPLOYEE_OTHER
(
RATE_HISTORY XML
)
Sample Data:
Insert into Employee
(EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT, EMP_DT_TERMINATION)
(SELECT EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT, EMP_DT_TERMINATION FROM
EMPLOYEE)
INSERT INTO EMPLOYEE_OTHER
(RATE_HISTORY)
(SELECT RATE_HISTORY FROM EMPLOYEE_OTHER)
DO NOT KNOW IF THIS HELPS.
the sample data should be like this.
INSERT INTO #EMPLOYEE -- temp table every one has
SELECT 'John Doe', 'some other stuff','some more stuff', 'then the rest' UNION ALL
SELECT 'John Paul', 'some other stuff','some more stuff', 'then the rest' UNION ALL
SELECT 'John Smith', 'some other stuff','some more stuff', 'then the rest' UNION ALL
SELECT 'Jane Doe', 'some other stuff','some more stuff', 'then the rest' UNION ALL
SELECT 'Jane Smith', 'some other stuff','some more stuff', 'then the rest' UNION ALL
SELECT 'Mark Ryan', 'some other stuff','some more stuff', 'then the rest'
that way we can run the inserts to the temp table and have data to play with.
EDIT:
It is almost now a study how absolutely stubborn and pig headed an individual can be about not providing details to a problem they have requested to have help on. This thread may end up serving as the epitome of obstinate refusal to provide enough information to solve a problem.
:w00t::-D:-D:-D
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 26, 2012 at 4:05 pm
njdevils39 (3/26/2012)
capn.hector (3/26/2012)
im amazed at your patience sean. im sitting here laughing. ill save you at least one time.We need the DDL and sample data. im supprised you are still getting responses as i would have give up after 3 or 4 times asking for the same information. just read the links and post the requested information. you will have an answer with in 15 min of doing so. (ok maby not 15 min but much faster than 70 posts)
DDL:
CREATE TABLE EMPLOYEE
(
EMP_NAME VARCHAR(MAX),
EMP_ORGANIZATION VARCHAR(MAX),
EMP_DEPARTMENT VARCHAR(MAX),
EMP_DT_TERMINATION DATETTIME
)
CREATE TABLE EMPLOYEE_OTHER
(
RATE_HISTORY XML
)
Sample Data:
Insert into Employee
(EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT, EMP_DT_TERMINATION)
(SELECT EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT, EMP_DT_TERMINATION FROM
EMPLOYEE)
INSERT INTO EMPLOYEE_OTHER
(RATE_HISTORY)
(SELECT RATE_HISTORY FROM EMPLOYEE_OTHER)
DO NOT KNOW IF THIS HELPS.
No this does not help this time...it did not help the last time you posted this completely useless script. How do you expect an insert to work that is selecting from itself? Look back several pages now and see the skeleton I provided.
Think about setting this up on another system and what information you might need.
I don't think you possess the ability to provide sample data. It is in the form of insert statements. Those statements can't be selecting from a table we don't have. They will have to be HARD CODED INSERTS.
I am still holding out hope that you might actually provide ddl and sample data before this thread breaks 100 posts.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 26, 2012 at 4:05 pm
njdevils39 (3/26/2012)
capn.hector (3/26/2012)
im amazed at your patience sean. im sitting here laughing. ill save you at least one time.We need the DDL and sample data. im supprised you are still getting responses as i would have give up after 3 or 4 times asking for the same information. just read the links and post the requested information. you will have an answer with in 15 min of doing so. (ok maby not 15 min but much faster than 70 posts)
DDL:
CREATE TABLE EMPLOYEE
(
EMP_NAME VARCHAR(MAX),
EMP_ORGANIZATION VARCHAR(MAX),
EMP_DEPARTMENT VARCHAR(MAX),
EMP_DT_TERMINATION DATETTIME
)
CREATE TABLE EMPLOYEE_OTHER
(
RATE_HISTORY XML
)
Sample Data:
Insert into Employee
(EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT, EMP_DT_TERMINATION)
(SELECT EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT, EMP_DT_TERMINATION FROM
EMPLOYEE)
INSERT INTO EMPLOYEE_OTHER
(RATE_HISTORY)
(SELECT RATE_HISTORY FROM EMPLOYEE_OTHER)
DO NOT KNOW IF THIS HELPS.
First of all, if I create the tables as you have them defined, your code fails as there is nothing to relate the employee table to the employee_other table.
Second, how do I populate the tables when the code you provided uses the tables themselves for the data?
March 26, 2012 at 4:14 pm
Sean Lange (3/26/2012)
Yeah I first requested ddl and sample data for employee and employee_other in post #5 of this thread. I am now writing post #68 and still asking for the same thing. For whatever reason the OP is absolutely refusing to post the most basic thing. I know that njdevils is probably so frustrated that he/she can't see straight. I am sure they can't possibly understand why it is so difficult not to just tell them what they are asking because we are the sql gurus right? I (and several of my other threadizens) have repeatedly asked over and over and over for something would take the OP about 10 minutes to put together. However we are 2 1/2 weeks into this and the information requested more than 60 posts ago has still not been provided. I appreciate the feedback about my patience level. At this point it goes way beyond patience. It is almost now a study how absolutely stubborn and pig headed an individual can be about not providing details to a problem they have requested to have help on. This thread may end up serving as the epitome of obstinate refusal to provide enough information to solve a problem.
No, I think it is a tie with the one I kept working at. Would have to look to see how many posts are in it, but just as stubborn when it comes to provided what was asked.
Edit: Okay, just checked the other one, only at 44 posts there.
March 26, 2012 at 4:50 pm
capn.hector (3/26/2012)
njdevils39 (3/26/2012)
capn.hector (3/26/2012)
im amazed at your patience sean. im sitting here laughing. ill save you at least one time.We need the DDL and sample data. im supprised you are still getting responses as i would have give up after 3 or 4 times asking for the same information. just read the links and post the requested information. you will have an answer with in 15 min of doing so. (ok maby not 15 min but much faster than 70 posts)
DDL:
CREATE TABLE EMPLOYEE
(
EMP_NAME VARCHAR(MAX),
EMP_ORGANIZATION VARCHAR(MAX),
EMP_DEPARTMENT VARCHAR(MAX),
EMP_DT_TERMINATION DATETTIME
)
CREATE TABLE EMPLOYEE_OTHER
(
RATE_HISTORY XML
)
Sample Data:
Insert into Employee
(EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT, EMP_DT_TERMINATION)
(SELECT EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT, EMP_DT_TERMINATION FROM
EMPLOYEE)
INSERT INTO EMPLOYEE_OTHER
(RATE_HISTORY)
(SELECT RATE_HISTORY FROM EMPLOYEE_OTHER)
DO NOT KNOW IF THIS HELPS.
the sample data should be like this.
INSERT INTO #EMPLOYEE -- temp table every one has
SELECT 'John Doe', 'some other stuff','some more stuff', 'then the rest' UNION ALL
SELECT 'John Paul', 'some other stuff','some more stuff', 'then the rest' UNION ALL
SELECT 'John Smith', 'some other stuff','some more stuff', 'then the rest' UNION ALL
SELECT 'Jane Doe', 'some other stuff','some more stuff', 'then the rest' UNION ALL
SELECT 'Jane Smith', 'some other stuff','some more stuff', 'then the rest' UNION ALL
SELECT 'Mark Ryan', 'some other stuff','some more stuff', 'then the rest'
that way we can run the inserts to the temp table and have data to play with.
EDIT:
It is almost now a study how absolutely stubborn and pig headed an individual can be about not providing details to a problem they have requested to have help on. This thread may end up serving as the epitome of obstinate refusal to provide enough information to solve a problem.
:w00t::-D:-D:-D
INSERT INTO #EMPLOYEE -- temp table every one has
SELECT 'Whitehead, Molly ', 'Silver Manor,Administrative', 'NULL' UNION ALL
SELECT 'Gentry, Clinton A ', Silver Manor','Administrative', 'NULL' UNION ALL
SELECT 'Crosby, Sherman', 'Silver Manor','Administrative', 'NULL' UNION ALL
SELECT 'Riddle, Nichole', 'Silver Manor','Maintenance', 'NULL'
FROM EMPLOYEE
INSERT INTO #EMPLOYEE_OTHER
SELECT '<Rates>
<Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>' UNION ALL
SELECT '<Rates>
<Rate date_changed="Apr 5 2009 12:00AM" not_active_since="04/05/2009" rate="11.73" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="11.44" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>' UNION ALL
SELECT '<Rates>
<Rate date_changed="Feb 22 2009 12:00AM" not_active_since="02/22/2009" rate="17.43" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>' UNION ALL
SELECT '<Rates>
<Rate date_changed="Feb 8 2009 12:00AM" not_active_since="02/08/2009" rate="14.00" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>'
FROM EMLOYEE_OTHER
NOTE: USE THE DDL FROM ABOVE
[/code]
March 26, 2012 at 5:26 pm
and the award for most creative use of IFCode shortcuts goes to....
I can't even begin to make sense of the XML test data insert, but I cleaned up the Employee fake data so it might be used, it was provided as:
INSERT INTO #EMPLOYEE -- temp table every one has
SELECT 'Whitehead, Molly ', 'Silver Manor,Administrative', 'NULL' UNION ALL
SELECT 'Gentry, Clinton A ', Silver Manor','Administrative', 'NULL' UNION ALL
SELECT 'Crosby, Sherman', 'Silver Manor','Administrative', 'NULL' UNION ALL
SELECT 'Riddle, Nichole', 'Silver Manor','Maintenance', 'NULL'
FROM EMPLOYEE
Also - changed DATETTIME to DATETIME in the CREATE table statement:
CREATE TABLE #EMPLOYEE (
EMP_NAME VARCHAR(MAX),
EMP_ORGANIZATION VARCHAR(MAX),
EMP_DEPARTMENT VARCHAR(MAX),
EMP_DT_TERMINATION DATE DATETIME
)
INSERT INTO #EMPLOYEE
SELECT 'Whitehead, Molly ', 'Silver Manor', 'Administrative', NULL UNION ALL
SELECT 'Gentry, Clinton A', 'Silver Manor', 'Administrative', NULL UNION ALL
SELECT 'Crosby, Sherman', 'Silver Manor', 'Administrative', NULL UNION ALL
SELECT 'Riddle, Nichole', 'Silver Manor', 'Maintenance', NULL
OP might want to observe that RED colored text in the SSMS query editor indicates string. If you see red where it doesn't belong, you have misplaced single quotes. As well, you probably don't want to enclose the value (or rather non value) NULL in quotes, particularly as you are inserting the value into a datetime column. Just sayin... :Whistling:
Viewing 15 posts - 61 through 75 (of 194 total)
You must be logged in to reply to this topic. Login to reply