adding up values

Hello,
I have a query that returns some transactions I have to look at:
select t.tradeID, ABS(t.volume) as totalVolume, ABS(tr.volume) as
partialVolume, t.symbol
from transactions tr, trades t
where tr.tradeID = t.tradeID AND (tr.isMatched = 0 OR tr.isMatched IS NULL)
Sample Data:
tradeID totalVolume partialVolume Symbol
247 4000 2000 ABC
247 4000 1000 ABC
247 4000 500 ABC
247 4000 500 ABC
248 2000 1000 XYZ
248 2000 1500 XYZ
What I want to do is that add the particalVolume column up to give me the
values 4000 and 1500 in this case.
SO far what I have.
Create Table #tempTable
(
ID numeric
)
INSERT INTO #tempTable Select transactionID from transactions where moniker
IS NULL
declare @.partialVolume int
set @.partialVolume = 0
declare @.tempTradeID int
WHILE Exists(Select ID from #tempTable)
begin
Select @.partialVolume = (volume from transactions Where tradeID = @.tempTradeID + @.partialVolume
END
It doesn't work.For those curious.
I got this to work using the SUM function.
Here is the SQL statement.
select t.tradeID, t.symbol, tr.[transaction], t.volume, sum(tr.volume),
t.accountNumber
from transactions tr, trades t
where tr.symbol = t.symbol AND LEFT(tr.[transaction], 1) =LEFT(t.[transaction], 1) AND tr.date = t.date AND tr.moniker is NULL
group by t.tradeid, t.symbol, tr.[transaction], t.volume, t.accountNumber
"Won Lee" <noemail> wrote in message
news:%23Ytzp90aDHA.2932@.tk2msftngp13.phx.gbl...
> Hello,
> I have a query that returns some transactions I have to look at:
> select t.tradeID, ABS(t.volume) as totalVolume, ABS(tr.volume) as
> partialVolume, t.symbol
> from transactions tr, trades t
> where tr.tradeID = t.tradeID AND (tr.isMatched = 0 OR tr.isMatched IS
NULL)
> Sample Data:
> tradeID totalVolume partialVolume Symbol
> 247 4000 2000 ABC
> 247 4000 1000 ABC
> 247 4000 500 ABC
> 247 4000 500 ABC
> 248 2000 1000 XYZ
> 248 2000 1500 XYZ
>
> What I want to do is that add the particalVolume column up to give me the
> values 4000 and 1500 in this case.
> SO far what I have.
> Create Table #tempTable
> (
> ID numeric
> )
> INSERT INTO #tempTable Select transactionID from transactions where
moniker
> IS NULL
> declare @.partialVolume int
> set @.partialVolume = 0
> declare @.tempTradeID int
> WHILE Exists(Select ID from #tempTable)
> begin
> Select @.partialVolume = (volume from transactions Where tradeID => @.tempTradeID + @.partialVolume
>
> END
> It doesn't work.
>

adding new volume and swapping drive letters....

Hello - I have a SQL 2000 server which has a D: drive that contains all of my databases (system and user). I am running out of space on this volume and need to migrate the contents of this volume to a larger one. My initial plan was to introduce a new volume to the server (say a K: drive). Backup all databases (of course), and then stop all SQL services. Copy all data from D: to K:. Once data is copied, swap drive letter names (D: to I: and then K: to D. Then restart SQL services. SQL should not know any better since everything was on the D: drive when it went down, and everythiing is still on the D: drive when it came back up, correct?

The other option mentioned is to detatch the databases, copy the data and then reattach them in their new locations. I understand this method, but it seems more involved (and riskier) than just renaming the drives. Does anyone have an opinion regarding these two migration methods? Thanks for your help.

Chris
I would use the attach / detach method, would be the best solution for me in this case.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||As you indicated, the drive rename method may be less effort. As long as you are using the Disk Management applet -and not just mapping drives, it should work just fine.

Adding Disks

I would like to add another volume to my two node sql 2005 cluster. From
what i have been reading i need to shut one of the cluster nodes, add
the drive to the cluster then bring the backup node back on line. Am i
missing anything?
Thanks for the help
Noah Hamilton
Sr Systems Administrator
Carpathia Hosting, Inc
21711 Filigree Court, Suite A
Ashburn, VA 20147
voice: (703) 297-4451
Fax: (703) 997 5577
www.carpathiahost.com
Once you have physically connected the disk (or carved a new LUN and
presented it to the host computers), you must create a clustered resource.
Use the cluster admin tool to create a disk resource and map it to the
physical disk. I usually create a temporary resource group to park it in
while I am setting things up. Test failover to make sure all host nodes can
"see" and use the clustered disk.
You then can move the disk into the SQL resource group. Finally, you must
make the SQL Service dependent on the new disk before SQL will use it for
data or logs. This will require stopping the SQL service while the
dependencies are changed.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Noah" <noah@.carpathiahost.com> wrote in message
news:%23$wrjlp2HHA.4584@.TK2MSFTNGP03.phx.gbl...
>I would like to add another volume to my two node sql 2005 cluster. From
>what i have been reading i need to shut one of the cluster nodes, add the
>drive to the cluster then bring the backup node back on line. Am i missing
>anything?
> Thanks for the help
> Noah Hamilton
> Sr Systems Administrator
> Carpathia Hosting, Inc
> 21711 Filigree Court, Suite A
> Ashburn, VA 20147
> voice: (703) 297-4451
> Fax: (703) 997 5577
> www.carpathiahost.com
>
|||Thanks for the help. I have the new drives up and running now.
Appreciate all the help.
Geoff N. Hiten wrote:
> Once you have physically connected the disk (or carved a new LUN and
> presented it to the host computers), you must create a clustered
> resource. Use the cluster admin tool to create a disk resource and map
> it to the physical disk. I usually create a temporary resource group to
> park it in while I am setting things up. Test failover to make sure all
> host nodes can "see" and use the clustered disk.
> You then can move the disk into the SQL resource group. Finally, you
> must make the SQL Service dependent on the new disk before SQL will use
> it for data or logs. This will require stopping the SQL service while
> the dependencies are changed.
>