Help required :: Recusive Method

  • I Have two tables are as below

    DECLARE @tblMonthYear TABLE

    (

    GUID INT,

    YearName VARCHAR(10),

    Quarter VARCHAR(2),

    MonthYear VARCHAR(10),

    MonthDay INT

    )

    INSERT @tblMonthYear

    SELECT 1,'08/09','Q4','Jan-09',1 UNION ALL

    SELECT 2,'08/09','Q4','Jan-09',2 UNION ALL

    SELECT 3,'08/09','Q4','Jan-09',3

    DECLARE @tblProject TABLE

    (

    ProjectID INT,

    ProjectTitle VARCHAR(10),

    TaskTitle VARCHAR(10),

    Milestone INT,

    MonthYear VARCHAR(10),

    MonthDay INT

    )

    INSERT @tblProject

    SELECT 63,'Arsenal',NULL,NULL,NULL,NULL UNION ALL

    SELECT 69,'COEP','Start',1,'Jan-09',1 UNION ALL

    SELECT 69,'COEP','Start',1,'Jan-09',2

    I want result set by using above tables …

    GUIDProjectID Molestone

    1630

    2630

    3630

    1691

    2692

    3690

    Can anyone suggest me the solution

    i am trying using recursive method. is this right way?

    Thank You,

    Jayraj Todkar

  • Can you elaborate more on how these two tables are linked? And what is the logic behind the output?

    --Ramesh


  • Hi Ramesh,

    Thank you for your interest ...

    These tables are linked by two fields "MonthYear" and "MonthDay".

    Output should be like ...

    1. All respective rows from @tblProject.

    2. Remaining All rows from both tables with milestone "0".

    In short I want all rows from @tblProject w.r.t. @tblMonthYear other wise “0” milestone for that rows which are not matched.Its like a Cartesian product but not a Cartesian product.

    Thanks a lot

    Jayraj

  • I've understood what you wanted and what is the logic behind it, but I'm confused with the output of row 5, according to the logic it should 1. Am I missing something here?

    --Ramesh


  • Yes Ramesh you are right it should be 1; sorry it’s my mistake.

  • Firstly, you should verify/cross-check your post before posting it in any forum, this will say a lot of time of posters.

    Secondly, here is the solution to your query (note that, I could have posted this in my last post only, but the listed output has caused me to wait...)

    DECLARE @tblMonthYear TABLE

    (

    GUID INT,

    YearName VARCHAR(10),

    Quarter VARCHAR(2),

    MonthYear VARCHAR(10),

    MonthDay INT

    )

    INSERT @tblMonthYear

    SELECT 1,'08/09','Q4','Jan-09',1 UNION ALL

    SELECT 2,'08/09','Q4','Jan-09',2 UNION ALL

    SELECT 3,'08/09','Q4','Jan-09',3

    DECLARE @tblProject TABLE

    (

    ProjectID INT,

    ProjectTitle VARCHAR(10),

    TaskTitle VARCHAR(10),

    Milestone INT,

    MonthYear VARCHAR(10),

    MonthDay INT

    )

    INSERT @tblProject

    SELECT 63,'Arsenal',NULL,NULL,NULL,NULL UNION ALL

    SELECT 69,'COEP','Start',1,'Jan-09',1 UNION ALL

    SELECT 69,'COEP','Start',1,'Jan-09',2

    --SELECT * FROM @tblMonthYear

    --SELECT * FROM @tblProject

    SELECTAP.GUID, AP.ProjectID, COUNT( P.ProjectID ) AS Milestone

    FROM(

    SELECTMY.GUID, MY.MonthYear, MY.MonthDay, P.ProjectID

    FROM@tblMonthYear MY

    CROSS JOIN

    (

    SELECTDISTINCT ProjectID

    FROM@tblProject

    ) P

    ) AP

    LEFT JOIN @tblProject P ON AP.ProjectID = P.ProjectID AND AP.MonthYear = P.MonthYear AND AP.MonthDay = P.MonthDay

    GROUP BY AP.GUID, AP.ProjectID

    --Ramesh


  • Hi Ramesh,

    Thank you very much for the solution.

    In the result will it be possible to get actual milestone value from @tblProject instead of project Count.

    If there is another project entry in @tblProject

    (70,'New Project', 'New Task',2,'Jan-09',1)

    For that project milestone should be 2.

    Solution is very helpful for me.

    Thank you very much.

  • ...Of course, you can replace the COUNT( P.ProjectID ) with MAX( P.Milestone )

    --Ramesh


  • Thanks Ramesh,

    This was an excellent solution. It worked straight away.

    once again Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply