Great point; sorry, I accidentally left out that "current year" requirement.

If you just want the MemberID, then as below.  Of course you can adjust the query against the cte to pull more columns if you want them.  Either way, I...

Can you clarify...

You want the unique clustered index on ( date_, ID_vard ), assuming that you do primarily query the table by date_, and not on just ID_vard.

You likely should encode the room name for internal use -- I'd probably call it "room_code" -- but you'll need at least a smallint, and I'd probably just "byte" the...

I am going to add this statement at the top of the SP, it will prevent or minimize deadlocks:

SET DEADLOCK_PRIORITY HIGH

No, it won't.  Instead, it will force SQL to kill...

Change the WHERE conditions to match:

...
WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and --<<-- "day = " removed
( (month = @month and year = @year...

I think it would be as below.  You must put the partition values in alpha order.

CREATE PARTITION FUNCTION PF_Postcode(varchar(100))

AS RANGE LEFT FOR VALUES

(

'7420_FMIL',

'C7_4JM',

'ND8_1ZE'

)

Hi Scott, getting an error on Column not found in this piece;

MAX(CASE WHEN month = @month AND year = @year THEN ControlPointName END)

That one is part of the...

Run CHECKDB to determine errors.  Backup the existing db to make sure you'll never be worse off than you are now.

If an error is in a non-clustered index, simply drop...

I think this will do it, but I have not tested it.

SELECT
    MAX(CASE WHEN month = @month AND year = @year THEN ControlPointName END)...

I'd strongly urge you to avoid a tinyint.  Mainly because the room number is "904", not just 4 (and not 94).  If you think about it, "room 4"...

;WITH cte_test_data AS (
    SELECT CAST('163 Bathroom {BATHROOM CLEANING}{2.5%}' AS varchar(100)) AS string
)
SELECT
    string AS original_string,
    CASE WHEN...

I'd strongly urge you to avoid a tinyint.  Mainly because the room number is "904", not just 4 (and not 94).  If you think about it, "room 4" is meaningless,...

I have to say, in this case I would use a char/varchar column for room numbers, not an int.  Even if the hotel guest rooms are all...