Query!

Hi All,

Declare @.FirstNamenvarchar(60)

Declare @.LastNamenvarchar(60)

select @.FirstName=Usr_First_nm,@.LastName=Usr_Last_nmfrom cpmwhere login_id='dattEMEA_superuser'

Consider the above example. I want to print the varaiable values using select statement. What should I do?

I don't want to use PRINT statm.

Regards

Abdul

try with this procedure...

Declare @.FirstName nvarchar(60)

Declare @.LastName nvarchar(60)

@.FirstName= selectUsr_First_nm from cpm where login_id='dattEMEA_superuser'

@.LastName=selectUsr_Last_nmfrom cpmwhere login_id='dattEMEA_superuser'

select * fromcpmwhere Usr_First_nm =@.FirstName and Usr_Last_nm = @.LastName

|||

select @.FirstName , @.LastName

query works in 2005 but not in 2000

Hi,
I have the following query, it works fine in sql server 2005, but in
sql server 2000 it errors out:
DECLARE @.datestart datetime
DECLARE @.dateend datetime
set @.datestart = '2004-11-29'
set @.dateend = '2006-11-29'
select convert(varchar, (convert(datetime, convert(varchar,
test.date_created,104), 104)), 1) dt, count(apples) fruits
from testtable1 test, testtable2 od
where test.code=od.code2
and (test.method<>3)
and test.date_created between '00:00 '+@.datestart AND '23:59:59
'+@.dateend
group by convert(datetime, convert(varchar, test.date_created, 104),
104)
order by convert(datetime, convert(varchar, test.date_created, 104),
104) desc
in sql 2000 i get the following error:
Msg 8120, Level 16, State 1, Line 7
Column 'test.DATE_CREATED' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.
In 2005 it returns the data fine.
I need to have it return sorted by date and grouped by date, but the
returned data needs to not display the time, thats why I was doing that
conversion in the select. Any reason why it would work in one and not
the other. Or anyway around this?
Thanks.Put the right stuff in a subquery and make the final conversion in the
outside query. Using subqueries is usually the best way of dealing with
Group By problems.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"phil2phil" <philtwophil@.yahoo.com> wrote in message
news:1164831064.173961.83960@.16g2000cwy.googlegroups.com...
> Hi,
> I have the following query, it works fine in sql server 2005, but in
> sql server 2000 it errors out:
>
> DECLARE @.datestart datetime
> DECLARE @.dateend datetime
> set @.datestart = '2004-11-29'
> set @.dateend = '2006-11-29'
> select convert(varchar, (convert(datetime, convert(varchar,
> test.date_created,104), 104)), 1) dt, count(apples) fruits
> from testtable1 test, testtable2 od
> where test.code=od.code2
> and (test.method<>3)
> and test.date_created between '00:00 '+@.datestart AND '23:59:59
> '+@.dateend
> group by convert(datetime, convert(varchar, test.date_created, 104),
> 104)
> order by convert(datetime, convert(varchar, test.date_created, 104),
> 104) desc
> in sql 2000 i get the following error:
> Msg 8120, Level 16, State 1, Line 7
> Column 'test.DATE_CREATED' is invalid in the select list because it is
> not contained in either an aggregate function or the GROUP BY clause.
> In 2005 it returns the data fine.
> I need to have it return sorted by date and grouped by date, but the
> returned data needs to not display the time, thats why I was doing that
> conversion in the select. Any reason why it would work in one and not
> the other. Or anyway around this?
> Thanks.
>|||phil2phil wrote on 29 Nov 2006 12:11:04 -0800:
> Hi,
> I have the following query, it works fine in sql server 2005, but in
> sql server 2000 it errors out:
> DECLARE @.datestart datetime
> DECLARE @.dateend datetime
> set @.datestart = '2004-11-29'
> set @.dateend = '2006-11-29'
> select convert(varchar, (convert(datetime, convert(varchar,
> test.date_created,104), 104)), 1) dt, count(apples) fruits
> from testtable1 test, testtable2 od
> where test.code=od.code2
> and (test.method<>3)
> and test.date_created between '00:00 '+@.datestart AND '23:59:59
> '+@.dateend
> group by convert(datetime, convert(varchar, test.date_created, 104),
> 104)
> order by convert(datetime, convert(varchar, test.date_created, 104),
> 104) desc
> in sql 2000 i get the following error:
> Msg 8120, Level 16, State 1, Line 7
> Column 'test.DATE_CREATED' is invalid in the select list because it is
> not contained in either an aggregate function or the GROUP BY clause.
> In 2005 it returns the data fine.
> I need to have it return sorted by date and grouped by date, but the
> returned data needs to not display the time, thats why I was doing that
> conversion in the select. Any reason why it would work in one and not
> the other. Or anyway around this?
> Thanks.
The error occurs because the SQL Server 2000 compiler sees a difference
between the SELECT list and the GROUP BY, due to the extra CONVERT you have
in the SELECT. The GROUP BY must match the SELECT with the summaried columns
removed. If you use this it should work in 2000:
DECLARE @.datestart datetime
DECLARE @.dateend datetime
set @.datestart = '2004-11-29'
set @.dateend = '2006-11-29'
select convert(varchar, (convert(datetime, convert(varchar,
test.date_created,104), 104)), 1) dt, count(apples) fruits
from testtable1 test, testtable2 od
where test.code=od.code2
and (test.method<>3)
and test.date_created between '00:00 '+@.datestart AND '23:59:59
'+@.dateend
group by convert(varchar, (convert(datetime, convert(varchar,
test.date_created,104), 104)), 1)
order by convert(datetime, convert(varchar, test.date_created, 104),
104) desc
Dan|||Hi,
I see that the SELECT and GROUPBY need to match, but the problem is I
need to return the datetime without the date, thats why i was
converting to varchar. And I can't do the Order by as varchar as then
it's not ordering it anymore by datetime, which it needs to. If I try
to make the SELECT and GROUPBY the same, it complains about the ORDERBY
also needing to be the same, thats the part where i'm stuck, I need no
time in the return but it needs to be ordered by datetime.
Sylvain is there anyway you can give me a sample of how to put it in a
subquery and then make the final conversion outside?
Thanks.
Daniel Crichton wrote:
> phil2phil wrote on 29 Nov 2006 12:11:04 -0800:
> > Hi,
> > I have the following query, it works fine in sql server 2005, but in
> > sql server 2000 it errors out:
> >
> > DECLARE @.datestart datetime
> > DECLARE @.dateend datetime
> >
> > set @.datestart = '2004-11-29'
> > set @.dateend = '2006-11-29'
> >
> > select convert(varchar, (convert(datetime, convert(varchar,
> > test.date_created,104), 104)), 1) dt, count(apples) fruits
> > from testtable1 test, testtable2 od
> > where test.code=od.code2
> > and (test.method<>3)
> > and test.date_created between '00:00 '+@.datestart AND '23:59:59
> > '+@.dateend
> > group by convert(datetime, convert(varchar, test.date_created, 104),
> > 104)
> > order by convert(datetime, convert(varchar, test.date_created, 104),
> > 104) desc
> >
> > in sql 2000 i get the following error:
> > Msg 8120, Level 16, State 1, Line 7
> > Column 'test.DATE_CREATED' is invalid in the select list because it is
> > not contained in either an aggregate function or the GROUP BY clause.
> >
> > In 2005 it returns the data fine.
> >
> > I need to have it return sorted by date and grouped by date, but the
> > returned data needs to not display the time, thats why I was doing that
> > conversion in the select. Any reason why it would work in one and not
> > the other. Or anyway around this?
> >
> > Thanks.
>
> The error occurs because the SQL Server 2000 compiler sees a difference
> between the SELECT list and the GROUP BY, due to the extra CONVERT you have
> in the SELECT. The GROUP BY must match the SELECT with the summaried columns
> removed. If you use this it should work in 2000:
> DECLARE @.datestart datetime
> DECLARE @.dateend datetime
> set @.datestart = '2004-11-29'
> set @.dateend = '2006-11-29'
> select convert(varchar, (convert(datetime, convert(varchar,
> test.date_created,104), 104)), 1) dt, count(apples) fruits
> from testtable1 test, testtable2 od
> where test.code=od.code2
> and (test.method<>3)
> and test.date_created between '00:00 '+@.datestart AND '23:59:59
> '+@.dateend
> group by convert(varchar, (convert(datetime, convert(varchar,
> test.date_created,104), 104)), 1)
> order by convert(datetime, convert(varchar, test.date_created, 104),
> 104) desc
>
> Dan|||Actually I got the subquery select working, used a select into
statement, to put it into a temp table, and then had the temp table
data return the first column as varchar, removing the time.
Thank you for the help.
phil2phil wrote:
> Hi,
> I see that the SELECT and GROUPBY need to match, but the problem is I
> need to return the datetime without the date, thats why i was
> converting to varchar. And I can't do the Order by as varchar as then
> it's not ordering it anymore by datetime, which it needs to. If I try
> to make the SELECT and GROUPBY the same, it complains about the ORDERBY
> also needing to be the same, thats the part where i'm stuck, I need no
> time in the return but it needs to be ordered by datetime.
> Sylvain is there anyway you can give me a sample of how to put it in a
> subquery and then make the final conversion outside?
> Thanks.
>
> Daniel Crichton wrote:
> > phil2phil wrote on 29 Nov 2006 12:11:04 -0800:
> >
> > > Hi,
> > > I have the following query, it works fine in sql server 2005, but in
> > > sql server 2000 it errors out:
> > >
> > > DECLARE @.datestart datetime
> > > DECLARE @.dateend datetime
> > >
> > > set @.datestart = '2004-11-29'
> > > set @.dateend = '2006-11-29'
> > >
> > > select convert(varchar, (convert(datetime, convert(varchar,
> > > test.date_created,104), 104)), 1) dt, count(apples) fruits
> > > from testtable1 test, testtable2 od
> > > where test.code=od.code2
> > > and (test.method<>3)
> > > and test.date_created between '00:00 '+@.datestart AND '23:59:59
> > > '+@.dateend
> > > group by convert(datetime, convert(varchar, test.date_created, 104),
> > > 104)
> > > order by convert(datetime, convert(varchar, test.date_created, 104),
> > > 104) desc
> > >
> > > in sql 2000 i get the following error:
> > > Msg 8120, Level 16, State 1, Line 7
> > > Column 'test.DATE_CREATED' is invalid in the select list because it is
> > > not contained in either an aggregate function or the GROUP BY clause.
> > >
> > > In 2005 it returns the data fine.
> > >
> > > I need to have it return sorted by date and grouped by date, but the
> > > returned data needs to not display the time, thats why I was doing that
> > > conversion in the select. Any reason why it would work in one and not
> > > the other. Or anyway around this?
> > >
> > > Thanks.
> >
> >
> > The error occurs because the SQL Server 2000 compiler sees a difference
> > between the SELECT list and the GROUP BY, due to the extra CONVERT you have
> > in the SELECT. The GROUP BY must match the SELECT with the summaried columns
> > removed. If you use this it should work in 2000:
> >
> > DECLARE @.datestart datetime
> > DECLARE @.dateend datetime
> >
> > set @.datestart = '2004-11-29'
> > set @.dateend = '2006-11-29'
> >
> > select convert(varchar, (convert(datetime, convert(varchar,
> > test.date_created,104), 104)), 1) dt, count(apples) fruits
> > from testtable1 test, testtable2 od
> > where test.code=od.code2
> > and (test.method<>3)
> > and test.date_created between '00:00 '+@.datestart AND '23:59:59
> > '+@.dateend
> > group by convert(varchar, (convert(datetime, convert(varchar,
> > test.date_created,104), 104)), 1)
> > order by convert(datetime, convert(varchar, test.date_created, 104),
> > 104) desc
> >
> >
> >
> > Dan|||phil2phil wrote:
> Hi,
> I see that the SELECT and GROUPBY need to match, but the problem is I
> need to return the datetime without the date, thats why i was
> converting to varchar. And I can't do the Order by as varchar as then
> it's not ordering it anymore by datetime, which it needs to. If I try
> to make the SELECT and GROUPBY the same, it complains about the ORDERBY
> also needing to be the same, thats the part where i'm stuck, I need no
> time in the return but it needs to be ordered by datetime.
> Sylvain is there anyway you can give me a sample of how to put it in a
> subquery and then make the final conversion outside?
>
Instead of converting, use this to "strip off" the time portion of a
date/time:
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Easy: to create a subquery, you put the query between parenthesis and you
give it an name (or alias). In your case, this should look like:
Select convert(varchar, SQ.dt, 1) as dt, fruits
From (
select convert(datetime, convert(varchar, test.date_created,104), 104) dt,
count(apples) fruits
from testtable1 test, testtable2 od
where test.code=od.code2 and (test.method<>3)
and test.date_created between '00:00 '+@.datestart AND '23:59:59
'+@.dateend
group by convert(datetime, convert(varchar, test.date_created, 104), 104)
) as SQ
order by SQ.dt desc
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"phil2phil" <philtwophil@.yahoo.com> wrote in message
news:1164899356.494835.194380@.j72g2000cwa.googlegroups.com...
> Actually I got the subquery select working, used a select into
> statement, to put it into a temp table, and then had the temp table
> data return the first column as varchar, removing the time.
> Thank you for the help.
> phil2phil wrote:
>> Hi,
>> I see that the SELECT and GROUPBY need to match, but the problem is I
>> need to return the datetime without the date, thats why i was
>> converting to varchar. And I can't do the Order by as varchar as then
>> it's not ordering it anymore by datetime, which it needs to. If I try
>> to make the SELECT and GROUPBY the same, it complains about the ORDERBY
>> also needing to be the same, thats the part where i'm stuck, I need no
>> time in the return but it needs to be ordered by datetime.
>> Sylvain is there anyway you can give me a sample of how to put it in a
>> subquery and then make the final conversion outside?
>> Thanks.
>>
>> Daniel Crichton wrote:
>> > phil2phil wrote on 29 Nov 2006 12:11:04 -0800:
>> >
>> > > Hi,
>> > > I have the following query, it works fine in sql server 2005, but in
>> > > sql server 2000 it errors out:
>> > >
>> > > DECLARE @.datestart datetime
>> > > DECLARE @.dateend datetime
>> > >
>> > > set @.datestart = '2004-11-29'
>> > > set @.dateend = '2006-11-29'
>> > >
>> > > select convert(varchar, (convert(datetime, convert(varchar,
>> > > test.date_created,104), 104)), 1) dt, count(apples) fruits
>> > > from testtable1 test, testtable2 od
>> > > where test.code=od.code2
>> > > and (test.method<>3)
>> > > and test.date_created between '00:00 '+@.datestart AND '23:59:59
>> > > '+@.dateend
>> > > group by convert(datetime, convert(varchar, test.date_created, 104),
>> > > 104)
>> > > order by convert(datetime, convert(varchar, test.date_created, 104),
>> > > 104) desc
>> > >
>> > > in sql 2000 i get the following error:
>> > > Msg 8120, Level 16, State 1, Line 7
>> > > Column 'test.DATE_CREATED' is invalid in the select list because it
>> > > is
>> > > not contained in either an aggregate function or the GROUP BY clause.
>> > >
>> > > In 2005 it returns the data fine.
>> > >
>> > > I need to have it return sorted by date and grouped by date, but the
>> > > returned data needs to not display the time, thats why I was doing
>> > > that
>> > > conversion in the select. Any reason why it would work in one and
>> > > not
>> > > the other. Or anyway around this?
>> > >
>> > > Thanks.
>> >
>> >
>> > The error occurs because the SQL Server 2000 compiler sees a difference
>> > between the SELECT list and the GROUP BY, due to the extra CONVERT you
>> > have
>> > in the SELECT. The GROUP BY must match the SELECT with the summaried
>> > columns
>> > removed. If you use this it should work in 2000:
>> >
>> > DECLARE @.datestart datetime
>> > DECLARE @.dateend datetime
>> >
>> > set @.datestart = '2004-11-29'
>> > set @.dateend = '2006-11-29'
>> >
>> > select convert(varchar, (convert(datetime, convert(varchar,
>> > test.date_created,104), 104)), 1) dt, count(apples) fruits
>> > from testtable1 test, testtable2 od
>> > where test.code=od.code2
>> > and (test.method<>3)
>> > and test.date_created between '00:00 '+@.datestart AND '23:59:59
>> > '+@.dateend
>> > group by convert(varchar, (convert(datetime, convert(varchar,
>> > test.date_created,104), 104)), 1)
>> > order by convert(datetime, convert(varchar, test.date_created, 104),
>> > 104) desc
>> >
>> >
>> >
>> > Dan
>

