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.
| Author |
Topic |
|
cidr
Posting Yak Master
207 Posts |
 Posted - 2009-08-10 : 09:10:20
|
| Hi there,I've been working on a query for a while to do with budgets.As some code requires a GROUP BY, I've created a view for part of that code because the main query has a subquery in the select list and this isn't allowed with GROUP BY.However, the code within the View originally had parameters. It seems paramters or declaring them isn't allowed in a view. The paramters are for dates. The dates will always be from the 1st of Febuary of the year a report is run to date. datAuditDate >= @StartDate and datAuditDate< @EndDate -- could use getdate()Does anyone know if there's a way round this for a view? perhaps calling a function or something?Thanks |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-10 : 11:23:06
|
| You can use this as the Feb 1 of current year:datAuditDate >= convert(datetime, datename(year, getdate()) + '-02-01')Be One with the OptimizerTG |
 |
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2009-08-11 : 05:12:58
|
| Thanks a bunch TG, I'll see if this will work and will let you know. I may just use this for the future. The code I have is starting to become so convoluted that I may have to simplify my approach.The dataset's for SSRS and I was going to use subqueries to create a row number to use CASE statements to bring duplicate budget column totals to "0" etc etc.The dataset was taking 5 minutes to run oft..Thanks again |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-08-11 : 08:03:36
|
| Why don't you post your query?Regards,Charlie.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2009-08-11 : 11:00:46
|
| The real reason I don't want to post it is because it's very long and convoluted. Usually I would, however, I feel it may be too long to wade through.If you insist, however, I'll post it.thanks |
|
|
|
|
|
|
|
|
Alternative to variables for a View