BI-Week Start and End - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

BI-Week Start and End

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-11 : 18:41:54

I have a billing system that bills every two weeks.

If my first bill was sent out on
01/01/2005 then
01/15/2005 then
01/29/2005 etc.

basically adding 14 days on to each billing cycle.

I need to find out if someone is added on 05/20/2007 what the next billing date will based off the '01/01/2005' start date, that this company falls into.

I am thinking of using something similiar to


DECLARE @d SMALLDATETIME ,@d2 SMALLDATETIME  
SET @d = '4/01/2001' --PlanStartDate
set @D2 = '4/01/2008'-- EmployerJoinDate
select dateadd(ww,(datediff(dd,@D, @D2)/ 14) * 2 + 2,@d)

This is not working correctly, I need to factor in leap years because In theory If my startdate is a Monday, my new date should also be a Monday since I am adding two weeks each time, but the datediff I think just uses the standard amount of days for the months w/o factoring in leap years. Any Sugestions?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-11 : 19:10:32

And what is the expected result ?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-11 : 19:27:02

This seems to do the job.


-- Generate first billing dates for all dates
-- from 2005-01-01 through 2010-12-31
select
	Date = convert(varchar(10),a.Date,121),
	[First Bill Date] =
	convert(varchar(10),dateadd(dd,(datediff(dd,'20050101',a.DATE)/14)*14,'20050115'),121)
from
	-- Date Table Function F_TABLE_DATE available on this link
	-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
	F_TABLE_DATE('20050101', '20101231') a
order by
	a.Date


Results:

Date       First Bill Date 
---------- --------------- 
2005-01-01 2005-01-15
2005-01-02 2005-01-15
2005-01-03 2005-01-15
2005-01-04 2005-01-15
2005-01-05 2005-01-15
2005-01-06 2005-01-15
2005-01-07 2005-01-15
2005-01-08 2005-01-15
2005-01-09 2005-01-15
2005-01-10 2005-01-15
2005-01-11 2005-01-15
2005-01-12 2005-01-15
2005-01-13 2005-01-15
2005-01-14 2005-01-15
2005-01-15 2005-01-29
2005-01-16 2005-01-29
2005-01-17 2005-01-29
2005-01-18 2005-01-29
2005-01-19 2005-01-29
2005-01-20 2005-01-29
2005-01-21 2005-01-29
2005-01-22 2005-01-29
2005-01-23 2005-01-29
2005-01-24 2005-01-29
2005-01-25 2005-01-29
2005-01-26 2005-01-29
2005-01-27 2005-01-29
2005-01-28 2005-01-29
2005-01-29 2005-02-12
2005-01-30 2005-02-12
...
2010-12-24 2010-12-25
2010-12-25 2011-01-08
2010-12-26 2011-01-08
2010-12-27 2011-01-08
2010-12-28 2011-01-08
2010-12-29 2011-01-08
2010-12-30 2011-01-08
2010-12-31 2011-01-08

(2191 row(s) affected)

CODO ERGO SUM

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-12 : 14:14:50

MVJ's code worked. For some reason the week conversion was causing the offset, but by keeping everything in days it works correct.


DECLARE @d SMALLDATETIME ,@d2 SMALLDATETIME  
SET @d = '4/01/2001' --PlanStartDate
set @D2 = '4/01/2008'-- EmployerJoinDate
select dateadd(dd,(datediff(dd,@D, @D2)/ 14) * 14 + 14,@d)

Thanks for the help, also that function MVJ posted is a really handy function, I'm sure I will find uses for it in other areas.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources