SQL Command Help

[eastwestk]

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 & ctm.rank is of int 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
---- =====================

Thank You

 

[bborissov]

On top off my head and NOT TESTED:

SELECT ctm1.ID, a.*,ctm1.Member
FROM (SELECT ctm.CommitteeTermID,
             MAX(CTM.ENDDATE) AS MaxYear
      FROM mms.dbo.vwcommitteetermmembers ctm
      INNER JOIN (SELECT CommitteeTermID,
                         MIN(Rank) AS Rank
                  FROM mms.dbo.vwcommitteetermmembers
                  WHERE Rank BETWEEN 700 AND 720
                  GROUP BY CommitteeTermID) Tbl1
      ON Ctm.CommitteeTermID = Tbl1.CommitteeTermID AND
         Ctm.Rank            = Tbl1.Rank)
      WHERE YEAR(CTM.ENDDATE) = CASE WHEN MONTH(GETDATE()) < 7 
                                     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         AND
         CTM1.Rank            = a.Rank
ORDER BY a.CommitteeTermID

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[eastwestk]

I tried running your command but I am getting an error as:

Msg 207, Level 16, State 3, Line 1
Invalid column name 'Rank'

Any suggestions & thank you so much for your help.


SELECT ctm1.ID, a.*, ctm1.Member
FROM (
SELECT ctm.CommitteeTermID, MAX(ctm.ENDDATE) AS MaxYear
FROM mms.dbo.vwcommitteetermmembers ctm
INNER JOIN
(SELECT CommitteeTermID,MIN(Rank) AS Rank
FROM mms.dbo.vwcommitteetermmembers
WHERE Rank BETWEEN 700 AND 720
GROUP BY CommitteeTermID
)Tbl1
ON (ctm.CommitteeTermID = Tbl1.CommitteeTermID
AND ctm.Rank = Tbl1.Rank)
WHERE YEAR(CTM.ENDDATE) = CASE WHEN MONTH(GETDATE()) < 7
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 AND ctm1.Rank=a.Rank
)
ORDER BY a.CommitteeTermID

 

[bborissov]

SELECT ctm1.ID, a.*,ctm1.Member
FROM (SELECT ctm.CommitteeTermID,
             MAX(ctm.Rank)    AS Rank,
             MAX(CTM.ENDDATE) AS MaxYear
      FROM mms.dbo.vwcommitteetermmembers ctm
      INNER JOIN (SELECT CommitteeTermID,
                         MIN(Rank) AS Rank
                  FROM mms.dbo.vwcommitteetermmembers
                  WHERE Rank BETWEEN 700 AND 720
                  GROUP BY CommitteeTermID) Tbl1
      ON Ctm.CommitteeTermID = Tbl1.CommitteeTermID AND
         Ctm.Rank            = Tbl1.Rank)
      WHERE YEAR(CTM.ENDDATE) = CASE WHEN MONTH(GETDATE()) < 7
                                     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         AND
         CTM1.Rank            = a.Rank
ORDER BY a.CommitteeTermID

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.