Help expanding a query .. group By

Im using vb code to generate a SQL query to return a recordset. The follwing
query returns 2 records as follows.
Select a.BHYEAR_MOVEDATE,
Case b.BTYear_TransCode
WHEN '17' THEN 'DD'
WHEN '01' THEN 'DD'
WHEN '18' THEN 'DD'
Else 'AUD' END AS 'TYPE'
from dbo.BacsHdrYearly as a
LEFT JOIN dbo.BacsTrnYear as b
on a.BHYear_LedgerKey = b.BTYear_LedgerKey
Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
BHYEAR_MOVEDATE TYPE
--- --
2005-04-21 00:00:00 DD
2005-04-21 00:00:00 DD
I need it to only return one record as for each date and type ( may be
multiple dates and types ).
BHYEAR_MOVEDATE TYPE
--- --
2005-04-21 00:00:00 DD
any suggestions on how to rephrase this query ?What ABout DISTINCT ?

> Select DISTINCT a.BHYEAR_MOVEDATE,
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
Newsbeitrag news:5AEEE5BB-67B2-4DEC-A918-F6C32E79312C@.microsoft.com...
> Im using vb code to generate a SQL query to return a recordset. The
> follwing
> query returns 2 records as follows.
> Select a.BHYEAR_MOVEDATE,
> Case b.BTYear_TransCode
> WHEN '17' THEN 'DD'
> WHEN '01' THEN 'DD'
> WHEN '18' THEN 'DD'
> Else 'AUD' END AS 'TYPE'
> from dbo.BacsHdrYearly as a
> LEFT JOIN dbo.BacsTrnYear as b
> on a.BHYear_LedgerKey = b.BTYear_LedgerKey
> Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
> OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
> GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
>
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> 2005-04-21 00:00:00 DD
>
> I need it to only return one record as for each date and type ( may be
> multiple dates and types ).
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> any suggestions on how to rephrase this query ?|||or
GROUP BY clause
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OoaGSMNRFHA.204@.TK2MSFTNGP15.phx.gbl...
> What ABout DISTINCT ?
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:5AEEE5BB-67B2-4DEC-A918-F6C32E79312C@.microsoft.com...
>|||Of couse ;-)
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:e$TxrUNRFHA.3076@.TK2MSFTNGP14.phx.gbl...
> or
> GROUP BY clause
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:OoaGSMNRFHA.204@.TK2MSFTNGP15.phx.gbl...
>|||Peter,
Your statement is correct, except that in the GROUP BY clause you have to us
e:
...
GROUP BY
a.BHYEAR_MOVEDATE,
Case b.BTYear_TransCode
WHEN '17' THEN 'DD'
WHEN '01' THEN 'DD'
WHEN '18' THEN 'DD'
Else 'AUD' END;
AMB
"Peter Newman" wrote:

> Im using vb code to generate a SQL query to return a recordset. The follwi
ng
> query returns 2 records as follows.
> Select a.BHYEAR_MOVEDATE,
> Case b.BTYear_TransCode
> WHEN '17' THEN 'DD'
> WHEN '01' THEN 'DD'
> WHEN '18' THEN 'DD'
> Else 'AUD' END AS 'TYPE'
> from dbo.BacsHdrYearly as a
> LEFT JOIN dbo.BacsTrnYear as b
> on a.BHYear_LedgerKey = b.BTYear_LedgerKey
> Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
> OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
> GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
>
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> 2005-04-21 00:00:00 DD
>
> I need it to only return one record as for each date and type ( may be
> multiple dates and types ).
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> any suggestions on how to rephrase this query ?sql

Help , Several Connections , Port : 1433

