Best Practive with regards to multiple @dateto params

  • I have 3 date params.

    @Now

    @MonthFrom

    @YearFrom

    What is the best practice?

    1.) do my count as a sub query ie:

    , (select count(sale_id) from sale where date >= @MonthFrom and date <= @Now) as 'MTD'

    , (select count(sale_id) from sale where date >= @YearFrom and date <= @Now) as 'YTD'

    or:

    2.) Use Joins Ie:

    , sum(SMTD.SaleID) as 'MTD'

    , Sum(SYTD.SaleID) as 'YTD'

    inner join sale SMTD (NoLock)

    on (join creteria) and @MonthFrom and date <= @Now

    inner join Sale SYTD (NoLock)

    on (join creteria) and @YearFrom and date <= @Now

  • select count(CASE WHEN date >= @MonthFrom THEN sale_id ELSE NULL END ) as 'MTD',

    count(CASE WHEN date >= @YearFrom THEN sale_id ELSE NULL END ) as 'YTD'

    from sale

    where (date >= @YearFrom OR date >= @MonthFrom ) AND date <= @Now

    _____________
    Code for TallyGenerator

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply