Link to home
Create AccountLog in
Avatar of basilhs_s
basilhs_s

asked on

sql syntax create a tempory table from two others

i am having the following tables
table_m
  tm_date_m                       datetime
  tm_number_m                  int
  tm_item_code                  nvarchar(10)
  tm_item_qty                     decimal(18,3)
  tm_item_val                     decimal(18,2)
  (primary key = (tm_date_m,tm_number_m)

i am having the following tables
table_p
  tp_date_m                      datetime
  tp_number_m                  int
  tp_number_p                   int
  tp_item_code                  nvarchar(10)
  tp_part_number              nvarchar(10)
  tp_item_qty                     decimal(18,3)
  (primary key = (tp_date_m,tp_number_m, tp_number_p)

in table_m we store all the transactions of a warehouse. in table_p we store the transactions that deal with items which have part numbers - this means that each record in table_p is stored in table_m also (but not vice versa) - tp_date_m = tm_date_m and tp_number_m = tp_number_p

we want to create a third table table_t as follows
table_t
  tt_date_m                      datetime
  tt_number_m                  int
  tt_number_p                   int
  tt_item_code                  nvarchar(10)
  tt_part_number              nvarchar(10)
  tt_item_qty                     decimal(18,3)
  tt_item_val                      decimal(18,2)

tablle_t should contain all the records of table_p.
  tt_item_val = tt_item_qty * tm_item_val / tm_item_qty.

it should contain also those records of table_m that do not have entries in table_p. in this case tt_number_p = 1 and tt_part_number = ''

i am using sql server 2000.
how can i do it?.


 
   
Avatar of Sydknee
Sydknee

select tp_date_m as tt_date_m
        , tp_number_m as tt_number_m
        , tp_number_p as tt_number_p
        , tp_item_code as tt_item_code
        , tp_part_number as tt_part_number
        , tp_item_qty as tt_item_qty
        , case when tm_item_qty = 0 then null else tt_item_qty * tm_item_val / tm_item_qty end as tt_item_val
into #table_t
from table_p left outer join table_m
   on tm_date_m = tp_date_m
  and tm_number_m = tp_number_m
ASKER CERTIFIED SOLUTION
Avatar of saji1976
saji1976

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of basilhs_s

ASKER

this selects shows correctly the records of table_p. it does not retrieve those records of table_m that do not have any entry in table_m
this selects shows correctly the records of table_p. it does not retrieve those records of table_m that do not have any entry in table_p
The right outer join should show all the recrods from Table_M even if there are no records in table_P. There is some special characters appearing in my query, please remove them and try.
thanks very much