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 2 years, 10 months ago by  kherald69. Reason: Fixed missing table/field aliasing
• This reply was modified 2 years, 10 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 just need to change the INNER JOINs to FULL JOINs and change the a.id field in the SELECT portion to COALESCE(a.id, b.id, c.id, d.id) and give it the alias...

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...