Duplicate rows

rjackmanwyn
rjackmanwyn used Ask the Experts™
on
I am trying to run a query that gives me revenue and pay per order. When I join the invoice tables I get the correct amounts

Terminal      Division      BillTo      Order#      Driver      Shipper      Consignee      LineHaul      Accessorials
GEN      PETRO      BENBEN      5011      IMLLO      NUSNOR01      WALNOR      NULL      12.10
GEN      PETRO      BENBEN      5011      IMLLO      NUSNOR01      WALNOR      151.30      NULL

but when I join the pay table I get multiple duplicate rows. Can someone tell me what simple thing I am doing wrong please?

Terminal      Division      BillTo      Order#      Driver      Shipper      Consignee      LineHaul      Accessorials      LHPay      FSC
GEN      PETRO      BENBEN      5011      IMLLO      NUSNOR01      WALNOR      NULL      12.10      NULL      NULL
GEN      PETRO      BENBEN      5011      IMLLO      NUSNOR01      WALNOR      NULL      12.10      3.01      NULL
GEN      PETRO      BENBEN      5011      IMLLO      NUSNOR01      WALNOR      NULL      12.10      5.59      NULL
GEN      PETRO      BENBEN      5011      IMLLO      NUSNOR01      WALNOR      151.30      NULL      NULL      NULL
GEN      PETRO      BENBEN      5011      IMLLO      NUSNOR01      WALNOR      151.30      NULL      3.01      NULL
GEN      PETRO      BENBEN      5011      IMLLO      NUSNOR01      WALNOR      151.30      NULL      5.59      NULL
One Results
SELECT distinct i.ivh_revtype1 Terminal, i.ivh_revtype2 Division, i.ivh_billto BillTo, 
	i.ord_hdrnumber Order#, i.ivh_driver Driver, i.ivh_showshipper Shipper, 
	i.ivh_showcons Consignee, 
	case when c.cht_basis = 'SHP' then d.ivd_charge end LineHaul, 
	case when c.cht_basis = 'ACC' then d.ivd_charge end Accessorials,
	--case when p.pyt_itemcode like '%LH%' then p.pyd_amount end LHPay, 
	--case when p.pyt_itemcode like '%FS%' then p.pyd_amount end FSC
FROM invoiceheader i
	inner join invoicedetail d on i.ivh_hdrnumber = d.ivh_hdrnumber
	left join chargetype c on d.cht_itemcode = c.cht_itemcode
	--left join paydetail p on i.ord_hdrnumber = p.ord_hdrnumber
where i.ivh_deliverydate between '20090101' and '20090131' 
	and i.ord_hdrnumber = '5011'
order by 1, 2, 4
 
Multipe Results
SELECT distinct top 8 i.ivh_revtype1 Terminal, i.ivh_revtype2 Division, i.ivh_billto BillTo, 
	i.ord_hdrnumber Order#, i.ivh_driver Driver, i.ivh_showshipper Shipper, 
	i.ivh_showcons Consignee, 
	case when c.cht_basis = 'SHP' then d.ivd_charge end LineHaul, 
	case when c.cht_basis = 'ACC' then d.ivd_charge end Accessorials,
	case when p.pyt_itemcode like '%LH%' then p.pyd_amount end LHPay, 
	case when p.pyt_itemcode like '%FS%' then p.pyd_amount end FSC
FROM invoiceheader i
	inner join invoicedetail d on i.ivh_hdrnumber = d.ivh_hdrnumber
	left join chargetype c on d.cht_itemcode = c.cht_itemcode
	left join paydetail p on i.ord_hdrnumber = p.ord_hdrnumber
where i.ivh_deliverydate between '20090101' and '20090131' 
	and i.ord_hdrnumber = '5011'
order by 1, 2, 4

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
the rows are not distinct according to all columns, ant thats because you get this difference

Author

