Building detailed records using stored procedure

Hi:
I have two tables
1-      tblPromo
2-      tblPromoDetails
3-      I need to create the second table (tblPromoDetails ), according to the data in table one. So the rows in table two equivalent to the year in between two promotion levels.
As shown in illustration excel attached.
Could anyone please help me do it in SQL (stored procedure, functions … whatever)
Please help.
                  tblPromo
                  
PromoDate      PromoLevel      PersNo      PromoID
25/10/2001      1      1      1
25/10/2005      2      1      2
25/10/2007      3      1      3
25/10/2012      4      1      4
                  
                  
                  
                  
                  tblPromoDetails
                  
YesrsPerLevel      PromoLevel      PersNo      PDId
2001      1      1      1
2002      1      1      2
2003      1      1      3
2004      1      1      4
2005      1      1      5
2006      2      1      6
2007      2      1      7
2008      3      1      8
2009      3      1      9
2010      3      1      10
2011      3      1      11
2012      3      1      12
2013      4      1      13
Microsoft-Excel-Worksheet-----.xlsx
Mohammad Alsolaimanapplication programmerAsked:
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please clarify how to determine the "last year"?
also, could there be 2 rows for the same year?
what version of sql server?

I would start with something like this (not tested):
;with data as (
select year(PromoDate  ) YearValue,     PromoLevel     , PersNo      , row_number() over ( order by year(PromoDate  ) ) rn
 from yourtable
 union all
select 2013 , null, null, null, count(*) + 1 from yourtable
)
, data2 as ( select d.YearValue, n.YearValue + 1 last_year, PromoLevel    , PersNo
     from data d
     left join data n on n.rn = d.rn + 1
  )