Query with subqueries

Hi
Is it possible to group in a subquery.
declare @.ext char(4)
declare @.site int
declare @.calltype char(1)
set @.site = 1
set @.calltype = 'E'
select digits, count(*) as total,
(select count(*) from V2Tickets where durationofconversation>0 and
siteid=@.site and calltype=@.calltype and len(digits)=4) as besvaret,
(select count(*) from V2Tickets where durationofconversation=0 and
releasecause='OC' and siteid=@.site and calltype=@.calltype and len(digits)=4)
as optaget,
(select count(*) from V2Tickets where durationofconversation=0 and
durationofcall<40 and releasecause='RL' and siteid=@.site and
calltype=@.calltype and len(digits)=4) as opgivet,
(select count(*) from V2Tickets where durationofconversation=0 and
durationofcall>=40 and siteid=@.site and calltype=@.calltype and
len(digits)=4) as ubesvaret
from [V2tickets]
where
siteid=@.site and calltype=@.calltype and len(digits)=4
group by digits
order by 1
As you can see this gives me the correct total (colum 2) for digits, but all
the subqueries (column 3-6) computes the total.
1000 ,19167,370251,109377,58621,52809
1001 ,74,370251,109377,58621,52809
1002 ,139,370251,109377,58621,52809
1003 ,113,370251,109377,58621,52809
1004 ,81,370251,109377,58621,52809
1005 ,385,370251,109377,58621,52809
1006 ,307,370251,109377,58621,52809
1007 ,237,370251,109377,58621,52809
1008 ,122,370251,109377,58621,52809
1009 ,79,370251,109377,58621,52809
1010 ,255,370251,109377,58621,52809
1011 ,288,370251,109377,58621,52809
regards
HenryOn Mon, 14 Feb 2005 22:02:10 +0100, Henry wrote:

