if in a query filter....function?

• antonela

  Ten Centuries

  Points: 1007

  More actions

  November 11, 2015 at 4:15 am

  #151444

  I need to make the querys where the filter is changing (depends on the values contained in some fields)

  create table #company

  (idcompany int not null,

  iddivision int not null,)

  insert into #company values (1,100)

  insert into #company values (1,200)

  insert into #company values (2,100)

  insert into #company values (2,240)

  select * from #company

  create table #fact

  (idcompany int not null,

  iddivision int not null,

  date datetime not null,

  amount decimal (18,3))

  insert into #fact values (1,100,'20150201',1000)

  insert into #fact values (1,100,'20141231',1000)

  insert into #fact values (1,200,'20150301',1050)

  insert into #fact values (2,100,'20141231',1080)

  insert into #fact values (2,240,'20141231',1090)

  select * from #fact

  for iddivision=100 (idcompany=1) and for iddivision=240 (idcompany=2) I need to find the total amount for previous year and current year

  --1 100 2000

  --2 240 1090,

  but for the others I'm interested only in the amount of current year

  --1 200 1050

  --2 100 NULL.

  I need to write a function which change the range of the filter for the query (something like this: select * from #fact where date between @myfunction).

  Is it possible to do this? Any other idea? Thanks

• lnardozi 61862

  SSCommitted

  Points: 1931

  More actions

  November 11, 2015 at 4:51 am

  #1839063

  How about

  select f.* from fact f where date between @lastYear and @thisYear

  cross apply ( select case when (ididivision=100 or iddivision=240) or date>=@thisYearBegin then 1 end) b

• antonela

  Ten Centuries

  Points: 1007

  More actions

  November 13, 2015 at 2:17 am

  #1839543

  I think this could function properly....select * from #t1 where 1 = (case when ididivision=100 and idcompany=1 or iddivision=240 and data between @beginLastYear and @endLastYear then 1

  when (ididivision<>100 and idcompany=1) and iddivision<>240 and data between @beginThisYear and @endThisYear then 1

  end)