Scribd
Upload
106 views2 pages

Koha Library Transaction Summary

The document contains 3 SQL queries: 1) The first query selects data on all completed transactions (issued and returned items) from various tables and orders by return date. 2) The second query selects the same data but limits to items returned between given dates. 3) The third query counts transactions between dates by type (check out, check in, renewal, amnesty, payment, other). It groups the results by transaction type and count.

Uploaded by

Sudhakar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
106 views2 pages

Koha Library Transaction Summary

The document contains 3 SQL queries: 1) The first query selects data on all completed transactions (issued and returned items) from various tables and orders by return date. 2) The second query selects the same data but limits to items returned between given dates. 3) The third query counts transactions between dates by type (check out, check in, renewal, amnesty, payment, other). It groups the results by transaction type and count.

Uploaded by

Sudhakar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

All Completed Transactions (Issued and Returned)

SELECT [Link], old_issues.issuedate, old_issues.date_due, old_issues.returndate,


[Link], [Link], [Link], [Link], [Link],
[Link], [Link], [Link], [Link],
[Link], [Link], [Link]

FROM old_issues

LEFT JOIN borrowers ON ( [Link] = old_issues.borrowernumber )

LEFT JOIN items ON ( [Link] = old_issues.itemnumber )

LEFT JOIN biblioitems ON ( [Link] = [Link])

LEFT JOIN biblio ON [Link]=[Link]

ORDER BY old_issues.returndate DESC

Checked in Between Dates (Return)


SELECT [Link], old_issues.issuedate, old_issues.date_due, old_issues.returndate,
[Link], [Link], [Link], [Link], [Link],
[Link], [Link], [Link], [Link],
[Link], [Link], [Link]

FROM old_issues

LEFT JOIN borrowers ON ( [Link] = old_issues.borrowernumber )

LEFT JOIN items ON ( [Link] = old_issues.itemnumber )

LEFT JOIN biblioitems ON ( [Link] = [Link])

LEFT JOIN biblio ON [Link]=[Link]

ORDER BY old_issues.returndate DESC

All Transactions Between Dates - Counts

SELECT

CASE type

WHEN 'issue' THEN "Check Outs"

WHEN 'return' THEN "Check Ins"

WHEN 'renew' THEN "Renewals"

WHEN 'writeoff' THEN "Amnesties"

WHEN 'payment' THEN "Payments"


ELSE "Others" END

AS "Transaction types", COUNT(datetime) AS "Quantity"

FROM statistics

WHERE DATE(datetime) BETWEEN '2022-06-01' AND '2023-01-21'

GROUP BY type

You might also like