Good Day To All,I'm working on a budgetreport that ask the user to enter the up to date.If the user enters let's say 08/2012 I'm able to get actual data from the begin of the beginning of the year to the end of the month of 08/2012.My problem is now the user wants to get the budgeted data for the remaining part of the year. For this example I need 09/2012 through 12/2012 budget data.

This is what I have so far but doesn't work properly.

sum(CASE When t.uMonth between dateadd(m,11,dbo.get_boy(dateadd(m, month('#date2#') + 1,'#date2#'),p.hmy)) and dateadd(m,11,dbo.get_boy(dateadd(y,1,'#date2#'),p.hmy)) THEN CASE a.iNormalBalance WHEN 1 THEN l.dpercent*.01*isnull(-t.sBudget,0) ELSE l.dpercent*.01*isnull(t.sBudget,0) END ELSE 0 END ) data8,

***dbo.get_BOY is a function that get Beginning Of Year*** #date2# ***** is a parameter that is entered by the user**** data 8 is the placeholder for the end result.**** a.iNormalBalance is a flag field when 1 do something when it's 0 do something else*** u.month is the month I test against from the enter parameterThanks

My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

I don't want to post it again, so Google around here for "Report_Periods"; it is a basic look-up table for temporal durations. Don't use UDFs; they screw up the optimizer, cannot port and are a bitch to maintain.

Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL

CELKO (9/18/2012)I don't want to post it again, so Google around here for "Report_Periods"; it is a basic look-up table for temporal durations. Don't use UDFs; they screw up the optimizer, cannot port and are a bitch to maintain.

Please ignore this stupid remark!Although in your case UDF is not really required, they are very helpful and useful where appropriate. UDF is a powerful feature of SQL Server and when UDFs implemented correctly, they do not screw up the optimiser. If you thinking to port your system into other RDBMS, believe me, having UDFs will not be your main concerns. They are not harder to maintain than stored procedures.Mr. Celko hates many different features of SQL Server (which he cannot comprehend) and he is very vocal about it...