How to list a calendar of billing dates.

[Former Member]

Hi. I have a list of communities who are billed on a monthly, quarterly, or semi-annual basis. I'd like to use the bill-cycle code to return the billing dates for "current year" for the first Monday of every month for monthly, every quarter for quarterly, every six months for semi-annual.

For instance, 2010 would look like this:

Monthly; Jan 4,

Feb 1,

Mar 1,

etc.

Quarterly; Jan 4,

Apr 5,

Jul 5,

etc.

Semi-annual; Jan 4,

Jul 5.

Does anyone have any ideas? Or how to come as close as possible to this request?

Thanks!!

Robin

[Former Member]

I posted my final solution for a working array of calendar dates, adjusted for holidays and weekends. Thanks for all the helpful comments.

[Former Member]

It doesn't look as if all payments are due on the 1st of the month...

How are you determining the day the payment is due?

How would you determine when the 1st (or next) payment due is?

[Former Member]

Thanks for your reply. The payment is due the first working day of the month, or the first Monday. Adjusted for holidays if possible, which I think is possible.

[Former Member]

The 1st Monday of the period is possible, but if you want to adjust for holidays, you're going to need to have a special table in your database that lists them out along with their dates.

[Former Member]

Okay, that's not a big problem. How can I get the 1st day or 1st Monday?

[Former Member]

I didn't define my thinking enough. What I need is the 1st day on each period (month, quarter, semi-annual) that does not fall on a Saturday or Sunday, for year(currentdate). If I could adjust for holidays from custom list or file, that would be even better, but not a report killer.

[Former Member]

Well I was simply going to recommend using a SQL Command to build out a temp table that builds and deletes itself each time the report runs. If you are going to add a file for holidays, you may as well build out a full calendar table or file.

Here is what it would look like... (small sample)

Date			DayOfWeek	MonthlyDueDate	QuarterlyDueDate	SemiDueDate
2010-03-29 00:00:00.000		2	NULL		NULL			NULL
2010-03-30 00:00:00.000		3	NULL		NULL			NULL
2010-03-31 00:00:00.000		4	NULL		NULL			NULL
2010-04-01 00:00:00.000		5	MonthCode	QuarterCode		SemiCode
2010-04-02 00:00:00.000		6	NULL		NULL			NULL
2010-04-03 00:00:00.000		7	NULL		NULL			NULL
2010-04-04 00:00:00.000		1	NULL		NULL			NULL
2010-04-05 00:00:00.000		2	NULL		NULL			NULL
2010-04-06 00:00:00.000		3	NULL		NULL			NULL
2010-04-07 00:00:00.000		4	NULL		NULL			NULL
2010-04-08 00:00:00.000		5	NULL		NULL			NULL
2010-04-09 00:00:00.000		6	NULL		NULL			NULL
2010-04-10 00:00:00.000		7	NULL		NULL			NULL

(instead of MonthCode, QuarterCode & SemiCode you would use the correct bill-cycle code)

... have a meeting to run to... will add more later

Jason

[Former Member]

Oh my. I'd hoped for some variation of the dateserial or interval functions for year(currentdate), with a FirstWeekDay or DayofWeek comparision thrown in somewhere to exclude weekends, stragetically placed within a bill-cycle code if statement....

[Former Member]

There are a variety of ways that you can accomplish this. The issue is that CR will not create records out of thin air... ie: It will not create an entirely artificial record based on a formula.

Having a calendar or dates table gives a basis for creating the record.

Here is a proof of concept I did some time ago using the same concept... [Amortization Schedule.rpt|http://docs.google.com/leaf?id=0B_0KY03Gs2knODY5ZmVjNDMtMzQxYi00MTRlLWExOGItMTc1MTQ4MDYzODI5&hl=en]

It's not dead on to what you are trying to do for a few reasons...

1) It assumes that every payment is due exactly 1 month apart regardless of weekends or holidays.

2) 100% of the data is calculated based on parameter values. Nothing is being pulled from any db tables.

