November 11, 2015 at 4:15 am
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
November 11, 2015 at 4:51 am
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
November 13, 2015 at 2:17 am
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)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply