row number.

All I am trying to do is return the row number (calculated field?)
with each row returned from an SQL query. I think I have done this in
the past but am a bit rusty after not using SQL for a while. If
anyone could help with a code snippet this would be gretly
appreciated.Here's an example from the Pubs database:

SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS rownum
,*
FROM Authors AS A

--
David Portas
SQL Server MVP
--|||
David Mackintosh wrote:
> All I am trying to do is return the row number (calculated field?)
> with each row returned from an SQL query. I think I have done this in
> the past but am a bit rusty after not using SQL for a while. If
> anyone could help with a code snippet this would be gretly
> appreciated.

There are now row numbers.

Zach|||>> All I am trying to do is return the row number (calculated field?)
with each row returned from an SQL query. <<

Since this would have to be for display purposes in the front end, you
ought to be doing in the front, not the database.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Row group 'footers' in matrices

I am trying to create a report that will append a calculated row of
percentages to the end of a group of test results. The results are
grouped by 'Category A' and have different subcategories. But
overall, I want to generate percentages for each column in 'Category
A'. The columns of Category A consist of Pass, Fail, and In
Progress. I want to be able to have a percentage of all the passes in
category A, but still have that distinction between a subcategory in
category A.
I have designed the matrix to look like such:
Static
Column
Category A | Sub Category
and I want it to produce a result like such:
Pass Fail In Progress
Category A Sub Category 1
1 3 0
Sub Category 2
3 0 1
Percentage
50% 37.5% 12.5%
Category B Sub Category 1
1 3 0
Sub Category 2
3 0 1
Percentage
50% 37.5% 12.5%
It is easy to format within Crystal Reports, but I have been mashing
my brain all day and I haven't found how to do it within Reporting
Services. HELP.Here are the layouts again. I didn't realize that it was going to get
ruined when it posts.
.Static Column
Category A | SubCategory
.................................P .F .IP
Category A Sub1 3 1 0
..................Sub2 1 2 1
..................Percentage 50% 37.5% 12.5%
Category B Sub1 3 1 0
..................Sub2 1 2 1
..................Percentage 50% 37.5% 12.5%

Row Correlated calculation Query

Hi,
I need help in writing a sql, where a calculated column depends on previous'
s row column, for example,
Table: test
Time Packet Seen Seq_num
--
10:00 20 25
10:01 15 40
10:02 17 57
10:03 10 60
10:04 12 72
Query Result: The output of the query should be
Time Packet Seen Seq_num drops
---
10:00 20 25 NULL
10:01 15 40 0
10:02 17 57 0
10:03 10 60 7 (=57+10-60)
10:04 12 72 0
Is this kind of calculation possible using SQL. Any help in this regards wil
l be highly appreciated.
Thanks
MonisDECLARE @.table TABLE(ident INT IDENTITY(1,1),Timecol VARCHAR(5), PacketSeen
INT, SeqNum INT)
INSERT @.table(Timecol, PacketSeen, SeqNum)
SELECT '10:00',20,25 UNION ALL
SELECT '10:01',15,40 UNION ALL
SELECT '10:02',17,57 UNION ALL
SELECT '10:03',10,60 UNION ALL
SELECT '10:04',12,72
SELECT Timecol, PacketSeen, SeqNum FROM @.table
SELECT
t1.Timecol,
t1.PacketSeen,
t1.SeqNum,
t2.SeqNum+t1.PacketSeen-t1.SeqNum AS drops
FROM
@.table t1
LEFT OUTER JOIN @.table t2 ON t1.ident = t2.ident+1
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"mmonis" wrote:

