error en variable dentro del generador de consultas

[angeles804]

tengo el siguiente codigo pero no puedo hacer que la fecha la tome como este tipo

[%0]

Pero no se por que no lo agarra esto lo pongo en la variable fecha y no me arroja nada, me manda un error, es el siguiente, alguien me puede decir como, por que quisiera que en el query al darle doble clic me diera el campo para que yo ponga la fecha y asi me haga el reporte.

me envia alertas recibidas o contratos de servicio o valores definidos por usuario. Help me, quiero que solo me pida la fecha y no quiere el sap.

Declare @fecha datetime
Set @fecha='2009/05/06'
SELECT distinct'series'=  case
when t0.series=1 then 'forjadores'
when t0.series=34 then 'universidad'
when t0.series=35 then 'libramiento'
when t0.series=36 then 'cangrejos'
when t0.series=37 then 'rosarito'
when t0.series=144 then 'santa rosa'
end,
(select count(docentry) from oinv where( series=t0.series and doctime between '700' and '800' and docdate =@fecha)) As '7:30 am a 8:00 am',
(select count(docentry) from oinv where( series=t0.series and doctime between '801' and '900' and docdate =@fecha)) As '8:00am a 900am',
(select count(docentry) from oinv where( series=t0.series and doctime between '901' and '1000' and docdate =@fecha)) As '9:00am a 10:00am',
(select count(docentry) from oinv where( series=t0.series and doctime between '1001' and '1100' and docdate =@fecha)) As '10:00am a 11:00am',
(SELECT Count(docentry) From OINV where (series= t0.series and doctime between '1101' and '1200' and docdate=@fecha)) As '11:00am a 12:00 am',
(SELECT Count(docentry) From OINV where (series = t0.series and doctime between '1201' and '1300' and docdate=@fecha)) As '12:00 pm a 1:00 pm',
(select count(docentry) from oinv where (series=t0.series and doctime between '1301' and '1400' and docdate=@fecha)) as '1:00 pm a 2:00 pm' ,
(select count(docentry) from oinv where( series=t0.series and doctime between '1401' and '1500' and docdate=@fecha)) as '2:00pm a 3:00 pm',
(select count(docentry) from oinv where (series=t0.series and doctime between '1501' and '1600' and docdate=@fecha)) as '3:00 pm a 4:00 pm',
(select count(docentry) from oinv where( series=t0.series and doctime between '1601' and '1700' and docdate =@fecha)) As '4:00 pm  a 5:00 pm',
(select count(docentry) from oinv where( series=t0.series and doctime between '1701' and '1800' and docdate =@fecha)) As '5:00 pm a 6:00 pm',
(select count(docentry) from oinv where( series=t0.series and doctime between '1801' and '1900' and docdate =@fecha)) As '6:00pm a 6:30 pm'
From OINV T0
where t0.docdate=@fecha

Edited by: mary merecias on May 13, 2009 4:41 PM

[Former Member · Accepted solution]

Al revisar la query, efectivamente me apareció el mensaje de error que mencionas, sin embargo al realizar la siguiente modificación:

Reemplacé:

Declare @fecha datetime

Set @fecha='2009/05/06'

, por:

Declare @fecha as datetime

Set @fecha = (SELECT DISTINCT T0.DocDate FROM OINV T0 WHERE T0.DocDate = )

, entendiendo que la fecha base es la fecha del documento, la query me arrojó los resultados sin error de por medio.

Chequealo y comenta si el resultado es el deseado.

Saludos,

Paul Ponce

[Former Member]

Estimada,

Solo debes poner la variable entre comilla simple.

'[%0]'

Eso es todo.

Att,

Manuel Lazcano

[angeles804]

Asi como me lo enviaste de esta manera me sale muy bien, gracias no se, pero solo poniendole la variable no se podía :(. Bueno muchas gracias. :d

Declare @fecha as datetime
Set @fecha = (SELECT DISTINCT T0.DocDate FROM OINV T0 WHERE T0.DocDate = [%0])
SELECT distinct'series'=  case
when t0.series=1 then 'forjadores'
when t0.series=34 then 'universidad'
when t0.series=35 then 'libramiento'
when t0.series=36 then 'cangrejos'
when t0.series=37 then 'rosarito'
when t0.series=144 then 'santa rosa'
end,
(select count(docentry) from oinv where( series=t0.series and doctime between '700' and '800' and docdate =@fecha)) As '7:30 am a 8:00 am',
(select count(docentry) from oinv where( series=t0.series and doctime between '801' and '900' and docdate =@fecha)) As '8:00am a 900am',
(select count(docentry) from oinv where( series=t0.series and doctime between '901' and '1000' and docdate =@fecha)) As '9:00am a 10:00am',
(select count(docentry) from oinv where( series=t0.series and doctime between '1001' and '1100' and docdate =@fecha)) As '10:00am a 11:00am',
(SELECT Count(docentry) From OINV where (series= t0.series and doctime between '1101' and '1200' and docdate=@fecha)) As '11:00am a 12:00 am',
(SELECT Count(docentry) From OINV where (series = t0.series and doctime between '1201' and '1300' and docdate=@fecha)) As '12:00 pm a 1:00 pm',
(select count(docentry) from oinv where (series=t0.series and doctime between '1301' and '1400' and docdate=@fecha)) as '1:00 pm a 2:00 pm' ,
(select count(docentry) from oinv where( series=t0.series and doctime between '1401' and '1500' and docdate=@fecha)) as '2:00pm a 3:00 pm',
(select count(docentry) from oinv where (series=t0.series and doctime between '1501' and '1600' and docdate=@fecha)) as '3:00 pm a 4:00 pm',
(select count(docentry) from oinv where( series=t0.series and doctime between '1601' and '1700' and docdate =@fecha)) As '4:00 pm  a 5:00 pm',
(select count(docentry) from oinv where( series=t0.series and doctime between '1701' and '1800' and docdate =@fecha)) As '5:00 pm a 6:00 pm',
(select count(docentry) from oinv where( series=t0.series and doctime between '1801' and '1900' and docdate =@fecha)) As '6:00pm a 6:30 pm'
From OINV T0
where t0.docdate=@fecha