Usually, we get to pass a date between two different dates eg. To find Employees Active in a company we can say current_date between the effectivedate and end date, this will return effectivedates that match the above condition.
But what if instead of the current date we have a date range, Postgres does offer a Time/Date function called Overlaps which can check if 2 date range OVERLAPs.
I can explain further with the set of example:
Code:
With SampleTable as(
SELECT 'Sohail' as Name, '2001-10-30'::Date as effectivedate, '2002-10-30'::Date as enddate
Union
SELECT 'Izebhijie' as Name, '2001-05-27'::Date as effectivedate, '2001-10-30'::Date as enddate
Union
SELECT 'Is' as Name, '2001-10-30'::Date as effectivedate, '2001-10-31'::Date as enddate
Union
SELECT 'A Good Boy' as Name, '2001-10-30'::Date as effectivedate, '2001-10-31'::Date as enddate
)
Select * from SampleTable
where
--(DATE '2001-02-16', DATE '2001-12-21') OVERLAPS (effectivedate, enddate)
/*4 Rows Because the above-fixed dates overlap the effective and enddate*/
--(DATE '2001-02-16', INTERVAL '100 days') OVERLAPS (effectivedate, enddate)
/*0 Rows because the Fixed date doesn't fall between the effective and enddate*/
--(DATE '2001-10-29', DATE '2001-10-30') OVERLAPS (effectivedate, enddate)
/*1 Row because the Fixed Date Last Date is not equal to the Effective date*/
--(DATE '2001-10-30', DATE '2001-10-30') OVERLAPS (effectivedate, enddate)
/*3 Rows because (DATE '2001-10-30', DATE '2001-10-30') is considered as one date and it should overlap between 2 dates.
Best Open Source Business Intelligence Software Helical Insight is Here
A Business Intelligence Framework
The reason the Izebhijie Row is not picked is because both the enddates(endpoint) match which then returns false*/
Feel Free to drop a comment below if you got doubts
When you login first time using a Social Login button, we collect your account public profile information shared by Social Login provider, based on your privacy settings. We also get your email address to automatically create an account for you in our website. Once your account is created, you'll be logged-in to this account.
DisagreeAgree
I allow to create an account
When you login first time using a Social Login button, we collect your account public profile information shared by Social Login provider, based on your privacy settings. We also get your email address to automatically create an account for you in our website. Once your account is created, you'll be logged-in to this account.
For deeper insights, you may also explore: SSRS Reports end of life SSRS structured migration approach SQL Server Reporting Services (SSRS) has served enterprises well for traditional reporting needs. However, as Microsoft continues to evolve toward cloud-first and modern analytics...
For deeper insights, you may also explore: why SSRS Reports migration is required SSRS automation limitations Migrating from SQL Server Reporting Services (SSRS) to Pentaho is a strategic initiative for organizations seeking scalable, flexible, and open-source business intelligence. A successful...
For deeper insights, you may also explore: Crystal Reports vs Jaspersoft SSRS step-by-step migration approach Organizations modernizing their BI and reporting stacks often face a familiar challenge: migrating from SQL Server Reporting Services (SSRS) to more flexible, open-source platforms like...
