By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,396 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

Scalar Subquery in MS-SQL Server 2000

P: n/a
Hi Gurus,

I have table (CallByCall) with following Data.

Input :
CallID Event DNIS UserID Time SeqID MobNo
-----------------------------------------------------------------------------
1 | 43 | 100 | |09:00:05 | 1 |
9833210770
1 | 40 | | |09:01:05 | 2
|
1 | 41 | | |09:02:05 | 3
|
1 | 42 | | 25 |09:03:05 | 4
|

2 | 43 | 200| |09:10:05 | 1 |
9833975536
2 | 40 | | |09:11:05 | 2
|
2 | 41 | | |09:12:05 | 3
|
2 | 42 | | 26 |09:13:05 | 4
|

3 | 43 | 300| |09:15:05 | 1 |
9813978536
3 | 40 | | |09:15:55 | 2
|
3 | 41 | | |09:16:05 | 3
|
3 | 42 | | 28 |09:18:05 | 4
|

4 | 43 | 100| |09:45:05 | 1 |
9223978896
4 | 40 | | |09:46:05 | 2
|
4 | 41 | | |09:47:05 | 3
|
4 | 42 | | 26 |09:48:05 | 4
|

Output:
CallID DNIS UserID MinTime MaxTime MobNo
--------------------------------------------------------------------------
1 | 100 | 25 |09:00:05 |09:03:05 |9833210770
2 | 200 | 26 |09:10:05 |09:13:05 |9833975536
3 | 300 | 28 |09:15:05 |09:18:05 |9813978536
4 | 100 | 26 |09:45:05 |09:48:05 |9223978896

In short I am getting DNIS and MobNo for Event 43 and
UserId for Event 42.

How can I achieve the above result?
Is it possible with scalar query?

Could any one help me for the same?

Thanking in Advance
Sanjeev
98332 10772
Mar 28 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Sanjeev" <sa**************@gmail.comwrote in message
news:f4**********************************@e23g2000 prf.googlegroups.com...
Hi Gurus,

I have table (CallByCall) with following Data.

Input :
CallID Event DNIS UserID Time SeqID MobNo
-----------------------------------------------------------------------------
1 | 43 | 100 | |09:00:05 | 1 |
9833210770
1 | 40 | | |09:01:05 | 2
|
1 | 41 | | |09:02:05 | 3
|
1 | 42 | | 25 |09:03:05 | 4
|

2 | 43 | 200| |09:10:05 | 1 |
9833975536
2 | 40 | | |09:11:05 | 2
|
2 | 41 | | |09:12:05 | 3
|
2 | 42 | | 26 |09:13:05 | 4
|

3 | 43 | 300| |09:15:05 | 1 |
9813978536
3 | 40 | | |09:15:55 | 2
|
3 | 41 | | |09:16:05 | 3
|
3 | 42 | | 28 |09:18:05 | 4
|

4 | 43 | 100| |09:45:05 | 1 |
9223978896
4 | 40 | | |09:46:05 | 2
|
4 | 41 | | |09:47:05 | 3
|
4 | 42 | | 26 |09:48:05 | 4
|

Output:
CallID DNIS UserID MinTime MaxTime MobNo
--------------------------------------------------------------------------
1 | 100 | 25 |09:00:05 |09:03:05 |9833210770
2 | 200 | 26 |09:10:05 |09:13:05 |9833975536
3 | 300 | 28 |09:15:05 |09:18:05 |9813978536
4 | 100 | 26 |09:45:05 |09:48:05 |9223978896

In short I am getting DNIS and MobNo for Event 43 and
UserId for Event 42.

How can I achieve the above result?
Is it possible with scalar query?

Could any one help me for the same?

Thanking in Advance
Sanjeev
98332 10772
Try this:

SELECT CallID,
MAX(DNIS) DNIS, MAX(UserID) UserID,
MIN(Time) MinTime, MAX(Time) MaxTime
FROM CallByCall
GROUP BY CallID;

--
David Portas
Mar 28 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.