CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Kevin
Kevin

Reputation: 2688

Figure Out Which OrderIDs are 0$ Payment Totals

I am in need to some help writing a SQL 2012 query that will help me find and mark orderID's that are a $0.00 payments due to reversal(s)

So far I have:

Select Distinct a.orderID, a.orderPaid,
    (Select SUM((c1.linePrice + c1.lineShippingCost + c1.lineTaxCost + c1.lineOptionCost) * c1.lineQuantity) 
            From vwSelectOrderLineItems c1 Where c1.orderID = a.orderID) As OrderAmount,
    (Select SUM(b1.payAmount) FROM vwSelectOrderPayments b1 Where b1.orderID = a.orderID) as Payment,
    1 As IsReversal
From vwSelectOrders a
Left Outer Join vwSelectOrderPayments b On b.orderID = a.orderID
Where b.payValid = 1 AND a.orderPaid = 0

Which is returning me some $0 payments on some orders. When I query that payment table with the orderID of these records, I can see that 2 payments were posted... 1 the original payment, 2 the reversal.

How Can I flag the Orders that are $0 payments?

Oders

CREATE TABLE [dbo].[TblOrders](
    [orderID] [bigint] IDENTITY(1000,1) NOT NULL,
    [orderPaid] [bit] NOT NULL,
    [orderPaidOn] [datetime] NULL
 CONSTRAINT [PK_TblOrders] PRIMARY KEY CLUSTERED 
(
    [orderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[TblOrders] ADD  CONSTRAINT [DF__TblOrders__order__1975C517]  DEFAULT ((0)) FOR [orderPaid]

Order Line Items

CREATE TABLE [dbo].[TblOrderLineItems](
    [lineID] [bigint] IDENTITY(1,1) NOT NULL,
    [orderID] [bigint] NOT NULL,
    [lineQuantity] [int] NOT NULL,
    [linePrice] [money] NOT NULL,
    [lineShippingCost] [money] NOT NULL,
    [lineTaxCost] [money] NOT NULL,
    [lineOptionCost] [money] NOT NULL,
 CONSTRAINT [PK_TblOrderLineItems] PRIMARY KEY CLUSTERED 
(
    [lineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[TblOrderLineItems] ADD  CONSTRAINT [DF_TblOrderLineItems_lineShippingCost]  DEFAULT ((0)) FOR [lineShippingCost]
GO

ALTER TABLE [dbo].[TblOrderLineItems] ADD  CONSTRAINT [DF_TblOrderLineItems_lineTaxCost]  DEFAULT ((0)) FOR [lineTaxCost]
GO

ALTER TABLE [dbo].[TblOrderLineItems] ADD  CONSTRAINT [DF_TblOrderLineItems_lineOptionCost]  DEFAULT ((0)) FOR [lineOptionCost]
GO

Order Payments

CREATE TABLE [dbo].[TblOrderPayments](
    [paymentID] [bigint] IDENTITY(1,1) NOT NULL,
    [orderID] [bigint] NOT NULL,
    [payAmount] [money] NOT NULL,
    [payPosted] [datetime] NOT NULL,
    [payValid] [bit] NOT NULL,
 CONSTRAINT [PK_TblOrderPayments] PRIMARY KEY CLUSTERED 
(
    [paymentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[TblOrderPayments] ADD  CONSTRAINT [DF_TblOrderPayments_payValid]  DEFAULT ((0)) FOR [payValid]
GO

Views

CREATE VIEW [dbo].[vwSelectOrderLineItems] AS 
SELECT linePrice, lineShippingCost, lineTaxCost, lineOptionCost, lineQuantity 
FROM [dbo].[TblOrderLineItems]

CREATE VIEW [dbo].[vwSelectOrderPayments] AS
SELECT paymentID, orderID, payAmount, payValid
FROM dbo.TblOrderPayments

CREATE VIEW [dbo].[vwSelectOrders] AS
SELECT orderID , orderPaid
FROM dbo.TblOrders

Note

I cannot change the table structure

Upvotes: 1

Views: 48

Answers (1)

gh9
gh9

Reputation: 10703

SELECT distinct a.orderid,
                a.orderPaid,
                c.OrderAmount
                d.Payment
From vwSelectOrders AS a
INNER JOIN ( Select SUM((linePrice + lineShippingCost + lineTaxCost + lineOptionCost) * lineQuantity) As orderAmount,OrderID
             From vwSelectOrderLineItems group by orderid) AS C on c.orderID = a.orderID
INNER JOIN (Select SUM(payAmount) as Payment,orderID FROM vwSelectOrderPayments WHERE isnull(SUM(PayAmount),0) > 0 GROUP BY OrderID) AS d ON d.orderID = a.orderID
Left Outer Join vwSelectOrderPayments b On b.orderID = a.orderID
Where b.payValid = 1 AND a.orderPaid = 0 AND

This is a better query as you do not have to us a correlated subquery. Correlated queries are when a subquery references an outerquery row. This isn't optimal because every row the outerquery runs the correlated subquery will execute. Once you give us table definitions we can probably fix the overall data return of your query.

Upvotes: 1

Related Questions