SELECT key
FROM dbo.your_table_name_here
GROUP BY key
HAVING MAX(CASE WHEN type = 'COLL' AND subtype = 'MOD' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN...

I don't think so.  PIVOT works on only one column at a time, IIRC.  A cross-tab is almost certainly the best method here.

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...