SQL query to filter out data based on certain condition

I have a data on my table like below

Id Val Date1 Date2 ProductId SnapshotDate TotVal AcctId Date3 Date4
1 100 2021-03-01 2021-03-15 NULL 04/01/2021 300 123 NULL NULL
NULL 200 NULL NULL NULL 04/01/2021 NULL 123 NULL NULL
4 300 2021-04-01 2021-04-10 4 04/03/2021 500 123 NULL NULL
2 500 2021-04-01 2021-04-10 2 04/03/2021 500 456 2021-04-01 2021-04-10
3 600 2021-04-15 2021-04-20 NULL 04/04/2021 200 567 2021-04-01 2021-04-20

I wanted to make sure I filter out the AcctId that has different values for TotVal and Val, Date3 and Date1, Date4 and Date2, Id and Product Id, Basically the pairs that I mentioned should have the same value, so that I don’t consider those entries for filtering.

Id != ProductId, Val != TotVal, Date1 != Date3, Date2 != Date4

If there are two entries for an AcctId on a particular snapshotdate, and if the TotaVal matches sum of the Val for both these entries, then we should exclude from the filter.

In the above example,

first row entry and second row entry should not be considered for the AcctId 123 for filtering, since TotVal(300) is equal to Val (100 + 200) and the snapshot date is same (04/01/2021)

where as third row entry for the AcctId 123 should be considered, since TotVal(500) has different value as that of Val(300) and also Date1 (2021-04-01) has different value as that of Date3 NULL and also Date 2(2021-04-10) has different value as that of Date4

Fourth row entry for AcctId 456 should not be considered, since

Id = ProductId (2), Val = TotVal (500), Date1 = Date3 (2021-04-01), Date2 = Date4 (2021-04-10) for a particular snapshot date

Fifth row entry for AcctId 567 should be considered for filter since

Id != ProductId, Val != TotVal, Date1 != Date3

I have been trying to write a query for this but I am yet to succeed, but if someone could help me out here, that would be great.

Thanks in advance.

submitted by /u/sriramchander89
[link] [comments]


Go to Source of this post
Author Of this post: /u/sriramchander89
Title Of post: SQL query to filter out data based on certain condition
Author Link: {authorlink}