Results 1 to 3 of 3

Thread: Alter VIEW

  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Wink Unanswered: Alter VIEW

    Hi All,

    Thank you for the forum and your time, I have a clunky but operational view as below and because two budget models are uploaded at different times of the year I would like to alter the budget model which is currently referencing 'bud08/092' to one with

    Code:
    case when modelnum = 'bud' + datepart(year, startdate) + '/' + datepart((year, startdate)+1+'2')
    then modelnum = 'bud' + datepart(year, startdate) + '/' + datepart((year, startdate)+1+'2')
    else
    modelnum = 'bud' + datepart(year, startdate) + '/' + datepart((year, startdate)+1+'1')
    end,
    in other words, if 'bud09/102' exits use it else use 'bud09/101'

    thanks again,

    Code:
    create view FinView
    with
    CurrentExpenditure (VoteNo, VoteType, VoteDescription, FinYear, MonthPart, CurrentExpenditure, PrevExpenditure)
    as
    (
        select     t.AccountNum as VoteNo,
            case when t.accountpltype = 0 then 'P&L' end as VoteType,
            t.accountname as VoteDescription,
            t.FinYear,
            datepart(month, t.transdate) as MonthPart,
            sum(t.amountcur) As CurrentExpenditure,
            sum(sum(t.amountcur)) over(partition by t.accountnum, t.accountpltype, t.accountname, t.FinYear) as PrevExpenditure
        from (
                select    lt.AccountNum,
                        lt.transdate, 
                        FinYear    = case  when datepart(month, lt.transdate) >= 7
                                            then convert(varchar(4), datepart(year, lt.transdate))
                                                  + '/'
                                                  + convert(varchar(4), datepart(year, lt.transdate) + 1)
                                            else convert(varchar(4), datepart(year, lt.transdate) - 1)
                                                  + '/'
                                                  + convert(varchar(4), datepart(year, lt.transdate))
                                            end,
                lt.amountcur,
                ta.accountpltype ,
                ta.accountname
                  from    ledgertrans lt
                          inner join ledgertable ta    on     lt.accountnum     = ta.accountnum
                  where     lt.transdate     >= '20080701'
        ) t
            group by t.accountnum,
                   t.accountpltype,
                   t.accountname,
                t.FinYear,
                   datepart(month, t.transdate)
    ),
    CurrentBudget (VoteNo, FinYear, CurrentBudget)
    as
    (
        select    t.AccountNum as VoteNo,
                t.FinYear,
                sum(t.amount) As CurrentBudget
            from   
            (
            select    v.AccountNum,
                            v.startdate,
                v.ModelNum,
                            FinYear = case  when datepart(month, startdate) >= 7
                                            then convert(varchar(4), datepart(year, startdate))
                                              + '/'
                                              + convert(varchar(4), datepart(year, startdate) + 1)
                                            else convert(varchar(4), datepart(year, startdate) - 1)
                                              + '/'
                                              + convert(varchar(4), datepart(year, startdate))
                                            end,
                            v.amount
                    from    ledgerbudget v
            ) t
            where   modelnum    = 'bud08/092'
            and    startdate    between '20080701' AND getdate()
            group by  t.FinYear, t.accountnum
    )
    select    e.VoteNo, e.VoteType, e.VoteDescription, e.FinYear, e.MonthPart, CurrentExpenditure, PrevExpenditure, CurrentBudget
    from    CurrentExpenditure e
            inner join CurrentBudget b    on    e.VoteNo    = b.VoteNo
    Last edited by pootle flump; 10-07-10 at 06:02.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I have wrapped [ code] tags round your code - easier to read.

    I am not clear what 'bud09/102' is: is it a value, a table, a column etc.?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2010
    Posts
    2

    Thumbs up Thank you

    Hi,

    Thank you for your speedy reply, the 'bud09/101' and 'bud09/102' inputs are values in the modelnum field.
    Actually the format is:

    'bud' + datename datepart(yy, startdate)/datename datepart((yy, startdate)+1+'1)
    as in the case of 'bud09/101' or
    'bud' + datename datepart(yy, startdate)/datename datepart((yy, startdate)+1+'2')
    as in the case of 'bud09/102'
    Last edited by durbslaw; 10-07-10 at 07:33. Reason: incorrect inputs

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •