Need some tip on a tricky SQL query

Question

I have a SQL challenge that I struggle with. My case is like this.

I got a table called #Balance where I have history data (always versionid=0) and prognosis data (incremented versionid > 0).

Every month a new prognosis is created.

So in December 2017, we have history data up to November 2017, but a prognosis 153 is created for December 2017 and the next months.

And in January 2018 we have history data up to December 2017 and a new prognosis starts January 2018, and so it goes.

But the trouble comes to reporting. Because every prognosis must include all history months up to the prognose start months.

Below you see an example.

The Version table is simplified, it has many more rows, so is Balance. What I want is the final result, which has historical data as is, but also adds history to all prognosis up to the prognose start month.

The SQL code should be generic so it can generate a result table for many more version ids. I need to use historyTo from Version and (AYear*100+APer) from Balance to find the result.

Could somebody try to help me here?

Thank you

Regards Geir

Version
x------------x------------------------x
|     Id     |   Name    | HistoryTo  |
x------------x------------------------x
|    0       |  History  |   200701   |
|    153     |  Nov 2017 |   201711   |
|    154     |  Des 2017 |   201712   |
x------------x-----------x------------x

Balance
x------------x--------------------x---------x
| VersionId  |   AYear |   APer   | Amount  |
x------------x--------------------x---------x
|    0       |  2017   |   10     |  29327  |
|    0       |  2017   |   11     |    351  |
|    0       |  2017   |   12     |   6530  |
|  154       |  2018   |    1     |  25000  |
|  154       |  2018   |    2     |  39136  |
|  154       |  2018   |    3     |  20000  |
|  153       |  2017   |   12     |   3000  |
|  153       |  2017   |    1     |  47000  |
|  153       |  2017   |    2     |  35000  |
x------------x---------x----------x---------|

Result
x------------x--------------------x---------x
| VersionId  |   AYear |   APer   | Amount  |
x------------x--------------------x---------x
|    0       |  2017   |   10     |  29327  |
|    0       |  2017   |   11     |    351  |
|    0       |  2017   |   12     |   6530  |
|  154       |  2017   |   10     |  29327  |
|  154       |  2017   |   11     |    351  |
|  154       |  2017   |   12     |   6530  |
|  154       |  2018   |    1     |  25000  |
|  154       |  2018   |    2     |  39136  |
|  154       |  2018   |    3     |  20000  |
|  153       |  2017   |   10     |  29327  |
|  153       |  2017   |   11     |    351  |
|  153       |  2017   |   12     |   3000  |
|  153       |  2017   |    1     |  47000  |
|  153       |  2017   |    2     |  35000  |
x------------x---------x---------x----------|




create table #Version (
    Id int 
,   [Name] varchar(100)
,   HistoryTo int 
,   constraint PK_Version primary key (Id)
)
GO

create table #Balance (
    Id int identity(1,1)
,   VersionId int
,   AYear int
,   APer int
,   Amount int
,   constraint PK_Balance primary key (Id)
)
GO

insert #Version
select 0, 'History', 200701 union
select 153, 'Nov 2017', 201711 union
select 154, 'Dec 2017', 201712

insert #Balance
select 0, 2017, 10, 29327 union
select 0, 2017, 11, 351 union
select 0, 2017, 12, 6530 union
select 154, 2018, 1, 25000 union
select 154, 2018, 2, 39136 union
select 154, 2018, 3, 20000 union
select 153, 2017, 12, 3000 union
select 153, 2018, 1, 47000 union
select 153, 2018, 2, 35000 

Answer 1

Here is a simple solution:

select v.id, b.Ayear, b.Aper, b.amount
from #Version v join #Balance b on v.historyTo >= (b.Ayear*100+b.Aper)
where v.Id != 0 and b.VersionId = 0
union 
select B.VersionId, B.Ayear, B.Aper, B.amount from #Balance B

you can test it here: http://rextester.com/CVEY79303

Answer 2

I have implemented the logic in query but it can be further optimized.

CREATE TABLE #Version (
    Id INT 
    , [Name] VARCHAR(100)
    , HistoryTo INT 
    , CONSTRAINT PK_Version PRIMARY KEY (Id)
)
GO

CREATE TABLE #Balance (
    Id INT IDENTITY(1,1)
    , VersionId INT
    , AYear INT
    , APer INT
    , Amount INT
    , CONSTRAINT PK_Balance PRIMARY KEY (Id)
)
GO

INSERT #Version
SELECT 0, 'History', 200701 UNION
SELECT 153, 'Nov 2017', 201711 UNION
SELECT 154, 'Dec 2017', 201712

INSERT #Balance
SELECT 0, 2017, 10, 29327 UNION
SELECT 0, 2017, 11, 351 UNION
SELECT 0, 2017, 12, 6530 UNION
SELECT 154, 2018, 1, 25000 UNION
SELECT 154, 2018, 2, 39136 UNION
SELECT 154, 2018, 3, 20000 UNION
SELECT 153, 2017, 12, 3000 UNION
SELECT 153, 2018, 1, 47000 UNION
SELECT 153, 2018, 2, 35000 


SELECT * FROM #Version
SELECT * FROM #Balance


DROP TABLE IF EXISTS #TempBalance
CREATE TABLE #TempBalance (
    Id INT IDENTITY(1,1)
    , VersionId INT
    , AYear INT
    , APer INT
    , Amount INT
    , CONSTRAINT PK_TempBalance PRIMARY KEY (Id)
)
GO


DECLARE @CurrentId INT ;

DECLARE TMP_Cursor CURSOR DYNAMIC FOR 
SELECT Id FROM #Version 

OPEN TMP_Cursor

FETCH NEXT FROM TMP_Cursor INTO @CurrentId

WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT INTO #TempBalance
    SELECT VersionId 
        , AYear
        , APer
        , Amount
    FROM #Balance WHERE VersionId = @CurrentId
    UNION ALL
    SELECT DISTINCT
        @CurrentId AS VersionId 
        , AYear
        , APer
        , Amount
    FROM #TempBalance
    WHERE CONCAT(AYear, APer) NOT IN (SELECT CONCAT(AYear, APer) FROM #Balance WHERE VersionId = @CurrentId)

  FETCH NEXT FROM TMP_Cursor INTO @CurrentId

END

CLOSE TMP_Cursor
DEALLOCATE TMP_Cursor

SELECT 
    VersionId,
    AYear,
    APer,
    Amount 
FROM 
    (
        SELECT 
            * 
            , ROW_NUMBER() OVER(PARTITION BY VersionId, AYear, APer ORDER BY Id DESC) AS Rank1
        FROM #TempBalance
    ) AS T
WHERE 
    Rank1 = 1
ORDER BY 
    VersionId ASC
    , AYear ASC
    , APer ASC