This would be inside of Crystal Reports, right?
I have solutions for both adding logic to the command, as well as a possible filter.
I am not sure that this will work in the report logic…at least not where exactly, it would be in the filtering of the data, and I not exactly sure where. Here is a solution:
{?start} is the parameter.
shared datevar startingDate;
shared datevar endDate;
local datevar temp;
if {?start} = #1/1/1900# then (
startingDate := Date(year(today), month(today), 1);
endDate := Date(year(today), month(today), 15);
)
else
if {?start} = #1/1/1850# then (
startingDate := Date(year(dateadd("m", -1, today)), month(dateadd("m", -1, today)), 16);
endDate := Date(year(dateadd("m", -1, today)), month(dateadd("m", -1, today)), day(dateadd("d", -day(today), today)));
);
In the SQL/Command, I would use something like:
declare @start date = '1/1/1900';
with setMonth as( --this sets the month correctly
select
starting = case @start
when '1/1/1900' then getdate()
when '1/1/1850' then dateadd(month, -1, getdate())
end
)
, setDate AS( --this sets the date that you want
select
startDate = case when MONTH(starting) < month(getdate())
then concat(month(starting), '/15/', year(starting))
when month(getdate()) = MONTH(starting)
then concat(month(starting), '/1/', year(starting))
end,
endDate = case when MONTH(starting) < month(getdate())
then concat(month(starting), '/', day(EOMONTH(starting)), '/', year(starting))
when month(getdate()) = MONTH(starting)
then concat(month(starting), '/15/', year(starting))
end
from setMonth
)
select *
from yourTablesHere
CROSS JOIN setDate //this gives you access to the dates above
where yourTable.dateField BETWEEN startDate AND endDate
The @start would be replaced by {?startDate} parameter of the report.
This is for SQL Server, Oracle would have different syntax for the GetDate function, and I not positive that Concat is a ANSI, so it might need to change as well.
HTH
|