Prorate a monthly charge to daily for a partial mo

问题:

I'm using SQL Server and SSRS. I have a table with a monthly charge for a service. I need to be able to calculate what the charge is for a partial month. I need the monthly charge prorated to a daily charge, but I can't just divide by 30. I need it depending on what month I am looking at. 31 for months with 31 days, 28 for February, etc.

Can I do this in SQL?

回答1:

You can get the total number of days for current month by:

 SELECT DAY(EOMONTH(DateColumn))
 FROM TABLE

And in your case, I am guessing the query should look like:

 SELECT TotalCharges * 1.0/DAY(EOMONTH(DateColumn))
 FROM TABLE