[Koha] MySQL query for serials late issues

[Suresh Kumar Tejomurtula]

Hi,

Below is my updated version of serials query for finding late issues based
on the already existing query in koha sql reports library.
Hope is it useful for other members.

SELECT @kp:=@kp+1 as S_No,serial.serialseq,
DATE_FORMAT(serial.planneddate, "%d %b %Y" ) as Planneddate,
DATE_FORMAT(subscription.startdate, "%d %b %Y" ) as Startdate,
DATE_FORMAT(serial.publisheddate, "%d %b %Y" ) as publisheddate,
biblio.title,
CASE WHEN serial.status = '3' or serial.status = '7' THEN 'LATE'
WHEN serial.status = '1' THEN 'EXPECTED'
END  AS STATUS,
CASE WHEN subscription.periodicity = '4' THEN 'Weekly'
WHEN subscription.periodicity = '5' THEN 'Fortnightly'
WHEN subscription.periodicity = '7' THEN 'Monthly'
WHEN subscription.periodicity = '8' THEN 'Bi-Monthly'
WHEN subscription.periodicity = '9' THEN 'Quarterly'
WHEN subscription.periodicity = '10' THEN 'Semi-Annual'
WHEN subscription.periodicity = '13' THEN 'Irregular'
END as Frequency,
CASE WHEN subscription.periodicity = '4' and (TO_DAYS(curdate())-TO_DAYS(
publisheddate))>7 THEN 'Late'
WHEN subscription.periodicity = '4' and (TO_DAYS(curdate())-TO_DAYS(
publisheddate))<7 THEN 'Expected'
WHEN subscription.periodicity = '5' and (TO_DAYS(curdate())-TO_DAYS(
publisheddate))>14 THEN 'Late'
WHEN subscription.periodicity = '5' and (TO_DAYS(curdate())-TO_DAYS(
publisheddate))<14 THEN 'Expected'
WHEN subscription.periodicity = '7' and (TO_DAYS(curdate())-TO_DAYS(
publisheddate))>30 THEN 'Late'
WHEN subscription.periodicity = '7' and (TO_DAYS(curdate())-TO_DAYS(
publisheddate))<30 THEN 'Expected'
WHEN subscription.periodicity = '7' and (TO_DAYS(curdate())-TO_DAYS(
publisheddate))>30 THEN 'Late'
WHEN subscription.periodicity = '9' and (TO_DAYS(curdate())-TO_DAYS(
publisheddate))<90 THEN 'Expected'
WHEN subscription.periodicity = '9' and (TO_DAYS(curdate())-TO_DAYS(
publisheddate))>90 THEN 'Late'
WHEN subscription.periodicity = '10' and (TO_DAYS(curdate())-TO_DAYS(
publisheddate))>180 THEN 'Late'
WHEN subscription.periodicity = '10' and (TO_DAYS(curdate())-TO_DAYS(
publisheddate))<180 THEN 'Expected'
WHEN subscription.periodicity = '13' and (TO_DAYS(curdate())-TO_DAYS(
publisheddate))>30 THEN 'Late'
WHEN subscription.periodicity = '13' and (TO_DAYS(curdate())-TO_DAYS(
publisheddate))<30 THEN 'Expected'
END as Status_calculated,
DATE_FORMAT(subscription.enddate, "%d %b %Y" ) as enddate,
(TO_DAYS(curdate())-TO_DAYS( publisheddate)) AS 'Delay Days',
left(aqbasket.basketname,7)) as PONumber,
CASE WHEN subscription.aqbooksellerid = 'xx' THEN 'Vendor1'
WHEN subscription.aqbooksellerid = 'XX' THEN 'Vendor2'
END as Vendor
FROM serial
LEFT JOIN serialitems on (serial.serialid=serialitems.serialid)
LEFT JOIN subscription on
(serial.subscriptionid=subscription.subscriptionid)
LEFT JOIN subscriptionhistory on
(serial.subscriptionid=subscriptionhistory.subscriptionid)
LEFT JOIN subscriptionroutinglist on
(serial.subscriptionid=subscriptionroutinglist.subscriptionid)
LEFT JOIN biblioitems on (biblioitems.biblionumber=serial.biblionumber)
LEFT JOIN biblio on (biblio.biblionumber=biblioitems.biblionumber)
LEFT JOIN aqbooksellers on (subscription.aqbooksellerid=aqbooksellers.id)
LEFT JOIN aqbasket on (subscription.aqbooksellerid=aqbasket.booksellerid),
(select @kp:= 0) AS kp
WHERE serial.planneddate < CURDATE() AND serial.STATUS NOT IN
('2','5','8','42','7') and
(serial.planneddate>=subscription.startdate and
serial.planneddate<=subscription.enddate) and subscription.closed='0'
order by biblio.title

-- 
Regards
T. Suresh Kumar