SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help required :: Recusive Method


Help required :: Recusive Method

Author
Message
Jayraj.Todkar
Jayraj.Todkar
SSC Veteran
SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)

Group: General Forum Members
Points: 259 Visits: 50
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 …
GUID ProjectID Molestone
1 63 0
2 63 0
3 63 0
1 69 1
2 69 2
3 69 0

Can anyone suggest me the solution

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



Thank You,
Jayraj Todkar
Ramesh Saive
Ramesh Saive
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10230 Visits: 2643
Can you elaborate more on how these two tables are linked? And what is the logic behind the output?

--Ramesh


Jayraj.Todkar
Jayraj.Todkar
SSC Veteran
SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)

Group: General Forum Members
Points: 259 Visits: 50
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
Ramesh Saive
Ramesh Saive
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10230 Visits: 2643
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


Jayraj.Todkar
Jayraj.Todkar
SSC Veteran
SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)

Group: General Forum Members
Points: 259 Visits: 50
Yes Ramesh you are right it should be 1; sorry it’s my mistake.
Ramesh Saive
Ramesh Saive
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10230 Visits: 2643
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

SELECT AP.GUID, AP.ProjectID, COUNT( P.ProjectID ) AS Milestone
FROM (
SELECT MY.GUID, MY.MonthYear, MY.MonthDay, P.ProjectID
FROM @tblMonthYear MY
CROSS JOIN
(
SELECT DISTINCT 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


Jayraj.Todkar
Jayraj.Todkar
SSC Veteran
SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)

Group: General Forum Members
Points: 259 Visits: 50
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.
Ramesh Saive
Ramesh Saive
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10230 Visits: 2643
...Of course, you can replace the COUNT( P.ProjectID ) with MAX( P.Milestone )

--Ramesh


Jayraj.Todkar
Jayraj.Todkar
SSC Veteran
SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)

Group: General Forum Members
Points: 259 Visits: 50
Thanks Ramesh,

This was an excellent solution. It worked straight away.

once again Thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search