locked
Make a View for forecasting based of a View with existing data RRS feed

  • Question

  • In my database I have a View Revenues with fields ProductID, EditionID, Year, January, February, March,..., December.

    I need to display in Excel for each ProductID and EditionID all the year and months revenues in a single record. In order to do that I execute the query:

    select *
    from Revenus r1
    join Revenues r2
    on r1.ProductID = r2.ProductID
    and r1.EditionID = r2.EditionID
    where r1.[Year] <> r2.[Year]

    That works perfectly if there at the most two different years per ProductID and EditionID. However since the sheet will be used also for forecasting purposes, I have been asked to have the years and month with a 10 years horizon. That means besides the data already existing in the database, given starting year 2013, I must have years and related month till 2023. The months related data must be equal to zero if there is no year year yet in the database.

    I tried with pivot table but it does not sound as the right solution. I might try with a fore each but I read on the web that is a bad practice. The best solution, yet a bit dirty, might be to join the existing data with a View built on the fly with empty records for months and increase the year for each "iteration". Anybody might help? Thanks

    EXAMPLE

    Same as 2015 till 2023




    • Edited by Trinakriae Tuesday, September 3, 2013 4:08 PM
    Tuesday, September 3, 2013 3:09 PM

Answers

  • Refer the below sample,

    declare @revenue table(ProductID int,EditionID int,[Year] int,Jan int,Feb int,Mar int)
    insert into @revenue values(1,1,2013,12,13,14)
    insert into @revenue values(2,1,2013,12,13,14)
    insert into @revenue values(3,2,2013,12,13,14)
    insert into @revenue values(1,1,2014,12,13,14)
    insert into @revenue values(3,2,2014,12,13,14)
    insert into @revenue values(3,1,2015,12,13,14)
    insert into @revenue values(3,4,2013,12,13,14)
    insert into @revenue values(3,1,2013,12,13,14)
    ------------------Select the necessary fields only
    select * from (
    	select distinct ProductID,EditionID from @revenue) Y left join 
    	(select * from @revenue where [Year] = Year(getdate())) Y1 on Y.ProductID=Y1.ProductID and Y.EditionID=Y1.EditionID left join 
    	(select * from @revenue where [Year] = Year(getdate())+1) Y2 on Y.ProductID=Y2.ProductID and Y.EditionID=Y2.EditionID left join 
    	(select * from @revenue where [Year] = Year(getdate())+2) Y3 on Y.ProductID=Y3.ProductID and Y.EditionID=Y3.EditionID left join 
    	(select * from @revenue where [Year] = Year(getdate())+3) Y4 on Y.ProductID=Y4.ProductID and Y.EditionID=Y4.EditionID left join 
    	(select * from @revenue where [Year] = Year(getdate())+4) Y5 on Y.ProductID=Y5.ProductID and Y.EditionID=Y5.EditionID left join 
    	(select * from @revenue where [Year] = Year(getdate())+5) Y6 on Y.ProductID=Y6.ProductID and Y.EditionID=Y6.EditionID left join 
    	(select * from @revenue where [Year] = Year(getdate())+6) Y7 on Y.ProductID=Y7.ProductID and Y.EditionID=Y7.EditionID
    order by Y1.ProductID
    	
    	


    Regards, RSingh

    • Marked as answer by Trinakriae Wednesday, September 4, 2013 1:23 PM
    Tuesday, September 3, 2013 4:42 PM

All replies


  • The months related data must be equal to zero if there is no year year yet in the database.



    Try this one:

    SELECT  *
    FROM    Revenus r1
            JOIN Revenues r2 ON r1.ProductID = r2.ProductID
                                AND r1.EditionID = r2.EditionID
    WHERE   r1.[Year] <> r2.[Year]
            AND r1.[Year] IN ( SELECT   [Year]
                               FROM     Revenus
                               WHERE    [Year] IS NOT NULL )
            AND r2.[Year] IN ( SELECT   [Year]
                               FROM     Revenus
                               WHERE    [Year] IS NOT NULL )


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog

    Tuesday, September 3, 2013 3:22 PM
  • Sorry maybe my explanation was a bit confusing...see example in the update
    Tuesday, September 3, 2013 4:11 PM
  • Refer the below sample,

    declare @revenue table(ProductID int,EditionID int,[Year] int,Jan int,Feb int,Mar int)
    insert into @revenue values(1,1,2013,12,13,14)
    insert into @revenue values(2,1,2013,12,13,14)
    insert into @revenue values(3,2,2013,12,13,14)
    insert into @revenue values(1,1,2014,12,13,14)
    insert into @revenue values(3,2,2014,12,13,14)
    insert into @revenue values(3,1,2015,12,13,14)
    insert into @revenue values(3,4,2013,12,13,14)
    insert into @revenue values(3,1,2013,12,13,14)
    ------------------Select the necessary fields only
    select * from (
    	select distinct ProductID,EditionID from @revenue) Y left join 
    	(select * from @revenue where [Year] = Year(getdate())) Y1 on Y.ProductID=Y1.ProductID and Y.EditionID=Y1.EditionID left join 
    	(select * from @revenue where [Year] = Year(getdate())+1) Y2 on Y.ProductID=Y2.ProductID and Y.EditionID=Y2.EditionID left join 
    	(select * from @revenue where [Year] = Year(getdate())+2) Y3 on Y.ProductID=Y3.ProductID and Y.EditionID=Y3.EditionID left join 
    	(select * from @revenue where [Year] = Year(getdate())+3) Y4 on Y.ProductID=Y4.ProductID and Y.EditionID=Y4.EditionID left join 
    	(select * from @revenue where [Year] = Year(getdate())+4) Y5 on Y.ProductID=Y5.ProductID and Y.EditionID=Y5.EditionID left join 
    	(select * from @revenue where [Year] = Year(getdate())+5) Y6 on Y.ProductID=Y6.ProductID and Y.EditionID=Y6.EditionID left join 
    	(select * from @revenue where [Year] = Year(getdate())+6) Y7 on Y.ProductID=Y7.ProductID and Y.EditionID=Y7.EditionID
    order by Y1.ProductID
    	
    	


    Regards, RSingh

    • Marked as answer by Trinakriae Wednesday, September 4, 2013 1:23 PM
    Tuesday, September 3, 2013 4:42 PM