Distinct Count

Say I have the following 3 peices of example data, I'm looking to be
able to get the distinct node count from each fragment, does xquery
have anything like this? I found distinct-value, but that looks at the
value of the element also. I have placed the counts after each
fragment that I'm looking for... (<profile> always exists so not having
that in the count is fine, I have included it in the following counts)
Each fragment represents one row.
I'm looking for something like (SQL 2005)
Select xmlData.query('count ** magic **') from Table
<profile>
<hairColor>Brown</hairColor>
<hairColor>None</hairColor>
<height>5'9''</height>
<height>6'6''</height>
<sex>M</sex>
</profile>
-- count: 4
<profile>
<birthDate>9-99-99</birthDate>
<height>6'6''</height>
<height>5'9''</height>
<sex>M</sex>
</profile>
-- count: 4
<profile>
<birthDate />
<sex>F</sex>
<sex>M</sex>
</profile>
-- count: 3
Thanks for the help,
JBSelect xmlData.query('count(distinct-values(for $a in /profile/* return
local-name($a)))') from Table|||Awesome, thanks alot!

Displaying the time in hh:mm PM

Hi all
How display the time in requried format
example acutal time is 3:15:23PM
requried format is 3:15Pm how to display in that format please give the reply for our requrimentin formula field :

currenttime - second (currenttime)

Displaying the output

