/*Difference in Days for LibreOffice LAST_CKO_Day is -9612*/

/*
item# in (select item# from ##nonficweed)

drop table ##nonficweed
*/

DECLARE @CALLPREFIX VARCHAR(10),@CALLNUMRANGE VARCHAR(10), @YEAR CHAR(4), @COLLECTION VARCHAR(10), @LASTCKO DATE
SET @CALLPREFIX='65';
SET @CALLNUMRANGE='[0-9]'; /* a single digit like '9' or a range like [1-5]*/
SET @YEAR='2012'; /*Put 19zz for 1999 since this we have anomalies like 19uu in 008 date subfield*/
SET @COLLECTION='anf';
SET @LASTCKO='01 Oct 2014';

SELECT item#,
call_reconstructed AS 'Call',
collection AS 'Collection',
processed AS 'Title',
ibarcode AS 'Barcode',
SUBSTRING(b.text,8,4) AS 'Pubdate',
iwt.item_status AS 'Item_Status',
CONVERT(VARCHAR(12),dateadd(dd,last_cko_date,'1 jan 1970')) AS 'Last_CKO_Date',
CONVERT(VARCHAR(12),dateadd(dd,creation_date,'1 jan 1970')) AS 'Date_Created',
n_ckos AS '# CKOs',
'Ranking' = (10000*n_ckos)/ ((SELECT datediff (dd, '1 Jan 1970', getdate ()))-creation_date+1),
'Age_in_Days'    = CONVERT(INT,((SELECT datediff (dd, '1 Jan 1970', getdate ()))-creation_date+1)),
'Days_on_Shelf'  = CASE
    WHEN last_cko_date IS NULL AND last_inhouse_use_date IS NULL
    THEN (CONVERT(INT,(SELECT datediff (dd, '1 Jan 1970', getdate ()))-creation_date))
    WHEN  last_inhouse_use_date IS NOT NULL AND last_cko_date IS NULL
    THEN (CONVERT(INT,(SELECT datediff (dd, '1 Jan 1970', getdate ()))-last_inhouse_use_date))
    WHEN last_inhouse_use_date > last_cko_date
    THEN (CONVERT(INT,(SELECT datediff (dd, '1 Jan 1970', getdate ()))-last_inhouse_use_date))
    WHEN last_inhouse_use_date <= last_cko_date
    THEN (CONVERT(INT,(SELECT datediff (dd, '1 Jan 1970', getdate ()))-last_cko_date))
    WHEN last_cko_date > 0
    THEN (CONVERT(INT,(SELECT datediff (dd, '1 Jan 1970', getdate ()))-last_cko_date))
  END
/*into ##nonficweed*/
FROM bib b
JOIN item_with_title iwt ON b.bib#=iwt.bib#
WHERE b.tag='008'
AND item_status != 'ordered'
AND collection=@COLLECTION
AND call_reconstructed LIKE (@CALLPREFIX+@CALLNUMRANGE+'%')
AND SUBSTRING(b.text,8,4) < @YEAR
/*and (iwt.last_cko_date < convert(int,(select datediff (dd, '1 Jan 1970', @LASTCKO))) or iwt.last_cko_date is NULL)*/


/*
select * from ##nonficweed nfw
where days_on_shelf > 1095
order by days_on_shelf
*/