How to exclude rows in SQL if the same key exists in other row with different state

Question

I have some table data like this:

account  createddate  closed_date   account_type  Initial_Amount    Record_date    Status   

1234      01/02/2023   01/01/2099   Normal         100           01/02/2023      ACTIVE
7892      02/02/2023   01/01/2099   Premimum       200           01/02/2023      ACTIVE
4567      03/02/2023   01/01/2099   Normal         500           01/02/2023      ACTIVE
8120      08/02/2023   01/01/2099   Normal         500           02/02/2023      ACTIVE
8890      04/02/2023   01/01/2099   Premimum       500           05/02/2023      ACTIVE
1234      04/02/2023   19/02/2023   Normal         0             19/02/2023      CLOSED
8890      04/02/2023   01/03/2023   Premimum       0             01/03/2023      CLOSED 
3542      02/03/2023   01/01/2099   Premimum       300           02/03/2023      ACTIVE 
3542      03/03/2023   01/01/2099   Normal         200           03/03/2023      ACTIVE 

as SQL:

CREATE TABLE retail_account (
    account integer
    , createddate varchar
    , closed_date varchar
    , account_type varchar
    , initial_amount integer
    , record_date varchar
    , status varchar
);

INSERT INTO retail_account VALUES
(1234, '01/02/2023', '01/01/2099', 'Normal', 100, '01/02/2023', 'ACTIVE')
, (7892, '02/02/2023', '01/01/2099', 'Premimum', 200, '01/02/2023', 'ACTIVE')
, (4567, '03/02/2023', '01/01/2099', 'Normal', 500, '01/02/2023', 'ACTIVE')
, (8120, '08/02/2023', '01/01/2099', 'Normal', 500, '02/02/2023', 'ACTIVE')
, (8890, '04/02/2023', '01/01/2099', 'Premimum', 500, '05/02/2023', 'ACTIVE')
, (1234, '04/02/2023', '19/02/2023', 'Normal', 0, '19/02/2023', 'CLOSED')
, (8890, '04/02/2023', '01/03/2023', 'Premimum', 0, '01/03/2023', 'CLOSED')
, (3542, '02/03/2023', '01/01/2099', 'Premimum', 300, '02/03/2023', 'ACTIVE') 
, (3542, '03/03/2023', '01/01/2099', 'Normal', 200, '03/03/2023', 'ACTIVE')
;

I need to return a few aggregations based on the user's selected date range (Date is DD/MM/YYYY format), For example, if the user selects data range as start date 01/02/2023 and end date as 10/02/2023 I need to return like below

Month Account_type  Count(Accouny_type)  sum(Initial_Amount) 
 02    Normal           3                       1100 
 02    Premimum         2                        700 

if the user selects the data range as start date 01/02/2023 and end date as 20/02/2023 I need to return like below

Month Account_type  Count(Accouny_type)  sum(Initial_Amount) 
 02    Normal           2                       1000 
 02    Premimum         2                        700 

I need to exclude account 1234 as it got closed and its status is CLOSED

if user selects data range as start date 01/02/2023 and end date as 03/03/2023 I need to return like below

Month Account_type  Count(Accouny_type)  sum(Initial_Amount) 
 02    Normal           2                       1000 
 02    Premimum         2                        700 
 03    Normal           1                        200  
 03    Premimum         1                        300 

I tried the below query but it still includes account 1234 and 8890 values as their previous status is active

SELECT
    DATEADD(month, 'DD/MM/YYYY') AS MM
    , Account_type
    , COUNT(Account_type) AS Count_Account_type
    , SUM(Initial_Amount) AS Sum_Initial_Amount
FROM
    retail_account
WHERE
    Record_date >= '01/02/2023' 
    AND
    Record_date <= '03/03/2023'
    AND
    Status = 'ACTIVE'
GROUP BY
    DATEADD(month, 'DD/MM/YYYY')
    , Account_type


DECLARE @CurrentYear INT;
DECLARE @NextYear INT;
DECLARE @PrevFinancialYearStart DATETIME;
DECLARE @CurrentFinancialYearStart DATETIME;

SET @CurrentYear = YEAR(GETDATE());
SET @NextYear = @CurrentYear + 1;
SET @PrevFinancialYearStart = CONVERT(DATETIME, CONVERT(VARCHAR, (@CurrentYear - 1)) + '-04-01', 120);
SET @CurrentFinancialYearStart = CONVERT(DATETIME, CONVERT(VARCHAR, @CurrentYear) + '-04-01', 120);


DECLARE @CurrentYear INT;
DECLARE @NextYear INT;
DECLARE @PrevFinancialYearStart DATETIME;
DECLARE @CurrentFinancialYearStart DATETIME;

