On an unrelated note, why are you using nvarchar(1) for your status instead of nchar(1)?...

Phil Parkin (12/2/2016)

---

Just having a mind warp on how to insert into the address table and get that unique int value and put it into the correct master row for...

JALLY (12/2/2016)

---

Your solution above will create a conflict between columns that rely on the the sub queries for their source of data and the multi part identifier 'inv.active' will not...

You can't have a WHERE clause in the middle of your JOIN clause. Just move your WHERE clause after ALL of the JOIN clauses.

...

Something seems off here. You're worried about a new student status affecting the sort order, but that's impossible, because the student status defines the partition. You say that...

Here is the updated code:

;

WITH unique_dates AS (

SELECT DISTINCT p.Price, d.dt, d.is_start

FROM #prices p

CROSS APPLY (VALUES(p.date_from, 1), (DATEADD(DAY, 1, p.date_to), 0)) d(dt, is_start)

)

, packed_dates AS (

SELECT ud.price, ud.dt, ud.is_start,

CASE

WHEN LEAD(ud.dt,...

UPDATE does allow you to use a named table as long as it's not the one being updated. If you use the name of the table that's being updated,...

You can't. Temp tables are only visible to the session that created them.

Drew

It depends on what you are trying to solve, which is why...

You have both a CASE expression and a COALESCE. For each of those, the possible values must be of compatible data types.

,coalesce(case WHEN FSI.FID IN ('1800','1810')

and FSI.StepCompletion is not...

wjh_uk (11/28/2016)

---

Thank you so much for code, it is a lot faster but it has some issues that I am struggling to figure out.

With the following data the faster version...

You say you want running totals, but your expected results look like they are the first step in getting your expected running totals. If that is the case, there...

This is a variation of the packing intervals problem. Since you have no gaps and no overlaps you can treat it as a gaps and islands problem where the...

Your text contains a single quote that hasn't been properly escaped. You need to use two single quotes in your text to get one single quote, so child's should...

Remove the GROUP BY. You're getting totals for every status when you only want the grand total.

Drew