I'm not sure if this is more efficient, but this also works.

SELECT PackageName = 'Project'
      ,ItemType = 'Project Parameter'
   ...

This sounds like something that should be done in the presentation layer, not the data layer.  This is easy in SSRS, for example.

Drew

You have your dates switched in the DATEDIFF function.  It should be DATEDIFF(DAY, LAG(...), visit_date_01).  Or you should be using LEAD() instead of LAG().

Drew

Use the TRY_CONVERT() or the TRY_CAST() function.  They will return the value if it can convert/cast it, and will return NULL otherwise.

Drew

• This reply was modified 6 years ago by drew.allen.

Certain windowed functions that use an ORDER BY clause require a frame.  If you do not provide a frame it uses the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. ...

First, most people are hesitant to open attached files from random strangers on the net.  You should provide scripts using the {;} Insert/edit code sample button just above the text...

And why does the col2 value switch from ab to dd on 08/04/2020?

It looks like you want a DENSE_RANK(), but the exact details depend on your answer to Phil`s question.

Drew

Based on Neil's sample data, here is a first attempt at a query.

SELECT c.Name, c.NameKey, c.SaleDate, c.SaleQuantity, c.CurrentCategory, h.Value
FROM #Current c
OUTER APPLY
(
SELECT TOP(1) *
FROM #History h
WHERE h.NameKey...

You've been around long enough to know that you should provide sample data and expected results.

Drew

A picture of your data is worthless.  You need to supply actual data by supplying a script using the {;} Insert/edit code sample to do the following.

• Create a temp...

We don't have enough information to tell you exactly what the problem is, but the error message gives you a LOT of information.

1. Your table dbo.ItemDetail has a primary key...

If there are no updates (impossible without a key) or deletes, I would suggest not using MERGE, but INSERT:

INSERT target (cols)
SELECT cols FROM source
WHERE NOT EXISTS...

Please do not cross post.  Please respond on this thread https://www.sqlservercentral.com/forums/topic/need-help-parsing-xml-3#post-3727725

Drew

Sometimes a backslash is used to insert a literal character.  It looks like whatever produced this XML hypercorrected and used \" to insert a literal double quote (") when it...

The issue that you're running into is that the two separate queries have different criteria and you aren't taking that into account when you are combining them into one query. ...