Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Rasta Pickles
Posting Yak Master
174 Posts |
 Posted - 2012-08-01 : 13:37:44
|
| [code]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 JOINReporting.dbo.annpremtest APT ON ST.CConcatex = APT.CConcatex[/code]@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. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-08-01 : 14:14:50
|
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. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-08-01 : 14:16:59
|
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. |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-08-01 : 14:21:45
|
| @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? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-08-01 : 14:26:53
|
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. |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-08-01 : 14:34:15
|
| [code]DECLARE @newrate VARCHAR(30)SET @newrate = '31 October 2011'[/code]So that's not right then? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-08-01 : 14:36:03
|
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. |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-08-02 : 04:05:55
|
| varchar(30) |
|
|
|
|
|
|
|
|
Can I do this when inserting into a table?