| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
02-23-09, 01:11
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
|
semi monthly
|
|
hi,
i have a table with columns ( table.startdate and table.enddate )
table.startdate = 01/01/09
table.enddate = 07/01/09
table.months = 6
how can i achieved this semi monthly lists?
using my columns?
Code:
01/15/2009
01/30/2009
02/15/2009
02/28/2009
03/15/2009
03/30/2009
04/15/2009
04/30/2009
05/15/2009
05/30/2009
06/15/2009
06/30/2009
thanks
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
Last edited by homer.favenir; 02-23-09 at 01:15.
|
02-23-09, 07:48
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
please do a SHOW CREATE TABLE for the table
also, do you have an integers table? like this --
Code:
CREATE TABLE integers
(i INTEGER NOT NULL PRIMARY KEY);
INSERT INTO integers (i) VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
02-23-09, 20:01
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
|
|
i dont have the integers table.
what is this integers table? 
Code:
CREATE TABLE `loanslist_tbl` (
`idloansList_tbl` int(10) unsigned NOT NULL auto_increment,
`AccNo` varchar(45) NOT NULL default '',
`Name` varchar(100) NOT NULL default '',
`Address` varchar(100) NOT NULL default '',
`Collateral` varchar(100) default NULL,
`LoanDays` float default NULL,
`LoanMonths` float default NULL,
`DateLoan` datetime NOT NULL default '0000-00-00 00:00:00',
`DueDate` datetime NOT NULL default '0000-00-00 00:00:00',
`LoanAmount` float NOT NULL default '0',
`Interest` float NOT NULL default '0',
`Agent` varchar(45) default NULL,
`AccType` varchar(45) NOT NULL default '',
`Area` varchar(45) default NULL,
`ClientIDNo` int(10) unsigned NOT NULL default '0',
`ModePayment` varchar(45) NOT NULL default '',
PRIMARY KEY (`idloansList_tbl`),
KEY `Index_2` (`AccNo`,`Name`,`LoanDays`,`LoanMonths`,`DateLoan`,`LoanAmount`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
`DateLoan` = 01/01/09
`DueDate` = 07/01/09
`LoanMonths` = 6
help please.
thanks
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
|
02-23-09, 22:21
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
the reason i asked you for the CREATE TABLE was to confirm that the dates were actual dates (like '2009-07-01') and not strings (like '07/01/09')
the following query will not generate the 30th of the month, rather, it will always generate the last day of the month
Code:
SELECT DateLoan + INTERVAL i MONTH
- INTERVAL 1 DAY AS PayDate
FROM loanslist_tbl
CROSS
JOIN integers
WHERE i > 0
AND DateLoan + INTERVAL i MONTH
< DueDate
UNION All
SELECT DateLoan + INTERVAL i MONTH
+ INTERVAL 14 DAY
FROM loanslist_tbl
CROSS
JOIN integers
WHERE DateLoan + INTERVAL i MONTH
+ INTERVAL 14 DAY
< DueDate
ORDER
BY PayDate
|
|
02-23-09, 23:55
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
very well script!...it works
so... how does the integers table help?
i dont understand the script but it works.
can you please explain how it works.
thanks
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
semi monthly