Hi,
I want to write a query such that part of the output is displayed and the
complete output is put into a temp table.
For example i have a table customer and i want the total customer details to
be put in a temp table and the top 5 to be displayed.
select * into #temp1 from customer
select top5 * from customer
I run this two queries for my result, but is there any way to get the result
in one query.
Thanks,
MaheshMahesh wrote:
> Hi,
> I want to write a query such that part of the output is displayed
> and the complete output is put into a temp table.
> For example i have a table customer and i want the total customer
> details to be put in a temp table and the top 5 to be displayed.
> select * into #temp1 from customer
> select top5 * from customer
> I run this two queries for my result, but is there any way to get the
> result in one query.
> Thanks,
> Mahesh
No. You are talking about two different queries. You need to use an
ORDER BY with the TOP clause, unless you want somewhat random data
returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi David,
My requirement is when i run a query it should go to temp table (e.g.
#temp1) and also display the output. This should happen on a single run of
query. Can you please help me in this.
Thanks,
Mahesh
"David Gugick" wrote:

> Mahesh wrote:
> No. You are talking about two different queries. You need to use an
> ORDER BY with the TOP clause, unless you want somewhat random data
> returned.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Mahesh
can you explain why you want to do that.
however it appears to be possible with functoon or procedure
--procedure
CREATE PROC DISPLAY
AS
select * into #temp1 from customer
select top5 * from customer ORDER BY CUSTOMER_ID
RETURN
GO
EXEC DISPLAY
GO
--function: you can't use temp table here but table variable
CREATE FUNCTION DISPLAYFUNCTION()
RETURNS @.TOPTABLE TABLE(EName varchar(100), mgid int)
AS
BEGIN
INSERT INTO @.TOPTABLE
SELECT * FROM EMPLOYEES
RETURN
END
GO
SELECT TOP 5 * FROM DISPLAYFUNCTION()
GO
Regards
R.D
--Knowledge gets doubled when shared
"Mahesh" wrote:

> Hi,
> I want to write a query such that part of the output is displayed and t
he
> complete output is put into a temp table.
> For example i have a table customer and i want the total customer details
to
> be put in a temp table and the top 5 to be displayed.
> select * into #temp1 from customer
> select top5 * from customer
> I run this two queries for my result, but is there any way to get the resu
lt
> in one query.
> Thanks,
> Mahesh|||space between top and 5 is missing; TOP 5
--
Regards
R.D
--Knowledge gets doubled when shared
"R.D" wrote:
> Mahesh
> can you explain why you want to do that.
> however it appears to be possible with functoon or procedure
> --procedure
> CREATE PROC DISPLAY
> AS
> select * into #temp1 from customer
> select top5 * from customer ORDER BY CUSTOMER_ID
> RETURN
> GO
> EXEC DISPLAY
> GO
>
> --function: you can't use temp table here but table variable
> CREATE FUNCTION DISPLAYFUNCTION()
> RETURNS @.TOPTABLE TABLE(EName varchar(100), mgid int)
> AS
> BEGIN
> INSERT INTO @.TOPTABLE
> SELECT * FROM EMPLOYEES
> RETURN
> END
> GO
> SELECT TOP 5 * FROM DISPLAYFUNCTION()
> GO
>
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Mahesh" wrote:
>|||RD, this is requires because i will be running multiple queries to analyze
the data, while i am running i would like to see the data and then when at
the end if it is stored in a temp table then i can run a simple select on
that temp table instead of running the whole query again. can you please hel
p
me on this.
Thanks,
Mahesh
"R.D" wrote:
> Mahesh
> can you explain why you want to do that.
> however it appears to be possible with functoon or procedure
> --procedure
> CREATE PROC DISPLAY
> AS
> select * into #temp1 from customer
> select top5 * from customer ORDER BY CUSTOMER_ID
> RETURN
> GO
> EXEC DISPLAY
> GO
>
> --function: you can't use temp table here but table variable
> CREATE FUNCTION DISPLAYFUNCTION()
> RETURNS @.TOPTABLE TABLE(EName varchar(100), mgid int)
> AS
> BEGIN
> INSERT INTO @.TOPTABLE
> SELECT * FROM EMPLOYEES
> RETURN
> END
> GO
> SELECT TOP 5 * FROM DISPLAYFUNCTION()
> GO
>
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Mahesh" wrote:
>|||David already answered. One query cannot both store the results in a temp ta
ble *and* display the
results. It is one of those, but not both. How about pushing all data into t
he temp table and then
SELECT TOP 5 from there?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mahesh" <Mahesh@.discussions.microsoft.com> wrote in message
news:E23898C7-8D15-492B-9CDE-0CFE24AC44CA@.microsoft.com...
> Hi David,
> My requirement is when i run a query it should go to temp table (e.g.
> #temp1) and also display the output. This should happen on a single run of
> query. Can you please help me in this.
> Thanks,
> Mahesh
> "David Gugick" wrote:
>|||Either you got to use sprocs or use two queries at a time with GO in betwee
n
--
Regards
R.D
--Knowledge gets doubled when shared
"Mahesh" wrote:
> RD, this is requires because i will be running multiple queries to analyze
> the data, while i am running i would like to see the data and then when at
> the end if it is stored in a temp table then i can run a simple select on
> that temp table instead of running the whole query again. can you please h
elp
> me on this.
> Thanks,
> Mahesh
> "R.D" wrote:
>|||Hi,
Try this. I did it with the northwind DB
select * into #temp1 from customers where customerid in (select top 5
customerid from customers)
Nils
"Mahesh" <Mahesh@.discussions.microsoft.com> schrieb im Newsbeitrag
news:1E806702-EB01-459C-AE86-B9E873C8DA22@.microsoft.com...
> Hi,
> I want to write a query such that part of the output is displayed and
> the
> complete output is put into a temp table.
> For example i have a table customer and i want the total customer details
> to
> be put in a temp table and the top 5 to be displayed.
> select * into #temp1 from customer
> select top5 * from customer
> I run this two queries for my result, but is there any way to get the
> result
> in one query.
> Thanks,
> Mahesh|||nils
> select * into #temp1 from customers where customerid in (select top 5
> customerid from customers)
this inserts only five rows while Mahesh wants all rows to temp and five
rows to be displayed.
--
Regards
R.D
--Knowledge gets doubled when shared
"Nils Wolf" wrote:

> Hi,
> Try this. I did it with the northwind DB
> select * into #temp1 from customers where customerid in (select top 5
> customerid from customers)
> Nils
>
> "Mahesh" <Mahesh@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:1E806702-EB01-459C-AE86-B9E873C8DA22@.microsoft.com...
>

displaying smallmoney to 2 decimal places

Heres an example of a query with the display
SELECT * FROM STOCKks WHERE Price < Cost; Both price and cost are
smallmoney datatypes. They display with 4 digits to the left of decimal pt.
How do I set it to display only 2 digits for both colums?
THanks, Kim
On Fri, 28 Oct 2005 18:05:01 -0700, Kim wrote:

