December 14, 2006 at 3:22 pm
Hi,
I have 2 problems and thought of seeking help here..
I have a table called InvMngt and it has tons of records.some of it came from excel sheets, some came from notepads and it also gets updated daily from a oracle source.
fields in the table are:
productid nvarchar(20) (PK)
[Date] nvarchar(10)
Qty int(4)
userid nvarchar(10)
sample data:
ProductID Date Qty userid
100A 2006-07-16 10 fromnotepad
100A 1/1/2006 10 excelimport
100A 1/1/2006 20 excelimport
100A 2006-11-25 10 fromoracle
100A 2006-11-26 2 fromoracle
now, my questions are:
a.I need to put a negative value to the qty that comes from userid "fromoracle".
b.I also need to correct the date values of "1/1/2006" to the format "2006-01-01".
Your help is appreciated.
Thanks,
December 14, 2006 at 3:40 pm
declare @table table (
productid nvarchar(20),
[Date] nvarchar(10) ,
Qty int,
userid nvarchar(20)
)
insert into @table
select '100A', '2006-07-16', 10, 'fromnotepad' union all
select '100A', '1/1/2006', 10, 'excelimport' union all
select '100A', '1/1/2006', 20, 'excelimport' union all
select '100A', '2006-11-25', 10, 'fromoracle' union all
select '100A', '2006-11-26', 2, 'fromoracle'
select * from @table
update @table
set Qty = -1
where userid = 'fromoracle'
update @table
set [date] = CONVERT(varchar(10),CAST([date] as datetime),20)
select * from @table
December 14, 2006 at 3:54 pm
Thanks John..the update for date worked perfect..the one for quantity failed..but made a small change to ur query and it worked fine too..
i modified it as
update @table
set Qty = (Qty * -1)
where userid = 'fromoracle'
Thanks a lot.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply