Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Summing for date range query

    I have the following table:

    Code:
    ITEM	LOC	DMDUNIT	DMDGROUP DFULOC	STARTDATE	TOTFCST
    89410	B400	89410	NLV	 B400	10/01/06	1.42
    89410	B400	89410	NLV	 B400	11/01/06	1.42
    89410	B400	89410	NLV	 B400	12/01/06	1.42
    89410	B400	89410	NLV	 B400	01/01/07	1.42
    89410	B400	89410	NLV	 B400	02/01/07	1.42
    89410	B400	89410	NLV	 B400	03/01/07	1.41
    89410	B400	89410	NLV	 B400	04/01/07	1.41
    89410	B400	89410	NLV	 B400	05/01/07	1.40
    89410	B400	89410	NLV	 B400	06/01/07	1.38
    89410	B400	89410	NLV	 B400	07/01/07	1.37
    89410	B400	89410	NLV	 B400	08/01/07	1.35
    89410	B400	89410	NLV	 B400	09/01/07	1.34
    I need a query that will sum the TOTFCST column for dates that fall within a certain range. The range is the beginning of the month following current month, and the end date is current date + x days.

    Example: if today is 9-27-06, and the number of days = 150, the date range would be from 10-1-06 to 4-1-07. The summed TOTFCST would be 9.91 (1.42 + 1.42 + 1.42 + 1.42 + 1.42 + 1.41 + 1.41).

    The tricky part however is what if the number of days is 75 for example. The date range would be from 10-1-06 to 11-1-06 plus 1/2 of the value of 12-1-06 (1.42 + 1.42 + 1.42 * .5) = 3.55

    The number of days will always be a multiple of 15.

  2. #2
    Join Date
    Sep 2004
    Posts
    60
    Please elobarate "The tricky part however is what if the number of days is 75"

    I don't see any problem with 75 days.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    This might do the trick

    Code:
    create table x 
    (
    	item number, 
        startdate   date,
        totfcst number
    )
    
    insert into x
    select 4910, to_date('01/01/06','dd/mm/yy'), 1.2 from dual union all
    select 4910, to_date('01/02/06','dd/mm/yy'), 1.2 from dual union all
    select 4910, to_date('01/03/06','dd/mm/yy'), 1.2 from dual union all
    select 4910, to_date('01/04/06','dd/mm/yy'), 1.2 from dual union all
    select 4910, to_date('01/05/06','dd/mm/yy'), 3.0  from dual union all
    select 4910, to_date('01/06/06','dd/mm/yy'),4.0 from dual union all
    select 4910, to_date('01/07/06','dd/mm/yy'), 5.0 from dual
    
    select sum(totfcst*ratio)
    from
    (
    	select item, startdate, nextdate, totfcst,
    	case 
    	when nextdate>to_date('10/05/06','dd/mm/yy') then (to_date('10/05/06','dd/mm/yy')-startdate)/(nextdate-startdate)
    	when startdate<to_date('10/02/06','dd/mm/yy') then (nextdate-to_date('10/02/06','dd/mm/yy') )/(nextdate-startdate) 
    	else 1 
    	end ratio  
    	from
    	(
    		select item, startdate, LEAD(startdate, 1) OVER (ORDER BY startdate)-1 AS nextdate, totfcst
    		from x
    	)  
    	where startdate between to_date('10/02/06','dd/mm/yy') and to_date('10/05/06','dd/mm/yy')
    	or nextdate between to_date('10/02/06','dd/mm/yy') and to_date('10/05/06','dd/mm/yy')
    )
    I have missed out the case where the interval is less than the interval between 2 records but I'm sure you can figure that one out.

    Alan

  4. #4
    Join Date
    Sep 2005
    Posts
    220
    Alan, thanks for the response. I'm having a hard time following this one, but it doesn't seem to work. What I need is a query that I can feed the number of days in increments of 15, and get the sum of the TOTFCST column. If the number of days = 15, I need 1/2 of the TOTFCST for the following month. If it's 30 I need all of next month's TOTFCST. If it's 45 days, I need the next month + 1/2 of the month after that.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Just replace the two dates with the date interval you want to get the sum for, the query will do the rest i.e. replace 10/02/06 with the startdate you want and replace to_date('10/05/06','dd/mm/yy') with your startdate+<no of days>

    Alan

  6. #6
    Join Date
    Sep 2005
    Posts
    220
    Here's what I have. I have inserted these rows to get it to somewhat replicate the dates I'm working with:

    Code:
    INSERT INTO x
    SELECT 4910, TO_DATE('01/09/06','dd/mm/yy'), 1.2 FROM dual UNION ALL
    SELECT 4910, TO_DATE('01/10/06','dd/mm/yy'), 1.2 FROM dual UNION ALL
    SELECT 4910, TO_DATE('01/11/06','dd/mm/yy'), 1.2 FROM dual UNION ALL
    SELECT 4910, TO_DATE('01/12/06','dd/mm/yy'), 1.2 FROM dual UNION ALL
    SELECT 4910, TO_DATE('01/01/07','dd/mm/yy'), 3.0  FROM dual UNION ALL
    SELECT 4910, TO_DATE('01/02/07','dd/mm/yy'),4.0 FROM dual UNION ALL
    SELECT 4910, TO_DATE('01/03/07','dd/mm/yy'), 5.0 FROM dual
    and per your last reply, I have changed the dates as follows:

    Code:
    SELECT SUM(totfcst*ratio)
    FROM
    (
    	SELECT item, startdate, nextdate, totfcst,
    	CASE 
    	WHEN nextdate>TO_DATE(SYSDATE + 60,'dd/mm/yy') THEN (TO_DATE(SYSDATE + 60,'dd/mm/yy')-startdate)/(nextdate-startdate)
    	WHEN startdate<TO_DATE(SYSDATE,'dd/mm/yy') THEN (nextdate-TO_DATE(SYSDATE,'dd/mm/yy') )/(nextdate-startdate) 
    	ELSE 1 
    	END ratio  
    	FROM
    	(
    		SELECT item, startdate, LEAD(startdate, 1) OVER (ORDER BY startdate)-1 AS nextdate, totfcst
    		FROM x
    	)  
    	WHERE startdate BETWEEN TO_DATE(SYSDATE,'dd/mm/yy') AND TO_DATE(SYSDATE + 60,'dd/mm/yy')
    	OR nextdate BETWEEN TO_DATE(SYSDATE,'dd/mm/yy') AND TO_DATE(SYSDATE + 60,'dd/mm/yy')
    )
    The result I get is 2.71034482758621 when it should be 4.2 (qty from 12/1/06 of 1.2 + qty from 1/1/07 of 3.0). Since today is 11/8/06, the next two months would be 12/1/06 and 1/1/07, that's what I wanted when I set the nextdate = sysdate + 60.

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    ssmith001, I thought you already solved this on this other thread?. Your logic is somewhat confusing for me, you seem to think every month has 30 days.

    I took AlanP sample data and did this:

    Assuming:
    a) Months are even (every month has 30 days).
    b) Proper index on startdate
    Code:
    SQL>
    SQL> variable days number
    SQL> exec :days := 45
    
    PL/SQL procedure successfully completed.
    
    SQL> select t.*,
      2         ( select sum( case when startdate <= add_months( t.startdate, :days / 30 )
      3                            then totfcst
      4                            else totfcst / 2 end )
      5             from x
      6            where startdate between t.startdate
      7                                and add_months( t.startdate, ceil( :days / 30 ) ) ) qty
      8    from x t
      9   order by startdate
     10  /
    
          ITEM STARTDATE    TOTFCST        QTY
    ---------- --------- ---------- ----------
          4910 01-JAN-06        1.2          3
          4910 01-FEB-06        1.2          3
          4910 01-MAR-06        1.2        3.9
          4910 01-APR-06        1.2        6.2
          4910 01-MAY-06          3        9.5
          4910 01-JUN-06          4          9
          4910 01-JUL-06          5          5
    
    7 rows selected.
    
    SQL>

  8. #8
    Join Date
    Sep 2005
    Posts
    220
    You are correct. I did have another thread going but was not able to get it resolved, so I'm trying once again. You are also correct in my assumption that every month has 30 days. I don't think that is relative here though. I just need a way to return a single summed quantity.

    Perhaps you can let me know what you don't understand about my logic and I will do my best to try and explain it further.

  9. #9
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I'm afraid the query I gave you gives the correct answer in that it calculates the ratio from the actual days difference and doesnt assume 30 day months.

    If you run the query from 8/11/06 (dd/mm/yy as I live in the UK) to 60 days in the future you get 07/01/07 (dd/mm/yy) so if you run the inner query it shows the following
    Code:
    ITEM	STARTDATE	NEXTDATE	TOTFCST	RATIO
    4910	01/11/2006	30/11/2006	1.2	0.75
    4910	01/12/2006	31/12/2006	1.2	1
    4910	01/01/2007	31/01/2007	3	0.2
    which does give you 2.7. If you want to always start at the first of the month use the trunc function on the start of the date interval. Even then though you get 3.0 and not 4.2.

    Alan

  10. #10
    Join Date
    Sep 2005
    Posts
    220
    I really appreciate your input. The problem is that 4.2 is the number I need.

    When the user runs the query for 30,60, 90, 120, 180, ... days, the calculation should just sum the full month's totfcst qty. When they use 15, 45, 75, 105, 135, etc, all this means is that I want the 1/2 of the totfcst qty.

    75 days / 30 = 2.5, so I need 2 months of totfcst quantities + 1/2 of the next month. Likewise, if I use 105 days, 105/30 = 3.5 so I need 3 months + 1/2 of the 4th month.

  11. #11
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Ok, I understand now, you want a whole qty.

    Using the same technique I used above.
    Code:
    SQL> variable days number
    SQL> variable thedate varchar2(20)
    SQL>
    SQL> exec :days := 60; :thedate := '11-08-2006';
    
    PL/SQL procedure successfully completed.
    
    SQL> select x.*,
      2         trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' ) p_startdate,
      3         trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' ) + :days p_enddate
      4    from x
      5   where startdate between trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' )
      6                       and trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' ) + :days
      7  /
    
          ITEM STARTDATE    TOTFCST P_STARTDA P_ENDDATE
    ---------- --------- ---------- --------- ---------
          4910 01-DEC-06        1.2 01-DEC-06 30-JAN-07
          4910 01-JAN-07          3 01-DEC-06 30-JAN-07
    
    SQL> select sum( case when startdate < add_months( p_startdate, :days / 30 )
      2                   then totfcst
      3                   else totfcst / 2
      4               end ) qty
      5    from (
      6  select x.*,
      7         trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' ) p_startdate,
      8         trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' ) + :days p_enddate
      9    from x
     10   where startdate between trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' )
     11                       and trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' ) + :days
     12         )
     13  /
    
           QTY
    ----------
           4.2
    
    SQL>
    SQL> exec :days := 75
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> select x.*,
      2         trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' ) p_startdate,
      3         trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' ) + :days p_enddate
      4    from x
      5   where startdate between trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' )
      6                       and trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' ) + :days
      7  /
    
          ITEM STARTDATE    TOTFCST P_STARTDA P_ENDDATE
    ---------- --------- ---------- --------- ---------
          4910 01-DEC-06        1.2 01-DEC-06 14-FEB-07
          4910 01-JAN-07          3 01-DEC-06 14-FEB-07
          4910 01-FEB-07          4 01-DEC-06 14-FEB-07
    
    SQL> select sum( case when startdate < add_months( p_startdate, :days / 30 )
      2                   then totfcst
      3                   else totfcst / 2
      4               end ) qty
      5    from (
      6  select x.*,
      7         trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' ) p_startdate,
      8         trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' ) + :days p_enddate
      9    from x
     10   where startdate between trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' )
     11                       and trunc( add_months( to_date( :thedate, 'mm-dd-yyyy' ), 1 ), 'MM' ) + :days
     12         )
     13  /
    
           QTY
    ----------
           6.2
    
    SQL>
    This should get you going.

  12. #12
    Join Date
    Sep 2005
    Posts
    220
    BINGO! Thank you sooooooo much!

Posting Permissions

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