1-15 then End of Month Formula

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