Using SQL to prorate based on DateTime values

The context of this problem is a billing system for a warehouse that stores widgets. The storage fee is based on the size of the widget (e.g $x * height * length * width). This part is easy. I use the following SQL to calculate Amount Due and aggregate by user id.

Select UserID, Sum((Height * Length * Width * 5) as AmtDue From Widget
Where StatusID=2
Group By UserID

5 is an arbitrary value for the moment. Eventually I'll pass it as a parameter or get it from a table in the database. Assume it's always 5 for the moment.

Here's the part I'm struggling with. Fees are calculated at the end of the month and are prorated according to the number of days in that month the widget was stored in the warehouse (e.g. if the widget sits in the warehouse for 15 days out of a 30 day month the fee due is 50% of the amount). In my Widget table I have a column for DateReceived and DateShipped. DateReceived always has a datetime value. DateShipped is sometimes null (e.g. if the widget is still in storage). I have parameters available for CycleStart and CycleEnd which represent the start and end of the relevant billing month. I'm trying to modify my SQL query to include in the select expression a prorate factor that is equal to (# of days in storage / # of days in month). In other words AmtDue becomes (height * length * width * 5 * prorate factor) How do I do this?

Here's an example to help illustrat开发者_运维知识库e how the prorate factor is calculated.

If @CycleStart = 7/1/11 and @CycleEnd = 7/31/11 mm/dd/yy

DateReceived  |  DateShipped  |  Prorate Factor

6/5/11              Null           100%
6/5/11              7/15/11      48.38%
7/30/11             8/5/11        6.45%
7/15/11             7/25/11      35.48%        

I'm really struggling with the conditional nature of this and the fact that we're dealing with datetime values.

---

Here you go. This

drop table #work
go
create table #work
(
  id           int      not null identity(1,1) primary key clustered ,
  DateReceived datetime not null ,
  DateShipped  datetime     null ,
)
go

insert #work ( DateReceived , DateShipped ) values ( '06/05/2011' , null         )
insert #work ( DateReceived , DateShipped ) values ( '06/05/2011' , '07/15/2011' )
insert #work ( DateReceived , DateShipped ) values ( '07/30/2011' , '08/05/2011' )
insert #work ( DateReceived , DateShipped ) values ( '07/15/2011' , '07/25/2011' )
go

declare
  @CycleStart   datetime ,
  @CycleEnd     datetime ,
  @DaysInPeriod money

set @CycleStart = '07/01/2011'
set @CycleEnd   = '07/31/2011'
set @DaysInPeriod = datediff(day,@CycleStart,dateadd(day,1,@CycleEnd))

select * ,
       DaysInStorage = datediff( day ,
                         case when DateReceived < @CycleStart
                           then @CycleStart
                           else DateReceived
                         end ,
                         case when DateShipped > @CycleEnd
                           then dateadd(day,1,@CycleEnd)
                           else dateadd(day,1,coalesce(DateShipped,@CycleEnd))
                         end
                         ) ,
       DaysInPeriod  = @DaysInPeriod ,
       ProrateFactor = datediff( day ,
                         case when DateReceived < @CycleStart
                           then @CycleStart
                           else DateReceived
                         end ,
                         case when DateShipped > @CycleEnd
                           then dateadd(day,1,@CycleEnd)
                           else dateadd(day,1,coalesce(DateShipped,@CycleEnd))
                         end
                         ) / @DaysInPeriod
from #work

Yields the following

id DateReceived            DateShipped             DaysInStorage DaysInPeriod ProrateFactor
-- ----------------------- ----------------------- ------------- ------------ -------------
 1 2011-06-05 00:00:00.000 NULL                               30        31.00        1.00
 2 2011-06-05 00:00:00.000 2011-07-15 00:00:00.000            14        31.00        0.4838
 3 2011-07-30 00:00:00.000 2011-08-05 00:00:00.000             1        31.00        0.0645
 4 2011-07-15 00:00:00.000 2011-07-25 00:00:00.000            10        31.00        0.3548

---

Try this:

SELECT CAST(DATEPART( dd,DateShipped) as decimal)/
CAST(DATEPART(dd,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) as decimal) as ProrateFactor

You can multiply by 100 if you want to get the percentage, but I assume that you want the decimal for calculation reasons.

You can run this one to see today's prorate factor:

SELECT CAST(DATEPART( dd,GETDATE()) as decimal)/
CAST(DATEPART(dd,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) as decimal) 

To roll it in with your current calculation you could do this (warning - it's hideous):

Select UserID, Sum((Height * Length * Width * 5 * 
               SELECT CAST(DATEPART( dd,DateShipped) as decimal)/
               CAST(DATEPART(dd,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) as decimal) ) as AmtDue From Widget
Where StatusID=2
Group By UserID