Print Page | Close Window

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: 18 May 2022 at 6:06am


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



Print Page | Close Window