declare @t table
(
Maxmonth int,
startmonth varchar,
Duration int,
endmonth varchar,
fixedAmount money,
RefferalAmount int
)
insert @t (Maxmonth,startmonth,Duration,endmonth,fixedAmount,RefferalAmount)
select DATEPART(MM,start_date) + fixeddDuration-1,
DATEPART(MM,start_date),
fixeddDuration ,
DATEPART(MM,end_date),
fixedAmount,
case when ( start_date >= Datepart(MM,start_date)) AND ( end_date <= Datepart(MM,end_date))
then fixedAmount/nullif(fixeddDuration,0)
ELSE 0
END
from TC_Project_Referal
where id_tc_projuser = 14
select * from @t
actually i want to calculate the refferalamount . here the fixedamount is given in database from the frontend..lets suppose 1000$ and duration is 6 months
so we need to calculate based on the date 2-1-2012 startdate and 6-1-2012 is enddate here the period of time is 6 months form feb1st to july 1st so we need to calculate.
1>=2 & 1<=7 ---1st month -- here we should not allow because the start date is from feb but it is jan so it is not allowed to calculation
2>=2 & 2<=7 --2nd month
3>=2 & 3<=7 --3rd month
4>=2 & 4<=7 --4th month
5>=2 & 5<=7 --5th month
6>=2 & 6<=7 --6th month
7>=2 & 7<=7 --7th month
8>=2 & 8<=7 --7th month---here calculation is not allowed because it is the 8th month but when you do for 8th month the referralamount will be show as zero because the duration period is only for 6 months as we take like that...it is based on the user
am getting error in case .....when And is used it is not working showing the result as 0 but when i use OR it is showing the result.....
Submit an article or tip