>Is it possible to group in a subquery.
>declare @.ext char(4)
>declare @.site int
>declare @.calltype char(1)
>set @.site = 1
>set @.calltype = 'E'
>select digits, count(*) as total,
>(select count(*) from V2Tickets where durationofconversation>0 and
>siteid=@.site and calltype=@.calltype and len(digits)=4) as besvaret,
>(select count(*) from V2Tickets where durationofconversation=0 and
>releasecause='OC' and siteid=@.site and calltype=@.calltype and len(digits)=4
)
>as optaget,
>(select count(*) from V2Tickets where durationofconversation=0 and
>durationofcall<40 and releasecause='RL' and siteid=@.site and
>calltype=@.calltype and len(digits)=4) as opgivet,
>(select count(*) from V2Tickets where durationofconversation=0 and
>durationofcall>=40 and siteid=@.site and calltype=@.calltype and
>len(digits)=4) as ubesvaret
>from [V2tickets]
>where
>siteid=@.site and calltype=@.calltype and len(digits)=4
>group by digits
>order by 1
>As you can see this gives me the correct total (colum 2) for digits, but al
l
>the subqueries (column 3-6) computes the total.
(snip)
Hi Henry,
You can fix this by expanding the subqueries with a reference to the row
in the outer query:
SELECT digits
, COUNT(*) AS total
,(SELECT COUNT(*)
FROM V2Tickets AS b
WHERE b.durationofconversation > 0
AND b.siteid = @.site
AND b.calltype = @.calltype
AND LEN(b.digits) = 4
AND b.digits = a.digits) AS besvaret
(other subqueries snipped)
FROM V2tickets AS a
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
GROUP BY digits
ORDER BY digits
However, in your case there's a faster method to get the various counts:
SELECT digits
, COUNT(*) AS total
, COUNT(CASE WHEN durationofconversation > 0
THEN 1 END) AS besvaret
, COUNT(CASE WHEN durationofconversation = 0
AND releasecouse = 'OC'
THEN 1 END) AS optaget
, COUNT(CASE WHEN durationofconversation = 0
AND releasecouse = 'RL'
AND durationofcall < 40
THEN 1 END) AS opgivet
, COUNT(CASE WHEN durationofconversation = 0
AND durationofcall >= 40
THEN 1 END) AS ubesvaret
FROM V2tickets
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
GROUP BY digits
ORDER BY digits
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> However, in your case there's a faster method to get the various counts:
> SELECT digits
> , COUNT(*) AS total
> , COUNT(CASE WHEN durationofconversation > 0
> THEN 1 END) AS besvaret
> , COUNT(CASE WHEN durationofconversation = 0
> AND releasecouse = 'OC'
> THEN 1 END) AS optaget
> , COUNT(CASE WHEN durationofconversation = 0
> AND releasecouse = 'RL'
> AND durationofcall < 40
> THEN 1 END) AS opgivet
> , COUNT(CASE WHEN durationofconversation = 0
> AND durationofcall >= 40
> THEN 1 END) AS ubesvaret
> FROM V2tickets
> WHERE siteid = @.site
> AND calltype = @.calltype
> AND LEN(digits) = 4
> GROUP BY digits
> ORDER BY digits
>
Hi Hugo
Hey, that was an excellent suggestion, that simplifies this quite a lot, and
it even works ;o)
I was think about, if it is possible to do calculations on the result
"fields" e.g
unidentifiedcalls = total - (besvaret+optaget+opgivet+ubesvaret)
or
percent besvaret = (total-unidentifiedcalls)/100 * besvaret
This is like a hole new world, I used to do this "manually" in my
application.
regards
Henry|||On Tue, 15 Feb 2005 19:06:48 +0100, Henry wrote:

