#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    1
    Rep Power
    0

    Allocate money using dates


    OK so i have this table:

    CREATE TABLE #TEST
    (StartDate DateTime,
    EndDate DateTime,
    Dollars DECIMAL(9,4))

    INSERT #TEST
    SELECT '3/12/2012','6/14/2012', 550.50

    What I need to do is allocate that 550.50 across March/April/May/June as such:

    There are 95 days between those two dates so 550.50/95 = 5.7947 per day

    March: 20 * 5.7947 = 115.89

    April: 30 * 5.7947 = 173.84

    May: 31 * 5.7947 = 179.64

    June: 14 * 5.7947 = 81.13

    I do have a table that contains dates:

    ID Month Quarter FiscalYear CalendarYear StartDate EndDate
    1 September Q1 2011 2010 9/1/2010 9/30/2010
    2 October Q1 2011 2010 10/1/2010 10/31/2010
    3 November Q1 2011 2010 11/1/2010 11/30/2010

    That I thought maybe I could join on some how, but I'm not sure how to do it. Any help would be appreciated
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    when you say "allocate" where exactly is the allocation supposed to go?

    also, i think you'l be a lot closer to a solution with a calendar table that has one row per day, rather than one row per month
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo