Filtering a query to present one-to-many data as one-to-one

Hammerklavier · February 15, 2018, 2:13pm

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.

************* CODE BELOW****************

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

************* END OF CODE****************

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.

harishgg1 · February 15, 2018, 3:50pm

Hi

Please see my attempt

Is it what you are looking for ?

Thanks

Create Data Script

drop table harish_ServiceCalls
go
drop table harish_ServiceCallEquipment
go
drop table harish_InvoiceDetails
go
drop table harish_Invoices
go
drop table harish_Orders
go

CREATE TABLE harish_ServiceCalls (CallPk INT, CallNumber VARCHAR(15), CallDate DATE)
CREATE TABLE harish_ServiceCallEquipment (CallFk INT, EquipmentFk INT)
CREATE TABLE harish_InvoiceDetails (InvoiceFk INT, EquipmentFk INT)
CREATE TABLE harish_Invoices (InvoicePk INT, InvoiceNum VARCHAR(15), OrderFk INT)
CREATE TABLE harish_Orders (OrderPk INT, OrderDate DATE, OrderNum VARCHAR(10))

INSERT INTO harish_ServiceCalls VALUES (1,'Call #1','2017/11/15')
INSERT INTO harish_ServiceCalls VALUES (2,'Call #2','2015/02/02')

INSERT INTO harish_ServiceCallEquipment VALUES (1,55)
INSERT INTO harish_ServiceCallEquipment VALUES (2,91)

INSERT INTO harish_InvoiceDetails VALUES (1020, 55)
INSERT INTO harish_InvoiceDetails VALUES (1055, 55)
INSERT INTO harish_InvoiceDetails VALUES (1079, 55)
INSERT INTO harish_InvoiceDetails VALUES (1003, 91)
INSERT INTO harish_InvoiceDetails VALUES (1098, 55)

INSERT INTO harish_Invoices VALUES (1020, 'Invoice A',480)
INSERT INTO harish_Invoices VALUES (1055, 'Invoice B',513)
INSERT INTO harish_Invoices VALUES (1079, 'Invoice C',710)
INSERT INTO harish_Invoices VALUES (1003, 'Invoice D',NULL)
INSERT INTO harish_Invoices VALUES (1098, 'Invoice E',NULL)

INSERT INTO harish_Orders VALUES (480, '01/01/2011','Order 1')
INSERT INTO harish_Orders VALUES (513, '10/04/2016','Order 2')
INSERT INTO harish_Orders VALUES (710, '12/12/2017','Order 3')

select * from harish_ServiceCalls
select * from harish_ServiceCallEquipment
select * from harish_InvoiceDetails
select * from harish_Invoices
select * from harish_Orders

My Solution

select * from harish_ServiceCalls a
join harish_ServiceCallEquipment b on a.CallPk = b.CallFk
join harish_InvoiceDetails c on c.EquipmentFk = b.EquipmentFk
join harish_Invoices d on d.InvoicePk = c.InvoiceFk
left join harish_Orders e on e.OrderPk = d.OrderFk

Hommer · February 15, 2018, 4:47pm

This will do the trick but it could be more elegant, i.e. using row_number() over (partition by...). That will also prevent returning multiple rows if they have the same order dates.

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
join (
SELECT
sc.CallNumber, max(o.OrderDate) as 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
where sc.CallDate>o.OrderDate
group by sc.CallNumber
) s2
on sc.CallNumber = s2.CallNumber and o.OrderDate = s2.OrderDate

union

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
where o.OrderNum is null
and sc.CallNumber not in (

SELECT
sc.CallNumber
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
join (
SELECT
sc.CallNumber, max(o.OrderDate) as 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
where sc.CallDate>o.OrderDate
group by sc.CallNumber
) s2
on sc.CallNumber = s2.CallNumber and o.OrderDate = s2.OrderDate
)

Hammerklavier · February 15, 2018, 5:41pm

I will give these approaches a try. Thank you!