# of billing cycles

[ML]

hello

i need to write an sql which calculates # of billing cycles for an
order for which ive order start and end date. the first invoice will
be cut on the order start date. Also if i apply a coupon for lets say
one month (30 days) to that order i need to calculate how many billing
cycles that coupon will apply to.
ive start and end date of coupon ina diff table for that order

for eg: if an order was started on 1/15 and ended on 4/14 i have 3
billing cycles (the invoice was suppose to cut on. 1/15 , 2/15 and
3/15)
lets say on 3/15 if a users applied one month coupon which means he
didnt have to pay for billing cycle 3/15.

can someone help me with this logic

thank u
ml

[Erland Sommarskog]

ML (mi*********@ya hoo.com) writes:

i need to write an sql which calculates # of billing cycles for an
order for which ive order start and end date. the first invoice will
be cut on the order start date. Also if i apply a coupon for lets say
one month (30 days) to that order i need to calculate how many billing
cycles that coupon will apply to.
ive start and end date of coupon ina diff table for that order

for eg: if an order was started on 1/15 and ended on 4/14 i have 3
billing cycles (the invoice was suppose to cut on. 1/15 , 2/15 and
3/15)
lets say on 3/15 if a users applied one month coupon which means he
didnt have to pay for billing cycle 3/15.

can someone help me with this logic

A general advice for this sort of questions is that you include:

o CREATE TABLE statements for the involved tables (simplified to
encompass the problem).
o INSERT statements with sample data.
o The desired output from that sample.

There is a little too much depending on how data is stored, to really
encourage me to just make up something, as may not be suitable at all.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp