February 14, 2018 at 5:00 pm
Hello,
I'm having some difficulty trying to figure out how to filter down my query.
Each ServiceCall can be associated with multiple Orders, but in the query below, I'm trying to associate each ServiceCall with just one Order. To identify which Order, we would check the following:
- If there is a corresponding Order, eliminate anything with an OrderDate more recent than the CallDate
- Among those remaining, choose the most recent CallDate
- If there are no corresponding Orders for that ServiceCall, return the row with a null Order.
CREATE TABLE #ServiceCalls (CallPk INT, CallNumber VARCHAR(15), CallDate DATE)
CREATE TABLE #ServiceCallEquipment (CallFk INT, EquipmentFk INT)
CREATE TABLE #InvoiceDetails (InvoiceFk INT, EquipmentFk INT)
CREATE TABLE #Invoices (InvoicePk INT, InvoiceNum VARCHAR(15), OrderFk INT)
CREATE TABLE #Orders (OrderPk INT, OrderDate DATE, OrderNum VARCHAR(10))
INSERT INTO #ServiceCalls VALUES (1,'Call #1','2017/11/15')
INSERT INTO #ServiceCalls VALUES (2,'Call #2','2015/02/02')
INSERT INTO #ServiceCallEquipment VALUES (1,55)
INSERT INTO #ServiceCallEquipment VALUES (2,91)
INSERT INTO #InvoiceDetails VALUES (1020, 55)
INSERT INTO #InvoiceDetails VALUES (1055, 55)
INSERT INTO #InvoiceDetails VALUES (1079, 55)
INSERT INTO #InvoiceDetails VALUES (1003, 91)
INSERT INTO #InvoiceDetails VALUES (1098, 55)
INSERT INTO #Invoices VALUES (1020, 'Invoice A',480)
INSERT INTO #Invoices VALUES (1055, 'Invoice B',513)
INSERT INTO #Invoices VALUES (1079, 'Invoice C',710)
INSERT INTO #Invoices VALUES (1003, 'Invoice D',NULL)
INSERT INTO #Invoices VALUES (1098, 'Invoice E',NULL)
INSERT INTO #Orders VALUES (480, '01/01/2011','Order 1')
INSERT INTO #Orders VALUES (513, '10/04/2016','Order 2')
INSERT INTO #Orders VALUES (710, '12/12/2017','Order 3')
--Showing the data as is
SELECT
sc.CallNumber,
sce.EquipmentFk,
i.InvoiceNum,
sc.CallDate,
o.OrderNum,
o.OrderDate
FROM
#ServiceCalls sc
LEFT JOIN
#ServiceCallEquipment sce ON sce.CallFk = sc.CallPk
LEFT JOIN
#InvoiceDetails ie ON ie.EquipmentFk = sce.EquipmentFk
LEFT JOIN
#Invoices i ON i.InvoicePk = ie.InvoiceFk
LEFT JOIN
#Orders o ON o.OrderPk = i.OrderFk
/*
Below is the results I'm trying to achieve
1) Call #1 is associated with Invoice B because Order 2 has a most recent date among those whose OrderDate precedes the Call Date
2) Call #2 is not associated with an order, but since it has no order associated with it, it still displays once in the result
*/
SELECT CallNumber = 'Call #1', EquipmentFk = 55, InvoiceNum = 'Invoice B', CallDate = '2017-11-15', OrderNum = 'Order 2', OrderDate = '2016-10-04'
UNION
SELECT CallNumber = 'Call #2', EquipmentFk = 91, InvoiceNum = 'Invoice D', CallDate = '2015-02-02', OrderNum = NULL, OrderDate = NULL
DROP TABLE #ServiceCalls
DROP TABLE #ServiceCallEquipment
DROP TABLE #InvoiceDetails
DROP TABLE #Invoices
DROP TABLE #Orders
I greatly appreciate any assistance with this! I realize that the query might seem silly, but I didn't design the data, and I'm told the results will be helpful to several of our users.
February 15, 2018 at 7:34 am
Does this work for you?
SELECT
CallNumber,
EquipmentFk,
InvoiceNum,
CallDate,
OrderNum,
OrderDate
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY sce.EquipmentFk ORDER BY OrderDate DESC) as rownr,
sc.CallNumber,
sce.CallFk,
sce.EquipmentFk,
i.InvoiceNum,
sc.CallDate,
o.OrderNum,
o.OrderDate
FROM
#ServiceCalls sc
LEFT JOIN
#ServiceCallEquipment sce ON sce.CallFk = sc.CallPk
LEFT JOIN
#InvoiceDetails ie ON ie.EquipmentFk = sce.EquipmentFk
LEFT JOIN
#Invoices i ON i.InvoicePk = ie.InvoiceFk
LEFT JOIN
#Orders o ON o.OrderPk = i.OrderFk and COALESCE(OrderDate, '19000101') < CallDate
) cte
WHERE rownr = 1
February 15, 2018 at 10:41 am
HanShi,
Thank you for the response! I'll give this solution a try.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply