My first step would be to rank the service_instruction
by specificity/generality - I don't think you have provided enough information to be sure what you are after but assuming that it is based on the number of 1
s and ties are broken by: bank_num
is more specific than loan_type_cd
is more specific than client_num
:
select *, row_number() over ( order by case when bank_num=1 then 1 else 0 end+
case when loan_type_cd=1 then 1 else 0 end+
case when client_num=1 then 1 else 0 end,
case when bank_num=1 then 1 else 0 end,
case when loan_type_cd=1 then 1 else 0 end,
case when client_num=1 then 1 else 0 end ) as gen
from service_instruction;
produces:
instruction_num service_num instruction_desc bank_num region_num loan_type_cd client_num gen
3 251 Take the photos, yeah 17 96 3 1 1
4 251 Bid for debris removal. 1 471 1 7 2
7 251 Bid for debris removal. Do not perform 1 3 1 1 3
This can then be joined to order
and work_order_line
and filtered for the row for each work_order_line_num
with the lowest generality (gen
), perhaps something like this:
with w as (
select *, row_number() over ( order by case when bank_num=1 then 1 else 0 end+
case when loan_type_cd=1 then 1 else 0 end+
case when client_num=1 then 1 else 0 end,
case when bank_num=1 then 1 else 0 end,
case when loan_type_cd=1 then 1 else 0 end,
case when client_num=1 then 1 else 0 end ) as gen
from service_instruction )
select *
from( select l.*, instruction_desc, row_number() over ( partition by l.work_order_line_num,
l.order_num,
l.service_num
order by gen ) as gen_rank
from work_order_line l join [order] o on(o.order_num=l.order_num)
join w on( l.service_num=w.service_num
and (o.bank_num=w.bank_num or w.bank_num=1)
and (o.loan_type_cd=w.loan_type_cd or w.loan_type_cd=1)
and (o.client_num=w.client_num or w.client_num=1)) ) z
where gen_rank=1
which produces:
work_order_line_num order_num service_num instruction_desc gen_rank
20 1 251 Bid for debris removal. Do not perform 1
21 2 251 Bid for debris removal. 1
22 3 251 Take the photos, yeah 1
26 4 251 Bid for debris removal. Do not perform 1
notes:
- I've assumed you need to partition by
work_order_line_num
, order_num
and service_num
but maybe not all are necessary depending on the PK of work_order_line
- I've ignored the
pricing_region
complexity you mention but don't specify completely - hopefully you'll be able to work it into the query in a similar manner to the rest
- You'll probably have to fix the CTE with your rules for specificity/generality
- I tested on 2008R2 - YMMV if you are on an earlier version
I don't see why this won't work:
IF OBJECT_ID(N'dbo.Header', N'U') IS NOT NULL
DROP TABLE dbo.Header;
IF OBJECT_ID(N'dbo.Body', N'U') IS NOT NULL
DROP TABLE dbo.Body;
GO
CREATE TABLE dbo.Header
(
iGroup int
, sName varchar(50)
, iValue int
);
CREATE TABLE Body
(
iGroup int
, sName varchar(50)
, iValue int
);
INSERT INTO dbo.Header (iGroup, sName, iValue)
VALUES (1, 'test1', 10)
, (2, 'test2', 20);
INSERT INTO dbo.Body (iGroup, sName, iValue)
VALUES (1, 'test3', 30)
, (2, 'test4', 40);
;WITH src AS
(
SELECT iGroup
, sName
, iValue
, 0 as sort_by
FROM Header
UNION ALL
SELECT iGroup
, sName
, iValue
, 1 as sort_by
FROM Body
)
SELECT Id = ROW_NUMBER() OVER (ORDER BY sort_by, iGroup, sName)
, src.*
FROM src
ORDER BY sort_by, iGroup, sName;
Results:
╔════╦════════╦═══════╦════════╗
║ Id ║ iGroup ║ sName ║ iValue ║
╠════╬════════╬═══════╬════════╣
║ 1 ║ 1 ║ test1 ║ 10 ║
║ 2 ║ 1 ║ test3 ║ 30 ║
║ 3 ║ 2 ║ test2 ║ 20 ║
║ 4 ║ 2 ║ test4 ║ 40 ║
╚════╩════════╩═══════╩════════╝
Also, don't use reserved keywords as column names, please!
Best Answer
There's probably a more elegant way to achieve this but it can be done by using the NTILE function inside a CTE.