SQL group by count across two tables

Question

I have got two tables called baseline and revisits

baseline

formid-------NoOfIssues

1--------------3

2--------------4

3--------------5

revisits

id------formid-------NoOfIssues-----------date--------------fid

1---------2--------------4-------------5/06/2016------------1

2---------3--------------3-------------15/06/2016-----------1

3---------1--------------4-------------20/07/2016-----------1

4---------1--------------3-------------25/07/2016-----------1

5---------2--------------5-------------28/07/2016-----------1

6---------1--------------5-------------01/06/2016-----------1

7---------3--------------8-------------21/02/2016-----------1

8---------3--------------2-------------21/02/2016-----------2

These tables are joined by 'formid'. I need to compare number of issues in baseline vs revisits(only first) and get a count as reduced, increased or equal

Based upon the above table i am expecting the following, for example across all three baseline entries no equals were found comparing NoOfissues in first revisit against same formid, but 1 equal and 2 increased were found

Addition: if same date and same formid is found than take the lower fid, so in the last two rows of revisits table both formid and date are equal but need to consider the lower formid which is 1

status----------Count

reduced----------0

equal------------1

increased--------2

Answer 1

I'm not familiar with intersystems-cache, but you can see if the following is valid SQL with that DB:

SELECT
    CASE
        WHEN BL.NoOfIssues = FR.NoOfIssues THEN 'equal'
        WHEN BL.NoOfIssues > FR.NoOfIssues THEN 'reduced'
        WHEN BL.NoOfIssues < FR.NoOfIssues THEN 'increased'
    END AS status,
    COUNT(*) AS Count
FROM
    Baseline BL
INNER JOIN Revisits FR ON FR.formid = BL.formid
LEFT OUTER JOIN Revisits R ON
    R.formid = BL.formid AND
    (
        R.date < FR.date OR
        (R.date = FR.date AND R.fid > FR.fid)
    )
WHERE
    R.formid IS NULL
GROUP BY
    CASE
        WHEN BL.NoOfIssues = FR.NoOfIssues THEN 'equal'
        WHEN BL.NoOfIssues > FR.NoOfIssues THEN 'reduced'
        WHEN BL.NoOfIssues < FR.NoOfIssues THEN 'increased'
    END

Some quick notes on your database though - You should probably decide on a standard of plural or singular table names and stick with it. Also, try to avoid common reserved words for object names, like date. Finally, if a revisit is basically the same as a visit, just on a later date then you should consider keeping them all in the same table.