Select distinct not given desired result

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select distinct not given desired result

Author

Topic

hectoreduardo
Starting Member

1 Post

Posted - 2013-07-01 : 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

52326 Posts

Posted - 2013-07-01 : 14:39:55

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources