The following table is given:

Columns

1. Deal start date
2. Deal end date
3. Amount

There is a table:

It is required to write a query that returns, for two dates, the total balances of active deals on these dates.

It is required to display balances on date 1 = 02.05.2020 and date 2 = 02.02.2021

I know how to get them one by one

SELECT SUM(Amount) 
FROM Table
WHERE 02.05.2020 >= End Date

Or

SELECT SUM(Amount) 
FROM Table
WHERE 02.05.2020 >= End Date OR 02.02.2021 >= End Date

But I have no idea how to create a separate table for dates:

date 1 || amount 1
date 2 || amount 2

Can you give me some advice or direction for a solution?

CodePudding user response：

You could use conditional aggregation or filtered sum function as the following:

SELECT 
  SUM(CASE WHEN '02.05.2020' >= End_Date THEN Amount ELSE 0 END) AS Date1,
  SUM(CASE WHEN '02.05.2020' >= End_Date OR '02.02.2021' >= End_Date THEN Amount ELSE 0 END) AS Date2
FROM table_name

Or:

SELECT 
  SUM(Amount) FILTER (WHERE '02.05.2020' >= End_Date) AS Date1,
  SUM(Amount) FILTER (WHERE '02.05.2020' >= End_Date OR '02.02.2021' >= End_Date) AS Date2
FROM table_name

See a demo.