Row count from another table?

Is it possible to directly get the row count of a different table in a RS2005 report?

I'd like to use this row count number in a calculation in another table...

This would be a lot cleaner than creating a new dataset to provide me with this value.

Thanks!

Alright, figured this out. Just had to use RowNumber("NameOfDataset") to return the number of rows in a table created by a dataset. Also, RowNumber(Nothing) returns the row number of a row in a table.

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:
>

Round Statement Incorrect Value

Hello,

I am having trouble getting the correct calculation with the statement below. The error is that QTR4 is being divided by Select SUM instead of all 4 quarters. I have tried closing the addition statements but get errors on all scenarios that Ive tried. How can I format this to correctly to add up all 4 quarters then do the division?

SELECT campus.campus,
ROUND(QTR1+QTR2+QTR3+QTR4/(SELECT SUM(QTR1+qtr2+qtr3+qtr4) FROM campus),2) "% CONT"
FROM campus;

CAMPUS % CONT
-- ----
Main 1300.16
East 700.08
West 300.04
North 350.04What do you mean by "closing the addition statement". Shouldn't you to put parenthesis over QTR1+QTR2+QTR3+QTR4 ?

SELECT campus.campus,
ROUND((QTR1+QTR2+QTR3+QTR4)/(SELECT SUM(QTR1+qtr2+qtr3+qtr4) FROM campus),2) "% CONT"
FROM campus;