Generating Month Names Between Two Dates

[fayuss]

Need help generating month names between two dates using CR 2016:

In Date = MAY 2017
Out Date = JAN 2018

I need:

MAY 2017
JUN 2017
JUL 2017
AUG 2017...
JAN 2018

The DateDiff only gives me a number of months between the two points. The DateAdd only gives me the end point which I already have. I need a formula or method to create the list of months between the two dates to use in a crosstab for summaries. Ultimate goal is to identify every month a record was "IN" but only using the start and end dates. I cannot use a calendar table and do not have access to the source database (Oracle).

Thanks for any help, in advance...

 

[supportsvc]

Take at look at this thread:

https://archive.sap.com/discussions/thread/3487116

Hope that helps

 

[fayuss]

Thanks - this helps some but is dependent upon the dates being in the database. I don't have that situation - only the low and high end points of the range. A similar scenario to what I'm trying to solve would be extended stays at a hotel. 100 People person check-in MAY 2017 and they all check-out in different months until JAN 2018. I need to figure out how many people were checked-in at any point each month.

 

[IanWaterman]

You can not show data that does not exist.
Ideally you need a table with all dates which can be your main table and then left join off this table to your data.
You can then display/group date data from this table. Months will then show even when no actual data in your main query exists.

Failing that you will need to convert report to a command and then create a temp table of dates you require to achieve the same results as above.

Ian

 

[ianoctdec]

Hello,

This is a nice piece of code (posted by somebody on Oracle forum, don't recall the name) that generates the calendar between two dates:
SELECT C.REF_DATE, TO_CHAR(C.REF_DATE, 'WW') AS WEEK_NO, TO_CHAR(C.REF_DATE, 'MM') AS MONTH_NO
FROM
(SELECT TRUNC({?StartDate}) - 1 + N.DAY_COUNTER AS REF_DATE
FROM
(SELECT ROWNUM AS DAY_COUNTER
FROM ( SELECT 1
FROM DUAL
CONNECT BY LEVEL <= {?StopDate} - {?StartDate} + 1
) T
) N
) C

I hope it helps.

Dana

 

[SkipVought]

this helps some but is dependent upon the dates being in the database.

Your company, I’d wager, has a business calendar database table, and likely has a way for users like you to access. Ask your supervisor. Ask your IT liaison.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kenhamady]

What you are really trying to do is turn one record into several records. I have found that the cleanest way to do that is with an "inflation table":

http://kenhamady.com/cru/archives/2806

http://kenhamady.com/cru/archives/2807

If your time period can span up to x months then you will need an inflation table with x records which will create x duplicates of the original record. If these records are already numbered 1 to x, even better. If not, you can write a formula so that each of the records has a consecutive number. The inflation table number will be different in each of the duplicates. You can then use that number to add months to the start date so that each record becomes a different month's date. Then you can filter out the dates that are not between the begin and end dates of the original record.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/