Avatar of qbjgqbjg
qbjgqbjg
Flag for United States of America asked on

While loop

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'))),' ')
Microsoft SQL Server 2005Microsoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
qbjgqbjg

8/22/2022 - Mon
Vikas Garg

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

ASKER CERTIFIED SOLUTION
JimFive

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
qbjgqbjg

ASKER
You are corrrect. I need to go after Minimum instead of Maximum. Thanks
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck