0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for developers and users of the secure, private and untraceable cryptocurrency Monero

DECLARE @mtest table
(claimid char(15),
memid char(1),
provid char(1),
claimline int,
dosfrom date,
servcode char(5),
servunits int,
claimamt money,
contractpaid money)
INSERT INTO @mtest
VALUES
('12345E00000','A','A',1,'20180601','97110',1,128,100),
('12345E00000','A','A',2,'20180603','97110',1,128,100),
('12345E00000','A','A',3,'20180607','97110',1,128,100),
('12345E00000','A','A',4,'20180613','97110',1,128,100),
('12345E00000','A','A',5,'20180601','97140',1,128,100),
('12345E00000','A','A',6,'20180603','97140',1,128,100),
('12345E00000','A','A',7,'20180607','97140',1,128,100),
('12345E00000','A','A',8,'20180613','97140',1,128,100),
('12345E00001','A','A',1,'20180607','97116',1,128,100),
('12345E00002','A','B',1,'20180603','97116',2,128,100),
('12345E00003','B','A',1,'20180601','97162',1,128,100),
('12345E00003','B','A',2,'20180601','97110',3,80,70),
('12345E00004','C','B',1,'20180701','97535',2,95,80),
('12345E00004','C','B',2,'20180701','97124',2,50,40),
('12345E00005','D','A',1,'20180401','G0283',2,30,15),
('12345E00005','D','A',2,'20180401','G0281',2,30,15)

DECLARE @ftest table
(codeid char(5),
fullfee money,
reducedfee money,
pervu real)
INSERT INTO @ftest
VALUES
('G0281',16.2,11.45,0.23),
('G0283',16.2,11.45,0.23),
('G0329',12.28,7.33,0.24),
('92507',83.84,65.9,0.87),
('92508',24.9,18.51,0.31),
('92521',122.78,93.69,1.41),
('92522',97.9,76.86,1.02),
('92523',213.26,161.9,2.49),
('92524',93.56,74.58,0.92),
('92526',92.3,70.85,1.04),
('92597',76.24,61.6,0.71),
('92607',142.06,105.14,1.79),
('92609',119.52,87.34,1.56),
('96125',128.4,95.61,1.59),
('97012',15.83,12.53,0.16),
('97016',17.43,12.07,0.26),
('97018',9.81,6.1,0.18),
('97022',21.2,13.77,0.36),
('97024',7.75,5.06,0.13),
('97026',6.92,4.65,0.11),
('97028',8.88,5.99,0.14),
('97032',16.66,12.94,0.18),
('97033',22.79,16.19,0.32),
('97034',16.45,12.12,0.21),
('97035',14.39,11.09,0.16),
('97036',40.01,25.16,0.72),
('97110',33.16,24.91,0.4),
('97112',37.85,28.15,0.47),
('97113',42.91,30.32,0.61),
('97116',32.75,24.7,0.39),
('97124',33.87,23.35,0.51),
('97140',30.16,22.94,0.35),
('97150',19.75,15.21,0.22),
('97161',90.97,67.66,1.13),
('97162',90.97,67.66,1.13),
('97163',90.97,67.66,1.13),
('97164',61.93,44.81,0.83),
('97165',98.81,71.58,1.32),
('97166',98.81,71.58,1.32),
('97167',98.81,71.58,1.32),
('97168',67.71,47.7,0.97),
('97530',44.76,30.53,0.69),
('97533',37.13,27.43,0.47),
('97535',37.7,27.18,0.51),
('97537',35.89,26.82,0.44),
('97542',36.3,27.02,0.45),
('97750',41.41,29.03,0.6),
('97755',42.17,32.48,0.47),
('97760',51.88,35.17,0.81),
('97761',44.45,31.46,0.63),
('97763',53.63,35.69,0.87)

DECLARE @mptest table
(claimid char(15),
memid char(1),
provid char(1),
claimline int,
dosfrom date,
servcode char(5),
servunits int,
claimamt money,
contractpaid money,
fullfee money,
reducedfee money,
pervu real)
INSERT INTO @mptest
SELECT claimid, memid, provid, claimline, dosfrom, servcode, servunits, claimamt, contractpaid, fullfee, reducedfee, pervu
FROM @mtest m
JOIN @ftest f ON m.servcode = f.codeid

DECLARE @mptab table
(claimid char(15),
memid char(1),
provid char(1),
claimline int,
dosfrom date,
servcode char(5),
servunits int,
claimamt money,
contractpaid money,
fullfee money,
reducedfee money,
pervu real,
maxpervu int,
maxfullfee int,
applyfullfee int)
INSERT INTO @mptab
SELECT *,
CASE WHEN pervu = (SELECT max(pervu) FROM @mptest WHERE memid = t.memid AND provid = t.provid AND dosfrom = t.dosfrom) THEN 1 ELSE 0 END [maxpervu],
CASE WHEN fullfee = (SELECT max(fullfee) FROM @mptest WHERE memid = t.memid AND provid = t.provid AND dosfrom = t.dosfrom AND CASE WHEN pervu = (SELECT max(pervu) FROM @mptest WHERE memid = t.memid AND provid = t.provid AND dosfrom = t.dosfrom) THEN 1 ELSE 0 END = 1) THEN 1 ELSE 0 END [maxfullfee],
CASE WHEN claimid = (SELECT min(claimid) FROM @mptest WHERE memid = t.memid AND provid = t.provid AND dosfrom = t.dosfrom AND CASE WHEN fullfee = (SELECT max(fullfee) FROM @mptest WHERE memid = t.memid AND provid = t.provid AND dosfrom = t.dosfrom AND CASE WHEN pervu = (SELECT max(pervu) FROM @mptest WHERE memid = t.memid AND provid = t.provid AND dosfrom = t.dosfrom) THEN 1 ELSE 0 END = 1) THEN 1 ELSE 0 END = 1)
AND claimline = (SELECT min(claimline) FROM @mptest WHERE memid = t.memid AND provid = t.provid AND dosfrom = t.dosfrom AND CASE WHEN fullfee = (SELECT max(fullfee) FROM @mptest WHERE memid = t.memid AND provid = t.provid AND dosfrom = t.dosfrom AND CASE WHEN pervu = (SELECT max(pervu) FROM @mptest WHERE memid = t.memid AND provid = t.provid AND dosfrom = t.dosfrom) THEN 1 ELSE 0 END = 1) THEN 1 ELSE 0 END = 1) THEN 1 ELSE 0 END [applyfullfee]
FROM @mptest t

SELECT * FROM @mptab

fork query

edited jul 23 18

created jul 22 18 anonymous