How can i create a view that matches Fig-1 to Fig-2

Question

How can i create a view that matches Fig-1 to Fig-2

bjaricha

SSC Enthusiast

Points: 153
More actions
June 14, 2016 at 5:31 am

#400272

Fig - 1
Version Period Amount
1 1 356801.88
2 1 352626.71
1 2 352626.71
2 2 348441.86
1 3 348419.71
2 3 344224.23
1 4 344180.63
2 4 339973.56
1 5 339909.23
2 5 335689.6
3 5 339973.56
4 5 339973.56
5 5 335689.6
1 6 335605.26
2 6 331372.08
3 6 335618.49
4 6 335639.5
5 6 335639.5
6 6 335639.5
Fig - 2
Version PeriodAmount
1 1356801.88
2 2348441.86
2 3344224.23
2 4339973.56
5 5335689.6
6 6335639.5
Need your help thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

bjaricha (6/14/2016)
Fig - 1
VersionPeriodAmount
11356801.88
21352626.71
12352626.71
22348441.86
13348419.71
23344224.23
14344180.63
24339973.56
15339909.23
25335689.6
35339973.56
45339973.56
55335689.6
16335605.26
26331372.08
36335618.49
46335639.5
56335639.5
66335639.5
Fig - 2
VersionPeriodAmount
11356801.88
22348441.86
23344224.23
24339973.56
55335689.6
66335639.5
Need your help thanks

Except for Period 1, you're choosing the max version per period (which is easy, see below). Why is this? What are the business rules?

WITH SampleData ([Version], [Period], Amount) AS (

SELECT 1, 1, 356801.88 UNION ALL

SELECT 2, 1, 352626.71 UNION ALL

SELECT 1, 2, 352626.71 UNION ALL

SELECT 2, 2, 348441.86 UNION ALL

SELECT 1, 3, 348419.71 UNION ALL

SELECT 2, 3, 344224.23 UNION ALL

SELECT 1, 4, 344180.63 UNION ALL

SELECT 2, 4, 339973.56 UNION ALL

SELECT 1, 5, 339909.23 UNION ALL

SELECT 2, 5, 335689.6 UNION ALL

SELECT 3, 5, 339973.56 UNION ALL

SELECT 4, 5, 339973.56 UNION ALL

SELECT 5, 5, 335689.6 UNION ALL

SELECT 1, 6, 335605.26 UNION ALL

SELECT 2, 6, 331372.08 UNION ALL

SELECT 3, 6, 335618.49 UNION ALL

SELECT 4, 6, 335639.5 UNION ALL

SELECT 5, 6, 335639.5 UNION ALL

SELECT 6, 6, 335639.5)

SELECT

[Version], [Period], Amount

FROM (

SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY [Period] ORDER BY [Version] DESC)

FROM SampleData

) d

WHERE rn = 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bjaricha SSC Enthusiast Points: 153 More actions · Answer 2

It is the business rule that after a restructure has been done, then a new version is created with the structured amount which will be taken into consideration.

bjaricha SSC Enthusiast Points: 153 More actions · Answer 3

I have the sample you gave me and its working, thats fine. How will i create a view now in SQL?

djj SSCoach Points: 18831 More actions · Answer 4

bjaricha (6/14/2016)
I have the sample you gave me and its working, thats fine. How will i create a view now in SQL?

Use CREATE VIEW putting the select statement after the AS

bjaricha SSC Enthusiast Points: 153 More actions · Answer 5

You guys are great, thanks very much for your assistance, its working, created the view and all is working.

Thanks very much have a great day

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

bjaricha (6/14/2016)
You guys are great, thanks very much for your assistance, its working, created the view and all is working.
Thanks very much have a great day

Thanks for the feedback!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

durga.palepu Hall of Fame Points: 3530 More actions · Answer 7

I request you to do minimum ground work before posting a question like CREATE VIEW syntax.

Just little googling can help you for syntax.