>Heres an example of a query with the display
>SELECT * FROM STOCKks WHERE Price < Cost; Both price and cost are
>smallmoney datatypes. They display with 4 digits to the left of decimal pt.
>How do I set it to display only 2 digits for both colums?
>THanks, Kim
Hi Kim,
The best advise is to:
a) do formatting in the front end, not at the server, and
b) avoid the use of money and smallmoney datatype, becuase they are
proprietary and non-portable AND because they suffer from some strange
rounding issues. Use DECIMAL (17,2) instead.
If you must use money and must do the formatting at the server, then
SELECT CAST (Price AS DECIMAL(17,2))
will work.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Displaying Result

hi there,

is there any way in populating data like the example below using Reporting Services?

SQL database table result:

Category Name

Group 1 John

Group1 May

Group2 Mary

Display in Report

Group 1 Group2

John, May Mary

regards

angela

Hi Angela

If you want the array of names to be shown next to
each other in instead of having to itterate for each item
in the group you could do the following.

Create a table report with 'group' as the group
and 'Name' as detail

In the expression for name you can the use the join function:

=JOIN(Fields!Name, " ")

This will append all the fields in the aggregation,
if you group by the 'group' field it should effectively
give only the names for that group, in the following formatt:

Group Name
-|
Group1 | Angela Johan Beckham |
--|-|
Group2 | Andre Jaco Peter |
--|-|

Hope this helps

Gerhard Davids

|||

Hi Gerhard Davids,

the solution u provide returns an error like this:

Overload resolution failed because no Public 'Join' can be called with these arguments:

'Public Shared Function Join(sourceArray As String(),[Delimiter As String = ] as string......

Regards

angela

|||

HiAngela

I'm sorry, I made a mistake: the join function is for
joining selected parameter arrays in a multi-selected
parameter. Your problem seems far more complex.

I am trying to find a solution for you, and I will let
you know as soon as I find one.

Sorry for not being more helpfull.

Gerhard Davids

|||

Hi Gerhard Davids,

Thanks 4 ur help, my problem has been solved..but was done in a very tedious way by writing the stored procedure.

regards

angela

Displaying Dynamically created columns

Hello,
I have a stored procedure that accepts input parameters and depending
on the input, returns multiple columns. For example depending on the month
range passed in, the stored procedure creates and populates temporary table
with columns for each month and returns the values in the temporary table.
How do I display these dynamically created columns using Reporting Services
(the number of coulmns returned by the stored proc varies between different
runs). Any help is appreciated.Sunil,
You would need to use the matrix control and then edit the
matrix_columngroup to use the data returned from your stored proc.
Hope this helps.
Bill Youngman
Anexinet, Inc.
"Sunil Oliver" <SunilOliver@.discussions.microsoft.com> wrote in message
news:D11DBA4E-C99F-4E37-8C0D-7F9D76192001@.microsoft.com...
> Hello,
> I have a stored procedure that accepts input parameters and
depending
> on the input, returns multiple columns. For example depending on the month
> range passed in, the stored procedure creates and populates temporary
table
> with columns for each month and returns the values in the temporary table.
> How do I display these dynamically created columns using Reporting
Services
> (the number of coulmns returned by the stored proc varies between
different
> runs). Any help is appreciated.

Displaying data in Columns based on Criteria

