To get individual library member's monthly status report use the following column level query that includes how many books have been issued and returned in the month of may 2010 for those members who have their registration date in the same month here is the query
select m.member_id as "Member ID",m.member_name as "Member Name"
,(select count(book_id)
from tbl_book_issue b
where m.member_id=b.member_id
and b.status=1)as "Total Issued Books"
, (select count(book_id)
from tbl_book_issue b
where m.member_id=b.member_id
and b.status=2)as "Total Returned Books" #status=1 for issued and status=2 for return
from tbl_member m
where m.registration_date between '2010-05-01 00:00:00' and '2010-05-31 23:59:59'
0 comments:
Post a Comment