Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mykel_vykers
Starting Member
5 Posts |
 Posted - 2010-01-20 : 02:01:17
|
| hi guys, can you help me get the correct output in this script. i can't get the correct amount in unserved_amt. here's the script.CREATE VIEW dbo._i_sales_order_unserved_testASSELECT dbo.a_so_header.so_number, dbo.a_so_release.qty_ordered, dbo.a_so_detail.unit_conv, dbo.a_so_release.qty_ordered * dbo.a_so_detail.unit_conv AS qty_order_conv, dbo.a_so_header.order_date, dbo.a_so_release.site, dbo.a_so_header.net_amount, (dbo.a_so_release.qty_delv - dbo.a_so_release.qty_ret_after_pa) * dbo.a_so_detail.unit_conv AS qty_delv_conv, dbo.a_so_release.qty_delv, dbo.a_so_detail.um, dbo.a_so_detail.sales_amt AS order_amt, dbo.a_so_header.cust_code, dbo.a_so_detail.item_number, (dbo.a_so_release.qty_delv - dbo.a_so_release.qty_ret_after_pa) * ISNULL(dbo.a_so_detail.sales_amt / NULLIF (dbo.a_so_release.qty_delv - dbo.a_so_release.qty_ret_after_pa, 0), 1) AS delivered_amt, (dbo.a_so_release.qty_ordered - dbo.a_so_release.qty_delv + dbo.a_so_release.qty_ret_after_pa) * dbo.a_so_detail.unit_conv AS unserved_qty, (dbo.a_so_detail.sales_amt / dbo.a_so_release.qty_ordered) * (dbo.a_so_release.qty_ordered - dbo.a_so_release.qty_delv) AS unserved_amt, dbo.a_so_header.salesman, dbo.a_salesman.salesman_name, dbo.a_salesman.sales_dept, dbo.a_salesman.terr_code, dbo._i_class_sub_class.class, dbo._i_class_sub_class.sub_class, dbo._i_sales_depot.terr_descFROM dbo.a_so_header INNER JOIN dbo.a_so_detail ON dbo.a_so_header.so_number = dbo.a_so_detail.so_number INNER JOIN dbo.a_so_release ON dbo.a_so_detail.so_number = dbo.a_so_release.so_number AND dbo.a_so_detail.item_number = dbo.a_so_release.item_number AND dbo.a_so_detail.um = dbo.a_so_release.um INNER JOIN dbo.a_salesman ON dbo.a_so_header.salesman = dbo.a_salesman.salesman INNER JOIN dbo._i_class_sub_class ON dbo.a_so_detail.item_number = dbo._i_class_sub_class.item_number INNER JOIN dbo._i_sales_depot ON dbo.a_salesman.terr_code = dbo._i_sales_depot.terr_codeWHERE (dbo.a_so_release.site = 'DISPATCH') |
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 2010-01-20 : 02:19:17
|
| It is easier for us to help you if you tell us your tables, some sample data on the table, and what you want to get. Right now, your script is not very useful for us to help you. |
 |
|
|
mykel_vykers
Starting Member
5 Posts |
Posted - 2010-01-20 : 21:45:08
|
| @ EugeneLim11 - tnx's sir got it solved... |
|
|
|
|
|
|
|
|
can't get the correct output in unserved amt???