Stuck with a SQL query. - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Stuck with a SQL query.

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2013-07-22 : 12:29:16

To Clarify: My aim is to eliminate the Cartesian product. How do I do this?

I'm a bit stuck with a query. I have to do a P&L based on orders, but each order can have several different units. Moreover the last inner join (d), causes a Cartesian Product. In the first example, I'm able to avoid that by adding 'DISTINCT' to the SELECT statement.

First Code

SELECT DISTINCT
	 a.[ID]
	 ,B.[ID]
	,b.[AverageCost]
	,b.[PostingFee]
	,d.[Commission]
	,c.[EbayTransactionFee]
	,b.[ShippingCost]
	,b.[LineTaxTotal]
	,a.[FinalShippingFee]
	,b.[ProductRebateValue]
	,a.[GrandTotal]
	,CONVERT(DECIMAL(18,2),a.[GrandTotal]-(b.[AverageCost] + d.[Commission] + a.[FinalShippingFee]))  AS 'Profit'
	
  FROM [SC].[dbo].[bvc_Order]				a
  INNER JOIN [SC].[dbo].[bvc_OrderItem]		b
  ON a.ID = b.OrderID
  INNER JOIN [SC].[dbo].[Product]			c
  ON b.[ProductID] = c.[ID]
  INNER JOIN [SC].[dbo].[BuyDotComOrders]	d
  ON d.[OrderSourceOrderId] = a.[OrderSourceOrderId]

However, when I add a GROUP BY clause to see the P&L based on orders, the distinct statement doesn't help in the same way.

Second Code

SELECT DISTINCT
	 a.[ID]
	,SUM(b.[AverageCost])
	,SUM(b.[PostingFee])
	,SUM(d.[Commission])
	,SUM(c.[EbayTransactionFee])
	,SUM(b.[ShippingCost])			-- <--- SUM OR NOT?
	,SUM(b.[LineTaxTotal])
	,a.[FinalShippingFee]
	,SUM(b.[ProductRebateValue])
	,SUM(a.[GrandTotal])
	,CONVERT(DECIMAL(18,2),a.[GrandTotal]-(b.[AverageCost] + d.[Commission] + a.[FinalShippingFee]))  AS 'Profit'
	
  FROM [SC].[dbo].[bvc_Order]				a
  INNER JOIN [SC].[dbo].[bvc_OrderItem]		b
  ON a.ID = b.OrderID
  INNER JOIN [SC].[dbo].[Product]			c
  ON b.[ProductID] = c.[ID]
  INNER JOIN [SC].[dbo].[BuyDotComOrders]	d
  ON d.[OrderSourceOrderId] = a.[OrderSourceOrderId]

GROUP BY a.[ID]

How do I proceed? If I can't figure this out, I have to make each column it's own subquery. Which will do the job, but it's not the smart way to do it.

-Sergio
I use Microsoft SQL 2008

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 12:44:05

Don't you need to sum the profits too?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-22 : 12:49:27

quote:
Originally posted by SwePeso

Don't you need to sum the profits too?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

and how about a.[FinalShippingFee]

SergioM
Posting Yak Master

170 Posts

Posted - 2013-07-22 : 12:50:26

quote:
Originally posted by SwePeso

Don't you need to sum the profits too?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

I'll have to re approach that one. Grand Total is for the entire order but each of it's components are line-specific.

-Sergio
I use Microsoft SQL 2008

SergioM
Posting Yak Master

170 Posts

Posted - 2013-07-22 : 12:51:05

quote:
Originally posted by MuMu88
and how about a.[FinalShippingFee]

Nope. That one is for the entire order.

-Sergio
I use Microsoft SQL 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources