September 25, 2008 at 11:17 am
Create procedure spTotalLibrosMenosPrestamosColeccion @Coleccion varchar(100), @FechaIni varchar(10), @FechaFin varchar(10)
as
select L.Titulo, count(*) as NumeroPrestamos
from Servicios S, Ejemplares E, Libros L, Colecciones C
where E.CodigoAcceso = S.CodigoAcceso
and E.IDLibro = L.IDLibro
and E.IDColeccion = C.IDColeccion
and C.Coleccion = @Coleccion
and S.FechaPrestamo between @FechaIni and @FechaFin
group by L.Titulo
having count(*)>1
order by NumeroPrestamos asc
I need to sort asc the totals per Titulo (the procedure recibe 3 parameters), This query groups me only by 'Titulo' (thats what i need) but apart of this i need to show others columns of the tables in the select.
How can i do this?
thanks for any help
September 25, 2008 at 11:40 am
If I understand correctly what you're trying to do then try something like this:
Create procedure spTotalLibrosMenosPrestamosColeccion @Coleccion varchar(100), @FechaIni varchar(10), @FechaFin varchar(10)
as
SELECT (list all the fileds you want)
FROM (list your tables and joins)
WHERE Titulo in
(select L.Titulo
from Servicios S, Ejemplares E, Libros L, Colecciones C
where E.CodigoAcceso = S.CodigoAcceso
and E.IDLibro = L.IDLibro
and E.IDColeccion = C.IDColeccion
and C.Coleccion = @Coleccion
and S.FechaPrestamo between @FechaIni and @FechaFin
group by L.Titulo
having count(*)>1)
order by NumeroPrestamos asc
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 25, 2008 at 12:09 pm
I would have to take a bit of diversion (only a little bit) and use a Join here because what I found so far is that "IN" seems to be quite expensive & time consuming and I would do the following -
Create procedure spTotalLibrosMenosPrestamosColeccion @Coleccion varchar(100), @FechaIni varchar(10), @FechaFin varchar(10)
as
SELECT B.NumeroPrestamos , B.Titulo
FROM (list your tables and joins)B
join
(select L.Titulo
from Servicios S, Ejemplares E, Libros L, Colecciones C
where E.CodigoAcceso = S.CodigoAcceso
and E.IDLibro = L.IDLibro
and E.IDColeccion = C.IDColeccion
and C.Coleccion = @Coleccion
and S.FechaPrestamo between @FechaIni and @FechaFin
group by L.Titulo
having count(*)>1)A
on A.Titulo = B.Titulo
order by B.NumeroPrestamos asc
September 25, 2008 at 12:38 pm
Good point. I don't know I have so much trouble adopting that method.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 25, 2008 at 2:10 pm
select L.Titulo, count(*) as NumeroPrestamos
from Servicios S, Ejemplares E, Libros L, Colecciones C
where E.CodigoAcceso = S.CodigoAcceso
and E.IDLibro = L.IDLibro
and E.IDColeccion = C.IDColeccion
and C.Coleccion = 'General'
and S.FechaPrestamo between '2005-01-01' and '2006-01-01'
group by L.Titulo
having count(*)>1
in this query it brings me:
Titulo NumeroPrestamo
Diseño digital 20
Dirección de marketing 22
Administración de la producción y operaciones para una ventaja competitiva23
Circuitos eléctricos 24
Economía internacional 24
Manual de laboratorio de química 24
Física T.I 25
Economía: una introducción contemporánea 25
Análisis de circuitos en ingeniería 26
in total of 174 registers
when i do this:
SELECT B.Titulo, B.CodigoAcceso, A.NumeroPrestamos
FROM (select L.Titulo, E.CodigoAcceso, L.Clasificacion
from Servicios S, Ejemplares E, Libros L, Colecciones C
where E.CodigoAcceso = S.CodigoAcceso
and E.IDLibro = L.IDLibro
and E.IDColeccion = C.IDColeccion
and C.Coleccion = 'General'
and S.FechaPrestamo between '2005-01-01' and '2006-01-01') B
join
(select L.Titulo, count(*) as NumeroPrestamos
from Servicios S, Ejemplares E, Libros L, Colecciones C
where E.CodigoAcceso = S.CodigoAcceso
and E.IDLibro = L.IDLibro
and E.IDColeccion = C.IDColeccion
and C.Coleccion = 'General'
and S.FechaPrestamo between '2005-01-01' and '2006-01-01'
group by L.Titulo
having count(*)>1) A
on A.Titulo = B.Titulo
order by A.NumeroPrestamos asc
in total of 2294 registers
why is that? it repeat Titulos, what i need is those 174 registers show adicionaly CodigoAcceso and Clasificacion
thanks for all your help =)
September 25, 2008 at 3:48 pm
Sorry, I was away & I just saw this.
Remove "B.CodigoAcceso" from the query and re-run and you should now only see 174 rows.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply