Find entries, meant to only be available on specific days, being set on incorrect days FYI: I'll be using different table names and column names to the ones actually used in the live system throughout this question, and (obviously) leaving out any tables I don't believe are relevant to the question.

So, this is a tricky one for me, and I'd like to get some help with this issue. I'm probably missing an easy solution here as I might just be a bit burnt out at the moment:

Tables and their relationship to each other:

Table "Cycle": This has "BeginDate" (type DATE), and "NumberOfWeeks" (type TINYINT). NumberOfWeeks will be either 4 or 5, and BeginDate will be some date, from which everything goes forward. For this question, let's say the BeginDate is 2017-10-23, and NumberOfWeeks is 5. This means that there's a cycle from the day set in BeginDate until 2017-11-26 (inclusive), and the next cycle then begins on 2017-11-27 (5 weeks after the initial BeginDate). These cycles continue forward like this indefinitely.

Table "Entity": This has EntityID as BIGINT Primary Key, TypeID as Foreign Key to next table Type, and EntityDate as type DATETIME.

Table "Type": This has TypeID as BIGINT Primary Key.

Table "TypeAvailable": This has TypeAvailID as BIGINT PrimaryKey, TypeID as Foreign Key to Type, Week as TINYINT, and Day as TINYINT.

Each table has other columns in them too, but none which relate to my problem at hand.

How it works:

Each person chooses Entities which are available to them on particular days. The Entity is chosen by the user, and has a particular Type. Their availability is determined by the following:

Say a cycle is 5 weeks long for a particular location. You want to offer entities of a new type (let's call it TypeA) on every day on the first week of each cycle, and on Wednesday on every third week of each cycle. TypeA would be added with, let's say, TypeID 1 to table Type.

TypeA is then also created in TypeAvailable twice. One entry, TypeAvailID 1, has Week set to 1, and Day set to 0 (zero denoting that it's available every day that week). The second entry, TypeAvailID 2, has Week set to 3, and Day set to 3.

Lets say you wanted another type, TypeB, and you wanted this type available on every Monday of every week of the cycle. It would have TypeID 2. Its corresponding TypeAvailable  (with TypeAvailID 3) has a Day set to 1, and Week set to 0 (again, denoting that it's available on every week of the cycle).

The user is looking at a particular day, and is presented with Types which are available to be picked on that day of the cycle. When they select one, it's added as an Entity, with the TypeID of the Type they chose and the EntityDate for the date they chose it on, to the Entity table.

The Problem:

At some point, junk data got introduced to the system. Specifically, some users now have Entities booked in their name on dates that the Type isn't actually set to be available.

For one example, a random user might have an Entity booked in their name for for an EntityDate which corresponds to week 2 of the cycle, on a Friday. However, the Entity is TypeA, which is only available all week on Week 1, and Wednesday on Week 3. I.e. it shouldn't be there in the Entity table, on week 2 of the cycle, on a Friday, at all.

I need to find the IDs of all Entities which are booked in for invalid dates.

I've been at this problem for a little while now and as of yet haven't cracked the problem (as mentioned, I think I'm a bit burnt out at the moment, which probably isn't helping, either). Any help?



I know that "Restore From Backup" is usually an ideal solution, but the issue is that this is a problem which has been building up for many months now, and the business only recently began to notice the issue. As such, it's not really a good option as a solution to this problem, so please don't offer that up as an answer. Thank you :)
There are a number of ways to do this. Here's one suggestion:

First, you need to be able to compare the dates for a given Entity to the available dates for its Type.

The available dates for a Type are given in terms of cycle week and week day. The dates for an Entity are actual calendar dates.

So, to my way of thinking, we should convert the calendar dates to cycle week and week day. You could do the reverse, of course, but you'd have to determine different dates for different cycles, which could get confusing.

Not sure how you're calculating days of the week and weeks; I would look at getting the number of days from Cycle.BeginDate to Entity.EntityDate, using % to get the number of days that is into the current cycle (this assumes that the Cycle.BeginDate is set once and left alone - the modulo may be unnecessary if the begin date is updated to match the current cycle). Then, that number of days should also determine the week in the cycle for that EntityDate ((NumDays + 1 / 7) + 1, assuming NumDays is an integer, should work).

I would, in addition, create a temp table with the expanded weeks and days when each Type is available. That is, replace rows with Week = 0 with 4 (or 5, depending on the Cycle.Weeks) rows (Week = 1, Week = 2, etc.), and then rows with Day = 0 with 5 (or 7, if you can book on weekends) rows, (Day = 1, Day = 2, etc.). This will allow a direct one-to-one match between the week and day values for each EntityDate, and the values from TypeAvailable. It's certainly possible to set up the comparison to handle the Week = 0 and Day = 0 values, but creating the temp table shouldn't be too expensive (depending on the number of rows in TypeAvailable), and leaves thing smuch easier for the next person to follow.

Now that you can directly compare the available weeks and days to the scheduled weeks and days, it's simply a matter of doing so, and reporting the entities without an available match. LEFT OUTER JOIN from the Entity values to the TypeAvailable temp table, and record all rows where there's no match in the temp table.