Reported as spam.

The OP should be able to solve this by adding SQL Server Central as a trusted contact.

Drew

It's a bad idea to use a NULL value to represent an unknown future date, because it forces you to use either a function (ISNULL or COALESCE) or an OR...

SELECT *
FROM
(
    Values (135,'Acute lymphocytic leukemia','Females'  ,0.23)
    ,    (136,'Acute lymphocytic leukemia','Males'  ,0.11) 
    ,    (138,'Acute myeloid leukemia','Females',0.34)
    ,    (139,'Acute myeloid leukemia','Males',0.13)
    ,    (141,'Anus','Females',0.44)
    ,    (142,'Anus','Males',0.55)
    ,    (144,'Bladder (including in situ)','Females',0.76)
    ,    (145,'Bladder...

I would use a CTE that included ALL of the aggregates and then in your main query use a CASE expression to choose which of those aggregates to actually include.

Reported as spam.

I did something like this until one of my coworkers mentioned that she was red/green colorblind.  I changed it to three "lights" with the appropriate one "lit".

Drew

Reported as spam.

/**** CROSS APPLY: This will perform well if LIC is dense (many licenses for each facility)
    and there is an appropriate index on at least FacilityID and License_Expiration_Date. ****/

Reported as spam.