COALESCE
The COALESCE function is passed an undefined number of arguments and it tests for the first non-null expression among them.
The syntax is as follows:
COALESCE(<expression1> [,...n])
It returns the data type of expression with the highest data type precedence. For data type precedence, see SQL Server Books Online.
If all of the arguments are NULL, then COALESCE returns NULL.
It is equivalent to this CASE function:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
END
Example:
SELECT
TrnPK TrnRVUTrnPK,
dpyear,
cptcode,
...
CASE
WHEN trnmod1='26' OR trnmod2='26' OR trnmod3 = '26' THEN '26'
WHEN trnmod1='TC' OR trnmod2='TC' OR trnmod3 = 'TC' THEN 'TC'
WHEN trnmod1='53' OR trnmod2='53' OR trnmod3 = '53' THEN '53'
ELSE NULL
END TrnCptMod,
ISNULL(ModAdj1.ModAdj,1) Adjust1,
ISNULL(ModAdj2.ModAdj,1) Adjust2,
ISNULL(ModAdj3.ModAdj,1) Adjust3,
TrnUnits,
/* ------- Compute the non-adjusted RVU's ------- */
COALESCE(dim_rvu.rvu_work,rvunomod.rvu_work,0) * TrnUnits WorkRVUs,
...
/* ------- Compute the adjusted RVU's ------- */
COALESCE(dim_rvu.rvu_work,rvunomod.rvu_work,0) * TrnUnits *
ISNULL(ModAdj1.ModAdj,1) *
ISNULL(ModAdj2.ModAdj,1) *
ISNULL(ModAdj3.ModAdj,1) AdjustedWorkRVUs,
...
FROM dim_transac
JOIN fact_transac ON trnpk = trnfkid
JOIN dim_cpt ON cptpk = trncptid
JOIN dim_pdmap ON dppk = trnpostpdid
JOIN dim_location ON locpk = trnlocid
JOIN dim_paycode ON TrnPayCodeID = PaycodePK
LEFT JOIN (SELECT * FROM dim_rvu WHERE rvu_mod IS NULL) rvunomod
ON rvunomod.rvu_year = dpyear AND rvunomod.rvu_hcpcs = cptcode
LEFT JOIN dim_rvu ON dim_rvu.rvu_year = dpyear AND dim_rvu.rvu_hcpcs = cptcode
AND ISNULL(dim_rvu.rvu_mod,'00') = ISNULL(CASE
WHEN trnmod1='26' OR trnmod2='26' OR trnmod3 = '26' THEN '26'
WHEN trnmod1='TC' OR trnmod2='TC' OR trnmod3 = 'TC' THEN 'TC'
WHEN trnmod1='53' OR trnmod2='53' OR trnmod3 = '53' THEN '53'
ELSE NULL
END,'00')
LEFT JOIN dim_modadj modadj1 ON modadj1.ModCode = TrnMod1
LEFT JOIN dim_modadj modadj2 ON modadj2.ModCode = TrnMod2
LEFT JOIN dim_modadj modadj3 ON modadj3.ModCode = TrnMod3
WHERE ((cptcode IS NOT NULL)
AND (PayCodeNum = '99'))
In this example, COALESCE is used to return the modified work RVU first, then the non-modified work RVU (if the modified work RVU is NULL), then 0 (if both the modified and non-modified work RVU’s are NULL).
BONUS SQL
Compute the Last Day of the Month:
DECLARE @temp_date datetime
SET @temp_date = CAST('02/15/2009' AS datetime)
SELECT DATEADD(month, DATEDIFF(month, -1, @temp_date), -1)
Results:
2009-02-28 00:00:00.000
It also works for the Last Day of the Quarter and the Last Day of the Year.
DECLARE @temp_date datetime
SET @temp_date = CAST('02/19/2009' AS datetime)
SELECT DATEADD(quarter, DATEDIFF(quarter, -1, @temp_date), -1)
SELECT DATEADD(year, DATEDIFF(year, -1, @temp_date), -1)
Results:
2009-03-31 00:00:00.000
2009-12-31 00:00:00.000
Strip the Time from a Date:
DECLARE @temp_date datetime
SET @temp_date = GETDATE()
SELECT @temp_date
SELECT CAST(FLOOR(CONVERT(FLOAT,@temp_date)) AS DATETIME)
Results:
2009-02-19 10:00:49.187
2009-02-19 00:00:00.000
This could be especially useful when joining a datetime data field to dim_date.
