Its not necessary to have cumulative data at first, we can do it in one go as well.

Thanks, its working but taking lot of time. I have almost 30000 prj_id's. Any suggestion on how to improve the query performance or any change in query that makes performance...

yes thats correct 24 months from current month i.e., till April13

Thanks for the reply, the output u have copies is half.. please check thr is also PRJ_ID = 2 and have data till sept12

the data I want is for last...

Below is the Create table script, and insert

CREATE TABLE Rev

(

PrjDate Datetime,

PRJ_ID varchar(7),

AMOUNT decimal(26,2)

)

INSERT INTO Rev

SELECT '2012-01-31', '1', 1000

UNION ALL

SELECT '2012-02-29', '1', 1500

UNION ALL

SELECT '2012-04-30', '1', 1800

UNION ALL

SELECT '2012-07-31', '1', 1200

UNION...

Thanks, for the reply, give me 5 - 10 mins I will give post the insert script

The first records set is the table data which is cumulative data, but i need to populate the data for the missing dates as well and insert new record set...

I want an idea on how should i build my sql query so that I can have parent-child kind of hierarchy. And then I can show that in SSRS report...

Thanks, i will try this option.

Regards,

Ravi

I already added that part, was some other issue, did some minor mistake

Thanks a Lot Dave for your Help

Regards,

Motz

Thanks Dave

Now i am trying to create a view out of that but it throws error as below

Msg 4511, Level 16, State 1, Procedure vtest, Line 3

Create View or...

Thanks for the query

it works fine when criteria is different for a particluar ID , but doesnt work well if Criteria is same for particluarID

I have attached the...

I have attached Create , Insert Query

expected output , thr can be more criteria and values

ID Criteria1 Value1 Criteria2 ...

No, its not working, it shows null values