>Hey, that was an excellent suggestion, that simplifies this quite a lot, an
d
>it even works ;o)
>I was think about, if it is possible to do calculations on the result
>"fields" e.g
>unidentifiedcalls = total - (besvaret+optaget+opgivet+ubesvaret)
>or
>percent besvaret = (total-unidentifiedcalls)/100 * besvaret
Hi Henry,
That is possible, but not directly. Since the column aliases are applied
after the select clause is evaluated (conceptually at least - optimizers
may and will change order of evaluation, but without changing the result),
you can't use these aliases is any part of the SELECT.
The workaround is to use a derived table.
So this query would fail:
SELECT Column1 + Column2 AS Sum1
Column1 - Column2 AS Diff1,
Sum1 * Diff1 AS Prod1
FROM SomeTable
But this query would work
SELECT Sum1, Diff1,
Sum1 * Diff1 AS Prod1
FROM (SELECT Column1 + Column2 AS Sum1
Column1 - Column2 AS Diff1
FROM SomeTable) AS D
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query with CASE and NULL values

Hi, please, take a look to this query:

declare @.IDCliente int
declare @.Cliente varchar(50)
declare @.IDUsuario int
declare @.IDUsuarioAlta int

set @.IDcliente = 0
set @.Cliente = ''
set @.IDUsuario = 0
set @.IDUsuarioAlta = 0

