While loop

qbjgqbjg
qbjgqbjg used Ask the Experts™
on
I have the following code that returns a SAL_GROUP field from a History table. The History table does not have a record until at least one change has been made. When a change is made a record is added with the change date and the old sal_Group code. I am retrieving the code for 10 consecutive years. The code below is just for the 1st year (in this case 2003). Sal_Group is 1 column in the query. This is the code I am using to build that column. The problem is: If it does not find a code in the history table <= the last day of the 1st year, then I would want to add 1 to the year and repeat the retrieval. How do I write the while loop into this code? Iwould like to do it without a complete rewrite of my existing code. Thanks

DECLARE @FromYear int
set @FromYear = 2003

, SalGrpHist = isnull((
   SELECT max(t1.SHST_SAL_GROUP)
      FROM  
      ESHSHSTD
        as t1
      INNER JOIN
     (SELECT
     Max(ESHSHSTD.SHST_CHG_DATE) as Max_DATE
     , ESHSHSTD.SHST_APPOINT_ID
          FROM ESHSHSTD
            GROUP By SHST_APPOINT_ID
                   , ESHSHSTD.SHST_CHG_DATE
     ) as t2
     on (t1.SHST_APPOINT_ID = t2.SHST_APPOINT_ID  
     and t1.SHST_APPOINT_ID = ESYAPPTR.APPOINT_ID
     and t1.SHST_SAL_GROUP is not null
          and t1.SHST_CHG_DATE = t2.Max_Date
   and t1.SHST_CHG_DATE <= (CAST(@FromYear as varchar(10)) + '-12-31'))),' ')
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vikas GargAssociate Principal Engineer
Top Expert 2014

Commented:
Hi,

You can try like this

DECLARE @FromYear int , @SalGrpHist VARCHAR(100)
set @FromYear = 2003

SET @SalGrpHist = ''

WHILE LEN(@SalGrpHist) < 1
BEGIN
SET @SalGrpHist = isnull((
   SELECT max(t1.SHST_SAL_GROUP)
      FROM  
      ESHSHSTD
        as t1
      INNER JOIN
     (SELECT
     Max(ESHSHSTD.SHST_CHG_DATE) as Max_DATE
     , ESHSHSTD.SHST_APPOINT_ID
          FROM ESHSHSTD
            GROUP By SHST_APPOINT_ID
                   , ESHSHSTD.SHST_CHG_DATE
     ) as t2
     on (t1.SHST_APPOINT_ID = t2.SHST_APPOINT_ID  
     and t1.SHST_APPOINT_ID = ESYAPPTR.APPOINT_ID
     and t1.SHST_SAL_GROUP is not null
          and t1.SHST_CHG_DATE = t2.Max_Date
   and t1.SHST_CHG_DATE <= (CAST(@FromYear as varchar(10)) + '-12-31'))),' ')
   
 SET @FromYear = @FromYear + 1
END

Open in new window

Commented:
It looks to me that you want to find the last history record in the earliest year that a history record exists.  You can change your SELECT Statement to do that instead of using a WHILE loop.

Most easily by changing
set @FromYear = 2003

Open in new window

to
select @FromYear = Min(Year(SHST_CHG_DATE))
FROM ESHSHSTD
WHERE <Whatever you are looking for here>

Open in new window

qbjgqbjgConsultant

Author

Commented:
You are corrrect. I need to go after Minimum instead of Maximum. Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial