Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
ABC
1ax
2by
3c1
4nullnull
How do i get this type of resultset ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200509/1
alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via droptable.com" <forum@.droptable.com> wrote in message
news:53B523BC4BB04@.droptable.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
A B C
--
1 a x
2 b y
3 c 1
4 null null
How do i get this type of resultset ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200509/1alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via droptable.com" <forum@.droptable.com> wrote in message
news:53B523BC4BB04@.droptable.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200509/1sql

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
A B C
--
1 a x
2 b y
3 c 1
4 null null
How do i get this type of resultset ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:53B523BC4BB04@.SQLMonster.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1

Distinct Type Count

I have a weird MDX request and I'm unsure of how to accomplish this.

My relation table has two fields Department(int) and Employee Type(int)

I need to get a distinct count of the number of distinct employee types per Department.

E.g for the data below: (calculated member) DistinctTypeCount=4 (when dep=1) (four distinct types of employees in this department

Dep. Emp. Type

-

1 23

1 2

1 4

1 23

1 4

1 4

1 10

Can anyone suggest an mdx query for this calculated member? If I redesigning the relational view on which the cube is based makes things easier I can definitely go that route.

>My relation table has two fields Department(int) and Employee Type(int)

How the fields are exposed in your UDM? Are they dimension attributes? What design has the dimension?

|||yes they are dimension attributes.

distinct row count in a table.

Hi,

I want a count of distinct rows in a table through a single query -- is it possible?

eg.

table-

create table ch1 (a int, b int, c int, d int)

insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)

Here distinct row count in a table is 3 which I want to achieve thro a query.

if I do

select count(distinct a) from ch1 it works fine and gives me output as 2.

but this is not working

select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

Please reply.

Cheers!
Ram.Hi,

I want a count of distinct rows in a table through a single query -- is it possible?

eg.

table-

create table ch1 (a int, b int, c int, d int)

insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)

Here distinct row count in a table is 3 which I want to achieve thro a query.

if I do

select count(distinct a) from ch1 it works fine and gives me output as 2.

but this is not working

select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

Please reply.

Cheers!
Ram.|||Try this...

SELECT COUNT(*)
FROM
(SELECT DISTINCT * FROM ch1)ch1|||Or
SELECT COUNT(DISTINCT *) AS Distinct_Rows FROM ch1|||:shocked:
select sum(case when count(*)>1 then 1 else 1 end)
from ch1 group by a,b,c,d|||threads merged

ramshree, please do not post the same question into multiple forums|||I have an example below: You should use the "having" clause.
db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1|||I have an example below: You should use the "having" clause.
db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1I think that you're "close, but no banana" on this... The code that you posted will actually count the non-distinct rows (how many rows have at least one duplicated row elsewhere).

-PatP

Disregard some parameters

Hi,

I am building a search Query which takes 7 parameters:

Lets call them @.P1 .. @.P7 (all Int's)

The query is a simple select query like:

SELECT * FROM MyTable WHERE (Field1 = @.P1) AND (Field2 = @.P2) ...

My problem is that if some parameters are -1 they shall be disregarded.

Is there any way to set a parameter to a value meaning "Anything", or do I
have to
remove the criteria from the select clause?

(In the latter case I cannot use a stored procedure, which is what I prefer)

Cheers
GunnarGunnar,

if your columns do not contain NULLs, then you can use

WHERE Field1 BETWEEN COALESCE(NULLIF(@.P1,-1),-2147483648) AND
COALESCE(NULLIF(@.P1,-1),2147483647)

This WHERE clause assumes that the value "-1" is your indication of a
missing parameter. If you use NULL instead of -1, then you can replace
NULLIF(@.P1,-1) with @.P1. It also assumes the int datatype. If the column
is of a different integer datatype (for example bigint), then the
minimum and maximum value need to be adjusted.

If your column do contain NULLs, then you can use

WHERE CASE WHEN @.P1=-1 THEN 1
CASE WHEN Field1=@.P1 THEN 1
ELSE 0 END = 1

But in general, the first approach will perform better.

Hope this helps,
Gert-Jan

Gunnar Liknes wrote:
> Hi,
> I am building a search Query which takes 7 parameters:
> Lets call them @.P1 .. @.P7 (all Int's)
> The query is a simple select query like:
> SELECT * FROM MyTable WHERE (Field1 = @.P1) AND (Field2 = @.P2) ...
> My problem is that if some parameters are -1 they shall be disregarded.
> Is there any way to set a parameter to a value meaning "Anything", or do I
> have to
> remove the criteria from the select clause?
> (In the latter case I cannot use a stored procedure, which is what I prefer)
> Cheers
> Gunnar

--
(Please reply only to the newsgroup)|||Hi,

One way to do the trick is to write:

SELECT * FROM MyTable WHERE ((@.P1 = -1) OR (Field1 = @.P1)) AND ...

If @.P1 is -1, the OR-clause will simply be true for all rows, effectively
ignoring the comparison with Field1.

-Jrgen

"Gunnar Liknes" <g_liknes.Tabortunderscores@.g_lobal-satcom.com> skrev i en
meddelelse news:4110a27d$1@.news.broadpark.no...
> Hi,
> I am building a search Query which takes 7 parameters:
> Lets call them @.P1 .. @.P7 (all Int's)
> The query is a simple select query like:
> SELECT * FROM MyTable WHERE (Field1 = @.P1) AND (Field2 = @.P2) ...
> My problem is that if some parameters are -1 they shall be disregarded.
> Is there any way to set a parameter to a value meaning "Anything", or do I
> have to
> remove the criteria from the select clause?
> (In the latter case I cannot use a stored procedure, which is what I
prefer)
> Cheers
> Gunnar
>|||"Gert-Jan Strik" wrote

> if your columns do not contain NULLs, then you can use
> WHERE Field1 BETWEEN COALESCE(NULLIF(@.P1,-1),-2147483648) AND
> COALESCE(NULLIF(@.P1,-1),2147483647)
> This WHERE clause assumes that the value "-1" is your indication of a
> missing parameter. If you use NULL instead of -1, then you can replace
> NULLIF(@.P1,-1) with @.P1. It also assumes the int datatype. If the column
> is of a different integer datatype (for example bigint), then the
> minimum and maximum value need to be adjusted.
> If your column do contain NULLs, then you can use
> WHERE CASE WHEN @.P1=-1 THEN 1
> CASE WHEN Field1=@.P1 THEN 1
> ELSE 0 END = 1
> But in general, the first approach will perform better.

Thank you both (Gert-Jan and Jrgen) for two excellent working solutions to
my problem. The COALESCE function was interesting. Will it perform better
than the "WHERE ((@.P1 = -1) OR (Field1 = @.P1)) "
approach?

Thanks,
Gunnar|||Gunnar Liknes wrote:
> Thank you both (Gert-Jan and Jrgen) for two excellent working solutions to
> my problem. The COALESCE function was interesting. Will it perform better
> than the "WHERE ((@.P1 = -1) OR (Field1 = @.P1)) "
> approach?
> Thanks,
> Gunnar

Yes, because if the column is indexed, index seeks can be used. The OR
solution needs an index scan.

Gert-Jan
--
(Please reply only to the newsgroup)|||Gunnar Liknes (g_liknes.Tabortunderscores@.g_lobal-satcom.com) writes:
> Thank you both (Gert-Jan and Jrgen) for two excellent working solutions
> to my problem. The COALESCE function was interesting. Will it perform
> better than the "WHERE ((@.P1 = -1) OR (Field1 = @.P1)) " approach?

Permit me to modify Gert-Jan's enthusiasm a little. It may perform better,
but I have not always be successful with it. And if the columns is not
indexed then it not matter much anyway.

The only way to find out is to benchmark.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Gert-Jan Strik" wrote
> Gunnar Liknes wrote:
> > Thank you both (Gert-Jan and Jrgen) for two excellent working solutions
to
> > my problem. The COALESCE function was interesting. Will it perform
better
> > than the "WHERE ((@.P1 = -1) OR (Field1 = @.P1)) "
> > approach?

> Yes, because if the column is indexed, index seeks can be used. The OR
> solution needs an index scan.

Does MS SQL perform complete boolean evaluations? If (@.P1=-1) it should
not have to check if (Field1 = @.P1) because the result of the statement is
already determined.

Gunnar|||Gunnar Liknes (g_liknes.Tabortunderscores@.g_lobal-satcom.com) writes:
> Does MS SQL perform complete boolean evaluations? If (@.P1=-1) it should
> not have to check if (Field1 = @.P1) because the result of the statement is
> already determined.

The answer is that, yes, SQL Server is able to make logical shortcuts,
but that is not applicable here.

When SQL Server builds a query plan for a stored procedure, it builds
the plan for the entire procedure at once, and is thus blind to what
the actual values of variables and parameters at the time of the statement.
It does take in regard the values of parameter to build the plan, but
since it don't know whether parameter changes value in the procedure or
not, SQL Server can choose a plan which would yield the wrong result if
the parameter is changed. Moreover, since the plan is cached, the procedure
might be called with some other values the next time.

Thus if you have:

SELECT *
FROM tbl
WHERE (field1 = @.p1 OR @.p1 IS NULL)
AND (field2 = @.p2 OR @.p2 IS NULL)

It cannot look at @.p1 and say "Hey @.p1 is NULL, I don't have to test
Field1". So it must pick a plan where it accesses field1. No, once it
comes to the statement it could opt to not actually check field1, but
the cost is not the check - the cost is the access. In this case,
the optimizer will most like to scan the table from left to right.

Here is another example:

SELECT *
FROM tbl
WHERE @.p1 = 0 OR EXISTS (SELECT *
FROM tbl2
WHERE tbl.col = tbl2.col)

Here, if @.p1 is 0 we retrieve all rows from tbl, but if @.p1 is 1 only
rows which has a matching row in tbl2 are to be returned. In this example,
SQL Server is actually able to avoid accessing tbl2 if @.p1 is 0, since
once @.p1 is evaluated, the other branch can be pruned. Note here that
is not your C-style of shortcutting - you get the same result if you
have the condition on @.p1 last.

To read more about this topic, I have an article on my web site:
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" wrote.
> Gunnar Liknes writes:

> > Does MS SQL perform complete boolean evaluations? If (@.P1=-1) it should
> > not have to check if (Field1 = @.P1) because the result of the statement
is
> > already determined.

> The answer is that, yes, SQL Server is able to make logical shortcuts,
> but that is not applicable here.

<snip explanation
> To read more about this topic, I have an article on my web site:
> http://www.sommarskog.se/dyn-search.html.

Thank you Erland, your article was very helpful. I also found the topics of
your
other articles very interresting. I'll read the one about Arrays & Lists
when I have
some time.

Regarding search. I'll have to wait until we get more data in our database
before
I decide which search alternative to use. For now I stick to IF / OR.

Cheers
Gunnar

Displaying seconds value as [mm]:ss

Hello
I am returning a int value from a database which is for an elasped
time in seconds. I want to convert this to mm:ss, however I dont want
it roll over to hours or days.
I trying something along the lines of:
=String.Format("{0:0:mm:ss}",CDate("0:0:0").AddSeconds(Sum(Fields!Airtime.Value)))
However this still allows values to go to hours/days, just doesn't
display them
So for example a value returned from the database of 19257 (seconds)
should be displayed as 320:57 ([mm]:ss)
This is achived in excel by
19257 / 24 = 802.375 / 60 = 13.37291667 /60 = 0.222881944
Then change the format on the cell to [mm]:ss
Then 320:57 is displayed.
Any ideas would be great
Thanks
DavidYou could create custom function, something like this:
Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = seconds - ss * 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
and then call it from textbox expression:
=Code.Seconds2mmss(Sum(Fields!Airtime.Value))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <david.glasgow@.unison.co.nz> wrote in message
news:9f43b964.0408112015.79b5c9b9@.posting.google.com...
> Hello
> I am returning a int value from a database which is for an elasped
> time in seconds. I want to convert this to mm:ss, however I dont want
> it roll over to hours or days.
> I trying something along the lines of:
> =String.Format("{0:0:mm:ss}",CDate("0:0:0").AddSeconds(Sum(Fields!Airtime.Value)))
> However this still allows values to go to hours/days, just doesn't
> display them
> So for example a value returned from the database of 19257 (seconds)
> should be displayed as 320:57 ([mm]:ss)
> This is achived in excel by
> 19257 / 24 = 802.375 / 60 = 13.37291667 /60 = 0.222881944
> Then change the format on the cell to [mm]:ss
> Then 320:57 is displayed.
> Any ideas would be great
> Thanks
> David|||Thanks for that, the final code that returned the result was:
Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = (seconds - ss) / 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
Thanks again for your help

display values depending on a rule

hello,
i have a table with 3 fields
CustNr (int)
artikleNr (int)
pieces (int)
simple example - all customer have bueyed the article with the nr 11
101 11 8
102 11 3
101 11 4
102 11 20
103 11 3
104 11 15
104 11 25
i want to display a information in the following way
if customer has < 10 pieces display 0
if customer has 10 -20 display real value 1...20
if customer has more then 20 display 20
101 8 + 4 =12 display 12
102 3 + 20=23 display 20
103 3 display 0
104 15+25=40 display 20
thanksSomething like this?
SELECT CustNr,
'SomeColumn' = CASE WHEN SUM(pieces) < 10 THEN 0
WHEN SUM(pieces) BETWEEN 10 AND 20 THEN SUM(pieces)
WHEN SUM(pieces) > 20 THEN 20
ELSE NULL END
FROM YourTable
WHERE artikleNr = 11 /* I don't know if this is a param that would limit
the data returned, or if you want to group by this column as well (in
addition to CustNr) */
Keith Kratochvil
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:6A10DB32-0D1E-4C48-B657-C5C83A78DDF7@.microsoft.com...
> hello,
> i have a table with 3 fields
> CustNr (int)
> artikleNr (int)
> pieces (int)
> simple example - all customer have bueyed the article with the nr 11
> 101 11 8
> 102 11 3
> 101 11 4
> 102 11 20
> 103 11 3
> 104 11 15
> 104 11 25
> i want to display a information in the following way
> if customer has < 10 pieces display 0
> if customer has 10 -20 display real value 1...20
> if customer has more then 20 display 20
>
> 101 8 + 4 =12 display 12
> 102 3 + 20=23 display 20
> 103 3 display 0
> 104 15+25=40 display 20
> thanks|||thanks Keith, it works perfect.
best regards
"Keith Kratochvil" wrote:

> Something like this?
> SELECT CustNr,
> 'SomeColumn' = CASE WHEN SUM(pieces) < 10 THEN 0
> WHEN SUM(pieces) BETWEEN 10 AND 20 THEN SUM(pieces)
> WHEN SUM(pieces) > 20 THEN 20
> ELSE NULL END
> FROM YourTable
> WHERE artikleNr = 11 /* I don't know if this is a param that would limit
> the data returned, or if you want to group by this column as well (in
> addition to CustNr) */
> --
> Keith Kratochvil
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:6A10DB32-0D1E-4C48-B657-C5C83A78DDF7@.microsoft.com...
>
>

display string having length greater than 255

I have a created a table and entered data into the table as follows:

CREATE TABLE t ( id INT , txtcol varchar(1000) )

INSERT INTO t ( id , txtcol ) VALUES ( 1 , 'ATXR_SOURCE_ID,CDDL_AG_PRICE,CDDL_ALLOW,CDDL_ALTDP_EXCD_ID,CDDL_CAP_IND,CDDL_CHG_AMT,CDDL_COINS_AMT,CDDL_CONSIDER_CHG,CDDL_COPAY_AMT,CDDL_DED_AC_NO,CDDL_DED_AMT,CDDL_DIS_PA_LIAB,CDDL_DISALL_AMT,CDDL_DISALL_EXCD,CDDL_DISC_AMT,CDDL_DP_PRICE,CDDL_FROM_DT,CDDL_PAID_AMT,CDDL_PF_PRICE,CDDL_PR_PYMT_AMT,CDDL_PRICE_IND,CDDL_REF_IND,CDDL_RISK_WH_AMT,CDDL_SB_PYMT_AMT,CDDL_SURF,CDDL_TOOTH_BEG,CDDL_TOOTH_END,CDDL_TOOTH_NO,CDDL_TOT_PA_LIAB,CDDL_UNITS,CDDL_UNITS_ALLOW,CGCG_ID,CGCG_RULE,DPCG_DP_ID_ALT,DPDP_ID,DPTC_CD,PDVC_LOBD_PTR,PSDC_ID,UTUT_CD' )

Now if i select data using the query below the txtcol field displays only 255 characters :

SELECT * FROM t

Y is this happening?

This is happening because of your client application (QA, SSMS or something else) has a setting not to display field values grater than 255 bytes. Inspect application options, then.