Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

create month end dates when dates not passed 2

Status
Not open for further replies.

hlmnsbuggy

Technical User
Mar 25, 2002
72
US
ODBC oracle, crystal report 2011

I have 2 crosstabs with monthly premium and premium earned info. I need the output to be fixed and therefore all information needs to be in the same exact position each month. In the report, I show only month end date, and premium amounts, I need show all of the months (January - December) and the months that have not yet passed ( Oct 2013 to Dec 2013) with just show as all zeros in premiums I cannot figure a way to show future months like ( Oct 2013 to Dec 2013) when data are not existed in our current booked dates. How can I solve this problem?
Please advice in details since I am not tech savvy, still learning…
Your help is greatly appreciated

ANN
 
Use a spreadsheet or Access table of all dates to link to the existing data. Link New.Date field from this new date table to Existing.Date
field (left outer joing from new to existing field)
Use the date filter on the new.date i/o existing date
Then you will have all dates, with null in dates where there are no records.
The new spreadsheet or table can be one column, and takes just a few minutes to create and link.
The w/c grouping will still work
 
Find the solution by using add command using the following sql
select last_day (add_months(trunc(sysdate,'mm'),level-12)) as E63_BOOK_DATE

from dual connect by level <= 25
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top