December 19, 2008 at 1:09 pm
hi
i have this store procedure that shows the total of Titles ("TitulosAdquisicion") and volumen ("EjemplaresAdquisicion"), the thing i whant to do its to show this 2 totals per year, in the @FechaIni it have a starting date to filter to at end date (@FechaFin), for example 01/01/2005 as starting date and 01/01/2008 as end date, the thing i whant to show the 2 total per diferent year like this:
TitulosAdquisicion EjemplaresAdquisicion year
12 20 2005
10 18 2006
6 13 2007
15 32 2008
The store procedure that i have its this one. But onli shows the total of "TitulosAdquisicion" and "EjemplaresAdquisicion" for that range of time.
create procedure spTotalAdquisicionEjemplaresTitulos @Dependencia varchar(100), @FechaIni varchar(10), @FechaFin varchar(10),@AreaAcademica varchar(100), @Grupo varchar(100)
as
select B.TitulosAdquisicion, C.EjemplaresAdquisicion, @FechaIni as FechaIni, @FechaFin as FechaFin
from
(select d.Dependencia, count(*) as TitulosAdquisicion
from Dependencias D, EjemplaresSolicitados ES, Titulos T
where ES.IDTitulo = T.IDTitulo
and T.IDDependencia = D.IDDependencia
and D.Dependencia = CASE @Dependencia WHEN 'Todos' THEN D.Dependencia ELSE @Dependencia END
and T.AreaBeneficiada = CASE @AreaAcademica WHEN 'Todos' THEN T.AreaBeneficiada ELSE @AreaAcademica END
and ES.FechaRecepcion between @FechaIni and @FechaFin
group by d.Dependencia
having count(*)>=0) B,
(select d.Dependencia, sum(EjemplaresSolicitados) as EjemplaresAdquisicion
from Dependencias D, EjemplaresSolicitados ES, Titulos T
where ES.IDTitulo = T.IDTitulo
and T.IDDependencia = D.IDDependencia
and D.Dependencia = CASE @Dependencia WHEN 'Todos' THEN D.Dependencia ELSE @Dependencia END
and T.AreaBeneficiada = CASE @AreaAcademica WHEN 'Todos' THEN T.AreaBeneficiada ELSE @AreaAcademica END
and ES.FechaRecepcion between @FechaIni and @FechaFin
group by d.Dependencia
having sum(EjemplaresSolicitados)>=0) C
Thank you any help to do this.
December 19, 2008 at 2:16 pm
Without seeing the DDL and having some test data, see the link in my signature, it is hard to give you an exact answer,
but I think this will do what you need:
[font="Courier New"]SELECT
D.DEPENDENCIA,
YEAR(ES.FECHARECEPCION) AS [year],
COUNT(*) AS TITULOSADQUISICION,
SUM(EJEMPLARESSOLICITADOS) AS EJEMPLARESADQUISICION
FROM
DEPENDENCIAS D JOIN
TITULOS T ON
D.IDDEPENDENCIA = T.IDDEPENDENCIA JOIN
EJEMPLARESSOLICITADOS ES ON
T.IDTITULO = ES.IDTITULO
WHERE
D.DEPENDENCIA = CASE @DEPENDENCIA
WHEN 'TODOS' THEN D.DEPENDENCIA
ELSE @DEPENDENCIA
END AND
T.AREABENEFICIADA = CASE @AREAACADEMICA
WHEN 'TODOS' THEN T.AREABENEFICIADA
ELSE @AREAACADEMICA
END AND
ES.FECHARECEPCION BETWEEN @FECHAINI AND @FECHAFIN
GROUP BY
D.DEPENDENCIA,
YEAR(ES.FECHARECEPCION)
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 15, 2009 at 9:40 am
Thank you very much Jack, it help me a lot.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply