June 16, 2009 at 11:10 am
Hello All,
Below is the SQL for getting the President Name when (Rank is 700)
But also I want:
1. If there is no President then get the Secretary Name(Rank is 710)
2. If there is no Secretary then get the Treasure Name (Rank is 720)
And ctm1.Member is of nvarchar datatype. I am using SQL Server 2000.
Please anybody let me know how to accomplish this.
SELECT DISTINCT ctm1.ID, a.*,ctm1.Member
FROM
(SELECT DISTINCT ctm.CommitteeTermID,
MAX(CTM.ENDDATE) AS MaxYear
FROM mms.dbo.vwcommitteetermmembers ctm
WHERE ctm.rank = 700 --700 Denotes President
AND YEAR(CTM.ENDDATE) =
CASE
WHEN MONTH(GETDATE()) IN (1,2,3,4,5,6) THEN YEAR(GETDATE())
ELSE YEAR(GETDATE())+1
END
GROUP BY ctm.CommitteeTermID) a
INNER JOIN mms.dbo.vwcommitteetermmembers ctm1 ON
ctm1.committeetermid = a.committeetermid
AND ctm1.enddate = a.maxyear
WHERE CTM1.Rank = 700
ORDER BY a.CommitteeTermID ASC
June 16, 2009 at 12:30 pm
Take a look at "Exists" in Books Online or on MSDN. If you include statements like "or rank = 720 and not exists (subquery)" in your Where clause in the sub-query, it should get you what you need. You'd of course have to replace where I have "subquery" with the correct query to determine if there is a rank 700 for that set.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 16, 2009 at 12:51 pm
not sure how to do that but I will try.
Can you please provide me the code if possible .
June 16, 2009 at 2:06 pm
Please try this, see if gets you what you want:
SELECT DISTINCT ctm1.ID, a.*,ctm1.Member
FROM
(SELECT DISTINCT ctm.CommitteeTermID,
MAX(CTM.ENDDATE) AS MaxYear
FROM mms.dbo.vwcommitteetermmembers ctm
WHERE YEAR(CTM.ENDDATE) =
CASE
WHEN MONTH(GETDATE()) IN (1,2,3,4,5,6) THEN YEAR(GETDATE())
ELSE YEAR(GETDATE())+1
END
AND
(ctm.rank = 700 --700 Denotes President
or
not exists
(select *
from mms.dbo.vscommitteetermmembers ctm2
where rank = 700
and YEAR(CTM.ENDDATE) =
CASE
WHEN MONTH(GETDATE()) IN (1,2,3,4,5,6) THEN YEAR(GETDATE())
ELSE YEAR(GETDATE())+1
END)
and ctm.rank = 710 -- 710 Denotes Secretary
or
not exists
(select *
from mms.dbo.vscommitteetermmembers ctm2
where rank in (700, 710)
and YEAR(CTM.ENDDATE) =
CASE
WHEN MONTH(GETDATE()) IN (1,2,3,4,5,6) THEN YEAR(GETDATE())
ELSE YEAR(GETDATE())+1
END)
and ctm.rank = 720) -- 720 Denotes Treasurer
GROUP BY ctm.CommitteeTermID) a
INNER JOIN mms.dbo.vwcommitteetermmembers ctm1 ON
ctm1.committeetermid = a.committeetermid
AND ctm1.enddate = a.maxyear
WHERE CTM1.Rank = 700
ORDER BY a.CommitteeTermID ASC;
Let me know if you need help understanding what I wrote and how it works.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 16, 2009 at 7:40 pm
Thank You so much!!!!! I just checked your reply, I will try this solution
June 17, 2009 at 11:29 am
You're welcome. Let me know if it does what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply