SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Can I do this when inserting into a table?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rasta Pickles
Posting Yak Master

United Kingdom
150 Posts
Posted - 08/01/2012 :  13:37:44  Show Profile  Reply with Quote 

INSERT INTO staging2 ([Date worked], ItemId, ItemTypeId, [Hours Worked], [Employee Name], [Type of Work], PConcatex, CConcatex, Parent, Rate)
SELECT [Date worked]
	  ,ItemId
	  ,ItemTypeId
	  ,CONVERT(DECIMAL(12,2),[Hours Worked])
	  ,[Employee Name]
	  ,[Type of Work]
	  ,ST.PConcatex
	  ,ST.CConcatex
	  ,CASE WHEN ItemTypeId IN (1) THEN ST.ItemId
	   WHEN ItemTypeId IN (0, 2, 3) THEN APT.CRID 
	   END AS Parent
	  ,CASE WHEN [Date worked] > @newrate THEN 'NewRate' ELSE 'OldRate' END AS Rate 
FROM staging ST INNER JOIN
Reporting.dbo.annpremtest APT ON ST.CConcatex = APT.CConcatex


@newrate is a date.

The reason I'm asking is that I'm getting some output with 'OldRate' assigned when I know that only 'NewRate' should apply.

Can anyone see anything wrong with what I'm trying to do?

TIA.
Edited by - Rasta Pickles on 08/01/2012 13:39:28

webfred
Flowing Fount of Yak Knowledge

Germany
8529 Posts
Posted - 08/01/2012 :  14:14:50  Show Profile  Visit webfred's Homepage  Reply with Quote
Do the SELECT without inserting and add [Date worked] as a column so you can see what happened.

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8529 Posts
Posted - 08/01/2012 :  14:16:59  Show Profile  Visit webfred's Homepage  Reply with Quote
I see now: it is already the first column. So what is the value of [Date worked] in that cases where you mean the value should not be 'Oldrate'?
Also add your variable as a column to see the value.

No, you're never too old to Yak'n'Roll if you're too young to die.
Edited by - webfred on 08/01/2012 14:18:27
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
150 Posts
Posted - 08/01/2012 :  14:21:45  Show Profile  Reply with Quote
@newrate = '31 October 2011'

What comes out of the staging table is in dd/mm/yyyy hh:mm:ss format.

Could that be skewing things or is SQL smart enough to know that 31 October 2011 is the same as 31/10/2011?
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8529 Posts
Posted - 08/01/2012 :  14:26:53  Show Profile  Visit webfred's Homepage  Reply with Quote
you should set the var to '20111031' that will be save. Additional the declare of @newrate should be datetime otherwise the query will do an implicit conversion from '20111031' to datetime with timepart set to zero.

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
150 Posts
Posted - 08/01/2012 :  14:34:15  Show Profile  Reply with Quote 

DECLARE @newrate VARCHAR(30)
SET @newrate = '31 October 2011'


So that's not right then?
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8529 Posts
Posted - 08/01/2012 :  14:36:03  Show Profile  Visit webfred's Homepage  Reply with Quote
In most cases - no.
Have a look at your staging table. What is the data type of that column?

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
150 Posts
Posted - 08/02/2012 :  04:05:55  Show Profile  Reply with Quote
varchar(30)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000