troubleshooting Question

sql syntax create a tempory table from two others

Avatar of basilhs_s
basilhs_s asked on
Microsoft SQL Server 2005SQL
6 Comments1 Solution188 ViewsLast Modified:
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?.


 
   
ASKER CERTIFIED SOLUTION
saji1976

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros