DISTINCT was clearly the key here. Thanks for this basic question. 🙂

Nice question. 🙂

I think the question here simple. Varchar(3) was never tricky. The only eye raising thing was for NULL value. As the SQL server treats default NULL as int, it should...

Bhaskar.Shetty (5/23/2013)

Insert Happens mince do you mean by AFTER INSERT OR INSTEAD OF INSERT, if it is after insert you can create three seprate triggers or suggsted in previous post...

Why not try three different triggers rather than using one ?

If you insist on making just one trigger based on all events, here is what you can do inside the...

It was very tricky. I would have easily fallen for it had I not experienced the same issue in one of my system. Thanks for this cool quesion. 🙂

Hi Chandra, first of all I'm only sqlnaive and definitely not "sir".

Secondly, just a small analysis by yourself will tell you that outer joins are not allowed in recursive CTEs...

Very good question. Got few points in my mind. 🙂

Did it got sorted ? What was the issue ?

This seems to be a normal issue with some datatype mismatch. Just check column by column data mapping in both the queries joined using UNION ALL and also the final...

Glad it helped. 🙂

Karim, I overlooked at the following message:

Msg 147, Level 15, State 1, Line 28

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in...

okies. Got it. For the table with following records:

Idnamevalue1value2totalPreviousRowId

111A 100100

24B 050111

57C 06024

80D 07057

100E 08080

112F 090100

The query is like this:

;with CTE1(Id, name, value1, value2, value3, PreviousRowId)

AS(

SELECT Id, name, value1, value2, value1+value2...

Does it helps (there was one extra ")" missing) ?

where (CAST(SUM(nUsed_Min) AS FLOAT(2)) / CAST(SUM(nSize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) >= 80

Here it is with PreviousRowId:

with CTE1(Id, name, value1, value2, value3, PreviousRowId)

AS(

SELECT Id, name, value1, value2, value1+value2 AS value3, 0 as PreviousRowId

FROM dbo.T1 a

WHERE a.Id = 1

UNION all

SELECT a.Id, a.name, a.value1,...