Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts

    logic issue - 'from' and 'to' dates, and reverse date ordering

    I've got a database table that logs payments made to organisations. When a payment is made, two dates are logged: the actual date of the payment and, (because payments are usually made about a month in arrears), the 'To' cutoff date: that is, the payment covers a period from the last payment's 'To' date, up until this date. I hope that makes sense.

    In addition, payments are categorised. Thus, the 'last payment' means the last payment of that category.

    I need to list these payments. For each row of payment detail, I need to display a 'From' and 'To' date.

    If I get a list in ascending date order (ie: oldest first), then the matter is simple. As I proceed through the recordset, I note what the 'To' date is for that payment (and which category it is), and use that as the 'From' date next time a payment in that category crops up.

    The client now wants it in descending date order

    I am stumped.

    I can see a number of possibilities; they all involve horrendous multiple-looping though recordsets or building fascinatingly intricate arrays of next-date-for-the-last-date-payment-date, etcetera.

    Can anyone whose brain isn't quite so fried suggest a simple, elegant and efficient way of looking ahead a few rows to find the next - that is, the previous - payment 'To' date?


  • #2
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    It would be very helpful if you could possibly provide the tables structure (could be done with SHOW CREATE TABLE) and the queries which are used to select data in ascending order ... If this is possible of course.

    I see that in the querying the DB the application logic is used. Probably it could be excluded. In this case ascending and descending order queries could be very similar... It is just a guess though.

  • #3
    New Coder
    Join Date
    Mar 2009
    Posts
    89
    Thanks
    5
    Thanked 14 Times in 14 Posts
    Hi,

    As @SKDevelopment said, it'll be better if you provide additional info, but this thread maybe helpful too.
    Regards
    URL2SEO URL shortener and redirection services

  • #4
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Hi - thanks for replies. Table structure as follows:

    Code:
    CREATE TABLE `payments` (
      `idPayments` int(10) unsigned NOT NULL auto_increment,
      `idOrganisation` int(10) unsigned NOT NULL,
      `paymentAmount` float NOT NULL,
      `paymentDate` datetime default NULL,
      `paymentToDate` datetime default NULL,
      `paymentType` varchar(45) NOT NULL,
      `paymentDescription` text,
      `paymentMethod` varchar(45) default NULL,
      PRIMARY KEY  (`idPayments`),
      KEY `FK_payments_1` (`idOrganisation`),
      CONSTRAINT `FK_payments_1` FOREIGN KEY (`idOrganisation`) REFERENCES `organisations` (`idOrganisation`)
    Query SQL is:

    Code:
    SELECT
    	p.idPayments
    	, p.paymentDate
    	, p.paymentToDate
    	, p.paymentReference
    	, p.paymentMethod
    	, p.paymentType
    	, p.paymentAmount
    FROM payments p
    WHERE idOrganisation = 123
    ORDER BY p.paymentDate DESC

  • #5
    New Coder
    Join Date
    Aug 2003
    Location
    Derby, UK
    Posts
    97
    Thanks
    0
    Thanked 14 Times in 14 Posts
    Can you clarify - do you want the payments listed in date order regardless of category? So your problem is that as you access each record in reverse order you don't know the start date yet, whereas doing it forwards you did?

    The most obvious answer to me if that is it worked fine in ascending order then do it in ascending order but instead of outputting directly to screen push each row into an array then when you are finished pop them back out of the array to screen?

    You don't have to process data in the same order it will be output on screen

  • Users who have thanked DaiWelsh for this post:

    Spudhead (02-18-2010)

  • #6
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Of course! Why does it seem so simple when someone points out the obvious answer: dump the loop output into an array, and loop backwards through that.

    Thank you

  • #7
    New Coder
    Join Date
    Aug 2003
    Location
    Derby, UK
    Posts
    97
    Thanks
    0
    Thanked 14 Times in 14 Posts
    Often the way - I have the same blind moments and I am sure everyone else does too, if it is any consolation


  •  

    Posting Permissions

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