So why show it? Hopefully it will give show you how to project future payments (thinks that don't exist in the database yet) by using nothing more than a list of dates in a temp table.

Jason

[Former Member]

Thanks for the example report, I get the gist of your intent, and I may need to do something like that.

I'm also wondering if one could create a date array of values for the date(year(currentdate), months 1 to 12, 01) then for bill-cycle month, display array datevar 1 to 12, for quarterly display array datevar in [1,4,7,10] and semi-annual in [1,7}. Set the field to can grow, it should display all the values, shouldn't it?

And I just bet some smart person can figure out or has figured out how to adjust the first day to weekdays only, but I could use just this list of dates for each community by bill-cycle.

[Former Member]

Alright... Here's what I have for you so far...

IF object_id('tempdb..#Temp') IS NOT NULL
BEGIN
   DROP TABLE #Temp
END
    CREATE TABLE #Temp (Date DateTime, DayOfWeek INT, DueDate INT)
DECLARE @Date DateTime, @PaymentType VarChar(20)
SET @Date = DateAdd(yy, DateDiff(yy,0, GetDate()), 0)
WHILE @Date < DateAdd(yy, DateDiff(yy,0, GetDate()) +1, 0)
BEGIN
	INSERT INTO #Temp (Date, DayOfWeek) Values (@Date, DatePart(dw,@Date))
	SET @Date = DateAdd(dd,1,@Date)
END
SET @PaymentType = 'Monthly'
UPDATE #Temp SET DueDate = CASE 
	WHEN @PaymentType = 'Monthly' THEN 
		CASE WHEN DatePart(dd, Date) = 1 AND DayOfWeek NOT IN (1,7) THEN 1
		     WHEN DatePart(dd, Date) = 2 AND DayOfWeek =2 THEN 1
		     WHEN DatePart(dd, Date) = 3 AND DayOfWeek =2 THEN 1 END
	WHEN @PaymentType = 'Quarterly' THEN 
		CASE WHEN DatePart(mm, Date) IN (1,4,7,10) AND DatePart(dd, Date) = 1 AND DayOfWeek NOT IN (1,7) THEN 1
		     WHEN DatePart(mm, Date) IN (1,4,7,10) AND DatePart(dd, Date) = 2 AND DayOfWeek =2 THEN 1
		     WHEN DatePart(mm, Date) IN (1,4,7,10) AND DatePart(dd, Date) = 3 AND DayOfWeek =2 THEN 1 END
	WHEN @PaymentType = 'Semi' THEN 
		CASE WHEN DatePart(mm, Date) IN (1,7) AND DatePart(dd, Date) = 1 AND DayOfWeek NOT IN (1,7) THEN 1
		     WHEN DatePart(mm, Date) IN (1,7) AND DatePart(dd, Date) = 2 AND DayOfWeek =2 THEN 1
		     WHEN DatePart(mm, Date) IN (1,7) AND DatePart(dd, Date) = 3 AND DayOfWeek =2 THEN 1 END
	END
SELECT * FROM #Temp WHERE DueDate = 1
DROP TABLE #Temp

[Former Member]

Took me a while to work out the kinks, but your message started my on track. I used an array solution to list the year of dates out. Here is the final working product.

dateVar Array liendates := [Date(year(currentdate),1,1),Date(year(currentdate),2,1),Date(year(currentdate),3,1),Date(year(currentdate),4,1),

Date(year(currentdate),5,1),Date(year(currentdate),6,1),Date(year(currentdate),7,1),Date(year(currentdate),8,1),Date(year(currentdate),9,1),

Date(year(currentdate),10,1),Date(year(currentdate),11,1),Date(year(currentdate),12,1)];

if {rm_bldg.baserentcc} = "ma" then

(

for eachmonth := 1 to 12

do

(

if liendates[eachmonth] in [date(year(currentdate),1,1),Date(year(currentdate),7,4)] then adjustdate := (liendates[eachmonth] + 1) else

adjustdate := liendates[eachmonth];

if dayofweek(adjustdate) = 1 then adjustdate := (adjustdate + 1);

if dayofweek(adjustdate) = 7 then adjustdate := (adjustdate + 2);

if ShowDates="" then

ShowDates := cstr(adjustdate)

else

ShowDates := ShowDates & chr(13) & cstr(adjustdate);

);

ShowDates

)

else

(

adjustdate := date(0,0,0);

ShowDates:=cstr(adjustdate);

);

ShowDates;

Thanks so much for you help!

Robin