1-15 then End of Month Formula
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22991
Printed Date: 19 Apr 2024 at 12:18pm
Topic: 1-15 then End of Month Formula
Posted By: BoltzGirl
Subject: 1-15 then End of Month Formula
Date Posted: 03 Dec 2021 at 9:19am
I am searching for how to create a formulas for my BegDate and EndDate to use in my parameters; that will pull the 1st through 15th of the month and then 16th to the end of the month.
We were thinking aloud something like this.
If BegDate = 01/01/1900 give me the 1st through the 15th
If Beg Date = 01/01/1850 give me the 16th through the end of the previous month.
We want to run the report regularly, scheduling it to run on the 16th, to pull 1-15 and then on the first of each month run it for 16th through the end of the previous month.
Hope I am making sense.
-------------
Always appreciate the help!
|
Replies:
Posted By: lockwelle
Date Posted: 06 Dec 2021 at 6:51am
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
|
|