Adding a Row_Number partitioned by the Year(SubmitDate) order by SubmitDate in the CTE and in the final select selecting values <= 5 on Row Number will get you there.....

Hmmm.. interesting to see this... i tried simulating the question by using 3 variations , cte, cross apply ( from nils gustav ) and OP's method.

Here is test code:

select...

The query you posted did not have a join statement, but the error message says differently. Is the procedure complete? or you just posted part of the SP?

You're welcome. Drop by here if you don't understand the code and the explanation....

Your answer to the interviewer is not wrong, you can use any looping mechanism ( cursor or while loop ) for this. But there are 2 more mechanisms of deriving...

See if this expalantion helps.

All the explanation are in the comments.

Declare @month varchar(10) = 'NOvember' ,

@date int = 24

--==...

I went on and ran the tests , one at a time, around 50 times. Now i see a difference. Both tests are yeilding more or less the same metrics....

Gail, i ran the tests for around 25 times.. i still see that behavior.. any weird bug in the system?

SQLRNNR (8/17/2011)

---

I have seen it many times more often in a CTE than the older Derived table. I think this syntax has some usefulness when thinking through it. ...

If you do not have any records in child table, then you wont get any rows in resultset. Can you specify your requiremtn exactly? some sample data and expected result...

Oracle_91 (8/3/2011)

---

Hi,

There is a slight difference 🙂

using my implementation if i execute

exec usp_p1 'b1',null,null,null

-- output which is expected.

/*

b1p12001-01-01 00:00:00.0002002-01-01 00:00:00.000

*/

using ur implementation

exec usp_p1 'b1',null,null,null

-- no output. which is...

How about this?

create proc usp_p2

@branchcode varchar(10),

@promocode varchar(10)

as

begin

select * from sample

where (1=1)

and branchcode = ISNULL(@branchcode , branchcode )

and promocode = ISNULL ( @promocode , promocode )

end

Seemed straight forward to me...

Try this:

SELECT DISTINCT c1.id ReferralID

,a1.id UAID

,c7.c_te QuestionInForm

...

What will happen if there are 2 differnct RcTypes other than 0 ?

How about this then?

; with TableCTE AS

(

SELECT *

FROM

...