Can you please explain the logic of your desired output,

I am not getting what you want.

Kingston Dhasian (4/13/2010)

---

; WITH cte_Schedule_Audit AS

(

SELECTROW_NUMBER() OVER( PARTITION BY ID, Action ORDER BY ID, ModifiedOn DESC ) RowNum, *

FROM#Schedule_Audit

)

SELECTI.ID, I.M_ID, I.T_Name, I.T_Desc, I.Sch_Time, I.CreatedBy, I.CreatedOn, U.ModifiedBy, U.ModifiedOn,

D.DeletedOn, D.DeletedBy

FROMcte_Schedule_Audit I

LEFT JOIN...

ID M_ID T_Name T_Desc Sch_Time CreatedBy CreatedOn ModifiedBy ModifiedOn DeletedBy DeletedOn Action

1 1 test1 testdesc 08:00 abc@xyz.com 2010-04-09 11:30:00.000 abc@xyz.com 2010-04-10 08:30:00.000 abc@xyz.com 2010-04-12 11:30:00.000

2 1 test2 testdesc 08:00...

@karthikeyan-2 : it is giving me only three rows as there are 4 ids and moreover giving only modifiedby and modifiedon

I am sending required output in moments to make...

I need only one row for each id

your query is giving me entire table data

Sorry I got the solution that was just a syntax error

I have not given alias name A in table...

Chris Morris-439714 (4/9/2010)

---

vaibhav.tiwari (4/9/2010)

---

Thanks ColdCoffee for your that much efforts your query is working fine and giving me expected output

but i got the simpler solution from some another forum...

COldCoffee (4/9/2010)

---

Hey vaibhav...

Tel me if this is the code u wnated

;WITH I_AND_U (ID)

AS

(

SELECT

DISTINCT s.ID

FROM #Schedule_Audit s

INNER JOIN

...

And please call me vaibhav not sir 🙂

COldCoffee (4/9/2010)

---

Vaibhav, pls correct me if my understanding is wrong

1. You need "I" rows that have atleast one "U" and present them.

2. For any "I' row that dont have a...

Yeah...

thats what i have done earlier but

Note that for ID 2 there is not any entry with action 'U'

so for that i need to show null.

This is clearly visible that any column 'Clusters' is identity column

and you are trying to pass the value for that

so you just select column names instead of *...

This can only be achieved by dynamic query...

becasue your condition operator is not fix, as for one condition it is <= and for another it is =

Case in...

Paul White NZ (4/6/2010)

---

vaibhav.tiwari (4/6/2010)

---

...but for some procedure its showing invalid character length and i observed that there is not purpose line in that i need to return null value...