select * from cliente
where
(IDUsuario = CASE @.IDUsuario WHEN 0 THEN IDUsuario ELSE @.IDUsuario END or idusuario is null)
AND (IDUsuarioAlta = CASE @.IDUsuarioAlta WHEN 0 THEN IDUsuarioAlta ELSE @.IDUsuarioAlta END or idusuarioalta is null)
AND idCliente = CASE @.idCliente WHEN 0 THEN idCliente ELSE @.idCliente END
AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

Cliente
IDCliente Cliente IDUsusario IDUsuarioAlta
1 Esteban 1 2
2 Jose 3 1
3 Mario 2 NULL
4 Pedro NULL 2
5 NULL 1 2

Its work fine, except for the NULL values. What can I do to fix it ?

thanks

Here you go:

Code Snippet

select IDCliente, Cliente,

case when IDUsusario is null then '' --or whatever you want in place of null

else IDUsusario

end as IDUsusario,

case when IDUsuarioAlta is null then '' -- same thing here

else IDUsuarioAlta

end as IDUsuarioAlta

from cliente

where

(IDUsuario = CASE @.IDUsuario WHEN 0 THEN IDUsuario ELSE @.IDUsuario END or idusuario is null)

AND (IDUsuarioAlta = CASE @.IDUsuarioAlta WHEN 0 THEN IDUsuarioAlta ELSE @.IDUsuarioAlta END or idusuarioalta is null)

