[Koha] Help with sql statement.

[James Ghiorzi]

Hi Jared,

Thanks for responding on a weekend :).  I changed the statement to:

SELECT b.code,c.code,COUNT(*)
  FROM borrowers WHERE DATE(dateenrolled)  between <<Date between (yyyy-mm-dd)>>
  AND DATE(dateenrolled) <<and (yyyy-mm-dd)>> and b.code<<Pick your branch|branches>>
  GROUP BY c.code

but its still not producing any data, I am thinking that I am missing something else maybe a left/right join on something.

Orginal SQL by Sharon :


SELECT branchcode,categorycode,COUNT(*)
  FROM borrowers WHERE MONTH(dateenrolled) = <<Month enrolled (mm)>> AND YEAR(dateenrolled)= <<Year enrolled (yyyy)>>
  GROUP BY branchcode,categorycode
  ORDER BY branchcode


Please advise,

James Ghiorzi
Data Management Coordinator
Martin Luther King Jr. Library
San Jose State University

________________________________
From: Jared Camins-Esakov [jcamins at cpbibliography.com]
Sent: Saturday, June 09, 2012 4:55 PM
To: James Ghiorzi
Cc: koha at lists.katipo.co.nz; nengard at bywatersolutions.com
Subject: Re: [Koha] Help with sql statement.

James,


I am trying to get the new patron report developed by Sharon Moreland, to do a date range and have a location selection for San Benito County Free Library.  I am sure I am doing something wrong, please advise as necessary,

SQL:

SELECT b.code,c.code,COUNT(*)
FROM borrowers WHERE DATE(dateenrolled)  between <<Date
between (yyyy-mm-dd)>> AND DATE(dateenrolled)= <<and (yyyy-mm-dd)>> and b.code=<<Pick your branch|branches>>
GROUP BY c.code

You have an extra = after the second DATE(dateenrolled)

Regards,
Jared

--
Jared Camins-Esakov
Bibliographer, C & P Bibliography Services, LLC
(phone) +1 (917) 727-3445
(e-mail) jcamins at cpbibliography.com<mailto:jcamins at cpbibliography.com>
(web) http://www.cpbibliography.com/