Hi!,
Here is a non-math version using PL/SQL date operators and functions
available in StepSqlite (https://www.metatranz.com/stepsqlite/).
Hopefully this should be easier to follow.
You can compile the below code directly to a win32 dll on the
StepSqlite website and then use it in your VB code.
Assumes Date column is in 'YYYY-MM-DD'. If using a different format,
just call DateTime.setDateFormat(<your-format>) to set proper format.
====
create table items(ID integer, Date date, Price float);
PACKAGE BODY MyPackage IS
PROCEDURE get_prices (start_month char, start_day char, end_month
char, end_day char ) IS
BEGIN
-- n_* below are dates normalized to fall in a given year, here I
chose year 2000 because its a leap year and has all possible
day-numbers for proper normalization.
FOR item IN
(SELECT id, to_char(date, 'MM') mon, to_char(date, 'DD') day, price
FROM (SELECT id, date, price,
to_date('2000-' || start_month||'-'||start_day,
'YYYY-MM-DD') n_start,
to_date('2000-' || end_month ||'-'||end_day,
'YYYY-MM-DD') n_end,
to_date(to_char(date, '2000-MM-DD'), 'YYYY-MM-DD')
n_date
FROM items
)
WHERE (n_start < n_end AND n_date between n_start and n_end)
OR (n_start > n_end AND n_date NOT between n_end and n_start)
ORDER BY to_char(date, 'MM-DD')
)
LOOP
DBMS_OUTPUT.put_line(item.id || ' ' || item.mon || '
'||item.day||' '||item.price);
END LOOP;
END;
BEGIN
insert into items(id, date, price) values(1,'2004-01-01', 1.1);
insert into items(id, date, price) values(2,'2004-02-01', 1.1);
insert into items(id, date, price) values(3,'2004-02-16', 1.1);
insert into items(id, date, price) values(4,'2004-10-01', 1.1);
insert into items(id, date, price) values(5,'2004-10-22', 1.1);
insert into items(id, date, price) values(51,'2005-01-01', 5.1);
insert into items(id, date, price) values(52,'2005-02-01', 5.1);
insert into items(id, date, price) values(53,'2005-02-16', 5.1);
insert into items(id, date, price) values(54,'2005-10-01', 5.1);
insert into items(id, date, price) values(55,'2005-10-22', 5.1);
insert into items(id, date, price) values(61,'2006-01-01', 6.1);
insert into items(id, date, price) values(62,'2006-02-01', 6.1);
insert into items(id, date, price) values(63,'2006-02-16', 6.1);
insert into items(id, date, price) values(64,'2006-10-01', 6.1);
insert into items(id, date, price) values(65,'2006-10-22', 6.1);
DBMS_OUTPUT.put_line('Price data Range: 02-15 to 10-21');
get_prices('02', '15', '10', '21');
DBMS_OUTPUT.put_line('Price data Range: 10-21 to 02-15');
get_prices('10', '21', '02','15');
rollback;
END;
====
Result:
====
Price data Range: 02-15 to 10-21
3 02 16 1.1
53 02 16 5.1
63 02 16 6.1
4 10 01 1.1
54 10 01 5.1
64 10 01 6.1
Price data Range: 10-21 to 02-15
1 01 01 1.1
51 01 01 5.1
61 01 01 6.1
2 02 01 1.1
52 02 01 5.1
62 02 01 6.1
5 10 22 1.1
55 10 22 5.1
65 10 22 6.1
====
Regards,
SK
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
