# Thread: Summing for date range query

1. Registered User
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. Registered User
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. Registered User
Join Date
Mar 2002
Location
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. Registered User
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. Registered User
Join Date
Mar 2002
Location
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. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Mar 2002
Location
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. Registered User
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. Registered User
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. Registered User
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
•