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.
| Author |
Topic |
|
drpkrupa
Yak Posting Veteran
74 Posts |
 Posted - 2008-01-07 : 12:33:12
|
| I have four column 1) Bill start Date2) bill frequency (month =1 ,querterly = 3 and annually = 12)3) today (getdate())4) Customer noI want a find out How many times customer should get invoiced based on bill start date and frequecy?Example: Bill frequency =1 and bill start date in 10/01/2007 then customer get invoiced 4 times (10,11,12 and 01/08)If freuency = 3 and bill start date in 10/01/07 then invoiced 2 times (10/01 and 01/01/08)How can i achive this results |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-07 : 12:44:41
|
| Try this:-SELECT CustomerNo, (DATEDIFF(d,BillStartDate,GETDATE())/(CASE bill frequency WHEN 1 THEN 30 WHEN 3 THEN 90 WHEN 12 THEN 360 END)) + 1 AS 'Invoice Count'FROM Table |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-08 : 02:16:58
|
So a quarter is always 90 days? And a year always 360 days and a month always 30 days?SELECT CustomerNo,CEILING(1.0 * DATEDIFF(MONTH, BillStartDate, CURRENT_TIMESTAMP) / BillFrequency)FROM Table1
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
Query help