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