CODEWITHSUNDEEP
sqlsql-server
MichaelEaton
MichaelEaton

Reputation: 195

ms sql subquery different where clause same tables

I'm trying to build the following query:

SELECT     
COUNT(dbo.[Order].OrderID) AS Orders, 
SUM(dbo.OrderLine.SubTotal) + SUM(dbo.OrderLine.Shipping) - SUM(dbo.OrderLine.Discount) AS Revenue, 
SUM(dbo.OrderLine.Shipping) AS Expr1, 
DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.[Order].PaymentDate)) AS RevenueDate, 
(SUM(dbo.OrderLine.SubTotal) + SUM(dbo.OrderLine.Shipping)) / COUNT(dbo.[Order].OrderID) AS RevenuePerOrder, 
SUM(dbo.OrderLine.Qty) AS Qty

FROM         
    dbo.[Order] 
            INNER JOIN dbo.OrderLine ON dbo.[Order].OrderID = dbo.OrderLine.OrderID
            INNER JOIN (
                        SELECT SUM(dbo.OrderLine.SubTotal) + SUM(dbo.OrderLine.Shipping) - SUM(dbo.OrderLine.Discount) AS BulkRevenue 
                        FROM dbo.OrderLine 
                        INNER JOIN dbo.[Order] ON dbo.OrderLine.OrderID = dbo.[Order].OrderID 
                        WHERE BulkOrder = 1) bulkrevenue


WHERE     (dbo.[Order].OrderStatusID IN (2)) 
    AND (dbo.[Order].PaymentDate >= CONVERT(DATETIME, '02/03/2014', 103)) 
    AND (dbo.[Order].PaymentDate <= CONVERT(DATETIME, '08/03/2014', 103)) 
    AND (dbo.[Order].WebsiteID = 2)


GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.[Order].PaymentDate)), dbo.[Order].WebsiteID

The Sub Query/second INNER JOIN is where things fail, is this something I can do? I want to get Bulk Order Revenue as a another column in the same result set.

Hope this makes sense.

Thanks,

Michael

Upvotes: 0

Views: 139

Answers (1)

JMari&#241;a
JMari&#241;a

Reputation: 324

It looks like your are not specifying what key to join on for your second inner join.

        INNER JOIN (
                    SELECT SUM(dbo.OrderLine.SubTotal) + SUM(dbo.OrderLine.Shipping) - SUM(dbo.OrderLine.Discount) AS BulkRevenue 
                    FROM dbo.OrderLine 
                    INNER JOIN dbo.[Order] ON dbo.OrderLine.OrderID = dbo.[Order].OrderID 
                    WHERE BulkOrder = 1) bulkrevenue ON bulkrevenue.___ = table.____

This may be a case where an outer apply, or cross apply will better suit your needs (I believe your current operation of sums will display the same for every row, not sure if that is intended) see http://sqlserverplanet.com/sql-2005/cross-apply-explained

Upvotes: 2

Related Questions