Professional Web Applications Themes

Calculation across date periods - Microsoft SQL / MS SQL Server

Do you want to be all inclusive? Meaning, do you want to include prices that may be half in and half out of your range? For example: your toDate on the last row is beyond your requested range, but the fromDate is within the range. If you don't mind including all prices that occured during your range, then you would use something like this: Select sum(price) as 'Summed Price' from priceTBL where toDate BETWEEN '08/10/2003' and '09/25/2003' or fromDate BETWEEN '08/10/2003' and '09/25/2003' This will include all prices that occured during your period, but does not guarantee that these price ...

  1. #1

    Default Re: Calculation across date periods

    Do you want to be all inclusive? Meaning, do you want to include
    prices that may be half in and half out of your range? For example:
    your toDate on the last row is beyond your requested range, but the
    fromDate is within the range. If you don't mind including all prices
    that occured during your range, then you would use something like
    this:

    Select sum(price) as 'Summed Price' from priceTBL where toDate BETWEEN
    '08/10/2003' and '09/25/2003' or fromDate BETWEEN '08/10/2003' and
    '09/25/2003'

    This will include all prices that occured during your period, but does
    not guarantee that these price periods did not extend beyond your
    designated range.


    Random



    On Sun, 17 Aug 2003 15:22:31 +0100, "AndrewM" <net>
    wrote:
     

    Random Guest

  2. #2

    Default Calculation across date periods

    Hello,

    I need to calculate a total based on a date period. My input values are 10
    August to 25 September. As you can see from this example the date period
    goes across 4 price periods.

    Your help will be much appreciated.

    Thanks,
    Andrew.

    CREATE TABLE priceTBL
    (fromDate [datetime] NULL,
    toDate [datetime] NULL,
    price [money] NULL)
    GO

    INSERT INTO priceTBL VALUES('08/01/2003', '08/15/2003', 15.30)
    INSERT INTO priceTBL VALUES('08/15/2003', '09/01/2003', 20.40)
    INSERT INTO priceTBL VALUES('09/01/2003', '09/15/2003', 25.50)
    INSERT INTO priceTBL VALUES('09/15/2003', '10/01/2003', 30.60)

    GO


    AndrewM Guest

  3. #3

    Default Re: Calculation across date periods

    Ahh. I think I see now. Are you asking for the sum of prices for all
    days in your time span? If you were only concerned with 8/1 - 8/15
    would you expect an answer like 214.20?




    On Sun, 17 Aug 2003 16:07:03 +0100, "AndrewM" <net>
    wrote:
     [/ref]
    >10 
    >>[/ref]
    >[/ref]

    Random Guest

  4. #4

    Default Re: Calculation across date periods

    If my earlier question was on the right track, then this should work
    for you, though you may need to convert the dates.... Can't test it
    right now because my server is importing.

    Random




    Select Sum(Case when fromDate > = '08/10/2003' and toDate <=
    '09/25/2003' then (toDate - fromDate)*price
    when fromDate <= '08/10/2003' and toDate <= '09/25/2003' then (toDate
    - '08/10/2003') * price
    when fromDate <= '08/10/2003' and toDate >= '09/25/2003' then
    ('09/25/2003' - '08/10/2003) * price
    when fromDate >= '08/10/2003' and toDate >= '09/25/2003' then
    ('09/25/2003' - fromDate)* price else 0 end) as 'Total Price'
    From priceTBL where toDate BETWEEN
    '08/10/2003' and '09/25/2003' or fromDate BETWEEN '08/10/2003' and
    '09/25/2003'


    On Sun, 17 Aug 2003 16:07:03 +0100, "AndrewM" <net>
    wrote:
     [/ref]
    >10 
    >>[/ref]
    >[/ref]

    Random Guest

  5. #5

    Default Re: Calculation across date periods

    "AndrewM" <net> wrote in message news:phx.gbl... 

    CREATE FUNCTION SumPricePeriods
    (start_date DATETIME,
    end_date DATETIME)
    RETURNS TABLE
    AS
    RETURN(
    SELECT start_date AS start_date,
    end_date As end_date,
    SUM(DATEDIFF(DAY,
    CASE WHEN start_date > fromDate
    THEN start_date
    ELSE fromDate
    END,
    CASE WHEN end_date < toDate
    THEN end_date
    ELSE toDate
    END) * price) AS total
    FROM priceTBL
    WHERE toDate >= start_date AND
    fromDate < end_date
    )

    SELECT *
    FROM SumPricePeriods('20030810', '20030925')

    start_date end_date total
    2003-08-10 00:00:00.000 2003-09-25 00:00:00.000 1086.3000

    Regards,
    jag


    John Guest

  6. #6

    Default Re: Calculation across date periods

    Hello Random,

    Thank you very much for your answer, but I see that I wasn't clear enough in
    the example. Firstly the dates are from 12 noon to 12 noon and this is why
    the dates overlap eg. 08/01/2003 to 08/15/2003 then 08/15/2003 to
    09/01/2003. Secondly, the price is per day not per period.

    Third, its okay if the date period requested extends beyond the date periods
    in the table (I think this is what you were asking).

    Thanks,
    Andrew.


    "Random" <Randomnwhere> wrote in message
    news:com... [/ref]
    10 
    >[/ref]


    AndrewM Guest

  7. #7

    Default Re: Calculation across date periods

    "AndrewM" <net> wrote in message news:phx.gbl... 

    Functions were introduced in SQL Server 2000 and I suppose you
    are running an earlier version? You can use a stored procedure
    instead.

    CREATE PROCEDURE SumPricePeriods
    (start_date DATETIME,
    end_date DATETIME)
    AS
    SELECT start_date AS start_date,
    end_date As end_date,
    SUM(DATEDIFF(DAY,
    CASE WHEN start_date > fromDate
    THEN start_date
    ELSE fromDate
    END,
    CASE WHEN end_date < toDate
    THEN end_date
    ELSE toDate
    END) * price) AS total
    FROM priceTBL
    WHERE toDate >= start_date AND
    fromDate < end_date

    EXEC SumPricePeriods '20030810', '20030925'

    start_date end_date total
    2003-08-10 00:00:00.000 2003-09-25 00:00:00.000 1086.3000

    Regards,
    jag
     
    > news:phx.gbl... [/ref]
    > 10 
    > >
    > > CREATE FUNCTION SumPricePeriods
    > > (start_date DATETIME,
    > > end_date DATETIME)
    > > RETURNS TABLE
    > > AS
    > > RETURN(
    > > SELECT start_date AS start_date,
    > > end_date As end_date,
    > > SUM(DATEDIFF(DAY,
    > > CASE WHEN start_date >[/ref]
    > fromDate 
    >
    >[/ref]


    John Guest

  8. #8

    Default Re: Calculation across date periods

    The CREATE FUNCTION statement needs to be in a separate batch. Try
    inserting a 'GO' immediately before this line:
     [/ref]

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "AndrewM" <net> wrote in message
    news:phx.gbl... 
    it to 
    error. 
    > news:phx.gbl... [/ref][/ref]
    values are [/ref][/ref]
    period 
    > >
    > > CREATE FUNCTION SumPricePeriods
    > > (start_date DATETIME,
    > > end_date DATETIME)
    > > RETURNS TABLE
    > > AS
    > > RETURN(
    > > SELECT start_date AS start_date,
    > > end_date As end_date,
    > > SUM(DATEDIFF(DAY,
    > > CASE WHEN start_date >[/ref]
    > fromDate [/ref]
    start_date [/ref]
    fromDate [/ref]
    toDate [/ref]
    end_date 
    >
    >[/ref]


    Dan Guest

  9. #9

    Default Re: Calculation across date periods

    Hello Jag,

    The function looks great, thank you very much, but I can seem to get it to
    work. I pasted into Query yzer but it produces the following error.

    Server: Msg 156, Level 15, State 1, Procedure SumPricePeriods, Line 23
    Incorrect syntax near the keyword 'SELECT'.

    Can you help ?

    Thanks,
    Andrew.

    "John Gilson" <org> wrote in message
    news:aOM%a.8044$nyc.rr.com... 
    news:phx.gbl... [/ref]
    10 
    >
    > CREATE FUNCTION SumPricePeriods
    > (start_date DATETIME,
    > end_date DATETIME)
    > RETURNS TABLE
    > AS
    > RETURN(
    > SELECT start_date AS start_date,
    > end_date As end_date,
    > SUM(DATEDIFF(DAY,
    > CASE WHEN start_date >[/ref]
    fromDate 


    AndrewM Guest

  10. #10

    Default Re: Calculation across date periods

    Thanks,

    All working now. I'll remember that one for next time.

    Andrew.

    "Steve Kass" <edu> wrote in message
    news:phx.gbl... [/ref]
    to 
    > >news:phx.gbl...
    > >
    > > 
    > >10
    > >
    > > [/ref][/ref]
    period 
    > >fromDate
    > >
    > > [/ref][/ref]
    toDate 
    > >
    > >
    > >
    > >[/ref]
    >[/ref]


    AndrewM Guest

Similar Threads

  1. Date calculation in ActionScript
    By gregd in forum Coldfusion Flash Integration
    Replies: 2
    Last Post: February 25th, 01:25 AM
  2. Question about the date calculation
    By Herbert in forum Linux / Unix Administration
    Replies: 7
    Last Post: December 23rd, 08:59 PM
  3. Date calculation
    By pugs in forum Macromedia ColdFusion
    Replies: 1
    Last Post: June 1st, 12:36 PM
  4. Date and time calculation
    By Werner in forum PERL Beginners
    Replies: 5
    Last Post: June 17th, 06:57 PM
  5. Date calculation
    By Jan in forum PERL Beginners
    Replies: 9
    Last Post: April 22nd, 03:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139