Post reply

Derived field help

  • July 16, 2012 at 2:44 pm

    #272578

    Hi

    My problem is I have a view named Forecast with data in that looks like this:

    RowID RegisteredSpendDate FinancialYrSpendPerFinYr($) FinQtr

    (DD/MM/YY)

    1 01/01/12 2012100 Q1

    2 01/04/12 2012100 Q2

    3 01/04/12 2012100 Q2

    4 01/07/12 2012100 Q3

    5 01/07/12 2012100 Q3

    6 01/07/12 2012100 Q3

    I want to add a new derived column ForecastSpendPerQtr that pro-ratas the SpendPerFinYr($) and spreads it across FinQtr so the view would look like this:

    RowIDRegisteredSpendDate FinancialYrSpendPerFinYr($) FinQtr ForecastSpendPerQtr

    (DD/MM/YY)

    1 01/01/12 2012100 Q1 100

    2 01/04/12 2012100 Q2 50

    3 01/04/12 2012100 Q2 50

    4 01/07/12 2012100 Q3 33.33

    5 01/07/12 2012100 Q3 33.33

    6 01/07/12 2012100 Q3 33.33

    This looks easy, but my brain has given up the ghost after much huffing and puffing – any ideas on an 'easy' way of populating the last column correctly?

    I don't have permissions to alter the underlying data tables.

    Cheers

    canazei65

  • July 16, 2012 at 2:57 pm

    #1513331

    This?

    ; WITH CTE (RowID, RegisteredSpendDate, FinancialYr, SpendPerFinYr,FinQtr) AS

    (

    SELECT 1, '01/01/12', 2012, 100, 'Q1'

    UNION ALL SELECT 2, '01/04/12', 2012, 100, 'Q2'

    UNION ALL SELECT 3, '01/04/12', 2012, 100, 'Q2'

    UNION ALL SELECT 4, '01/07/12', 2012, 100, 'Q3'

    UNION ALL SELECT 5, '01/07/12', 2012, 100, 'Q3'

    UNION ALL SELECT 6, '01/07/12', 2012, 100, 'Q3'

    )

    SELECT C.RowID

    , C.RegisteredSpendDate

    , C.FinancialYr

    , C.SpendPerFinYr

    , C.FinQtr

    ,( C.SpendPerFinYr /(COUNT (*) OVER (PARTITION BY C.FinancialYr, C.FinQtr) * 1.00 )

    )

    FROM CTE C

  • July 16, 2012 at 3:19 pm

    #1513341

    Or, if you want to revise the view itself, try something like this:

    ;WITH cte as

    (Select [FinancialYr],[FinQtr],Count(FinQtr) as NumCount from vForecast group by [FinancialYr],[FinQtr])

    Update vForecast

    Set ForecastSpendPerQtr=f.[SpendPerFinYr($)]/cte.NumCount

    From vFin f JOIN cte ON f.[FinancialYr]=cte.[FinancialYr] and f.[FinQtr]=cte.[FinQtr]

    Afterward, you can obtain the data by a simple "Select * from vForecast"

    Elliott

  • July 16, 2012 at 4:11 pm

    #1513354

    Thank you guys -

    I've not written much SQL for a while and this (use of cte's) has really refreshed my knowledge.

    I'll implement and let you know how it goes.

    Cheers

    canazei65

  • July 20, 2012 at 5:51 am

    #1515461

    Elliott and SSC Crazy - After a bit of a delay, I've implemented Elliott's solution and will be changing a few other Finance views too. This will make reporting much easier.

    Thanks for the lowdown, a great help.

    Cheers

    Canazei65

  • July 20, 2012 at 9:51 am

    #1515616

    Canazei65 (7/20/2012)

    Elliott and SSC Crazy - After a bit of a delay, I've implemented Elliott's solution and will be changing a few other Finance views too. This will make reporting much easier.

    Thanks for the lowdown, a great help.

    Cheers

    Canazei65

    One downside to Elliot's solution, it updates the existing SpendPerFY column. So down the line, you will loose the actual SpendPerFY value of each row.

  • July 20, 2012 at 10:04 am

    #1515621

    Good point and I'll definitely bear it in mind. We usually report well over a month in arrears though, so hopefully we should have plenty of time to adjust it.

    Cheers

  • July 20, 2012 at 10:40 am

    #1515645

    Hello again,

    Just a quick note:

    My (quick and dirty) solution does not act to alter the existing data, but to update the new column that the OP intends to add to the view (the ForecastSpendPerFinQtr column). I think if you look closely, you will see that the code leaves the existing data untouched.

    If I missed something, let me know.

    Thanks,

    Elliott

  • July 20, 2012 at 1:55 pm

    #1515743

    Hi

    Yes, we're just talking views here, so you're correct.

    Cheers

    Canazei65

  • July 23, 2012 at 12:02 am

    #1516071

    Canazei65 (7/16/2012)

    Hi

    RowIDRegisteredSpendDate FinancialYrSpendPerFinYr($) FinQtr ForecastSpendPerQtr

    (DD/MM/YY)

    4 01/07/12 2012100 Q3 33.33

    5 01/07/12 2012100 Q3 33.33

    6 01/07/12 2012100 Q3 33.33

    Cheers

    canazei65

    I couldn't help noticing that in the last 3 rows of your desired row set, you've lost precision in the allocated amount.

    If some beancounter comes along and complains about that, you can use the technique I wrote about in this article to fix it: http://www.sqlservercentral.com/articles/Financial+Rounding/88067/

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • August 1, 2012 at 3:40 pm

    #1520570

    Thanks Dwain - nice to come back from hols to some interesting reading. Looks very useful.

    Cheers

    Canazei

Viewing 11 posts - 1 through 10 (of 10 total)

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