Commented:
I know sorry I had that out, but I was just tring different things. How can I get only the 2 rows I need?
Remove distinct and use group by. You have to decide about handling values from different invoicedatail rows, I've used SUM:
SELECT top 8 i.ivh_revtype1 Terminal, i.ivh_revtype2 Division, i.ivh_billto BillTo, 
	i.ord_hdrnumber Order#, i.ivh_driver Driver, i.ivh_showshipper Shipper, 
	i.ivh_showcons Consignee, 
	SUM(case when c.cht_basis = 'SHP' then d.ivd_charge end) LineHaul, 
	SUM(case when c.cht_basis = 'ACC' then d.ivd_charge end) Accessorials,
	SUM(case when p.pyt_itemcode like '%LH%' then p.pyd_amount end) LHPay, 
	SUM(case when p.pyt_itemcode like '%FS%' then p.pyd_amount end) FSC
FROM invoiceheader i
	inner join invoicedetail d on i.ivh_hdrnumber = d.ivh_hdrnumber
	left join chargetype c on d.cht_itemcode = c.cht_itemcode
	left join paydetail p on i.ord_hdrnumber = p.ord_hdrnumber
where i.ivh_deliverydate between '20090101' and '20090131' 
	and i.ord_hdrnumber = '5011'
group by i.ivh_revtype1, i.ivh_revtype2, i.ivh_billto, 
	i.ord_hdrnumber, i.ivh_driver, i.ivh_showshipper, 
	i.ivh_showcons
order by 1, 2, 4

Open in new window

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Above SELECT gives you one line only. If you need two lines, add   c.cht_basis  column to GROUP BY list.
Please give this a try
SELECT distinct top 8 
	i.ivh_revtype1 Terminal, 
	i.ivh_revtype2 Division, 
	i.ivh_billto BillTo, 
	i.ord_hdrnumber Order#, 
	i.ivh_driver Driver, 
	i.ivh_showshipper Shipper, 
	i.ivh_showcons Consignee, 
	case when c.cht_basis = 'SHP' then d.ivd_charge end LineHaul, 
	case when c.cht_basis = 'ACC' then d.ivd_charge end Accessorials,
	case when p.pyt_itemcode like '%LH%' then p.pyd_amount end LHPay, 
	case when p.pyt_itemcode like '%FS%' then p.pyd_amount end FSC
FROM invoiceheader i
	inner join invoicedetail d on i.ivh_hdrnumber = d.ivh_hdrnumber
	left join chargetype c on d.cht_itemcode = c.cht_itemcode
	left join (select distinct ord_hdrnumber, pyt_itemcode from paydetail) p on i.ord_hdrnumber = p.ord_hdrnumber
where i.ivh_deliverydate between '20090101' and '20090131' 
	and i.ord_hdrnumber = '5011'
order by 1, 2, 4

Open in new window

Author

Commented:
Perfect Thank You
Sorry I meant like this:
SELECT distinct top 8 
	i.ivh_revtype1 Terminal, 
	i.ivh_revtype2 Division, 
	i.ivh_billto BillTo, 
	i.ord_hdrnumber Order#, 
	i.ivh_driver Driver, 
	i.ivh_showshipper Shipper, 
	i.ivh_showcons Consignee, 
	case when c.cht_basis = 'SHP' then d.ivd_charge end LineHaul, 
	case when c.cht_basis = 'ACC' then d.ivd_charge end Accessorials,
	case when p.pyt_itemcode like '%LH%' then p.pyd_amount end LHPay, 
	case when p.pyt_itemcode like '%FS%' then p.pyd_amount end FSC
FROM invoiceheader i
	inner join invoicedetail d on i.ivh_hdrnumber = d.ivh_hdrnumber
	left join chargetype c on d.cht_itemcode = c.cht_itemcode
	left join (select ord_hdrnumber, max(pyt_itemcode) pyt_itemcode, max(pyd_amount) pyd_amount from paydetail group by ord_hdrnumber) p on i.ord_hdrnumber = p.ord_hdrnumber
where i.ivh_deliverydate between '20090101' and '20090131' 
	and i.ord_hdrnumber = '5011'
order by 1, 2, 4

Open in new window

Slow fingers or pcelba too fast :)

Author

Commented:
Thanks ralmada
This was an exception :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial