Author |
Topic  |
|
hectoreduardo
Starting Member
Guatemala
1 Posts |
Posted - 07/01/2013 : 14:26:22
|
Hello everybody, hope someone can help me with this. The soonest reply is very appreciated, but I know we all have lot to do. Thanks!!
I have written the following query: select itd.ItemID, ito.voucher, isd.COSTAMOUNTSETTLED, ITO.COSTAMOUNTADJUSTMENT, isr.OperationsAccount, ito.DATEPHYSICAL,isr.Dimension, isr.Dimension2_,isr.Dimension3_, ITd.CustVendAc, isnull(VT.Name,'SinProveedor') as Name from inventtrans itd left outer join INVENTTABLE it on itd.DATAAREAID = it.DATAAREAID and itd.ITEMID = it.ITEMID left outer join inventsettlement isd on itd.dataareaid = isd.dataareaid and itd.inventtransid = isd.inventtransid left outer join inventsettlement isr on isd.dataareaid = isr.dataareaid and isd.settletransid = isr.settletransid left outer join inventtrans ito on isr.dataareaid = ito.dataareaid and isr.inventtransid = ito.inventtransid left outer join VENDTABLE vt on ito.dataareaid = vt.dataareaid and itd.CUSTVENDAC = vt.ACCOUNTNUM where itd.dataareaid='CAD' and itd.statusissue=0 and it.ITEMGROUPID not In ('AZUCAR_TUR','MELADURA','MELAZA','CAÑA','PROD_TER M','ACG','ATG') and isd.CANCELLED=0 and ito.DATEPHYSICAL between '01/05/2012' and '30/04/2013' and ito.statusissue=1 AND itd.transrefid not in (select pt.purchid from purchtable pt where PT.dataareaid=itd.dataareaid and pt.ISS_CAJACHICA=1) and isr.OPERATIONSACCOUNT like '710%' and ito.inventtransid='00259176_078' order by isr.OperationsAccount, ito.voucher, itd.itemid
And I receive the following results: Record#1 ItemId: F526-244 Voucher: DESPF_009278 CostAmountSettled: 14.75 CostAmountAdjusted: -1.77 OperationsAccount: 710210 DatePhysical: 2012-07-18 Dimension: 60 Dimension2_: 600004 Dimension3_: 000 CustVendAc: PINJ01 Name: Inversiones Joel y Johan, SA
Record#2 ItemId: F526-244 Voucher: DESPF_009278 CostAmountSettled: 15.95 CostAmountAdjusted: -1.77 OperationsAccount: 710210 DatePhysical: 2012-07-18 Dimension: 60 Dimension2_: 600004 Dimension3_: 000 CustVendAc: PINJ01 Name: Inversiones Joel y Johan, SA
I want to have only one record, with the TOTAL of CostAmountSettled and the unique value (MIN,MAX,AVG) of CostAmountAdjusted. Like this:
Record#I want ItemId: F526-244 Voucher: DESPF_009278 CostAmountSettled: 30.7 (14.75+15.95) CostAmountAdjusted: -1.77 (this value) OperationsAccount: 710210 DatePhysical: 2012-07-18 Dimension: 60 Dimension2_: 600004 Dimension3_: 000 CustVendAc: PINJ01 Name: Inversiones Joel y Johan, SA
This is because I need to have the result of (CostAmountSettledRec#1 + CostAmountSettledRec#2) - CostAmountAdjusted in one field. How should I write the select distinct statement to obtain the result I want???
Héctor |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 07/01/2013 : 14:39:55
|
select itd.ItemID, ito.voucher, SUM(isd.COSTAMOUNTSETTLED), MAX(ITO.COSTAMOUNTADJUSTMENT), isr.OperationsAccount,
ito.DATEPHYSICAL,isr.Dimension, isr.Dimension2_,isr.Dimension3_, ITd.CustVendAc, isnull(VT.Name,'SinProveedor') as Name
from inventtrans itd
left outer join INVENTTABLE it on itd.DATAAREAID = it.DATAAREAID and itd.ITEMID = it.ITEMID
left outer join inventsettlement isd on itd.dataareaid = isd.dataareaid and itd.inventtransid = isd.inventtransid
left outer join inventsettlement isr on isd.dataareaid = isr.dataareaid and isd.settletransid = isr.settletransid
left outer join inventtrans ito on isr.dataareaid = ito.dataareaid and isr.inventtransid = ito.inventtransid
left outer join VENDTABLE vt on ito.dataareaid = vt.dataareaid and itd.CUSTVENDAC = vt.ACCOUNTNUM
where itd.dataareaid='CAD'
and itd.statusissue=0
and it.ITEMGROUPID not In ('AZUCAR_TUR','MELADURA','MELAZA','CAÑA','PROD_TER M','ACG','ATG')
and isd.CANCELLED=0
and ito.DATEPHYSICAL between '01/05/2012' and '30/04/2013'
and ito.statusissue=1
AND itd.transrefid not in (select pt.purchid from purchtable pt where PT.dataareaid=itd.dataareaid and
pt.ISS_CAJACHICA=1)
and isr.OPERATIONSACCOUNT like '710%' and ito.inventtransid='00259176_078'
group by itd.ItemID, ito.voucher, isr.OperationsAccount,
ito.DATEPHYSICAL,isr.Dimension, isr.Dimension2_,isr.Dimension3_, ITd.CustVendAc, isnull(VT.Name,'SinProveedor')
order by isr.OperationsAccount, ito.voucher, itd.itemid
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs
|
 |
|
|
Topic  |
|
|
|