June 23, 2009 at 7:55 am
Hi Everyone, I'm working on some range data for a quote type system and I am having some issues. I've been working on this for two days and I can't come up with a solution so I guess it's time to ask for some help. I have four tables and I am trying to essentially merge the data with a single select statement.
I will give you the four tables and some same data and then show you what I am trying to achieve in the output. The database is SQL 2008 Ent.
The tables are divided by Fee Categories
The uniqueness of the tables are from these three columns (FeeProdId FeeProd2 StateId)
FeeCateId in each table is essentially just a surrogate key.
I've tried using PIVOT, GROUP BY, and Merge and I haven't been able to come up with it yet.
Let me know if you need anymore info!
Table1
FeeCategory1
FeeCatIdFeeProdId FeeProd2 StateIdRangeUpToFee1Fee2Fee3
1111100000255055
2111150000456055
3111200000557065
4111250000658095
5211100000255055
6211150000456055
7211500000557065
Table2
FeeCategory2
FeeCat2Id FeeProdId FeeProd2 StateId RangeUpToFee1Fee2Fee3
1111100000255055
2111200000255055
3111300000255055
4111400000255055
5211100000455055
6211200000606570
72113000008090100
8211400000105105105
Table3
FeeCategory3
FeeCat3Id FeeProdId FeeProd2 StateId RangeUpToFee1Fee2Fee3
1111300000100200300
2111500000150250350
3211500000150250500
Table4
FeeCategory4
FeeCat4Id FeeProdId FeeProd2 StateId RangeUpToFee1Fee2Fee3
111150000051020
221160000051020
Desired Ouput
KeyFC1RangUptoFC2RangUptoFC3RangUptoFC4RangUptoFC1fee1FC1fee2FC1fee3FC2fee1FC2fee2FC2fee3….FC4Fee3
1,1,1100000100000300000500000255055....
1,1,1150000200000300000500000
1,1,1200000200000300000500000
1,1,1250000300000300000500000
2,1,1100000100000500000600000
2,1,1150000200000500000600000
2,1,1500000300000500000600000
2,1,1500000400000500000600000
June 24, 2009 at 1:00 pm
Kay_dba (6/23/2009)
a single select statement.
A relative definition 😉
Let me tell you that those data are really a funny approach...
Since this is 2008 forum, maybe somebody has another solution, but try this...
DECLARE @FeeCategory1 TABLE (FeeCatId INT, FeeProdId INT, FeeProd2 INT, StateId INT, RangeUpTo INT, Fee1 INT, Fee2 INT, Fee3 INT);
DECLARE @FeeCategory2 TABLE (FeeCatId INT, FeeProdId INT, FeeProd2 INT, StateId INT, RangeUpTo INT, Fee1 INT, Fee2 INT, Fee3 INT);
DECLARE @FeeCategory3 TABLE (FeeCatId INT, FeeProdId INT, FeeProd2 INT, StateId INT, RangeUpTo INT, Fee1 INT, Fee2 INT, Fee3 INT);
DECLARE @FeeCategory4 TABLE (FeeCatId INT, FeeProdId INT, FeeProd2 INT, StateId INT, RangeUpTo INT, Fee1 INT, Fee2 INT, Fee3 INT);
INSERT INTO @FeeCategory1
SELECT '1', '1', '1', '1', '100000', '25', '50', '55'
UNION ALL SELECT '2', '1', '1', '1', '150000', '45', '60', '55'
UNION ALL SELECT '3', '1', '1', '1', '200000', '55', '70', '65'
UNION ALL SELECT '4', '1', '1', '1', '250000', '65', '80', '95'
UNION ALL SELECT '5', '2', '1', '1', '100000', '25', '50', '55'
UNION ALL SELECT '6', '2', '1', '1', '150000', '45', '60', '55'
UNION ALL SELECT '7', '2', '1', '1', '500000', '55', '70', '65';
INSERT INTO @FeeCategory2
SELECT '1', '1', '1', '1', '100000', '25', '50', '55'
UNION ALL SELECT '2', '1', '1', '1', '200000', '25', '50', '55'
UNION ALL SELECT '3', '1', '1', '1', '300000', '25', '50', '55'
UNION ALL SELECT '4', '1', '1', '1', '400000', '25', '50', '55'
UNION ALL SELECT '5', '2', '1', '1', '100000', '45', '50', '55'
UNION ALL SELECT '6', '2', '1', '1', '200000', '60', '65', '70'
UNION ALL SELECT '7', '2', '1', '1', '300000', '80', '90', '100'
UNION ALL SELECT '8', '2', '1', '1', '400000', '105', '105', '105';
INSERT INTO @FeeCategory3
SELECT '1', '1', '1', '1', '300000', '100', '200', '300'
UNION ALL SELECT '2', '1', '1', '1', '500000', '150', '250', '350'
UNION ALL SELECT '3', '2', '1', '1', '500000', '150', '250', '500';
INSERT INTO @FeeCategory4
SELECT '1', '1', '1', '1', '500000', '5', '10', '20'
UNION ALL SELECT '2', '2', '1', '1', '600000', '5', '10', '20';
; WITH
result_rows_all AS
(
SELECT
FeeProdId,
FeeProd2,
StateId,
COUNT(*) row_count,
MAX(RangeUpTo) RangeUpTo,
MAX(Fee1) Fee1,
MAX(Fee2) Fee2,
MAX(Fee3) Fee3
FROM @FeeCategory1
GROUP BY
FeeProdId,
FeeProd2,
StateId
UNION ALL
SELECT
FeeProdId,
FeeProd2,
StateId,
COUNT(*) row_count,
MAX(RangeUpTo) RangeUpTo,
MAX(Fee1) Fee1,
MAX(Fee2) Fee2,
MAX(Fee3) Fee3
FROM @FeeCategory2
GROUP BY
FeeProdId,
FeeProd2,
StateId
UNION ALL
SELECT
FeeProdId,
FeeProd2,
StateId,
COUNT(*) row_count,
MAX(RangeUpTo) RangeUpTo,
MAX(Fee1) Fee1,
MAX(Fee2) Fee2,
MAX(Fee3) Fee3
FROM @FeeCategory3
GROUP BY
FeeProdId,
FeeProd2,
StateId
UNION ALL
SELECT
FeeProdId,
FeeProd2,
StateId,
COUNT(*) row_count,
MAX(RangeUpTo) RangeUpTo,
MAX(Fee1) Fee1,
MAX(Fee2) Fee2,
MAX(Fee3) Fee3
FROM @FeeCategory4
GROUP BY
FeeProdId,
FeeProd2,
StateId
),
result_rows_max AS
(
SELECT
FeeProdId,
FeeProd2,
StateId,
MAX(RangeUpTo) RangeUpTo,
MAX(Fee1) Fee1,
MAX(Fee2) Fee2,
MAX(Fee3) Fee3,
MAX(row_count) row_count
FROM result_rows_all
GROUP BY
FeeProdId,
FeeProd2,
StateId
),
result_rows AS
(
SELECT
ak.FeeProdId,
ak.FeeProd2,
ak.StateId,
RangeUpTo,
Fee1,
Fee2,
Fee3,
t.N row_num
FROM Tally t
CROSS JOIN result_rows_max ak
WHERE t.N <= ak.row_count
),
f1 AS
(
SELECT
FeeProdId,
FeeProd2,
StateId,
RangeUpTo,
Fee1,
Fee2,
Fee3,
ROW_NUMBER() OVER (PARTITION BY FeeProdId, FeeProd2, StateId ORDER BY RangeUpTo) row_num
FROM @FeeCategory1
),
f2 AS
(
SELECT
FeeProdId,
FeeProd2,
StateId,
RangeUpTo,
Fee1,
Fee2,
Fee3,
ROW_NUMBER() OVER (PARTITION BY FeeProdId, FeeProd2, StateId ORDER BY RangeUpTo) row_num
FROM @FeeCategory2
),
f3 AS
(
SELECT
FeeProdId,
FeeProd2,
StateId,
RangeUpTo,
Fee1,
Fee2,
Fee3,
ROW_NUMBER() OVER (PARTITION BY FeeProdId, FeeProd2, StateId ORDER BY RangeUpTo) row_num
FROM @FeeCategory3
),
f4 AS
(
SELECT
FeeProdId,
FeeProd2,
StateId,
RangeUpTo,
Fee1,
Fee2,
Fee3,
ROW_NUMBER() OVER (PARTITION BY FeeProdId, FeeProd2, StateId ORDER BY RangeUpTo) row_num
FROM @FeeCategory4
)
SELECT
rr.FeeProdId,
rr.FeeProd2,
rr.StateId,
ISNULL(f1.RangeUpTo, rr.RangeUpTo),
ISNULL(f2.RangeUpTo, rr.RangeUpTo),
ISNULL(f3.RangeUpTo, rr.RangeUpTo),
ISNULL(f4.RangeUpTo, rr.RangeUpTo),
ISNULL(f1.Fee1, rr.Fee1),
ISNULL(f2.Fee1, rr.Fee1),
ISNULL(f3.Fee1, rr.Fee1),
ISNULL(f4.Fee1, rr.Fee1),
ISNULL(f1.Fee2, rr.Fee2),
ISNULL(f2.Fee2, rr.Fee2),
ISNULL(f3.Fee2, rr.Fee2),
ISNULL(f4.Fee2, rr.Fee2),
ISNULL(f1.Fee3, rr.Fee3),
ISNULL(f2.Fee3, rr.Fee3),
ISNULL(f3.Fee3, rr.Fee3),
ISNULL(f4.Fee3, rr.Fee3)
FROM result_rows rr
LEFT JOIN f1
ON rr.FeeProdId = f1.FeeProdId
AND rr.FeeProd2 = f1.FeeProd2
AND rr.StateId = f1.StateId
AND rr.row_num = f1.row_num
LEFT JOIN f2
ON rr.FeeProdId = f2.FeeProdId
AND rr.FeeProd2 = f2.FeeProd2
AND rr.StateId = f2.StateId
AND rr.row_num = f2.row_num
LEFT JOIN f3
ON rr.FeeProdId = f3.FeeProdId
AND rr.FeeProd2 = f3.FeeProd2
AND rr.StateId = f3.StateId
AND rr.row_num = f3.row_num
LEFT JOIN f4
ON rr.FeeProdId = f4.FeeProdId
AND rr.FeeProd2 = f4.FeeProd2
AND rr.StateId = f4.StateId
AND rr.row_num = f4.row_num
ORDER BY
rr.FeeProdId,
rr.FeeProd2,
rr.StateId
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply