List premises & accounts within time period

Question

I need to pull data from our MS SQL Server database regarding Premises (i.e. houses) and the accounts (i.e. people) that have live there (or are still living there) and their service period at that premise.

There could be three types of cases that I have to account for:

1. The most basic case where a person moved in, started service and is still living there
2. The next case is someone who's moved in, had service for a time period, but then has moved out and is no longer living in the location
3. The last scenario is someone who moved in, had service, moved out (ended service) and then subsequently moved back in (think apartment living).

The last scenario is where I am having issues and need some help.

The database has fields in an account_service table that list "from" and "to" dates. To show how that would look in the database, I will list the data corresponding to the three cases above:

     Premise#  - Account# -   From Date -   To Date --     (details on the data)
  1. 00125466,   146732,      2/2/2001,     NULL            (no to_date since still living there)
  2. 10085279,   180887,      6/12/1978,    1/15/2020       (moved in and moved out)
  3. 00005110,   **267110**,    10/15/2020, 12/14/2021   (live there from Oct. 2020 - Dec. 2021)
  3a.00005110,   273341,      12/14/2021,   12/09/2022     (live there from Dec. 2021 - Dec. 2022)
  3b.00005110,   **267110**,    12/09/2022, NULL         (moved in Dec. 2022 and still there now)

What complicates all this is that the account_service table lists ALL the services that a Premise has active during the account holder's time there. This can be water, sewer, trash, etc.

I am also only interested in accounts that had (or have) service in the last three years.

My SQL looks like this: (looking at just the #3 scenario above)

SELECT DISTINCT TOP (100) PERCENT dbo.servicelocation.propertynumber, dbo.account.accountnumber, dbo.accountservice.fromdate, dbo.accountservice.todate
FROM   dbo.account INNER JOIN
       dbo.accountservice ON dbo.account.account_id = dbo.accountservice.account_id INNER JOIN
       dbo.service ON dbo.accountservice.service_id = dbo.service.service_id INNER JOIN
       dbo.servicelocation ON dbo.service.servicelocation_id = dbo.servicelocation.servicelocation_id
WHERE  (dbo.servicelocation.propertynumber = '00005110') AND (dbo.accountservice.todate > CONVERT(DATETIME, '2021-01-01 00:00:00', 102)) OR
       (dbo.servicelocation.propertynumber = '00005110') AND (dbo.accountservice.todate IS NULL)
ORDER BY dbo.accountservice.fromdate DESC

The output from the SQL above look like this:

Ideally, I would like to have the first line followed by the fourth line and then the fifth line as the final line:

For reference, the SQL view looks like this:

The data in the software looks like this:

I hope this explains fully what I am after, but please let me know if more information is needed. Thank you!

[Edit - More Clarification] Ideally, I would like the MIN(from_date) and MAX(to_date) for each premise and account. That works when there is only one resident, but when we have several tennants moving in and out, it gets complicated fast. My query will list the two accounts (for scenario #3), but will only show 267110 living there from Oct. 15, 2020 to the present time as they are still there. This creates an issue because 273341 lived there DURING that time. We can't have two occupants/accounts at a Premise for the same time period.

I've tried various permutations on the view... using MAX(to_date) helps in certain cases, but misses the scenario #3 described earlier. I think this is possible, but am having a tough time coming up with the correct SQL.

Answer 1

If we sort by [from date] and add a row counter that resets for each property+account we can identify individual occupation periods.

SELECT DISTINCT TOP (100) PERCENT propertynumber, dbo.account.accountnumber, dbo.accountservice.fromdate,dbo.accountservice.todate, 
     ROW_NUMBER() OVER(PARTITION BY dbo.servicelocation.propertynumber+dbo.account.accountnumber ORDER BY FromDate DESC) as OccupationSequence
FROM   dbo.account 
INNER JOIN  dbo.accountservice ON dbo.account.account_id = dbo.accountservice.account_id 
INNER JOIN  dbo.service ON dbo.accountservice.service_id = dbo.service.service_id 
INNER JOIN  dbo.servicelocation ON dbo.service.servicelocation_id = dbo.servicelocation.servicelocation_id

fiddle

propertynumber	accountnumber	fromdate	todate	OccupationSequence
00005110	267110	2020-10-15	2021-12-14	2
00005110	267110	2022-12-09	null	1
00005110	273341	2021-12-14	2022-12-09	1

propertynumber	accountnumber	MinFrom	MaxTo
00005110	267110	2022-12-09	null
00005110	273341	2021-12-14	2022-12-09
00005110	267110	2020-10-15	2021-12-14