CODEWITHSUNDEEP
mysqlsql
Jorge Zapata
Jorge Zapata

Reputation: 2336

Select between two dates from other table

I have a mysql table (file_payments) to keep records of payments contained in a file and looks like this

ID  FILE   START_DATE   END_DATE    NO_PAYMTS
--  -----  ----------   ----------  ---------
1   file1  2013-10-11   2013-10-15     6
2   file2  2013-10-16   2013-10-20     10

Then I have another table (payments) with more details about this payments

   ID   DATE        AMOUNT       BANK   
   ---  ----------  ----------   ----  
    1   2013-10-11  100.00        3
    2   2013-10-12  500.00        3
    3   2013-10-13  400.00        2
    4   2013-10-15  200.00        2 
    5   2013-10-16  400.00        4  
    6   2013-10-18  300.00        1 
    7   2013-10-19  700.00        3

I need to relate both tables to verify that the NO_PAYMTS in first table correspond to the actual number of payments in the second one, So I'm thinking about counting the records on the second table which are between START_DATE and END_DATE from the first one. The output expected in this example is:

START_DATE   END_DATE    NO_PAYMTS   ACTUAL_PAYMTS
----------   ----------  ---------   -------------
2013-10-11   2013-10-15     6             4
2013-10-16   2013-10-20     10            3

I'm confused how to do the query, but probably would be something like:

SELECT ID,FILE,START_DATE,END_DATE,NO_PAYMTS FROM file_payments WHERE ()

Obviously this doesn't work because there is no criteria in WHERE clause to join the tables, how can I make it work?

Upvotes: 2

Views: 7189

Answers (4)

chris_techno25
chris_techno25

Reputation: 2487

Try this...It works on my side :) I have taken the liberty to count just the ID from table 2 since theoretically it should be faster compared to using *.

SELECT T1.ID,
       T1.FILE,
       T1.START_DATE,
       T1.END_DATE,
       T1.NO_PAYMTS,
       (SELECT COUNT(T2.ID) FROM TABLE2 T2 WHERE T2.DATE>=T1.START_DATE AND T2.DATE<=T1.END_DATE) as ACTUAL_PAYMTS
FROM TABLE1 T1;

Upvotes: 1

throrin19
throrin19

Reputation: 18207

Query : 

SELECT f.id, f.file, f.start_date, f.end_date, f.no_paymnts, COUNT(p.bank) 
from file_payments f, payments p
WHERE p.date BETWEEN f.start_date AND f.end_date
GROUP BY f.id;

JSFiddle : http://sqlfiddle.com/#!2/8446f/13

Upvotes: 1

Senthilmurugan
Senthilmurugan

Reputation: 383

Best way to do this is to use SQL functions. Create the function like below, then execute it with your query.

Function :

DELIMITER $$
DROP FUNCTION IF EXISTS `getPaymentCount`$$

CREATE  FUNCTION `getPaymentCount`(startDate DATE,endDate DATE) RETURNS INT(10)
BEGIN
    DECLARE paymentCount INT(10);
    SELECT  COUNT(*) INTO paymentCount FROM payments WHERE  DATE  BETWEEN startDate AND endDate;
    RETURN paymentCount;
END$$

DELIMITER ;

Query : 

SELECT ID,FILE,START_DATE,END_DATE,NO_PAYMTS,getPaymentCount(start_date,end_date) AS ACTUAL_NO_OF_PAYMENTS FROM file_payments;

Upvotes: 0

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

SELECT t1.ID,
       t1.FILE,
       t1.START_DATE,
       t1.END_DATE,
       t1.NO_PAYMTS,
       (SELECT COUNT(*)
        FROM Table2 t2
        WHERE t2.DATE >= t1.START_DATE
        AND t2.DATE <= t1.END_DATE ) AS ACTUAL_PAYMTS
FROM Table1 t1

Result:

| ID |  FILE | START_DATE |   END_DATE | NO_PAYMTS | ACTUAL_PAYMTS |
|----|-------|------------|------------|-----------|---------------|
|  1 | file1 | 2013-10-11 | 2013-10-15 |         6 |             4 |
|  2 | file2 | 2013-10-16 | 2013-10-20 |        10 |             3 |

Upvotes: 5

Related Questions