0

I have two tables that feed a view. The main table provides a date range to indicate eligibility for a benefit. The override table indicates that their option type is different based on a different date range.

Main Table

MemberId OptionId OptionType StartDate EndDate
000012345 123456789 Type1 1/1/2023 12/31/2023

Override Table

MemberId OptionId OptionType StartDate EndDate
000012345 123456789 Type2 11/1/2023 11/30/2023

In the example tables, the override table indicates that the member has a non-standard benefit during the override period. This means that the view feeding from these tables needs to show 3 separate records like:

View

MemberId OptionId OptionType StartDate EndDate
000012345 123456789 Type1 1/1/2023 10/31/2023
000012345 123456789 Type2 11/1/2023 11/30/2023
000012345 123456789 Type1 12/1/2023 12/31/2023

How do I achieve this efficiently?

I have tried looping through the records to populate a separate temp table using while loops and/or cursors but it runs for a very long time. This view feeds a private member facing website that needs to populate immediately.

markalex
  • 8,623
  • 2
  • 7
  • 32
StealthEmployed
  • 1
  • 2
  • 1
    Please see [tips-for-asking-a-good-structured-query-language-question](https://meta.stackoverflow.com/questions/271055) and how to provide a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) – Stu Apr 10 '23 at 21:19
  • 1
    Am I correct to assume, that override table can contain more than one row of overrides for every main table row? – markalex Apr 10 '23 at 21:41
  • Yes it can contain multiple overrides and also contain overrides that overlap two or more records from the main table. – StealthEmployed Apr 10 '23 at 22:37
  • 1
    In the case of multiple overrides how do you determine which override takes priority? – tinazmu Apr 11 '23 at 02:49
  • If it contains arbitrary number of overrides, I would argue that procedural processing is the way to go. – markalex Apr 11 '23 at 13:34
  • How often does the data change? If the "private member facing website" has to be displayed without delay then you may be better off caching the computed results when the data changes once a week/day/hour/femtosecond, e.g. by using a trigger, rather than trying to brute force the result using a supercomputer. – HABO Apr 11 '23 at 14:39
  • The [`tsql`](https://stackoverflow.com/tags/tsql/info) tag covers multiple products. Which one are you using? Is it a modern version? – HABO Apr 11 '23 at 18:51

1 Answers1

0

Join the Main table to a Calendar table and then left outer join to the Override table.

You can then select OptionId,OptionType,StartDate and EndDate values from Main when there is no value for Override and from Override when there are values.

Use Distinct to pick only one row for each date range.

Paolo de Sinno
  • 61
  • 4