0

When i execute this sql code:

    SELECT 
        ROW_NUMBER() OVER (ORDER BY schdate), 
        ACCT , 
        SCHDATE AS DATES,
        NULL  AS ORS 
        FROM AMORT 
        WHERE ACCT = '0000360' AND CYCLE = '0001' AND principal <> '0.00'
    UNION ALL
    SELECT 
         ROW_NUMBER() OVER (ORDER BY ddate), 
         ACCT, 
         DDATE AS DATES,
         OR_NO AS ORS 
         FROM LEDGER 
         WHERE (ACCT = '0000360' AND CYCLE = '0001') AND (t_code = '10' OR t_code = '11' OR t_code = '12' OR t_code = '13')
     ORDER BY DATES

I've got this result., but how can i accomplish to have a days between dates?

ACCT    |     DATES |      ORS
-------------------------------------
0000360 |2014-07-26 | (NULL)
0000360 |2014-08-02 | (NULL)
0000360 |2014-08-05 | 011290000113
0000360 |2014-08-09 | (NULL)
0000360 |2014-08-16 | (NULL)
0000360 |2014-08-19 | 011290000559

I'd like to get the days between the dates like the illustration below..

ACCT    |     DATES |      ORS
-------------------------------------
======================
0000360 |2014-07-26 || (NULL)
0000360 |2014-08-02 || (NULL)
======================
0000360 |2014-08-05 | 011290000113
======================
0000360 |2014-08-09 || (NULL)
0000360 |2014-08-16 || (NULL)
======================
0000360 |2014-08-19 | 011290000559

Get the dates without ORS and count the days between that day for example: 

ACCT    |     DATES |      ORS
-------------------------------------
======================
0000360 |2014-07-26 || (NULL)
0000360 |2014-08-02 || (NULL)
======================

So, between 2014-07-26 and 2014-08-02 the counting days is 7

======================
0000360 |2014-08-09 || (NULL)
0000360 |2014-08-16 || (NULL)
======================

and for this 2014-08-09 and 2014-08-16 the counting days is 7

Michael Blanza
  • 701
  • 2
  • 7
  • 13
  • [`BETWEEN`](http://www.mysqltutorial.org/mysql-between) is an option. See under "Related" over to the right also. One of which being http://stackoverflow.com/q/510012/ – Funk Forty Niner Sep 14 '14 at 03:36
  • 1
    What RDBMS are you using? You have this tagged `mysql`, but it doesn't have the `ROW_NUMBER()` function. – Barmar Sep 14 '14 at 03:43
  • im using sqlanywhere. how can i get the difference specially they are in one column – Michael Blanza Sep 14 '14 at 04:26

2 Answers2

0
<?php

 $date1 = strtotime("2014-08-09");
 $date2 = strtotime("2010-08-16");
 $datediff = abs($date1 - $date2);
 echo floor($datediff/(60*60*24));

?>

The abs is there so you always get a positive outcome.

In Mysql use:

SELECT DATEDIFF('new_date', 'old_date');

SELECT DATEDIFF('2014-08-16','2014-08-09');
Arthur Brown
  • 41
  • 2
0

One way you can have MySQL compute some data for each row in a result set, and include that as part of the result, is to use user-defined variables.

For example, (test data):

CREATE TABLE `demo` (
  `date` date NOT NULL
);

INSERT INTO `demo` (`date`) VALUES
('2014-09-09'),
('2014-09-10'),
('2014-09-12');

And then the query:

SET @date_last = '2014-08-01';
SELECT date, DATEDIFF(`date`, @date_last), (@date_last := `date`) FROM `demo`;

Returns

|2014-09-09|39|2014-09-09
|2014-09-10| 1|2014-09-10
|2014-09-12| 2|2014-09-12
inquist
  • 197
  • 4