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'))),' ')
qbjgqbjgConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:
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

JimFiveCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qbjgqbjgConsultantAuthor Commented:
You are corrrect. I need to go after Minimum instead of Maximum. Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.