January 30, 2009 at 12:10 am
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
January 30, 2009 at 6:28 am
Can you elaborate more on how these two tables are linked? And what is the logic behind the output?
--Ramesh
February 1, 2009 at 10:53 pm
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
February 2, 2009 at 1:33 am
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
February 2, 2009 at 2:07 am
Yes Ramesh you are right it should be 1; sorry it’s my mistake.
February 2, 2009 at 6:11 am
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
February 2, 2009 at 9:32 am
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.
February 3, 2009 at 2:44 am
...Of course, you can replace the COUNT( P.ProjectID ) with MAX( P.Milestone )
--Ramesh
February 3, 2009 at 3:37 am
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