[Koha] Report help

[ramirouvia .]

Elaine:

I think you should use 008 date:

SELECT
CASE SUBSTR(ExtractValue(m.marcxml,'//controlfield[@tag="008"]'),8,2)
      WHEN '19' THEN 'XX'
      WHEN '20' THEN 'XXI'
      WHEN '18' THEN 'XVIII'
      WHEN '17' THEN 'XVII'
      ElSE 'OLDER' END
   AS bibtype, count(DISTINCT biblionumber) AS bibs FROM biblioitems m
GROUP BY bibtype

I made this report based on two reports from the library by Nicole C.
Engard.

Ramiro



2014-07-23 15:57 GMT-03:00 Elaine Bradtke <eb at efdss.org>:

> This is the basic report:
> SELECT count(biblio.copyrightdate), biblio.copyrightdate,items.itype FROM
> items LEFT JOIN biblioitems on
> (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on
> (biblioitems.biblionumber=biblio.biblionumber) GROUP BY
> biblio.copyrightdate ORDER BY biblio.copyrightdate asc
>
> I also used an expanded version with more fields so I could see what the
> numbers really mean.
>
>
>    - It doesn't pick up everything.  A lot of our 18th and 19th century
>    publications have partial or conjectural dates in square brackets. These
>    seem to be ignored.
>    - It gives a count of the items.  Not the biblios.  So this skews the
>    count further (we have a lot of 18th century pamphlets bound together -
>    many biblios, but only one item record for the bound volume).
>
> Is there any way we can be more precise?
> Would a query on the MARCXML for the  260 c produce a more accurate result?
> Can we to force it to count biblios instead of items?
> It may be we have to settle for doing the counting in the spreadsheet
> rather than via Koha. But it would be nice to have some control over what
> is counted.
>
> I know next to nothing about SQL, any help would be greatly appreciated.
>
> Thanks
>
>
>
> On Wed, Jul 23, 2014 at 12:31 AM, Robin Sheat <robin at catalyst.net.nz>
> wrote:
>
> > Elaine Bradtke schreef op di 22-07-2014 om 17:35 [+0100]:
> > > One of my colleagues was asking if I could give statistics on what we
> > > have
> > > from the 17th, 18th and first half of the 19th century.  Preferably by
> > > year.
> > >
> > When my Elasticsearch work is finished, this'll be just a matter of
> > finding an elasticsearch visualisation tool that lets you explore your
> > data, and telling it to do what you want.
> >
> > The way I'd do it currently would be to script something that pulls the
> > date from the MARC of each entry and exports that. Then you could pop it
> > into libreoffice calc and make graphs or whatever you need.
> >
> > --
> > Robin Sheat
> > Catalyst IT Ltd.
> > ✆ +64 4 803 2204
> > GPG: 5FA7 4B49 1E4D CAA4 4C38  8505 77F5 B724 F871 3BDF
> >
> > _______________________________________________
> > Koha mailing list  http://koha-community.org
> > Koha at lists.katipo.co.nz
> > http://lists.katipo.co.nz/mailman/listinfo/koha
> >
>
>
>
> --
> Elaine Bradtke
> Data Wrangler
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel    +44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --------------------------------------------------------------------------
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
> ---------------------------------------------------------------------------
> "Writing about music is like dancing about architecture"
> --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>