**self-hosted org**
My predecessor wrote a script that I am trying to adjust to meet current needs. So the red arrow in the image below points to a line in the script that makes a column of values go to the end of the previous month. That is great when they are running this for a single month, but in some cases, my coworkers run the report across two or more months. The purple arrow in the image points to where I commented out that function and made the date values go to whatever start date is chosen, and that work great too. But how can I make BOTH option available? How can I make it evaluate the date parameters on an If/then basis? Is that even possible when declaring a value?
Thanks,
Ashley Elliott
Database Administrator
St. Louis Symphony Orchestra
314-286-4198
ashleye@slso.org
IF/ELSE can be used for setting values, certainly, as long as the declaration is outside of it. So:
declare @PM_End datetime
if @months > 1
begin
@PM_End = @startdate
end
else begin
@PM_End = dbo.function(@enddate)
Am I reading your question correctly?
I think so... I will try this next week and reply back.
Thank you so much!
Ashley
Omitting and forgetting things in my example, for instance forgot to add the select or set commands...
Another way to express this is with an "inline IF" using the IIF function. You can also declare and assign variables in the same statement:
DECLARE @PM_End datetime = IIF(<condition>, @startDate, dbo.fnEndOfPreviousMonth(@enddate));
When breaking up the declaration and assignment, I also prefer to use SET instead of SELECT because if you make a mistake, it's not possible to return a result set from your procedure by accident with a SET.
You can do something like this:
Declare@StartDate date --= '02/01/2020',@EndDate datetime --= '01/01/2021',@NumberOfMonths int-- =3if @StartDate > @EndDate set @EndDate = null Set @NumberOfMonths = ISNULL (@NumberOfMonths,1)if @NumberOfMonths < 0 set @NumberOfMonths = 1Set @StartDate = isnull(@StartDate ,dateadd(month,(@NumberOfMonths -1 ) * -1,DATEADD(month , DATEDIFF(month, 0, isnull(@EndDate,GETDATE())), 0)))Set @EndDate = dbo.LFN_MBA_EOD ( ISNULL (@EndDate , DATEADD (dd, -1, DATEADD(month, DATEDIFF(month, 0 , DATEADD (month,@NumberOfMonths - 1 ,@StartDate )) + 1, 0))))SELECT @StartDate StartDate, @EndDate EndDate
LFN_MBA_EOD is a function to calculate the end of day, the logic is:
dateadd(ms,-3,cast(dateadd(day,1,cast(isnull(@DateValue,getdate()) as date)) as datetime))
Running it with nulls yields this result:
Setting only the number of months:
Setting an end date
Setting a start date
Setting a start and end date
It is not 100% perfect, but it provides the basics of what you were looking for and could be made into a table function so the logic could be reused without having to type the code over and over again. Implementing it would look something like this:
Select @StartDate = StartDate , @EndDate = endate from LFT_Some_Function_Name(@StartDate , @EndDate , @NumberOfMonths )
*SOLVED*
I combined parts of Nick's reply and parts of Ronald's reply and achieved success. THANK YOU ALL SO MUCH! These forums and this community are a life saver.