Environment:
SQL Server Standard Edition
Microsoft Windows NT - 5.2 (3790)
8.00.760 (SP3)
DOS Command : netstat -n . Results as follows
Find several connections about port 1433
Who can help me ? How to resolve the problem ?
Please , thank you .
.
.
.
.
TCP 202.168.198.117:4941 202.181.68.244:1433 FIN_WAIT_1
TCP 202.168.198.117:4942 202.181.71.66:1433 FIN_WAIT_1
TCP 202.168.198.117:4943 202.168.198.116:1433 TIME_WAIT
TCP 202.168.198.117:4945 202.181.68.123:1433 FIN_WAIT_1
TCP 202.168.198.117:4950 202.181.68.123:1433 FIN_WAIT_1
TCP 202.168.198.117:4959 202.181.70.183:1433 FIN_WAIT_1
TCP 202.168.198.117:4959 202.181.92.3:1433 FIN_WAIT_1
TCP 202.168.198.117:4963 202.181.71.66:1433 FIN_WAIT_1
TCP 202.168.198.117:4964 202.181.95.40:1433 FIN_WAIT_1
TCP 202.168.198.117:4966 202.181.87.84:1433 FIN_WAIT_1
TCP 202.168.198.117:4966 202.181.94.137:1433 FIN_WAIT_1
TCP 202.168.198.117:4968 202.181.66.159:1433 FIN_WAIT_1
TCP 202.168.198.117:4970 202.181.71.66:1433 FIN_WAIT_1
TCP 202.168.198.117:4971 202.181.64.246:1433 FIN_WAIT_1
TCP 202.168.198.117:4976 202.181.94.137:1433 FIN_WAIT_1
TCP 202.168.198.117:4984 202.181.95.40:1433 FIN_WAIT_1
TCP 202.168.198.117:4985 202.181.95.40:1433 FIN_WAIT_1
TCP 202.168.198.117:4989 202.181.90.133:1433 FIN_WAIT_1
TCP 202.168.198.117:4992 202.181.66.159:1433 FIN_WAIT_1
TCP 202.168.198.117:4992 202.181.90.133:1433 FIN_WAIT_1
TCP 202.168.198.117:4996 202.181.88.249:1433 FIN_WAIT_1
TCP 202.168.198.117:4999 202.181.87.238:1433 FIN_WAIT_1
.
.
.Hi
You may want to do a netstat -n -o to find what process is trying to connect
to the servers. It sounds like some process is browsing the network for
servers and port 1433 is open on the remote servers.
John
"test" wrote:
>
> Environment:
> SQL Server Standard Edition
> Microsoft Windows NT - 5.2 (3790)
> 8.00.760 (SP3)
>
> DOS Command : netstat -n . Results as follows
> Find several connections about port 1433
>
> Who can help me ? How to resolve the problem ?
> Please , thank you .
>
> ..
> ..
> ..
> ..
> TCP 202.168.198.117:4941 202.181.68.244:1433 FIN_WAIT_1
> TCP 202.168.198.117:4942 202.181.71.66:1433 FIN_WAIT_1
> TCP 202.168.198.117:4943 202.168.198.116:1433 TIME_WAIT
> TCP 202.168.198.117:4945 202.181.68.123:1433 FIN_WAIT_1
> TCP 202.168.198.117:4950 202.181.68.123:1433 FIN_WAIT_1
> TCP 202.168.198.117:4959 202.181.70.183:1433 FIN_WAIT_1
> TCP 202.168.198.117:4959 202.181.92.3:1433 FIN_WAIT_1
> TCP 202.168.198.117:4963 202.181.71.66:1433 FIN_WAIT_1
> TCP 202.168.198.117:4964 202.181.95.40:1433 FIN_WAIT_1
> TCP 202.168.198.117:4966 202.181.87.84:1433 FIN_WAIT_1
> TCP 202.168.198.117:4966 202.181.94.137:1433 FIN_WAIT_1
> TCP 202.168.198.117:4968 202.181.66.159:1433 FIN_WAIT_1
> TCP 202.168.198.117:4970 202.181.71.66:1433 FIN_WAIT_1
> TCP 202.168.198.117:4971 202.181.64.246:1433 FIN_WAIT_1
> TCP 202.168.198.117:4976 202.181.94.137:1433 FIN_WAIT_1
> TCP 202.168.198.117:4984 202.181.95.40:1433 FIN_WAIT_1
> TCP 202.168.198.117:4985 202.181.95.40:1433 FIN_WAIT_1
> TCP 202.168.198.117:4989 202.181.90.133:1433 FIN_WAIT_1
> TCP 202.168.198.117:4992 202.181.66.159:1433 FIN_WAIT_1
> TCP 202.168.198.117:4992 202.181.90.133:1433 FIN_WAIT_1
> TCP 202.168.198.117:4996 202.181.88.249:1433 FIN_WAIT_1
> TCP 202.168.198.117:4999 202.181.87.238:1433 FIN_WAIT_1
> ..
> ..
> ..
>
>
>|||netstat -ano , find : SID = 5664
tasklist , find : 5664 = syswinxp.exe
"syswinxp.exe" maybe a trojan .
Thanks for your help .
"John Bell" <jbellnewsposts@.hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:46CCFA26-F813-4A39-8EF9-EF517084BB6E@.microsoft.com...
> Hi
> You may want to do a netstat -n -o to find what process is trying to
> connect
> to the servers. It sounds like some process is browsing the network for
> servers and port 1433 is open on the remote servers.
> John
> "test" wrote:
>>
>> Environment:
>> SQL Server Standard Edition
>> Microsoft Windows NT - 5.2 (3790)
>> 8.00.760 (SP3)
>>
>> DOS Command : netstat -n . Results as follows
>> Find several connections about port 1433
>>
>> Who can help me ? How to resolve the problem ?
>> Please , thank you .
>>
>> ..
>> ..
>> ..
>> ..
>> TCP 202.168.198.117:4941 202.181.68.244:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4942 202.181.71.66:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4943 202.168.198.116:1433 TIME_WAIT
>> TCP 202.168.198.117:4945 202.181.68.123:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4950 202.181.68.123:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4959 202.181.70.183:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4959 202.181.92.3:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4963 202.181.71.66:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4964 202.181.95.40:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4966 202.181.87.84:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4966 202.181.94.137:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4968 202.181.66.159:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4970 202.181.71.66:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4971 202.181.64.246:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4976 202.181.94.137:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4984 202.181.95.40:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4985 202.181.95.40:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4989 202.181.90.133:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4992 202.181.66.159:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4992 202.181.90.133:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4996 202.181.88.249:1433 FIN_WAIT_1
>> TCP 202.168.198.117:4999 202.181.87.238:1433 FIN_WAIT_1
>> ..
>> ..
>> ..
>>
>>
>>

help

Hi
I got a table with 2 columns as follows
col1 col2
10 193.51
10 194.5
10 202.71
20 192.79
20 197.6
20 192.9
30 192.76
30 191.91
30 187.9
Now i need to add a column dynamically thru sql statement to the table
so that my output should be as follows
here
0.511601468=(194.5/193.51-1)*100
4.221079692=(202.71/194.5-1)*100
and so on
col1 col2 col3
10 193.51 0.511601468
10 194.5 4.221079692
10 202.71 null
20 192.79 2.494942684
20 197.6 -2.37854251
20 192.9 null
30 192.76 -0.440962855
30 191.91 -2.08952113
30 187.9 NullPlease do not multipost, answered in: comp.databases.ms-sqlserver
Thanks, Jens Suessmeyer.