| Author |
 Topic  |
|
|
Durbslaw
Starting Member
43 Posts |
 Posted - 09/14/2010 : 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
USA
547 Posts |
Posted - 09/14/2010 : 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 - 09/15/2010 : 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
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
Durbslaw
Starting Member
43 Posts |
|
|
mikgri
Starting Member
39 Posts |
Posted - 09/15/2010 : 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 - 09/15/2010 : 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
|
Edited by - X002548 on 09/15/2010 16:51:49 |
|
|
|
Durbslaw
Starting Member
43 Posts |
Posted - 09/16/2010 : 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
|
Edited by - Durbslaw on 09/16/2010 05:05:38 |
|
|
|
Durbslaw
Starting Member
43 Posts |
Posted - 09/16/2010 : 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
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 09/16/2010 : 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 - 09/16/2010 : 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.
________________________________________________
|
Edited by - Durbslaw on 09/16/2010 12:30:17 |
|
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 09/17/2010 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 09/20/2010 : 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 |
|
|
| |
Topic |
|
|
|
Modify View
