First post so sorry! I have:

I want:

I tried:

SELECT [PARTICIPANT], SUM([VALUE]), [Period]
FROM (
SELECT [PARTICIPANT], [VALUE], [RULE], [Period]
FROM [TABLE]
WHERE [RULE]='RULE 1'
UNION
SELECT [PARTICIPANT], [VALUE], [RULE], [Period]
FROM [TABLE]
WHERE [RULE]='RULE 2'
) AS [T1]
GROUP BY [Participant], [Period]

I have four columns [Participant, Value, Rule and Period] There are only two types of [Rule].

I want to Select the participant's value for each Period only if the value of Rule 1 is >= to value of Rule 2.

I am not sure how to denote the comparison since each participant has multiple periods and each period may or may not have both rules.

I also want to sum the value of the selected periods by participant to perform functions on later.

CodePudding user response：

The following should do you good

SELECT [TR1].[PARTICIPANT], SUM([TR1].[VALUE]) AS [VALUE], [TR1].[Period]
FROM 
(
    SELECT [PARTICIPANT], SUM([VALUE]) AS [VALUE], [RULE], [Period]
    FROM [TABLE]
    WHERE [RULE]='RULE 1'
    GROUP BY [PARTICIPANT], [RULE], [Period]
) [TR1] LEFT JOIN
(
    SELECT [PARTICIPANT], SUM([VALUE]) AS [VALUE], [RULE], [Period]
    FROM [TABLE]
    WHERE [RULE]='RULE 2'
    GROUP BY [PARTICIPANT], [RULE], [Period]
) [TR2]
ON [TR1].[PARTICIPANT] = [TR2].[PARTICIPANT] AND 
    [TR1].[Period] = [TR2].[Period]
WHERE [TR1].[VALUE] >= COALESCE([TR2].[VALUE], 0)

CodePudding user response：

It looks like you want something like this: a HAVING with conditional aggregation.

But it's unclear what you want for selecting the VALUE column. Is it a conditional SUM?

SELECT
  t.PARTICIPANT,
  t.PERIOD,
  SUM(CASE WHEN t.[RULE] = 'RULE 1' THEN t.VALUE END) VALUE
FROM [TABLE] t
GROUP BY
  t.PARTICIPANT,
  t.PERIOD
HAVING SUM(CASE WHEN t.[RULE] = 'RULE 1' THEN t.VALUE END)
    >= SUM(CASE WHEN t.[RULE] = 'RULE 2' THEN t.VALUE END)

db<>fiddle