I have data in rows that I want to display in columns based on a value in
each row.
Here's an example of the data:
Employee ID Type Code Amount
123 PAY BONUS 1,000
123 PAY SALARY 5,000
123 DED INS 500
123 DED DENTAL 100
123 DED FLEX 50
Here's how I'd like to display the data:
EMPLOYEE CODE AMOUNT CODE AMOUNT
123 BONUS 1,000 INS 500
123 SALARY 5,000 DENTAL 100
123 FLEX 50
My problem is understanding how to display the items in each column starting
at the top.
Thanks for any help.
--
Charles Allen, MVPOn Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> I have data in rows that I want to display in columns based on a value in
> each row.
> Here's an example of the data:
> Employee ID Type Code Amount
> 123 PAY BONUS 1,000
> 123 PAY SALARY 5,000
> 123 DED INS 500
> 123 DED DENTAL 100
> 123 DED FLEX 50
> Here's how I'd like to display the data:
> EMPLOYEE CODE AMOUNT CODE AMOUNT
> 123 BONUS 1,000 INS 500
> 123 SALARY 5,000 DENTAL 100
> 123 FLEX 50
> My problem is understanding how to display the items in each column starting
> at the top.
> Thanks for any help.
> --
> Charles Allen, MVP
The best way to produce this type of layout is to create a matrix
report where your pivot column (the column that gets split into
multiple columns based on distinct values) is Type. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I'll give it a shot. Thanks
--
Charles Allen, MVP
"EMartinez" wrote:
> On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> > I have data in rows that I want to display in columns based on a value in
> > each row.
> >
> > Here's an example of the data:
> > Employee ID Type Code Amount
> > 123 PAY BONUS 1,000
> > 123 PAY SALARY 5,000
> > 123 DED INS 500
> > 123 DED DENTAL 100
> > 123 DED FLEX 50
> >
> > Here's how I'd like to display the data:
> >
> > EMPLOYEE CODE AMOUNT CODE AMOUNT
> > 123 BONUS 1,000 INS 500
> > 123 SALARY 5,000 DENTAL 100
> > 123 FLEX 50
> >
> > My problem is understanding how to display the items in each column starting
> > at the top.
> >
> > Thanks for any help.
> > --
> > Charles Allen, MVP
>
> The best way to produce this type of layout is to create a matrix
> report where your pivot column (the column that gets split into
> multiple columns based on distinct values) is Type. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Please do suggest me on the below requirement in addition to what Charles
Allen has asked for:
How can I add another column that shows the difference between first CODE
column value and second CODE column value?
Thanks in advance!
"Charles Allen" <callen@.nospam-bkd.com> wrote in message
news:AD136641-F3A8-42B3-A900-92E78174D443@.microsoft.com...
> I'll give it a shot. Thanks
> --
> Charles Allen, MVP
>
> "EMartinez" wrote:
>> On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
>> > I have data in rows that I want to display in columns based on a value
>> > in
>> > each row.
>> >
>> > Here's an example of the data:
>> > Employee ID Type Code Amount
>> > 123 PAY BONUS 1,000
>> > 123 PAY SALARY 5,000
>> > 123 DED INS 500
>> > 123 DED DENTAL 100
>> > 123 DED FLEX 50
>> >
>> > Here's how I'd like to display the data:
>> >
>> > EMPLOYEE CODE AMOUNT CODE AMOUNT
>> > 123 BONUS 1,000 INS 500
>> > 123 SALARY 5,000 DENTAL 100
>> > 123 FLEX
>> > 50
>> >
>> > My problem is understanding how to display the items in each column
>> > starting
>> > at the top.
>> >
>> > Thanks for any help.
>> > --
>> > Charles Allen, MVP
>>
>> The best way to produce this type of layout is to create a matrix
>> report where your pivot column (the column that gets split into
>> multiple columns based on distinct values) is Type. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>|||On Sep 30, 11:20 pm, Charles Allen <cal...@.nospam-bkd.com> wrote:
> I'll give it a shot. Thanks
> --
> Charles Allen, MVP
> "EMartinez" wrote:
> > On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> > > I have data in rows that I want to display in columns based on a value in
> > > each row.
> > > Here's an example of the data:
> > > Employee ID Type Code Amount
> > > 123 PAY BONUS 1,000
> > > 123 PAY SALARY 5,000
> > > 123 DED INS 500
> > > 123 DED DENTAL 100
> > > 123 DED FLEX 50
> > > Here's how I'd like to display the data:
> > > EMPLOYEE CODE AMOUNT CODE AMOUNT
> > > 123 BONUS 1,000 INS 500
> > > 123 SALARY 5,000 DENTAL 100
> > > 123 FLEX 50
> > > My problem is understanding how to display the items in each column starting
> > > at the top.
> > > Thanks for any help.
> > > --
> > > Charles Allen, MVP
> > The best way to produce this type of layout is to create a matrix
> > report where your pivot column (the column that gets split into
> > multiple columns based on distinct values) is Type. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

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 text as HTML?

