I have a crystal report date range that is used for a time sheet. (something I inherited) I am looking for help on how to change this into part of my sql query out of Oracle for a SSRS report
Today - Round((((Today - Date (1998,11,23 ))/14) - Truncate((Today - Date (1998,11,23 ))/14)) * 14,0)
Thank you in advance Steven (who is still very new to sql and ssrs reporting)
The direct translation of what you have seems to be:
trunc(sysdate) - round((((trunc(sysdate) - date '1998-11-23')/14) - trunc((trunc(sysdate) - date '1998-11-23')/14)) * 14,0)
But you can simplify that to:
trunc(sysdate) - mod(trunc(sysdate) - date '1998-11-23', 14)
The two trunc(sysdate) calls give the current date with time truncated to midnight, so currently 2016-02-12. If you subtract your fixed start date (presumably the first ever period in your system?) you get a number, from how Oracle does datetime arithmetic:
select trunc(sysdate) - date '1998-11-23' from dual;
TRUNC(SYSDATE)-DATE'1998-11-23'
---------------------------------------
6290
Because both dates were at midnight that is a whole number - the whole number of days between today and the fixed date. If you divide that by 14 you get 449.285, which is 449 whole two-week periods, and .285 of the current one. Your original calculation takes that and removed the truncated version 449 to just leave .285, then multiples that by 14 to get that leftover part back as a whole number, which is 4.
But that's exactly what the mod() function gives you - "the remainder of n2 divided by n1". In other words, mod(6290, 14) is the remainder of 6290 divided by 14, which is also 4. It's getting the same result from one expression instead of two.
So then you just subtract that calculated value of 4 from the current day, which today tales you back to 2016-02-08.
Seeing how that changes for a selection of generated dates:
with t (dt) as (
select trunc(sysdate) - level from dual connect by level < 21
)
select dt,
dt - round((((dt - date '1998-11-23')/14) - trunc((dt - date '1998-11-23')/14)) * 14) long_version,
dt - mod(dt - date '1998-11-23', 14) period_start,
dt - mod(dt - date '1998-11-23', 14) + 14 period_end
from t
order by dt;
DT LONG_VERSION PERIOD_START PERIOD_END
---------- ------------ ------------ ----------
2016-01-23 2016-01-11 2016-01-11 2016-01-25
2016-01-24 2016-01-11 2016-01-11 2016-01-25
2016-01-25 2016-01-25 2016-01-25 2016-02-08
2016-01-26 2016-01-25 2016-01-25 2016-02-08
...
2016-02-06 2016-01-25 2016-01-25 2016-02-08
2016-02-07 2016-01-25 2016-01-25 2016-02-08
2016-02-08 2016-02-08 2016-02-08 2016-02-22
2016-02-09 2016-02-08 2016-02-08 2016-02-22
2016-02-10 2016-02-08 2016-02-08 2016-02-22
2016-02-11 2016-02-08 2016-02-08 2016-02-22
You can either get the period end by adding days to the result, or calculate it separately using a start date 14 days later. If you're looking for records in a range you could then do:
where some_date >= trunc(sysdate) - mod(trunc(sysdate) - date '1998-11-23', 14)
and some_date < trunc(sysdate) - mod(trunc(sysdate) - date '1998-11-23', 14) + 14
If none of the dates ever have a time after midnight (which may be the case for a timesheet) you could use between and make the period end 13 days later, as you suggested you were currently doing in a comment:
where some_date between trunc(sysdate) - mod(trunc(sysdate) - date '1998-11-23', 14)
and trunc(sysdate) - mod(trunc(sysdate) - date '1998-11-23', 14) + 13
I prefer the >= and < approach though, as it won't trip you up when you do something on data where the times do matter too.