Alter View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Alter View

Author

Topic

Durbslaw
Starting Member

43 Posts

Posted - 2010-09-14 : 02:52:41

Hi All,

I have created a view with great help from many people, the modification I need to make is that, we have 2 budget models being uploaded at different times of the year for eaxmple bud10/111 will be loaded at the begining of the financial year, and bud/112 is uploaded with updates later, the view should query the updated model if present or else it should use the first budget model, also i'm not sure if the statement;

AND startdate between datepart(yyyy,startdate)+'0701' and datepart((yyyy+1),startdate))+'0630' is redundant

here is the code and modification;

ALTER view [dbo].[FinView]
as
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

*********************************************************************
)t
case when modelnum = 'bud' datepart(year, startdate) + '/' + 'bud' datepart((year,startdate)+1)+'2'
then
modelnum = 'bud' datepart(year, startdate) + '/' + 'bud' datepart((year,startdate)+1)+'2'
else
modelnum = 'bud' datepart(year, startdate) + '/' + 'bud' datepart((year,startdate)+1)+'1'
AND startdate between datepart(yyyy,startdate)+'0701' and datepart((yyyy+1),startdate))+'0630'
end,
)
group by t.finyear, t.accountnum
*********************************************************************

Thanks most generously for your time and efforts....

mikgri
Starting Member

39 Posts

Posted - 2010-09-14 : 14:52:22

sysntax for datepart is:
datepart(datepart,date) look into your code
AND startdate between datepart(yyyy,startdate)+'0701' and datepart((yyyy+1),startdate))+'0630'
datatype parameter can't be yyyy+1
you can add 1 after datepart function like this datepart(yyyy,startdate)+1 or what is the logic of your task.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources