SQLTeam.com | Weblogs | Forums

SQL Server Lookup Value

I am a newbie to Sql Server and only know simple Select and Join statements.

1 have 3 tables: Table1 is Sales Data, Table2 is Item Master and Table3 shows the different UOMs for each Item as follows:

Table1
Invoice ItemCode UOM Qty
1001 A1234 Tray 5
1002 A1234 Pc 23
1003 A1235 Carton 10
1004 A1235 Box 12
1005 A1235 Pc 28

Table2
ItemCode ReportUOM
A1234 Tray
A1235 Carton

Table3
ItemCode UOM Rate
A1234 Pc 1
A1234 Tray 12
A1235 Pc 1
A1235 Box 8
A1235 Carton 96

Remarks: Item A1234: 1 Tray = 12 Pc and A1235: 1 Carton = 12 Box = 12 x 8 Pc/Box = 96 Pc

I need help to use SQL Server to extract data as below:

Desired Output
Invoice ItemCode ReportUOM Qty
1001 A1234 Tray 5
1002 A1234 Tray 1.916666667
1003 A1235 Carton 10
1004 A1235 Carton 1
1005 A1235 Carton 0.291666667

Thank you in advance for your help!

Joseph

HI

i tried to understand and do this ..

please click arrow to the left for DROP CREATE data Script
DROP TABLE TABLE1
CREATE TABLE TABLE1
(
Invoice INT,  ItemCode VARCHAR(100), UOM VARCHAR(100), Qty INT
)

INSERT INTO TABLE1 SELECT 1001,'A1234','Tray', 5
INSERT INTO TABLE1 SELECT 1002,'A1234','Pc', 23
INSERT INTO TABLE1 SELECT 1003,'A1235','Carton', 10
INSERT INTO TABLE1 SELECT 1004,'A1235','Box', 12
INSERT INTO TABLE1 SELECT 1005,'A1235','Pc', 28
GO 
SELECT 'TABLE 1 DATA',* FROM TABLE1
GO 

DROP TABLE TABLE2
CREATE TABLE TABLE2
(
ItemCode VARCHAR(100) , ReportUOM VARCHAR(100)
)
GO 
INSERT INTO TABLE2 SELECT 'A1234','Tray'
INSERT INTO TABLE2 SELECT 'A1235','Carton'
GO 
SELECT 'TABLE 2 DATA',* FROM TABLE2

DROP TABLE TABLE3
CREATE TABLE TABLE3
(
ItemCode VARCHAR(100) , UOM  VARCHAR(100)  , Rate INT
)
GO 

INSERT INTO TABLE3 SELECT 'A1234', 'Pc', 1
INSERT INTO TABLE3 SELECT 'A1234', 'Tray', 12
INSERT INTO TABLE3 SELECT 'A1235', 'Pc', 1
INSERT INTO TABLE3 SELECT 'A1235', 'Box', 8
INSERT INTO TABLE3 SELECT 'A1235', 'Carton', 96
GO 

SELECT 'TABLE 3 DATA', * FROM TABLE3
go 

DROP TABLE DesiredOutput
GO 

CREATE TABLE DesiredOutput
(
Invoice int,  ItemCode VARCHAR(100), ReportUOM VARCHAR(100), Qty DECIMAL(20,8)
)
GO 
INSERT INTO DESIREDOUTPUT SELECT 1001, 'A1234', 'Tray', 5
INSERT INTO DESIREDOUTPUT SELECT 1002, 'A1234', 'Tray', 1.916666667
INSERT INTO DESIREDOUTPUT SELECT 1003, 'A1235', 'Carton', 10
INSERT INTO DESIREDOUTPUT SELECT 1004, 'A1235', 'Carton', 1
INSERT INTO DESIREDOUTPUT SELECT 1005, 'A1235', 'Carton', 0.291666667
GO 
SELECT 'DesiredOutput ', * FROM DesiredOutput

something seems wrong !!! the output ..
please explain what you are trying to do for example
!!!

Thank you harishgg1 for your reply.

Taking ItemCode A1234 as an example. ReportUOM for this item is in Tray and 1 Tray = 12 Pc (Table2). Invoice 1001 is already expressed in Tray, so no conversion is needed. Invoice 1002 for the same item is in Pc, so we convert it to Tray by dividing the Qty in Pc by 12, that gives us 23/12 = 1.91667.

hi

i got what you are saying .. i will do the SQL next

i have a real interest in making things real easy and very simple for people to understand
-- here is my attempt

1 Like

That's right, harishgg1! Thank you!

And, the reason for conversion to a single Report UOM is so that we can add up the total qty sold.

hi i tried to do this !!

i got till here !! i am missing something have to figure it out

; with cte as 
(
SELECT 
       a.Invoice 
	 , a.ItemCode 
	 , a.UOM 
	 , a.qty 
	 , b.ReportUOM  
FROM  
   TABLE1 a 
       join 
   TABLE2 b 
        on a.ItemCode = b.itemcode 
) 
select 
        a.*
	 ,  b.UOM as Table3UOM 
	 ,  b.rate 
	 ,  Qty/rate   
from 
   cte a 
     join 
  TABLE3 b 
     on a.ItemCode = b.ItemCode 
	       and 
	    a.UOM <> b.UOM

Thanks! But the output should be:

Invoice ItemCode ReportUOM Qty
1001 A1234 Tray 5
1002 A1234 Tray 1.916666667
1003 A1235 Carton 10
1004 A1235 Carton 1
1005 A1235 Carton 0.291666667

hi

i was able to get it to this
!! somethings are missing !!! dont know where .. i am going with your explanation
r u sure u are explaining everything !!!

; with cte as 
(
SELECT 
       a.Invoice 
	 , a.ItemCode 
	 , a.UOM 
	 , a.qty 
	 , b.ReportUOM  
FROM  
   TABLE1 a 
       join 
   TABLE2 b 
        on a.ItemCode = b.itemcode 
) 
select 'SQL Output'
       , a.*
	 ,  b.UOM as Table3UOM 
	 ,  b.rate 
	 ,  Qty/rate   
from 
   cte a 
     join 
  TABLE3 b 
     on a.ItemCode = b.ItemCode 
	       and 
	    a.UOM <> b.UOM
		  and 
		( a.UOM = a.ReportUOM OR  a.ReportUOM = b.UOM ) 

You may need to adjust the CASE expression for other items - but this returns the results.

Declare @table1 Table (Invoice int, ItemCode char(5), UOM varchar(10), Qty int);
Declare @table2 Table (ItemCode char(5), ReportUOM varchar(10));
Declare @table3 Table (ItemCode char(5), UOM varchar(10), Rate int);

 Insert Into @table1
 Values (1001, 'A1234', 'Tray', 5)
      , (1002, 'A1234', 'Pc', 23)
      , (1003, 'A1235', 'Carton', 10)
      , (1004, 'A1235', 'Box', 12)
      , (1005, 'A1235', 'Pc', 28);

 Insert Into @table2 (ItemCode, ReportUOM)
 Values ('A1234', 'Tray')
      , ('A1235', 'Carton');

 Insert Into @table3 (ItemCode, UOM, Rate)
 Values ('A1234', 'Pc', 1)
      , ('A1234', 'Tray', 12)
      , ('A1235', 'Pc', 1)
      , ('A1235', 'Box', 8)
      , ('A1235', 'Carton', 96);

 Select *
      , Quantity = cast(Case When t1.UOM = t2.ReportUOM Then t1.Qty
                             Else t1.Qty * iif(t1.UOM = 'Box', 8.0, 1.0) / t3.Rate
                         End As decimal(12,9))
   From @table1                 t1
  Inner Join @table2            t2 On t2.ItemCode = t1.ItemCode
  Inner Join @table3            t3 On t3.ItemCode = t2.ItemCode
                                  And t3.UOM = t2.ReportUOM;

Thank you, harishgg1! It's almost there, except that the rate for ItemCode A1235 for Invoice 1004 is 12.

1 Carton = 96 Pc and 1 Box = 8 Pc, so 1 Carton = 12 Box.

Thank you, jeffw8713! There are many UOMs in the database, Pc, Bottle, Tray, Set, Box, Carton etc for different Item Codes.

The formula to use is A/B, where A = the Rate for UOM in Table3 that corresponds to the UOM in Table1 and B = the Rate for UOM in Table3 that corresponds to the ReportUOM in Table2.

Example, the UOM for ItemCode A1235 for Invoice 1004 in Table1 is Box, the Rate for Box for ItemCode A1235 in Table3 is 8, so A = 8.

The ReportUOM for ItemCode A1235 is Carton per Table2, and the corresponding Rate to use in Table3 for Carton for Item A1235 is 96, so B = 96.

So, the Rate to use is A/B = 8/96 = 1/12.

So - what is wrong with the solution I provided?

Based on what you posted - It looks like table2 determines the rate that will be used for all items on that invoice. So for A12345 the rate will be based on a carton so all items on that invoice need to be adjusted to the size of the carton.

1003 is a carton - so 10 cartons - 10 * 96 units / 96 = 10
1004 is a box - so 12 boxes at 8 per box - 12 * 8 / 96 = 1
1005 is a Pc - so 28 pieces - 28 / 96 = .291

Another way of writing this is to join to t3 twice - once through t2 to get the report UOM and a second time to get the individual rate:

 Select *
      , Quantity = cast(t1.Qty * t2.Rate * 1.0 / t4.Rate As decimal(12,9))
   From @table1                 t1

  Inner Join @table3            t2 On t2.ItemCode = t1.ItemCode
                                  And t2.UOM = t1.UOM

  Inner Join @table2            t3 On t3.ItemCode = t1.ItemCode
  Inner Join @table3            t4 On t4.ItemCode = t3.ItemCode
                                  And t4.UOM = t3.ReportUOM;

Here - we join to @table3 to get the factor to multiply the quantity, then we join to table2 and table3 to get the rate and now we can take table1 quantity, multiply by the rate from table3 and divide by rate for 'table4'.

1 Like

Thank you jeffw8713! The script is perfect!

You are welcome

One more thing, jeffw8713.. how do I get rid of the unnecessary columns in the final output:

Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 Column9 Column10 Column11 Column12 Column13
Invoice ItemCode UOM Qty ItemCode UOM Rate ItemCode ReportUOM ItemCode UOM Rate Quantity
1001 A1234 Tray 5 A1234 Tray 12 A1234 Tray A1234 Tray 12 5
1002 A1234 Pc 23 A1234 Pc 1 A1234 Tray A1234 Tray 12 1.916666667
1003 A1235 Carton 10 A1235 Carton 96 A1235 Carton A1235 Carton 96 10
1004 A1235 Box 12 A1235 Box 8 A1235 Carton A1235 Carton 96 1
1005 A1235 Pc 28 A1235 Pc 1 A1235 Carton A1235 Carton 96 0.291666667

I don't need columns 5 to 8 and 10 to 12.

I only need the following columns:

Column1 Column2 Column3 Column4 Column9 Column13
Invoice ItemCode UOM Qty ReportUOM Quantity
1001 A1234 Tray 5 Tray 5
1002 A1234 Pc 23 Tray 1.916666667
1003 A1235 Carton 10 Carton 10
1004 A1235 Box 12 Carton 1
1005 A1235 Pc 28 Carton 0.291666667

And, how do I name the table, say as "Sales Table"?

Thank you.

Replace the * with the columns you want

Got it, thank you!