CODEWITHSUNDEEP
mysqldatabasedatetimecountnull
Clorae
Clorae

Reputation: 73

How to Count and Select all Record based on the Month where it has no data

I have a table named tbl_remit.

id   emp_id    comp_name    e_from       e_to         amount
1     123      a            2016-01-01   2016-01-31   10.00
2     123      a            2016-02-01   2016-02-29    5.00
3     123      a            2016-03-01   2016-03-31   20.00
4     456      b            2016-02-01   2016-02-29   50.00
5     789      c            2016-02-01   2016-02-29   10.00
6     789      c            2016-03-01   2016-03-31   15.00

I need to count and select all emp_id who did not pay from 2016-01-01 to 2016-01-31.

total employers: 3 total employers who did not pay from (2016-01-01 to 2016-01-31): 2

id   emp_id    comp_name    
1     456      b            
2     789      c

Is there any way to count where there is no data? this is the statement that i used this statement.

SELECT COUNT(PEN) FROM view_remit WHERE E_FROM != '"& varDateFrom &"' AND E_TO != '"& varDateTo &"' AND Assigned_ACCOUNT = '"& varAssignedAccount &"'

Sorry for this novice question.

Upvotes: 0

Views: 29

Answers (2)

S.Gupta
S.Gupta

Reputation: 1

SELECT DISTINCT emp_id,comp_name FROM tbl_remit where e_from<= '2016-01-01' AND e_from>= '2016-01-31';

Upvotes: 0

Matijs
Matijs

Reputation: 2553

I you have just this table you can get this data using a negative match on a sub-query:

SELECT DISTINCT emp_id, comp_name
  FROM tbl_remit
  WHERE emp_id NOT IN 
    (SELECT emp_id 
      FROM tbl_remit
      WHERE (e_from <= '2016-01-31' AND e_to >= '2016-01-01')
    )

First the sub-query select all emp_id's from rows from tbl_remit the are in the time period in question. Next you ask for those emp_id's not in that set.

This query would not show emp_id's of those that never paid anything. If you have a separate table that contains the possible emp_id's you could also use that table as the main query and match the emp_id's against those in the tbl_remit sub-query.

Upvotes: 1

Related Questions