, numbers as ( 
     SELECT top 100 n = ROW_NUMBER() OVER (ORDER BY [object_id]) 
     FROM sys.all_objects 
)
, years as ( select n - 1 +  ( select min(YearValue) from data ) year_value
   from numbers
  where (n - 1) <= ( select max(YearValue) - min(YearValue) from data )
select y.year_value, d.PromoLevel    , d.PersNo, row_number() over ( order by y.year_value ) PDId
  from data2 d
  join years y
    on d.YearValue <= y.YearValue
  and d.last_year >= y.YearValue

Open in new window

Éric MoreauSenior .Net ConsultantCommented:
Using a cursor, you can easily do it:
DECLARE @tblPromo TABLE ( PromoDate DATE, PromoLevel INT, PersNo INT, PromoId INT  NULL)

INSERT INTO @tblPromo ( PromoDate, PromoLevel, PersNo, PromoId )
VALUES  
	('2001/10/25', 1, 1, 1 ),
    ('2005/10/25', 2, 1, 2 ),
    ('2007/10/25', 3, 1, 3 ),
    ('2012/10/25', 4, 1, 4 )

DECLARE @tblPromoDetails TABLE ( YesrsPerLevel INT, PromoLevel INT, PersNo INT, PDId INT )

SELECT * FROM @tblPromo
--SELECT * FROM @tblPromoDetails

DECLARE @NextPromoDate DATE, @NextPromoLevel INT, @NextPersNo INT
DECLARE @PromoDate DATE, @PromoLevel INT, @PersNo INT, @PromoId INT

DECLARE MyCursor CURSOR FOR 
	SELECT PromoDate, PromoLevel, PersNo
	FROM @tblPromo
	ORDER BY PromoDate

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @PromoDate, @PromoLevel, @PersNo 

SET @PromoId = 0
WHILE @@FETCH_STATUS = 0
BEGIN
    --SELECT @PromoDate
 
    FETCH NEXT FROM MyCursor INTO @NextPromoDate, @NextPromoLevel, @NextPersNo

	 IF @@FETCH_STATUS = 0
	 BEGIN 
		WHILE YEAR(@PromoDate) <= YEAR(@NextPromoDate)
		BEGIN
			SET @PromoId = @PromoId + 1

			INSERT INTO @tblPromoDetails ( YesrsPerLevel, PromoLevel, PersNo, PDId )
			VALUES  ( YEAR(@PromoDate), @PromoLevel, @PersNo, @PromoId )

			SET @PromoDate = DATEADD(YEAR, 1, @PromoDate)
		END 
		
		SET @PromoDate = DATEADD(YEAR, 1, @NextPromoDate)
		SET @PromoLevel = @NextPromoLevel
		SET @PersNo = @NextPersNo	
	 END
     
END 
CLOSE MyCursor;
DEALLOCATE MyCursor;

		WHILE YEAR(@PromoDate) <= YEAR(GETDATE())
		BEGIN
			SET @PromoId = @PromoId + 1

			INSERT INTO @tblPromoDetails ( YesrsPerLevel, PromoLevel, PersNo, PDId )
			VALUES  ( YEAR(@PromoDate), @PromoLevel, @PersNo, @PromoId )

			SET @PromoDate = DATEADD(YEAR, 1, @PromoDate)
		END 

SELECT * FROM @tblPromoDetails

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
Mohammad Alsolaimanapplication programmerAuthor Commented:
1-      Last year should be the year before next PromoDate.
2-      No, it should not have two rows with the same year.
3-      2008 R2
thanks for your try, I'll test it at work "in sha'allah" then I'll let you know.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Last year should be the year before next PromoDate.
what I means/asked was the "last year" for the "last promo".
your last promo date is 2012, and your last requested output data is 2013. why 2013 and not 2014 (or 2015)?
Mohammad Alsolaimanapplication programmerAuthor Commented:
Thanks Éric I’ll test it at work in sha’allah
Mohammad Alsolaimanapplication programmerAuthor Commented:
the "last year" for the "last promo" should be +1
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then please replace this line
select 2013 , null, null, null, count(*) + 1 from yourtable

by:
select max( year(YearValue) + 1 ) , null, null, null, count(*) + 1 from yourtable
Éric MoreauSenior .Net ConsultantCommented:
>>the "last year" for the "last promo" should be +1

Replace the last loop of my proposed solution :

		WHILE YEAR(@PromoDate) <= YEAR(GETDATE())
		BEGIN
			SET @PromoId = @PromoId + 1

			INSERT INTO @tblPromoDetails ( YesrsPerLevel, PromoLevel, PersNo, PDId )
			VALUES  ( YEAR(@PromoDate), @PromoLevel, @PersNo, @PromoId )

			SET @PromoDate = DATEADD(YEAR, 1, @PromoDate)
		END

Open in new window



With:
		SET @PromoId = @PromoId + 1

			INSERT INTO @tblPromoDetails ( YesrsPerLevel, PromoLevel, PersNo, PDId )
			VALUES  ( YEAR(@PromoDate), @PromoLevel, @PersNo, @PromoId )

Open in new window

Mohammad Alsolaimanapplication programmerAuthor Commented:
Mr. "Éric Moreau"
Your code was terrific, and very understandable to me.
Mohammad Alsolaimanapplication programmerAuthor Commented:
hello Mr. "Guy Hengel [angelIII / a3"
I do my best to understand your code and run it at my side server but it was very hard for me to understand.
Now I had this message
" Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'select'."
My SQL Statement is "see snipet of code attached please"
;with data as (
select year(PromoDate  ) YearValue,     PromoLevel     , PersNo      , row_number() over ( order by year(PromoDate  ) ) rn
 from [myTestDB].[dbo].[tblPromo]
 union all
select 2013 , null, null, null, count(*) + 1 from [myTestDB].[dbo].[tblPromo]
)
, data2 as ( select d.YearValue, n.YearValue + 1 last_year, PromoLevel    , PersNo
     from data d
     left join data n on n.rn = d.rn + 1
  )
, numbers as ( 
     SELECT top 100 n = ROW_NUMBER() OVER (ORDER BY [object_id]) 
     FROM sys.all_objects 
)
, years as ( select n - 1 +  ( select min(YearValue) from data ) year_value
   from numbers
  where (n - 1) <= ( select max(YearValue) - min(YearValue) from data )
select y.year_value, d.PromoLevel    , d.PersNo, row_number() over ( order by y.year_value ) PDId
  from data2 d
  join years y
    on d.YearValue <= y.YearValue
  and d.last_year >= y.YearValue

Open in new window


Would you please explane the code to me so I could arrange the marks.
Mohammad Alsolaimanapplication programmerAuthor Commented:
thank you very much
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

From novice to tech pro — start learning today.