Hello:

What is a more efficient way of doing the following such that DATEDIFF() does not have to calculated numerous times?

CASE
  WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 0 THEN '<= 0D'
  WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 0 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 7 THEN '> 0D AND <= 7D'
  WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 7 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 30 THEN '> 7D AND <= 30D'
  WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 30 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 60 THEN '> 30D AND <= 60D'
  WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 60 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 90 THEN '> 60D AND <= 90D'
  WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 90 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 120 THEN '> 90D AND <= 120D'
  WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 120 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 150 THEN '> 120D AND <= 150D'
  WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 150 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 180 THEN '> 150D AND <= 180D'
  WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 180 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 365 THEN '> 180D AND <= 365D'
  WHEN DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) > 365 AND DATEDIFF(DD, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) <= 1825 THEN '> 365D AND <= 5YR'
  ELSE '> 5YR'
  END AS [CalcDateGroup]

Thank you in advance!

In your query you can use

select ..., case when dif.DaysDifference between ...

from myTable

CROSS APPLY (select DATEDIFF(Day, @Today, COALESCE(POS.[PurchaseDate], POS.[FinalizedDate])) as DaysDifference) dif

We can't use BETWEEN because the ranges are not inclusive.

We also rather not do CROSS APPLY since there are various other JOINS and similar CASE statements in the query already.

We're hoping to a something more similar to this syntax if it exists:

,CASE DATEDIFF(DD, POS.[PurchaseDate], @Today) AS X
  WHEN X <= 7 THEN '<= 7D'
  WHEN X > 7 AND X <= 30 THEN '> 7D AND <= 30D'
  WHEN X > 30 AND X <= 60 THEN '> 30D AND <= 60D'
  WHEN X > 60 AND X <= 90 THEN '> 60D AND <= 90D'
  WHEN X > 90 AND X <= 120 THEN '> 90D AND <= 120D'
  WHEN X > 120 AND X <= 150 THEN '> 120D AND <= 150D'
  WHEN X > 150 AND X <= 180 THEN '> 150D AND <= 180D'
  WHEN X > 180 AND X <= 365 THEN '> 180D AND <= 365D'
  WHEN X > 365 AND X <= 1825 THEN '> 365D AND <= 5YR'
  ELSE '> 5YR' 
  END AS DaysSincePurchased

Hi,

you can create variable table and do join.

DECLARE @Ranges AS TABLE(
MinNumber INT,
MaxNumber INT,
RangeText VARCHAR(100))

INSERT INTO @Ranges VALUES
(0,0,'<= 0D'),
(0,7,'> 0D AND <= 7D'),
(7,30,'> 7D AND <= 30D'),
(30,60,'> 30D AND <= 60D')

SELECT *
FROM @Ranges

The between was just an example, you can use your ranges fine. CROSS APPLY here would work just fine, it is a syntactic sugar for creating a computed column on the fly and re-using that computed column. See sample of that idea in this blog post

http://blogs.lessthandot.com/index.php/datamgmt/datadesign/parsing-fullname-field-to-individual/

Note, please, that this type of CROSS APPLY doesn't use FROM clause, e.g. it is only used for creating a computed column on th