a special upgrade

• antonio arciga

  SSC-Addicted

  Points: 463

  More actions

  September 9, 2009 at 8:40 am

  #385330

  create table [dbo].[discounts]

  ([idcust] [int],

  [pfizer] [int],

  [bayer] [int],

  [pharma] [int],

  [jhonson] [int],

  [glaxo] [int],

  [hoffmann] [int],

  [sanofi] [int],

  [novartis] [int],

  [astra] [int],

  [abbott] [int],

  [merck] [int],

  [bristol] [int],

  [eli] [int],

  [amgen] [int],

  [boehringer] [int],

  [baxter] [int],

  [takeda] [int],

  [genentech] [int],

  [procter] [int],

  [teva] [int],

  [astellas] [int],

  [daiichi] [int],

  [novo] [int],

  [eisai] [int],

  [alcon] [int],

  [akzo] [int],

  [nycomed] [int],

  [solvay] [int],

  [gilead] [int],

  [biogen] [int],

  [shire] [int],

  [cephalon] [int],

  [mylan] [int],

  )

  insert into discounts (

  [idcust],

  [pfizer],

  [bayer],

  [pharma],

  [jhonson],

  [glaxo],

  [hoffmann],

  [sanofi],

  [novartis],

  [astra],

  [abbott],

  [merck],

  [bristol],

  [eli],

  [amgen],

  [boehringer],

  [baxter],

  [takeda],

  [genentech],

  [procter],

  [teva],

  [astellas],

  [daiichi],

  [novo],

  [eisai],

  [alcon],

  [akzo],

  [nycomed],

  [solvay],

  [gilead],

  [biogen],

  [shire],

  [cephalon],

  [mylan]

  )

  select '819','12','23','17','13','20','14','11','13','22','24','16','23','12','18','28','13','12','22','24','14','22','12','21','13','13','11','18','22','18','21','11','14','12' union all

  select '382','15','21','22','18','22','13','15','12','23','23','25','22','15','12','29','12','12','22','24','12','23','11','20','12','14','15','17','23','17','22','16','17','15' union all

  select '590','10','22','18','23','25','12','14','11','28','22','21','21','10','13','25','13','13','22','25','13','28','11','18','11','13','14','16','21','16','22','16','16','10' union all

  select '937','11','21','21','20','22','11','12','12','20','25','18','22','11','17','28','14','13','22','24','12','20','12','19','12','18','12','15','22','15','21','15','17','11' union all

  select '412','17','22','25','13','23','12','13','13','25','22','12','21','17','23','27','13','12','22','23','14','25','10','21','13','19','13','14','23','15','22','13','21','17' union all

  select '128','10','23','16','18','24','13','12','14','23','20','13','23','10','12','29','16','15','22','22','15','23','11','22','14','12','12','13','20','13','21','12','19','10'

  create table [dbo].[sales]

  ([idcust] [int],

  [item] [int],

  [idlab] char(15),

  [qty] [int],

  [price] [money],

  [dsc] [int]

  )

  insert into sales ([idcust],[item],[idlab],[qty],[price],[dsc])

  select '128','2234','mylan','2',11.90,'0' union all

  select '128','9067','sanofi','4',4.23,'0' union all

  select '128','8623','pfizer','1',12.956,'0' union all

  select '128','4428','amgen','6',23.51,'0' union all

  select '590','4398','bristol','3',8.54,'0' union all

  select '590','3390','shire','9',2.78,'0' union all

  select '590','2235','mylan','3',12.33,'0' union all

  select '590','4429','amgen','8',31.12,'0' union all

  select '590','2190','baxter','2',17.94,'0' union all

  select '590','8656','glaxo','9',2.78,'0' union all

  select '590','1901','pharma','3',6.22,'0' union all

  select '590','9330','alcon','5',15.65,'0' union all

  select '412','2234','mylan','9',11.90,'0' union all

  select '412','4428','amgen','3',23.51,'0' union all

  select '412','3390','shire','1',2.78,'0' union all

  select '412','2235','mylan','5',12.33,'0' union all

  select '412','1901','pharma','8',6.22,'0' union all

  select '412','2121','cephalon','3',17.28,'0' union all

  select '412','1010','bayer','4',14.23,'0' union all

  select '412','9789','biogen','7',23.28,'0' union all

  select '412','4428','amgen','3',23.51,'0'

  I need updgrade 'dsc' column in 'sales' table from 'discounts' table.

  The columns [idcust],[pfizer],[bayer],[pharma],[jhonson],[glaxo],[hoffmann] ...... [mylan] in table 'discounts'

  contains an amount that applies to column 'dsc' in table sales according each idcust and idlab column

  Before the upgrade

  select idcust,item,desc,idlab from sales

  the result in 'sales' table must be:

  128 2234 10 mylan

  128 9067 14 sanofi

  128 8623 10 pfizer

  128 4428 29 amgen

  590 4398 21 bristol

  590 3390 16 shire

  590 2235 10 mylan

  590 4429 25 amgen

  590 2190 13 baxter

  590 8656 25 glaxo

  590 1901 18 pharma

  590 9330 13 alcon

  412 2234 17 mylan

  412 4428 23 amgen

  412 3390 13 shire

  412 2235 17 mylan

  412 1901 25 pharma

  412 2121 21 cephalon

  412 1010 23 bayer

  412 9789 22 biogen

  412 4428 23 amgen

  could you help me

  thanks

• September 10, 2009 at 4:30 am

  This was removed by the editor as SPAM

• September 10, 2009 at 7:32 am

  This was removed by the editor as SPAM