NOLOCK makes sense in very limited cases.  For example, for lookup tables, such as a table of state codes.  There's zero reason to do locking when reading that table.

The big...

If you want every day in every month, then something like this:

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
  ...

Maybe this, if I understand the requirements correctly:

SELECT *
FROM POSDLYCHMQ P
LEFT OUTER JOIN (SELECT P0ISBN, p0CHN, P0WEDT, 
COUNT(DISTINCT CASE WHEN cast(cast(sq.P0ADAT as varchar(30)) as date) 
BETWEEN DATEADD(DAY,-7,DATEDIFF(DAY,0,cast(cast(sq.P0WEDT...

As Jeffrey stated.

Most importantly, make sure you have the best clustering index on all the main tables so that you limit the scan/search activity on the tables.  (Hint: most often...

You don't need to actually count VLFs yourself, you can use sys.dm_db_log_stats instead (via column total_vlf_count).

You could fairly easily capture previous count(s) and compare them to current count using a...

The problem in  your original statement is that your calc -- CHARINDEX('^',RIGHT(UNDERLYING_SYMBOL, LEN(UNDERLYING_SYMBOL) - CHARINDEX('^', UNDERLYING_SYMBOL))) -- involves only numeric values, so the result is numeric.  Since it's being assigned...

update   dbo.MainTable
set     UNDERLYING_SYMBOL = STUFF(UNDERLYING_SYMBOL, 1, 1, '')
where    underlying_symbol LIKE '^%'

It's possible it would be far better to cluster the dbo.User_Events table by AuditTimestamp rather than UserID.  That could be a significant change, however, so if you would first run...

And to make the code not reliant on a particular DATEFIRST setting, don't use DATEPART, instead do this:

...
AND DATEDIFF(DAY, 0, a.date) % 7 <> 4 -- Ensure...

I need just the passenger events (0 or o,1).  But I think I may have gotten the answer from a colleague:

SELECT costCenterCode, scheduleDate, tripid, routeId, routename, activityId, eventOrder, CASE...

It's just another expression per value, not statement.  You could pre-code 10 or even 20 values without a lot of trouble:

SELECT ResourceType, CustomerID, DOB,
   ...

I'm not sure I fully understand the requirements.

Do you need the first and last [activityId] = 0 / IN (0, 1) in the partition regardless, or does some other row...

WITH cteRowNum(GroupName,AgentName,AgentEmail,

TicketsCompleted, RowNums) AS (

SELECT GroupName,AgentName,AgentEmail,TicketsCompleted,

DENSE_RANK() OVER(PARTITION BY GroupName ORDER BY TicketsCompleted DESC) AS RowNums

FROM Table

WHERE AgentName IS NOT NULL

)

SELECT cteRowNum.GroupName,cterowNum.AgentName,cteRowNum.AgentEmail, cteRowNum.TicketsCompleted

FROM cteRowNum

WHERE cteRowNum.RowNums = 1;

I assume you could have a Function2 and then data for it.  If so, there needs to be something to order the rows: an identity column, a datetime, etc..  Do...

Perhaps you could create a view with the columns in your (client's) preferred order?  It it was a full view, it should be updatable and therefore the view name could...