| 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 |
|
|
|
Select distinct not given desired result