Sunday, May 16, 2010

SQL Query For Monthly Calculation

0 comments
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: