Results 1 to 5 of 5

Thread: semi monthly

  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: 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
    Last edited by homer.favenir; 02-23-09 at 02:15.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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);
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Red face

    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;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •