SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Select distinct not given desired result
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hectoreduardo
Starting Member

Guatemala
1 Posts

Posted - 07/01/2013 :  14:26:22  Show Profile  Reply with Quote
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
52249 Posts

Posted - 07/01/2013 :  14:39:55  Show Profile  Reply with Quote

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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000