AND idCliente = CASE @.idCliente WHEN 0 THEN idCliente ELSE @.idCliente END

AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

|||

I'm sorry, I think I didn't explain my self.

The problem is in the WHERE part, No in the SELECT.

When @.IDUsuario has a value, then the query return the record with the NULL value, and that is not correct. If I take off or idusuario is null

then the record with de NULL value is never return.

thanks and sorry my english !.

|||

AH, gotcha.

How about this then:

Code Snippet

select *

from cliente

where

(IDUsuario = CASE @.IDUsuario WHEN 0 THEN IDUsuario

ELSE @.IDUsuario

END

or (idusuario is null and @.IDUsuario = 0) )

AND (IDUsuarioAlta = CASE @.IDUsuarioAlta WHEN 0 THEN IDUsuarioAlta

ELSE @.IDUsuarioAlta

END

or (idusuarioalta is null and @.IDUsuarioAlta = 0) )

AND (idCliente = CASE @.idCliente WHEN 0 THEN idCliente ELSE @.idCliente END

or (idCliente is null and @.idCliente = 0) )

AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

|||

This might be a little cleaner:

Code Snippet

select *

from cliente

where

((@.IDUsuario <> 0 and idusuario = @.IDUsuario ) or

(@.IDUsuario = 0))

AND ((@.IDUsuarioAlta <> 0 and IDUsuarioAlta = @.IDUsuarioAlta ) or

(@.IDUsuarioAlta = 0))