SET @CurrentYear = YEAR(GETDATE());
SET @NextYear = @CurrentYear + 1;
SET @PrevFinancialYearStart = CONVERT(DATETIME, CONVERT(VARCHAR, (@CurrentYear - 1)) + '-04-01', 120);
SET @CurrentFinancialYearStart = CONVERT(DATETIME, CONVERT(VARCHAR, @CurrentYear) + '-04-01', 120);

SELECT *
FROM T1
WHERE NOT EXISTS (
    SELECT 1
    FROM T1 AS t2
    WHERE T1.Account = t2.Account
        AND (
            (t2.Status = 'cls' AND t2.ClsDate >= @CurrentFinancialYearStart)
            OR
            (t2.Status != 'cls' AND t1.OpenDate >= @PrevFinancialYearStart AND t1.OpenDate <= DATEADD(DAY, -1, @CurrentFinancialYearStart))
        )
);



DECLARE @CurrentYear INT;
DECLARE @NextYear INT;
DECLARE @PrevFinancialYearStart DATETIME;
DECLARE @CurrentFinancialYearStart DATETIME;

SET @CurrentYear = YEAR(GETDATE());
SET @NextYear = @CurrentYear + 1;
SET @PrevFinancialYearStart = CONVERT(DATETIME, CONVERT(VARCHAR, (@CurrentYear - 1)) + '-04-01', 120);
SET @CurrentFinancialYearStart = CONVERT(DATETIME, CONVERT(VARCHAR, @CurrentYear) + '-04-01', 120);

SELECT *
FROM T1
WHERE NOT EXISTS (
    SELECT 1
    FROM T1 AS t2
    WHERE T1.Account = t2.Account
        AND (
            (t1.OpenDate >= @PrevFinancialYearStart AND t1.OpenDate <= DATEADD(DAY, -1, @CurrentFinancialYearStart))
            OR
            (
                t1.OpenDate >= @CurrentFinancialYearStart
                AND t1.OpenDate <= DATEADD(DAY, -1, DATEADD(YEAR, 1, @CurrentFinancialYearStart))
                AND t2.Status = 'cls'
                AND (t2.ClsDate IS NULL OR t2.ClsDate >= @PrevFinancialYearStart)
            )
        )
);

Answer 1

Have you tried something like this?

WITH FilteredData AS (
    SELECT
        Account_number,
        TO_CHAR(TO_DATE(Record_date, 'DD/MM/YYYY'), 'MM') AS Month,
        Account_type,
        COUNT(Account_type) AS Count_Account_type,
        SUM(Initial_Amount) AS Sum_Initial_Amount
    FROM
        your_table_name
    WHERE
        TO_DATE(Record_date, 'DD/MM/YYYY') BETWEEN TO_DATE('01/02/2023', 'DD/MM/YYYY') AND TO_DATE('03/03/2023', 'DD/MM/YYYY')
)
SELECT
    Month,
    Account_type,
    Count_Account_type,
    Sum_Initial_Amount
FROM
    FilteredData
WHERE
    Account_number NOT IN (
        SELECT DISTINCT Account_number
        FROM your_table_name
        WHERE Status = 'CLOSED'
        AND TO_DATE(closed_date, 'DD/MM/YYYY') < TO_DATE('01/02/2023', 'DD/MM/YYYY')
    )
ORDER BY
    Month,
    Account_type;

Answer 2

There are some issues with the current table/query design:

• having the date columns defined as varchar means you can't rely on simple string comparisons (eg, Record_date <= '03/03/2023') to work as you expect
• dateadd() is used to perform date 'math'; you probably want something like datepart()

Instead of addressing these issues I'm going to assume OP's real query is correct and behaves as described, and will just add a not exists() subquery that should generate OP's desired outcome:

SELECT
    DATEADD(month, 'DD/MM/YYYY') AS MM
    , Account_type
    , COUNT(Account_type) AS Count_Account_type
    , SUM(Initial_Amount) AS Sum_Initial_Amount
FROM
    retail_account ra1
WHERE
    Record_date >= '01/02/2023' 
    AND
    Record_date <= '03/03/2023'
    AND
    Status = 'ACTIVE'

    and not exists(select  1
                   from    retail_account ra2
                   where   ra2.Record_date >= '01/02/2023'   -- same limits as with ra1
                   and     ra2.Record_date <= '03/03/2023'   -- same limits as with ra1
                   and     ra2.account = ra1.account         -- join with ra1
                   and     ra2.status  = 'CLOSED')           -- further limit by status

GROUP BY
    DATEADD(month, 'DD/MM/YYYY')
    , Account_type

This should generate:

Month Account_type  Count(Accouny_type)  sum(Initial_Amount) 
 02    Normal           2                       1000 
 02    Premimum         1                        200
 03    Normal           1                        200  
 03    Premimum         1                        300 

NOTSE:

• untested as I don't have access to a Sybase/SAP IQ instance
• I'm assuming OP's expected outcome has a typo in the 2nd line (ie, instead of 02 / Premimum / 2 / 700 it should be 02 / Premimum / 1 / 200)