Modify 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

Transact-SQL (2005)

Modify View

Author

Topic

Durbslaw
Starting Member

43 Posts

Posted - 2010-09-14 : 04:52:44

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....

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-14 : 11:06:06

Two strong suggestions:

1)Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

2) SQL is a data base language, so we like to use Calendar auxiliary tables for reporting in fiscal periods. What you have here mimics 1950's COBOL instead. You actually build temporal data in strings! This will let the optimizer use indexes, your code will port, it ought to about one order of magnitude faster and people can maintain it by changing the Calendar auxiliary table.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Durbslaw
Starting Member

43 Posts

Posted - 2010-09-15 : 01:07:47

PLEASE do not post crap like this if you don't have any constructive to post....

All I asked for was help with replacing old code with the one within asterisks.

quote:
Originally posted by jcelko

Two strong suggestions:

1)Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

2) SQL is a data base language, so we like to use Calendar auxiliary tables for reporting in fiscal periods. What you have here mimics 1950's COBOL instead. You actually build temporal data in strings! This will let the optimizer use indexes, your code will port, it ought to about one order of magnitude faster and people can maintain it by changing the Calendar auxiliary table.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-09-15 : 11:09:51

Joe is trying to help. He is one of the foremost SQL gurus around. What you call crap, others call "help" or "instruction".

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Durbslaw
Starting Member

43 Posts

Posted - 2010-09-15 : 13:11:37

My point is, if you don't have anything constructive to add,like try this or tweak that, then don't reply, I'd rather get no answer as opposed to a answer that is as "helpful" as a 1 legged entrant in a butt kicking contest

quote:
Originally posted by DonAtWork

Joe is trying to help. He is one of the foremost SQL gurus around. What you call crap, others call "help" or "instruction".

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

mikgri
Starting Member

39 Posts

Posted - 2010-09-15 : 16:34:07

syntax for datepart is:
datepart(datepart,date) look into your code
AND startdate between datepart(yyyy,startdate)+'0701' and datepart((yyyy+1),startdate))+'0630'
datepart 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.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-15 : 16:48:46

I don't know...I thought they were pretty good suggestions...

He didn't come out and say...."Your code is garbage and you need to go back and take a class or read a book...and you should really read about data modeling and normalization..otherwise you should be working at McDonalds"

See, now that would have been bad

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Durbslaw
Starting Member

43 Posts

Posted - 2010-09-16 : 04:32:31

Wow more "helpful" advice, I wonder where I could get more, hmmmmmm, Mcdonalds maybe, thats me off then, thanks for pointing me in that direction.

quote:
Originally posted by X002548

I don't know...I thought they were pretty good suggestions...

He didn't come out and say...."Your code is garbage and yhe mae ou need to go back and take a class or read a book...and you should really read about data modeling and normalization..otherwise you should be working at McDonalds"

See, now that would have been bad

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Durbslaw
Starting Member

43 Posts

Posted - 2010-09-16 : 05:20:30

Hi mikgri,

Thank you for your time,

my logic is to replace the bugetmodel which references just one financial year, with either an updated one (if existing and prefered) or the model marked 'bud'yy'1', I will also drop the second parameter which requires the check between '0701'yyyy and '0630' (yyyy+1)because it is redundant....

Thanks again,

quote:
Originally posted by mikgri

syntax for datepart is:
datepart(datepart,date) look into your code
AND startdate between datepart(yyyy,startdate)+'0701' and datepart((yyyy+1),startdate))+'0630'
datepart 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.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2010-09-16 : 12:19:50

quote:
Originally posted by Durbslaw

Wow more "helpful" advice, I wonder where I could get more, hmmmmmm, Mcdonalds maybe, thats me off then, thanks for pointing me in that direction.

Bye. Why don't you fill out a job application while you are there, just for good measure?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Durbslaw
Starting Member

43 Posts

Posted - 2010-09-16 : 12:27:15

Wow...I had no idea there were so many out there, it must be some sort of legless convergence of cosmic proportions, still in the land of the blind the one eyed jack is king, but you can see him coming can you.....Whoops my bad...

quote:
Originally posted by blindman

quote:
Originally posted by Durbslaw

Wow more "helpful" advice, I wonder where I could get more, hmmmmmm, Mcdonalds maybe, thats me off then, thanks for pointing me in that direction.

Bye. Why don't you fill out a job application while you are there, just for good measure?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2010-09-17 : 13:11:06

Oh, there are tons out there. Do not worry, you are not alone: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69568

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 06:51:54

quote:
Originally posted by Durbslaw

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;
......
......
......
<FIGHT>
......
......
......

Hi Durbslaw,

Not sure if you are still reading this thread or not. Mr (Dr?) Celko gives quite 'brisk' advice but it is good. He's a bit of legend and insulting his advice probably wasn't a good idea.

Anyway.

From your description of the problem isn't what you want just to select the most recent budget model from somewhere. If there is a nice updated one then you'll get that anyway. If not you'll get the original budget model anyway.

The view looks very clunky. Especially the dates part.

The reason we want people to post the DDL for the tables and sample data is because, most of the time, the SQL they post isn't particularly suited for the problem (you wouldn't be here in the first place if your code was nice and working).

If you post the table structures, sample data and the required output from that data, you'll get constructive suggestions on the best way to get the output you are looking for.

So good luck, chill out a little and if you still are reading, post.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources