Thanks! Cheers!
#>-----Original Message----- #>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- #>boun...@sqlite.org] On Behalf Of sub sk79 #>Sent: Monday, October 12, 2009 9:35 PM #>To: General Discussion of SQLite Database #>Subject: Re: [sqlite] Need Help SQL #> #>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 #>sqlite-users@sqlite.org #>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