AND ((@.idCliente <> 0 and idCliente = @.idCliente ) or

(@.idCliente = 0))

AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

|||*** !, it work perfect, i think you know this !! like we say in Argentina: "Sos Groso !!"..... is like say You are Big !... I think so..sql

Query to merge duplicate records

Hello,

I have the following Query:

1 declare @.StartDatechar(8)
2 declare @.EndDatechar(8)
3 set @.StartDate ='20070601'
4 set @.EndDate ='20070630'
5 SELECT Initials, [Position], DATEDIFF(mi,[TimeOn],[TimeOff])AS ProTime
6 FROM LogTableWHERE
7 [TimeOn]BETWEEN @.StartDateAND @.EndDateAND
8 [TimeOff]BETWEEN @.StartDateAND @.EndDate
9 ORDER BY [Position],[Initials]ASC

The query returns the following data:

Position Initials ProTime
---------------- --- ----
ACAD JJ 127
ACAD JJ 62
ACAD KK 230
ACAD KK 83
ACAD KK 127
ACAD TD 122
ACAD TJ 127

What I'm having trouble with is the fact that I need to return a results that has the totals for each set of initials for each position. For Example, the final output that I'm looking to get is the following:

Postition Initials ProTime

ACAD JJ 189
ACAD KK 440
ACAD TD 122
ACAD TJ 127

