asked on 9/24/2015

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

Last Comment

qbjgqbjg

9/26/2015

Vikas Garg

9/25/2015

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

9/25/2015

THIS SOLUTION IS 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

qbjgqbjg

9/26/2015

ASKER

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