SELECT  CASE STDEV(DefinitionValue) 
                      WHEN NULL THEN 70641.1907289367
                      WHEN 0 THEN 70641.1907289367
                      ELSE STDEV(DefinitionValue)
                   END     
     FROM #WorkLoad
    WHERE DefinitionDate >= DATEADD(MONTH, DATEDIFF(MONTH, '19000401', CURRENT_TIMESTAMP), '19000101')
	   AND DefinitionDate <= DATEADD(dd,DATEDIFF(dd,0, GETDATE() ),0) 

  SELECT  CASE 
                      WHEN STDEV(DefinitionValue) IS NULL THEN 70641.1907289367
                      WHEN STDEV(DefinitionValue) = 0 THEN 70641.1907289367
                      ELSE STDEV(DefinitionValue)
                   END     
     FROM #WorkLoad
    WHERE DefinitionDate >= DATEADD(MONTH, DATEDIFF(MONTH, '19000401', CURRENT_TIMESTAMP), '19000101')
	   AND DefinitionDate <= DATEADD(dd,DATEDIFF(dd,0, GETDATE() ),0)   

CASE STDEV(DefinitionValue) WHEN NULL 

CASE when STDEV(DefinitionValue)=NULL 

quote:

---

Originally posted by yosiasz

Greetings

When doing following select,

  SELECT  CASE STDEV(DefinitionValue) 
                      WHEN NULL THEN 70641.1907289367
                      WHEN 0 THEN 70641.1907289367
                      ELSE STDEV(DefinitionValue)
                   END     
     FROM #WorkLoad
    WHERE DefinitionDate >= DATEADD(MONTH, DATEDIFF(MONTH, '19000401', CURRENT_TIMESTAMP), '19000101')
	   AND DefinitionDate <= DATEADD(dd,DATEDIFF(dd,0, GETDATE() ),0) 

whereas this works as wanted but would rather not call STDEV so many times. Am I missing something.

  SELECT  CASE 
                      WHEN STDEV(DefinitionValue) IS NULL THEN 70641.1907289367
                      WHEN STDEV(DefinitionValue) = 0 THEN 70641.1907289367
                      ELSE STDEV(DefinitionValue)
                   END     
     FROM #WorkLoad
    WHERE DefinitionDate >= DATEADD(MONTH, DATEDIFF(MONTH, '19000401', CURRENT_TIMESTAMP), '19000101')
	   AND DefinitionDate <= DATEADD(dd,DATEDIFF(dd,0, GETDATE() ),0)   

Thanks

---

SELECT  COALESCE(NULLIF(STDEV(DefinitionValue),0),70641.1907289367)
                        
     FROM #WorkLoad
    WHERE DefinitionDate >= DATEADD(MONTH, DATEDIFF(MONTH, '19000401', CURRENT_TIMESTAMP), '19000101')
	   AND DefinitionDate <= DATEADD(dd,DATEDIFF(dd,0, GETDATE() ),0)