how do i get the below output in t-sql 2008?

Question

0.00/5 (No votes)

See more:

Hi ,

i got one requirement like to write the recursive query. the same issue is resolved in oracle 10g with the help of connect by prior functionality.

here i am going to post my table structures with expected out put.

SQL

CREATE TABLE PROJWBS
(
wbs_id int,
parent_wbs_id int,
wbs_name varchar(100),
Proj_id int
)
INSERT INTO PROJWBS VALUES(1000,0,'A',1234)
INSERT INTO PROJWBS VALUES(1001,1000,'A.1',1234)
INSERT INTO PROJWBS VALUES(1002,1000,'B.1',1234)

CREATE TABLE TASK
(
task_id int,
task_code varchar(100),
Start_date1 datetime,
End_Date datetime,
wbs_id int
)
INSERT INTO TASK VALUES(10023,'A1010','01-12-2012','03-25-2013',1001)
INSERT INTO TASK VALUES(10025,'A1020','01-01-2012','03-10-2013',1001)
INSERT INTO TASK VALUES(10026,'B1200','09-08-2012','04-30-2013',1002)
INSERT INTO TASK VALUES(10027,'B1201','01-12-2012','01-01-2013',1002)
select * from projwb

select * from task

my expected output is:

wbs_id      wbs_anme       start_date  End_Date
1000        A               1-Jan-12   30-Apr-13
1001          A.1          1-Jan-12        25-Mar-13
1002          B.1           8-Sep-12      30-Apr-13

Posted 20-Jan-13 0:53am

FranklinRemo

Updated 20-Jan-13 0:55am

Sandeep Mewara

v2

Add a Solution

Comments

Sandeep Mewara 20-Jan-13 6:56am

And the issue is? What did you try? Where are you stuck?

FranklinRemo 20-Jan-13 7:12am

first take the above script and try what i mentioned in output. then let you know what you need to do .
that's y i clearly mentioned required output with tables script.
then what clarification you need .

Tharaka MTR 20-Jan-13 11:30am

I'm clear about following rows
1001 A.1 1-Jan-12 25-Mar-13
1002 B.1 8-Sep-12 30-Apr-13

But not first row
1000 A 1-Jan-12 30-Apr-13

How do you took the start date and end date for wbs_id = 1000?

FranklinRemo 21-Jan-13 0:40am

thank you tharaka , that is only problem here , if i know "How do you took the start date and end date for wbs_id = 1000?" then i will not post this issue here.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

milenalukic · Answer 1 · 2013-01-20T10:56:00

Solution 1

Hi,

As others have mentioned the result for A is unclear.

Assuming this is an error what you need to to is:

SQL

Select wbs_id,wbs_name,startdate1 as startdate, end_date
from projwbs inner join task on projwbs.wbs_id=task.wbs_id

Hope this helps you

Posted 20-Jan-13 10:56am

milenalukic

Comments

FranklinRemo 21-Jan-13 0:39am

it will not help ful because alredy i tried it. this requirement will full fill only with WITH CTE.

FranklinRemo · Answer 2 · 2013-01-20T19:32:00

hi finally i resolved the issue my self.

i did the following code

SQL

with q as
(
  select wbs_id root_wbs_id, wbs_name root_wbs_name, wbs_id,  0 level
  from projwbs
  union all
  select q.root_wbs_id, q.root_wbs_name, p.wbs_id, q.level+1
  from projwbs p
  join q
    on p.parent_wbs_id = q.wbs_id
)
select q.root_wbs_id wsb_id,
        q.root_wbs_name wbs_name,
        min(start_date1) start_date,
        max(end_date) end_date
from TASK t
join q
  on q.wbs_id = t.wbs_id
group by q.root_wbs_id, q.root_wbs_name