Hi,
I have a historic table with LINEAS columns FECHA type DATE, and
NOMBRE type VARCHAR. I want to extract the COUNT of NOMBREs for each
month when FECHA is inside a date interval, grouped by month and year.
In Oracle, this is the query which does what I want:
WITH DATOS_MESES AS
( SELECT NOMBRE,EXTRACT(MONTH FROM FECHA) AS
MES,EXTRACT(YEAR FROM FECHA) AS ANYO FROM LINEAS WHERE
FECHA >= '01-JUN-10' AND FECHA < '01-MAR-11' )
SELECT COUNT(NOMBRE) AS ALTAS_MES,MES,ANYO FROM DATOS_MESES
GROUP BY ANYO,MES ORDER BY ANYO,MES ASC;
Which outputs the following:
ALTAS_MES MES ANYO
---------- ---------- ----------
739 6 2010
871 7 2010
811 8 2010
604 9 2010
701 10 2010
692 11 2010
1148 12 2010
1177 1 2011
859 2 2011
Anyone know how could I manage to create that query from a
DBIx::Class::ResultSet in SQL::Abstract lingo? It would save me some
12 aditional queries.
I don't mind cramming some literal SQL somewhere, but I'm worried
about returning the results; this query is really a SELECT and I need
them of course.
Thanks in advance
Regards
Jorge
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
