You could also move the CASE statement into a CROSS APPLY to calculate the newAmount.

SELECT 
    ca.newAmount
  , ca.newAmount + t.Adjust as TotalAmount
From...

• This reply was modified 4 years, 3 months ago by kherald69. Reason: Fixed missing table/field aliasing
• This reply was modified 4 years, 3 months ago by kherald69.

In my company's case we use that date of 2078-12-31 as the maximum date because there are fields in some tables that are defined as SMALLDATETIME and since the maximum...

Ken, that is some pretty neat code.  I had to tweak it just a bit to support a scenario that I forgot to include in my original post.

The extra scenario...

I completely forgot about trying the EXCEPT route. The method I was trying is similar to what you have by expanding the date ranges but my method of remerging the...

You were almost there.  You correctly parameterized the @localtable variable in the first field of the select. However, to ensure that the variables that you concatenate for the object names...

Please be aware that when converting a DATETIME to a SMALLDATETIME that when the seconds are <30 the minutes will match but when the seconds are 30+...

I can see that it works but what is the meaning of the time int value.

Here is an example of why I say it is stored as a float.

with cte...

Ed Wagner (11/25/2014)

PHYData DBA (11/25/2014)

Since DateTime is stored as an Integer,...

:ermm: Is it just me or are options 1 and 4 identical?

Option #1: FROM,ON,JOIN,WHERE,GROUP BY,CUBE | ROLLUP,HAVING,SELECT,DISTINCT,ORDER BY,TOP

Option #4: FROM,ON,JOIN,WHERE,GROUP BY,CUBE | ROLLUP,HAVING,SELECT,DISTINCT,ORDER BY,TOP

I chose option #1 and it looks...

Ok, I just have to toss a wrench into the logic here.

Since we are talking about birthdays, what if the person is over 100 years old? This logic will...

You can use a recursive common table expression to achieve this.

DECLARE @startDate DATETIME = '8/1/2014'

DECLARE @endDate DATETIME = '8/31/2014'

DECLARE @dayOfWeek INT = 1 -- 1=Sun, 7=Sat

;WITH cte_Recursion AS

(

SELECT...