I would also be asking WHY you need to re-insert it with the correct ColumnID record.

If ID contiguity (i.e. no breaks) is an audit requirement then you have just failed...

• This reply was modified 3 years ago by  aaron.reese.

It really depends on whether you understand the consequences of NOLOCK.  It can result in a 'dirty read' if there are uncomitted transactions in the database that affect the data...

Like Phil said, you should know better.....

Your table has no name

you have provided a table structure but no data

What is a PTO (I am assuming Period of Time Off)

Your table...

+1 for Scotts suggestion.  Personally I prefer the COALESCE() operator over ISNULL.  There are too many other places where the NULL word appears in syntax so I find COALESCE() has...

If you are doing an aggregation every field must be either an aggregation type or included in the GROUP BY.

If you don't want it aggregated then you will need to...

The Pedant in me yells back Common Table Expression.

Technically a CTE is a named query.  But it is probably not in the context of the OP question

It's a conceptual thing.   in SQL server a query is an ad-hoc thing that you write in Management Studio (or Azure Data Studio, or VS Code).  Access doesn't have ad-hoc...

Don't forget that if you are looking for 30 days rolling you will need to inject missing dates so that they are contiguous as the BETWEEN is looking at row...

That sounds like a suitable temporary solution. If you are in discussions with the customer can you get the data format changed to either XML or JSON or even an...

This is one where you are probably going to have to make a tradeoff between performance and cognitivie load / maintenance costs.  Your approach means than any changes in business...

Thanks Brian,  I was being faceicous about the comments, I know it's not Microsofts fault 🙂

I didn't state the problem clearly enough.  I am not trying to develop the code...

OK, you learn something new every day...

Just ran the create script for the index and it contains a WHERE condition.

CREATE UNIQUE NONCLUSTERED INDEX [IX_TableName_Current] ON [dbo].[TableName]
(
[Entity_Identifier] ASC
)
INCLUDE([AttributeField_1],[AttributeField_2]...)...

not wanting to get off-topic and there are dozens of threads on EAV on this site, why would you prefer EAV for this problem.  I can turn it into EAV...

Thanks Scott, you have waaay too much time on your hands.

That was effectivley what I was going to do but rather than create 300 views, just dump the data sets,...

Have a look at the DataVault pattern.  It allows you to store temporal changes in a very efficient manner as you can split the columns in a table into fast...