1

I am trying to figure out how to apply a unique constraint based on weeks in a quarter. I have a table that includes Quarter (1Q2014, 2Q2014, ...) as well as an Effective Date and an Expiration date.

I need to allow multiple records for the same Qtr as long as Effective and Expiration Dates do not overlap.

For example, these records would be legitimate:

Qtr         EffDte          ExpDte
--------------------------------------
1Q2014      2014-01-06      2014-02-03

1Q2014      2014-02-10      2014-02-24

1Q2014      2014-03-03      2014-03-17

While these records would not be legitimate:

Qtr         EffDte          ExpDte
--------------------------------------
1Q2014      2014-01-06      2014-03-24

1Q2014      2014-02-10      2014-03-10

1Q2014      2014-03-03      2014-03-17

I am not sure if I need to alter my table design to achieve this goal.

Does anyone have any suggestions?

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
John W.
  • 1,756
  • 1
  • 12
  • 9
  • 2
    You may want to look at this: http://stackoverflow.com/questions/26693571/custom-function-with-check-constraint-sql-server-2008 – Felix Pamittan Dec 23 '14 at 02:08
  • 1
    if I were u, I would handle it from application by just validating each new record against its preceding one to avoid performance hassle – Muhammad Gouda Dec 23 '14 at 09:39
  • Okay, Thanks! I can validate from the application, I was just hoping to enforce a constraint at the database level to ensure compliance in case of any unexpected errors in the application validation. – John W. Dec 23 '14 at 17:29

0 Answers0