> Hi,
> I need help in writing a sql, where a calculated column depends on
> previous's row column, for example,
> Table: test
> Time Packet Seen Seq_num
> --
> 10:00 20 25
> 10:01 15 40
> 10:02 17 57
> 10:03 10 60
> 10:04 12 72
>
> Query Result: The output of the query should be
> Time Packet Seen Seq_num drops
> ---
> 10:00 20 25 NULL
> 10:01 15 40 0
> 10:02 17 57 0
> 10:03 10 60 7 (=57+10-60)
> 10:04 12 72 0
>
> Is this kind of calculation possible using SQL. Any help in this
> regards will be highly appreciated.
> Thanks
> Monis
>
> --
> mmonis
> ---
> Posted via http://www.codecomments.com
> ---
>|||Hi
Yes , it is possible by using T-SQL , however much more easier doing such
reports on the client side
"mmonis" <mmonis.28cehp@.mail.codecomments.com> wrote in message
news:mmonis.28cehp@.mail.codecomments.com...
> Hi,
> I need help in writing a sql, where a calculated column depends on
> previous's row column, for example,
> Table: test
> Time Packet Seen Seq_num
> --
> 10:00 20 25
> 10:01 15 40
> 10:02 17 57
> 10:03 10 60
> 10:04 12 72
>
> Query Result: The output of the query should be
> Time Packet Seen Seq_num drops
> ---
> 10:00 20 25 NULL
> 10:01 15 40 0
> 10:02 17 57 0
> 10:03 10 60 7 (=57+10-60)
> 10:04 12 72 0
>
> Is this kind of calculation possible using SQL. Any help in this
> regards will be highly appreciated.
> Thanks
> Monis
>
> --
> mmonis
> ---
> Posted via http://www.codecomments.com
> ---
>|||This looks like it depends on identity being sequential and matching the
order TimeCol. Failed inserts will cause gaps in identity, and we may not
be able to depend on TimeCol always being inserted sequentially either. In
a production environment, I think this will break more often than not. At a
minimum, I think we need a correlated subquery here, no?
"MeanOldDBA" <MeanOldDBA@.discussions.microsoft.com> wrote in message
news:ED9AB916-DD8B-423D-92D2-0AD19932AE91@.microsoft.com...
> DECLARE @.table TABLE(ident INT IDENTITY(1,1),Timecol VARCHAR(5),
PacketSeen
> INT, SeqNum INT)
> INSERT @.table(Timecol, PacketSeen, SeqNum)
> SELECT '10:00',20,25 UNION ALL
> SELECT '10:01',15,40 UNION ALL
> SELECT '10:02',17,57 UNION ALL
> SELECT '10:03',10,60 UNION ALL
> SELECT '10:04',12,72
> SELECT Timecol, PacketSeen, SeqNum FROM @.table
> SELECT
> t1.Timecol,
> t1.PacketSeen,
> t1.SeqNum,
> t2.SeqNum+t1.PacketSeen-t1.SeqNum AS drops
> FROM
> @.table t1
> LEFT OUTER JOIN @.table t2 ON t1.ident = t2.ident+1
>
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "mmonis" wrote:
>

Rounding issue in calculated field using round (,1) function

I have a couple of calculated fields in a BI Dev Studio Report as follows:

1. round(Fields!CM_Perf_1st_Mth.Value, 1)

2. round(Fields!CM_Perf_1st_Mth.Value, 1) - round(Fields!BM_Perf_1st_Mth.Value, 1)

The first calc field above is returning wrong results i.e. for a value of 2.25, instead of returning 2.3, it is returning 2.2. Similarly for -0.05, it is returning 0.0, instead of -0.1.

Since the results from the first function are wrong, the second function is also returning off values.

Has anyone faced this issue? How does one get around this? I have SQL 2005 Reporting Services with SP1. The result was the same without SP1 also. Seems like a big bug in the round function...

TIA.

Had similiar issues and went with doing rounding functions in a custom code function.

Try using the functions there, you will see a difference.

Daryl

|||The dataset is coming from a SQL Server 2000 database stored proc.|||

doesn't matter where the dataset is coming from. use the =Code.myroundfunction in the field, and pass the field to the code function.

IE:

field1

if you had "=round(field!myfield.value)"

change it to =code.myroundfunction(field!myfield.value)

|||what does the raw sql data look like?|||

Wrote a custom code fx as:

Public Function MyRound(byVal x as decimal, byval y as int16) as decimal
return round(x,y)
end function

Calling this from the calculated field expression does not make a difference at all.

And here's the raw data:

dbo.Composite_Performance

Composite_Code

LU_Weight_Type_Code

Perf_Date

LU_Data_Stage

Perf_Gross

lc1s

ac

9/30/06

Prelim

2.25

dbo.Composite_Performance

Composite_Code

LU_Weight_Type_Code

Perf_Date

LU_Data_Stage

Perf_Gross

ls

ac

9/30/06

Prelim

-0.05

Any other ideas would be greatly appreciated as I am stuck on this and the deliverable is long due and this is the only issue left... TIA.

|||

Here is a link to another forum which should help you out.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=315706&SiteId=1

Daryl

|||

This function works for me.

Public Function MyRound(ByVal x As Decimal, ByVal y As Integer) As Decimal

Return Round(x, y, MidpointRounding.AwayFromZero)

End Function

the definition for round is really confusing.

Daryl

Rounding Calculated Member

I have the following calculated member that is being used to create Offline/Local cubes.

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage]

AS Case

When IsEmpty( [Measures].[Event Count] )

Then 0

Else ((

[EE Event Template Type Dim].[Event Types].CurrentMember,

[Measures].[Event Count]) /

( [EE Event Template Type Dim].[Event Types].[(All)].[All],

[Measures].[Event Count]

)*100)

End,

FORMAT_STRING = "###.##%",

BACK_COLOR = 12632256 /*Silver*/ ,

FORE_COLOR = 16744576 /*R=128, G=128, B=255*/ ,

VISIBLE = 1 ;

I'm looking for a way to round the result. The FORMAT_STRING functionality does not carry over when I create the local cube so I end up with values like 28.90909097.

Any suggestions would be appreciated.

Tristan

Tristan,

Have you tried the "format" function?

Format(

Case

When IsEmpty( [Measures].[Event Count] )

Then 0

... rest of case statement from original calculation

,"###.##%")

|||That did the trick! Much Thanks!