Filter clause query

1

Hi,

I need help on a query to extract all rows between the period - 01/04/2021 and 31/03/2022 based on last_modified_date column.

However, the problem is I need to include all rows where balances are above 0 but the last_modified_date is out of the above range. This is because the balance has remained unchanged in the period between 01/04/2021 and 31/03/2022.

For example -

The latest last_modified_date for an ID is 01/01/2020 (max date but falls outside the above range - 01/04/2021-31/03/2022) and the balance is 100. This means the current balance stayed at 100 even in the date range - 01/04/2021 - 31/03/2022 as this balance remain unchanged from 01/01/2020. So as per the logic this row of data should be included even if it is outside of the range.

Can somebody please help with this issue ?

Thanks.

2

(Removed per next comment from OP)

3

Thanks, this is not related to the dates which are April 01 till Mar 31 respectively by the way. I am stuck on the logic for the query.

This is just blocking realistic solutions.

4
1 Like
5

Thanks again, my requirement is entirely different as mentioned in the original post.

I need to extract all data between the dates of April 01, 2021 and March 31, 2022 where the balance is more than 0 (zero) based on the last modified date.

For example, say the last modified date when the balance was updated to 100 was on January 01, 2021. So it means the balance has continued to be 100 in between the range of April 01, 2021 and March 31, 2022 as it has remain unchanged since January 01, 2021. The last modified date is outside of the range when I use it in the where clause filter for checking the dates but I still need to show this data on the report.

I mean when I use the logic last modified date between April 01, 2021 and March 31, 2022, the above row of data will not show as the last modified date is out of the range. This is where I am stuck and need help.

I hope the requirement is clear.

6

we can help you if you help us by providing full access to your database (sarcasm) or give us sample data with DDL and DML

create table #sample(last_modified_date  datetime, balance int)

insert into #sample
select getdate(), 55 union
select getdate(), 33
7

Let me give you full access to the database, that would be more appropriate as you already understand the requirement clearly, right ???

8

Here are some posting guidelines for you

https://forums.sqlteam.com/guidelines

9

Given that you haven't provided sample data as examples, and didn't write a clear explanation of anything, no, we don't understand the requirements.

1 Like
10

I provided an answer on SQL Server Central - did that work for you?