Best Practive with regards to multiple @dateto params

• ZA_Crafty

  Ten Centuries

  Points: 1228

  More actions

  August 20, 2008 at 1:07 am

  #77074

  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

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  August 20, 2008 at 1:52 am

  #860461

  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