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.
April 11, 2022 at 4:27 pm
How about:
SELECT A , B , C ...
From Table_A
WHERE ( last_modified_date between '01/04/2021 00:00:00' and '31/03/2022 23:59:59' )
OR ( balance > 0 and last_modified_date NOT between '01/04/2021 00:00:00' and '31/03/2022 23:59:59' )
This will select dates before & after the date range if balance > 0.
April 11, 2022 at 5:38 pm
How about:
SELECT A , B , C ...
From Table_A
WHERE ( last_modified_date between '01/04/2021 00:00:00' and '31/03/2022 23:59:59' )
OR ( balance > 0 and last_modified_date NOT between '01/04/2021 00:00:00' and '31/03/2022 23:59:59' )This will select dates before & after the date range if balance > 0.
Be careful using BETWEEN with date times. If that column is a datetime data type - then you could be missing database between :59.003 and :59.997. If the columns data type is datetime2 you could be missing data between :59.0000001 and :59.9999999.
It should also be noted that 31/03/2022 (March 31st) comes before 01/04/2022 (April 1st) - if these are actually formatted as DD/MM/YYYY. It does seem a bit unusual to be checking for the last day of the month only.
I am just guessing here - but I am think the OP really wants everything up to the end of the month that either has a balance > 0 or a last_modified_date on the last day of the month. Something like:
WHERE Balance > 0
OR (last_modified_date >= '2022-03-31' AND last_modified_date < '2022-04-01')
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 12, 2022 at 11:40 am
homebrew01 wrote:How about:
SELECT A , B , C ...
From Table_A
WHERE ( last_modified_date between '01/04/2021 00:00:00' and '31/03/2022 23:59:59' )
OR ( balance > 0 and last_modified_date NOT between '01/04/2021 00:00:00' and '31/03/2022 23:59:59' )This will select dates before & after the date range if balance > 0.
Be careful using BETWEEN with date times. If that column is a datetime data type - then you could be missing database between :59.003 and :59.997. If the columns data type is datetime2 you could be missing data between :59.0000001 and :59.9999999.
It should also be noted that 31/03/2022 (March 31st) comes before 01/04/2022 (April 1st) - if these are actually formatted as DD/MM/YYYY. It does seem a bit unusual to be checking for the last day of the month only.
I am just guessing here - but I am think the OP really wants everything up to the end of the month that either has a balance > 0 or a last_modified_date on the last day of the month. Something like:
WHERE Balance > 0
OR (last_modified_date >= '2022-03-31' AND last_modified_date < '2022-04-01')
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.
Thanks.
April 12, 2022 at 5:15 pm
Please show the query you have written.
I completely missed the different years - so change the where clause to:
WHERE Balance > 0
OR (last_modified_date >= '20210401' AND last_modified_date < '20220401')
So if the item has a balance > 0 (regardless of last_modified_date) or it has been modified during that period it will be returned. Assuming you don't want items with a last_modified_date that is greater than 20220401:
WHERE (Balance > 0 AND last_modified_date < '20220401')
OR (last_modified_date >= '20210401' AND last_modified_date < '20220401')
Any items modified on or after April 1st will not be included in the results.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 12, 2022 at 8:46 pm
I completely missed the different years - so change the where clause to:
WHERE Balance > 0
OR (last_modified_date >= '20210401' AND last_modified_date < '20220401')So if the item has a balance > 0 (regardless of last_modified_date) or it has been modified during that period it will be returned. Assuming you don't want items with a last_modified_date that is greater than 20220401:
WHERE (Balance > 0 AND last_modified_date < '20220401')
OR (last_modified_date >= '20210401' AND last_modified_date < '20220401')Any items modified on or after April 1st will not be included in the results.
I completely missed the different years - so change the where clause to:
WHERE Balance > 0
OR (last_modified_date >= '20210401' AND last_modified_date < '20220401')So if the item has a balance > 0 (regardless of last_modified_date) or it has been modified during that period it will be returned. Assuming you don't want items with a last_modified_date that is greater than 20220401:
WHERE (Balance > 0 AND last_modified_date < '20220401')
OR (last_modified_date >= '20210401' AND last_modified_date < '20220401')Any items modified on or after April 1st will not be included in the results.
Many thanks for your very helpful reply, it worked perfectly !!!
April 12, 2022 at 9:02 pm
You are welcome, thank you for the feedback
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 12, 2022 at 10:27 pm
With a small tweak, the method in the following article is what I use for such bleedovers. It's NOT a short article because it actually explains how to think about such things so that you no longer have to think about it in the future. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
April 15, 2022 at 6:58 pm
This was removed by the editor as SPAM
April 15, 2022 at 7:00 pm
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply