Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help required :: Recusive Method Expand / Collapse
Author
Message
Posted Friday, January 30, 2009 12:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, August 22, 2010 10:09 PM
Points: 29, 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
Post #646679
Posted Friday, January 30, 2009 6:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 8, 2014 12:54 AM
Points: 2,552, Visits: 2,598
Can you elaborate more on how these two tables are linked? And what is the logic behind the output?

--Ramesh

Post #646868
Posted Sunday, February 1, 2009 10:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, August 22, 2010 10:09 PM
Points: 29, 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
Post #647810
Posted Monday, February 2, 2009 1:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 8, 2014 12:54 AM
Points: 2,552, Visits: 2,598
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

Post #647848
Posted Monday, February 2, 2009 2:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, August 22, 2010 10:09 PM
Points: 29, Visits: 50
Yes Ramesh you are right it should be 1; sorry it’s my mistake.
Post #647858
Posted Monday, February 2, 2009 6:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 8, 2014 12:54 AM
Points: 2,552, Visits: 2,598
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

Post #647954
Posted Monday, February 2, 2009 9:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, August 22, 2010 10:09 PM
Points: 29, 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.
Post #648122
Posted Tuesday, February 3, 2009 2:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 8, 2014 12:54 AM
Points: 2,552, Visits: 2,598
...Of course, you can replace the COUNT( P.ProjectID ) with MAX( P.Milestone )

--Ramesh

Post #648652
Posted Tuesday, February 3, 2009 3:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, August 22, 2010 10:09 PM
Points: 29, Visits: 50
Thanks Ramesh,

This was an excellent solution. It worked straight away.

once again Thanks.
Post #648684
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse