select sum(column_A)
from temp_t
where date >= tdb_datestr_to_epoch('21/11/1995 10:00:00','dd/mm/yyyy hh24:mi:ss')
and date < tdb_datestr_to_epoch('22/11/1995 10:00:00','dd/mm/yyyy hh24:mi:ss')
the problems is i have to do the above for 7 days repeatedly so im trying to automated this
the ones in orange are DD from day filed and they should be consecutive
i tried this
DECLARE
i number(1);
BEGIN
i:=7;
while (i > 0 )
LOOP
DBMS_OUTPUT.put_line (TO_CHAR (SYSDATE-i,'DD/MM/YYYY'));
i:= i -1;
END LOOP;
END;
so now how to input these in to the the above sql query i.e in > condition it should if its 12/01/2013 then in < condition the date should be 13/01/2013[/CODE][/COLOR]
Why do you need a PL/SQL for this? You can provide date range in your SQL itself.
E.g:
select sum(column_A) from temp_t where date >= SYSDATE - 7 and date < SYSDATE ;
I guess tdb_datestr_to_epoch is a user-defined function, never seen it before! You can use to_char to format the SYSDATE as per the function argument.
---------- Post updated at 15:47 ---------- Previous update was at 15:43 ----------
Something like:
select sum(column_A) from temp_t
where
date >= tdb_datestr_to_epoch(to_char(SYSDATE - 7, 'dd/mm/yyyy hh24:mi:ss')) and
date < tdb_datestr_to_epoch(to_char(SYSDATE, 'dd/mm/yyyy hh24:mi:ss')) ;
the problem in the above is im taking sum only for specific hours of day and hence for example for 10AM to 10AM and its fixed for all days also
tdb_datestr_to_epoch is a function which will convert the date to epoch form
i also thought of an idea :
1.to take last seven days dates from current days in to one file and fixed hours(like 10:00 ) as said above in to another file the join them with space in between
but problems is how do pass the values from the file consecutively in to sql
And let's say you want to fetch the sums of COLUMN_A for dates starting from 15/01/2013 to 21/01/2013.
Now, if you ran your current query for the date: 15/01/2013, then your filter conditions would pick up the two rows in red color - the ones with IDs 3 and 4. And the sum of COLUMN_A would be (101 + 102 =) 203.
If you ran your current query for the date: 16/01/2013, then your filter conditions would pick up the three rows in green color - the ones with IDs 5, 6 and 7. And the sum of COLUMN_A would be (110 + 220 + 330 =) 660.
Similarly for date: 18/01/2013, the sum would be (111 + 122 =) 233.
For date: 20/01/2013, the sum would be (444 + 555=) 999.
For date: 21/01/2013, the sum would be (123 + 456=) 579.
The following query achieves the same result mentioned above:
SQL>
SQL> --
SQL> -- Given a "start date" of "15-JAN-2013", the following query fetches the sum
SQL> -- of column_a for all dates - from : 15-JAN-2013 10:00:00 AM
SQL> -- to : 21-JAN-2013 10:00:00 AM
SQL> --
SQL>
SQL> with dt (start_date) as (
2 select TO_DATE ('15/01/2013', 'DD/MM/YYYY') from dual
3 ),
4 dt_range (from_date, to_date) as (
5 select (start_date + level - 1) + 10/24 AS from_date,
6 (start_date + level) + 10/24 AS to_date
7 from dt
8 connect by level <= 7
9 )
10 select
11 dr.from_date,
12 SUM (tt.column_a) AS sum
13 from temp_t tt, dt_range dr
14 where tt.dt >= dr.from_date
15 and tt.dt < dr.to_date
16 group by dr.from_date
17 order by dr.from_date
18 /
FROM_DATE SUM
--------- ----------
15-JAN-13 203
16-JAN-13 660
18-JAN-13 233
20-JAN-13 999
21-JAN-13 579
5 rows selected.
SQL>
SQL>
Notice that the output is "sparse" i.e. it does not contain every date in the range: 15/01/2013 to 21/01/2013.
The date 17/01/2013 is missing because there are no records in TEMP_T that have a DT value that lies between "17/01/2013 10:00:00 AM" and "18/01/2013 09:59:59" both inclusive.
The date 19/01/2013 is missing because there are no records in TEMP_T that have a DT value that lies between "19/01/2013 10:00:00 AM" and "20/01/2013 09:59:59" both inclusive.
If you still wanted to see those two dates with the sum value of 0 i.e. if you wanted the output to be "dense", then an outer join could be used like so -
SQL>
SQL> -- Densely populated data
SQL> -- Return **every** date in the range, whether or not
SQL> -- there are any records for that date. If there are
SQL> -- no records for a date, then return 0 as the sum.
SQL>
SQL> with dt (start_date) as (
2 select TO_DATE ('15/01/2013', 'DD/MM/YYYY') from dual
3 ),
4 dt_range (from_date, to_date) as (
5 select (start_date + level - 1) + 10/24 AS from_date,
6 (start_date + level) + 10/24 AS to_date
7 from dt
8 connect by level <= 7
9 )
10 select
11 dr.from_date,
12 NVL (SUM (tt.column_a), 0) AS sum
13 from dt_range dr
14 left outer join temp_t tt
15 on (
16 tt.dt >= dr.from_date
17 and tt.dt < dr.to_date
18 )
19 group by dr.from_date
20 order by dr.from_date
21 /
FROM_DATE SUM
--------- ----------
15-JAN-13 203
16-JAN-13 660
17-JAN-13 0
18-JAN-13 233
19-JAN-13 0
20-JAN-13 999
21-JAN-13 579
7 rows selected.
SQL>
SQL>