SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Modify View
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Durbslaw
Starting Member

43 Posts

Posted - 09/14/2010 :  04:52:44  Show Profile  Reply with Quote
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

USA
547 Posts

Posted - 09/14/2010 :  11:06:06  Show Profile  Visit jcelko's Homepage  Reply with Quote
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
Go to Top of Page

Durbslaw
Starting Member

43 Posts

Posted - 09/15/2010 :  01:07:47  Show Profile  Reply with Quote
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


Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2143 Posts

Posted - 09/15/2010 :  11:09:51  Show Profile  Reply with Quote
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
Go to Top of Page

Durbslaw
Starting Member

43 Posts

Posted - 09/15/2010 :  13:11:37  Show Profile  Reply with Quote
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

Go to Top of Page

mikgri
Starting Member

39 Posts

Posted - 09/15/2010 :  16:34:07  Show Profile  Reply with Quote
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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/15/2010 :  16:48:46  Show Profile  Reply with Quote
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




Edited by - X002548 on 09/15/2010 16:51:49
Go to Top of Page

Durbslaw
Starting Member

43 Posts

Posted - 09/16/2010 :  04:32:31  Show Profile  Reply with Quote
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






Edited by - Durbslaw on 09/16/2010 05:05:38
Go to Top of Page

Durbslaw
Starting Member

43 Posts

Posted - 09/16/2010 :  05:20:30  Show Profile  Reply with Quote
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.

Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 09/16/2010 :  12:19:50  Show Profile  Reply with Quote
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.
________________________________________________
Go to Top of Page

Durbslaw
Starting Member

43 Posts

Posted - 09/16/2010 :  12:27:15  Show Profile  Reply with Quote
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.
________________________________________________


Edited by - Durbslaw on 09/16/2010 12:30:17
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 09/17/2010 :  13:11:06  Show Profile  Reply with Quote
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.
________________________________________________
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 09/20/2010 :  06:51:54  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000