• Replies 6 replies
  • Subscribers 338 subscribers
  • Views 128 views
  • Users 0 members are here
Related

Can if/then be used when declaring values?

Ashley Elliott (Past Member)
$organization

**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

 

Parents
  • $organization

    You can do something like this:

    Declare
    @StartDate                    date --= '02/01/2020'
    ,@EndDate                    datetime --= '01/01/2021'
    ,@NumberOfMonths            int-- =3


    if @StartDate > @EndDate set @EndDate = null
    Set @NumberOfMonths = ISNULL (@NumberOfMonths,1)
    if @NumberOfMonths < 0 set @NumberOfMonths = 1
    Set @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 )

Reply
  • $organization

    You can do something like this:

    Declare
    @StartDate                    date --= '02/01/2020'
    ,@EndDate                    datetime --= '01/01/2021'
    ,@NumberOfMonths            int-- =3


    if @StartDate > @EndDate set @EndDate = null
    Set @NumberOfMonths = ISNULL (@NumberOfMonths,1)
    if @NumberOfMonths < 0 set @NumberOfMonths = 1
    Set @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 )

Children
No Data