IF EXISTS(select * from sys.databases where name='man') DROP DATABASE man IF EXISTS(select * from sys.databases where name='po') DROP DATABASE po CREATE TABLE man ([id] varchar(1), [razdel] varchar(2), [vidv] varchar (1), [sroks] varchar(10), [srokpo] varchar(10)) ; INSERT INTO man ([id], [razdel], [vidv], [sroks], [srokpo]) VALUES ('1', '80', '1', '01.02.2017', '30.04.2017'), ('1', '80', '2', '01.02.2017', '30.04.2017'), ('1', '16', '2', '01.02.2017', ''), ('1', '16', '2', '01.04.2017', ''), ('1', '80', '2', '01.05.2017', '31.07.2017'), ('1', '16', '2', '01.05.2017', '') ; CREATE TABLE po ([id] varchar(1), [sroks] varchar(10), [srokpo] varchar(10), [summ] varchar(3), [izm] varchar(10)) ; INSERT INTO po ([id], [sroks], [srokpo], [summ], [izm]) VALUES ('1', '01.05.2017', '31.07.2017', '100', '01.01.2017'), ('1', '01.05.2017', '', '200', '01.01.2017') ;

Please tell razdel depending on the value of razdel , you need to select the summ sum from the po table. First, from the man table, select the desired line with the desired razdel value and the most recent date of sroks . Then you need to select the corresponding line from the po table. Compliance is checked by coincidence of id and dates in sroks and srokpo , since there is no razdel field in table po . It should turn out: at razdel=80 summ=100 , and at razdel=16 summ=200 . The problem is that sroks may be the same, and srokpo may be empty for one of the sections. How to compare null with null ? Base DB9.

Code:

 SELECT * FROM ( SELECT p.id, p.summ, p.sroks, p.srokpo, razdel FROM PO p INNER JOIN ( SELECT id, vidv, razdel, m1.sroks, m1.srokpo FROM man m1 WHERE sroks=( SELECT max(r2.sroks) FROM man m2 WHERE m2.id=m1.id and m2.vidv=m1.vidv ) ) razd on razd.id=p.id WHERE (p.id, p.izm, p.sroks) IN (SELECT id, MAX(izm), MAX(sroks) FROM PO GROUP BY id) and p.srokpo=razd.srokpo ) where id='1' and razdel='80'

razdel='16' an empty string when razdel='16' .

If you change and p.srokpo=razd.srokpo to and p.srokpo=razd.srokpo or p.srokpo is null it gives both lines when razdel='80'

  • You have a problem with the priorities of operations, OR always spoils everything, because when you add it you get (все остальные уловия с AND) OR условие . Therefore, put brackets on прочие условия AND (p.srokpo=razd.srokpo or p.srokpo is null) or it may be shorter than coalesce(razd.srokpo,'01.01.3000')=coalesce(p.srokpo,'01.01.3000') . Personally, I don’t like NULL when working with intervals of dates and prefer the end date to be set in the database in the year 3000 to indicate a gap that has not yet ended - Mike
  • In a sense like this? WHERE (p.id, p.izm, p.sroks) IN (SELECT id, MAX(izm), MAX(sroks) FROM VPL.PODMO GROUP BY id) and (p.srokpo=razd.srokpo or p.srokpo is null) Something does not work. So when razdel='16' , nothing vibrates either. When razdel='80' selects the correct string. - J. Doe
  • Perform the request in parts, see what part of what data gives and which of the conditions of the connection will or will not work. I think there is something due to the fact that in the first table is NULL, although the condition on the second table p.srokpo is null should have covered this situation, but apparently something is going wrong and you need something like that at all (p.srokpo=razd.srokpo or (p.srokpo is null and razd.srokpo is null)) so with coalesce it is probably already easier ... - Mike

0