AnsweredAssumed Answered

To calculate difference between 2 dates(parameters)

Question asked by Riya on Aug 10, 2011
Latest reply on Aug 11, 2011 by Riya
Hi,
I'm calculating difference between 2 dates(start and end dates from the parameter filter on the report) to calculate weeks and multiply no.of weeks * 37.5 hours to get available hours. My report shows emplty available hours and if i filter on dates the report is empty.

Please let me know what must be wrong. Any suggestions?

Select
CASE
WHEN TR.PERSON_TYPE=300 THEN
(DATEDIFF(week, @WHERE:PARAM:USER_DEF:DATE:VSDATE@, @WHERE:PARAM:USER_DEF:DATE:VEDATE@) * 37.5)
WHEN TR.PERSON_TYPE=301 THEN
(DATEDIFF(week, @WHERE:PARAM:USER_DEF:DATE:VSDATE@, @WHERE:PARAM:USER_DEF:DATE:VEDATE@) * 40)
ELSE (DATEDIFF(week, @WHERE:PARAM:USER_DEF:DATE:VSDATE@, @WHERE:PARAM:USER_DEF:DATE:VEDATE@) * 37.5)
END AS BASELN_HOURS,
from
....
where
....
AND ((S.SLICE_DATE >= @WHERE:PARAM:USER_DEF:DATE:VSDATE@ OR @WHERE:PARAM:USER_DEF:DATE:VSDATE@ IS NULL)
AND (S.SLICE_DATE <= @WHERE:PARAM:USER_DEF:DATE:VEDATE@ OR @WHERE:PARAM:USER_DEF:DATE:VEDATE@ IS NULL))



Thank you
RK

Outcomes