Is it possible to display text saved with HTML tags as true HTML inside a text box? For example I have some text saved that displays in red when viewed on a web page, but in the report it actually prints the HTML tags that set the color to red. Any ideas? Thanks!
JeffNOPE. Reporting Services just prints out the text AS IS and is not parsed by the browser. I have spent a very good amount of time on this issue for one of the projects I worked on.
I even brought it up during one of the MSDN events with a Microsoft SQL Server MVP speaker. He asked me to email him in detail about it and when I did he never replied back.|||Thanks for the reply. That's what I was afraid of. Guess I'll probably end up using Active Reports for now. Thanks again!

display null values

Hi,

I have the following problem.

I have created different tables in my database with descriptions in from other tables (example : a table named errors with errorid and errordescription as fields in it)

In a report i want to display the total nr of errors during a certain period for a certain department.

i have created that report with a list (by department). in this list i have a table where i group my errors.

until here all ok. when i display my report it shows all the errors.

but what it doesn't show is all the errors who have value 0 (or errors that didn't occur during that period i defined)

how can i display all my errors, even if they did not occur (0 times)

Greetings

vinnie

hi Vsempoux

I am a little confused as to this line

"but what it doesn't show is all the errors who have value 0 (or errors that didn't occur during that period i defined)"

are you trying to show all errors or no errors ?

Bernard

|||

Hello Vinnie,

I think you need to change your data query to left outer join instead of inner join. Can you paste your query?

Hope this helps.

Jarret

Display leading zeros

I want my SQL 2005 output to display leading zeros up to a total of 6
characters. Foe example, the number 23 would display 000023, 2000 would
display 002000, etc. Is there a "Fill" type of command or can I do this in
a CAST function? Thanks.
DavidHi,
no there isn=B4t but you can you this snippet of mine to achieve it:
DECLARE @.SomeValue INT
SET @.SomeValue =3D 34
DECLARE @.TrailingDigits INT
SET @.TrailingDigits =3D 7
SELECT RIGHT(
REPLICATE('0',@.TrailingDigits) +
CONVERT(VARCHAR(200),@.SomeValue)
,@.TrailingDigits
)
I think you should be smart enough to put a function around it, right
:-)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||David,
better to format the output in the client application. Try:
declare @.i int
set @.i = 23
select right(replicate('0', 6) + ltrim(@.i), 6)
go
AMB
"David" wrote:

> I want my SQL 2005 output to display leading zeros up to a total of 6
> characters. Foe example, the number 23 would display 000023, 2000 would
> display 002000, etc. Is there a "Fill" type of command or can I do this i
n
> a CAST function? Thanks.
> David
>
>|||Here is one option. I had to jump through a few hoops because I stored 23
in an int column....
DECLARE @.foo table (col1 int)
INSERT INTO @.foo (col1) values (23)
SELECT REPLICATE('0', 6 - DATALENGTH(CONVERT(varchar(6),col1))) +
CONVERT(varchar(6),col1) FROM @.foo
if your data is stored as varchar you can do something like this:
DECLARE @.foo table (col1 varchar(6))
INSERT INTO @.foo (col1) values (23)
SELECT REPLICATE('0', 6 - DATALENGTH(col1)) + col1 FROM @.foo
Keith Kratochvil
"David" <dlchase@.lifetimeinc.com> wrote in message
news:%23NOVduNXGHA.3448@.TK2MSFTNGP03.phx.gbl...
>I want my SQL 2005 output to display leading zeros up to a total of 6
>characters. Foe example, the number 23 would display 000023, 2000 would
>display 002000, etc. Is there a "Fill" type of command or can I do this in
>a CAST function? Thanks.
> David
>|||That worked! Thanks.
David
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F7D66A28-CB7F-4EC1-948D-A9F5BE29BD0D@.microsoft.com...
> David,
> better to format the output in the client application. Try:
> declare @.i int
> set @.i = 23
> select right(replicate('0', 6) + ltrim(@.i), 6)
> go
>
> AMB
> "David" wrote:
>