Re: Scalar Subquery in MS-SQL Server 2000

"Sanjeev" <sanjeev.atvankar@xxxxxxxxx> wrote in message
news:f4528ba9-cac2-4af3-b8a4-a4d6653b602a@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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


.

Relevant Pages