Any assistance greatly appreciated.

Use the GROUP BY syntax, and SUM() the time diffs.

Your query will look like this:

SELECT Initials, [Position], SUM(DATEDIFF(mi,[TimeOn],[TimeOff])) AS ProTime
FROM LogTableWHERE
[TimeOn]BETWEEN @.StartDateAND @.EndDateAND[TimeOff]BETWEEN @.StartDateAND @.EndDate

GROUP BY Initials, Position

Leave off the ORDER BY from your statement - you see that the SQL is grouping by the columns you wish to merge and summing the variable data.

|||

Try:

SELECT Initials, [Position], SUM(DATEDIFF(mi,[TimeOn],[TimeOff]))AS ProTime
FROM LogTable

WHERE
[TimeOn]BETWEEN @.StartDateAND @.EndDateAND
[TimeOff]BETWEEN @.StartDateAND @.EndDate

GROUP BY Initials, [Position]
ORDER BY [Position],[Initials]ASC

|||

That was it! I knew it was going to be simple, but I could not "see the forest for the trees".

Thanks

Dan

Query that works in Management Studio, fails in Reporting Services

I can run the following query in Management Studio, but get the error listed below when I run it from the data tab in Reporting Services:

declare @.starttime as datetime
declare @.endtime as datetime
declare @.timezone as integer
declare @.date as datetime

set @.timezone = 1
set @.date = '5/1/2007'

set @.starttime = dateadd(hh, @.timezone, @.date)
set @.endtime = dateadd(d, 1, @.starttime)

select @.Starttime, @.endtime from site

Error Message:

TITLE: Microsoft Report Designer

An error occurred while executing the query.
The variable name '@.starttime' has already been declared. Variable names must be unique within a query batch or stored procedure.

ADDITIONAL INFORMATION:

The variable name '@.starttime' has already been declared. Variable names must be unique within a query batch or stored procedure. (Microsoft SQL Server, Error: 134)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=134&LinkId=20476

BUTTONS:

OK

What I am trying to accomplish is the ability for users to select which time zone they want the data in the report to display in. To do this, I created a timezone parameter that has the offset from Central Time (which is how all data is stored in our database).

Any help would be greatly appreciated!

Add the word "my" as a prefix to your variables:

DECLARE @.myStartTime smalldate time

etc...

Adamus

|||

Hi,

Please note that expressions in SSRS are case sensitive. In the code above, the starttime is declared as @.starttime and while referring to it in the select clause, you have typed it as @.Starttime. This is causing the conflict. SSRS created a report parameter, Starttime. Since SQL is not case sensitive, while execution it got two declarations for @.starttime and hence the error that you are facing.

After changing the Select clause, delete the parameter from Reports --> Parameters menu option and also from the dataset --> parameters Tab and your query should work.

Also, there is no need for the from clause.

HTH.

Regards,

Ashish

|||They are in fact case-sensitive and I've corrected it and the query now runs is RS. Thank you so much!!!

Query Syntax Error

This thing is giving me 'Incorrect syntax near the keyword declare'. What's the correct form?

declare @.Query varchar(8000)

set @.Query = 'insert into PortfolioStock (PortfolioID, StockSymbol) select ' + cast(@.Portfolio as varchar) + ', StockSymbol from PortfolioStock where StockSymbol in (''' + replace(ltrim(rtrim(@.Textbox)), ' ', ''', ''') + ''')'

exec @.Queryexec(@.Query)