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 Multiple Values into a row/column help

Whats the fastest easiest way to take a select that returns say 4 values for the expression into a single column on defined row

basically I mean i want to do an update to say a persons i dunno ummm places they have traveled and I want it listed like france;usa;germany etc etc and the data would always be in the tables i pull from so I can overwrite the data each time i run it but has to take 3 or more values from a query and put them in separated by say a ; into the same persons coloumn that stores the info.

I did this once before with a cursor and adding a variable to itself with colasce or whatever the command was, but was just wondering if there is a fast way to do this by chance that im not thinking about :P.

Thanks!The following example will collect the values from a column in a select statement and create a delimited list from the values.

This will denormalize the values from the source table into a single column so the destination table will not meet the requirements of first normal form. This may be best used for reporting operations, that said:

Two tables are created, one to hold values for the list, and one where the results are inserted.

Test values are inserted into the test table and then a select statement collects the values. (Example supports only 4000 characters)

--Create Test Table
CREATE TABLE dbo.test (
dataField NVARCHAR(10) NOT NULL,
PRIMARY KEY (dataField)
)
GO

--Create Results Table
CREATE TABLE dbo.testResults (
resultId INT IDENTITY (1,1) NOT NULL,
result NVARCHAR(4000) NOT NULL,
PRIMARY KEY (resultId)
)
GO

--Insert Test Data
INSERT dbo.test (dataField) values ('here')
INSERT dbo.test (dataField) values ('there')
INSERT dbo.test (dataField) values ('everywhere')

--Verify Test Data
SELECT dataField FROM dbo.test

--Retrieve colon delimited list of dataField without a cursor
DECLARE @.collectValues NVARCHAR(4000)
SET @.collectValues = ('')

SELECT
@.collectValues = @.collectValues + dataField + ';'
FROM dbo.test

--Verify delimited list
SELECT @.collectValues

--Insert into result table
INSERT dbo.testResults
(result)
VALUES
(@.collectValues)

--Verify inserted data
SELECT resultId, result FROM dbo.testResults

The last select statement should return the result value:
everywhere;here;there;|||Im confused, this doesnt seem like I could get the results correctly from this, You could just use one single select to get all the data like that from that, but what if this works as above, then how would it diferentiate from members and there intrests. Let me give an exampe. Member1 has intrests of fishing,boating,camping, member2 has intrests of fising,hiking,running, how would i basically convert the below

table one

customer intrest

member1 fishing
member1 boating
member1 camping
member2 fishing
member2 hiking
member2 running

go from that data, to this data

table two

customer intrests
member1 fishing;boating;bamping
member2 fising;hiking;running

I dont think the above example can do this can it? Or Am I just missing something? Thanks! hehe|||You are absolutely correct. I misread your intention as wanting the value for a single person (as though you would add this update to a procedure for updating the base table, etc...).

adding column in resultset from SP

I have a sp: mysp_getstuff
it contains the following:

SELECT Adress,City FROM tblUserData WHERE UserName='john'

as you can see it returns 2 columns.
I also have another SP: mysp_GetNr. This sp returns an integer.

I want to call mysp_getnr from mysp_getstuff and add the result to the 2 columns in a column named 'Number'
So the resultset from mysp_getstuff should be:
Adress, City, Number (in which the number column contains the result from mysp_GetNr)

How can I do that?

Use an OUTPUT Parameter to get the value from mysp_getnr.

Declare @.num int

EXEC mysp_getnr @.params... @.num OUTPUT

Then, in your mysp_getstuff you can either return @.num as its OUTPUT param or along with the result set.

SELECT Adress,City,Number=@.num FROM tblUserData WHERE UserName='john'.

Adding Carriage Return and Line Feeds to a text box

Hi All,

I am trying to print 10 values in a single cell in a text box.....

To make it readable I put carriage returns and line feeds into the boxes.

These rendered ok in visual studio but did not render ok in explorer....no carriage return or line feed.

This is what I added...

=Fields!segment_sdesc_00.Value & chr(10) & chr(13)

Can anyone advise me as to what is wrong with this? There must be a way to put a cr/lf or newline into a report in a text box.....

Thanks in Advance

Peter

www.peternolan.com

Try using the + sign over the ampersand.

Code Snippet

=Fields!segment_sdesc_00.Value + chr(10) + chr(13)

This has worked for me in Explorer so far.

|||

If you are working with SRS and need to combine two CRM fields together, but having each on its own line, use the following expression:

=Fields!ShipToName.Value & vbCRLF & Fields!ShipToAddress.Value

vbCRLF is a special system constant that contains the values of carriage return and line feed characters.

- Mitch Milam’s Microsoft Discussions

|||

Hi All,

thanks for the tips...will try both....

Peter

|||

Hi Simone,

some of the fields are numbers and from memory it complained about the plus sign when there was a mixture of numeric and string fields...but I will try again.

Thanks

Peter

Adding an Expression to a Report Text Box

I added this expression to the Text Box on the Report and it returns the
Nothing Value, yet the value does exist.
=Iif(Fields!USCATVLS_1.Value = "External",(Fields!Billing_Amount.Value*Fields!BNFITAMT_5.Value/100),Nothing )
If I add:
=(Fields!USCATVLS_1.Value)
the Value "External" returns to the report.
The documentaion indicates this should work.
Pls Help.
--
MickOn Jun 6, 6:54 am, Mick Egan <MickE...@.discussions.microsoft.com>
wrote:
> I added this expression to the Text Box on the Report and it returns the
> Nothing Value, yet the value does exist.
> =Iif(Fields!USCATVLS_1.Value => "External",(Fields!Billing_Amount.Value*Fields!BNFITAMT_5.Value/100),Nothing )
> If I add:
> =(Fields!USCATVLS_1.Value)
> the Value "External" returns to the report.
> The documentaion indicates this should work.
> Pls Help.
> --
> Mick
Are you sure that Fields!BNFITAMT_5.Value is not nothing? The
expression seems to be fine otherwise.
Regards,
Enrique Martinez
Sr. Software Consultant|||Enrique,
When I swap places with the "Nothing" value it returns all the values, so
the calculation is valid.
Mick
--
Mick
"EMartinez" wrote:
> On Jun 6, 6:54 am, Mick Egan <MickE...@.discussions.microsoft.com>
> wrote:
> > I added this expression to the Text Box on the Report and it returns the
> > Nothing Value, yet the value does exist.
> >
> > =Iif(Fields!USCATVLS_1.Value => > "External",(Fields!Billing_Amount.Value*Fields!BNFITAMT_5.Value/100),Nothing )
> >
> > If I add:
> > =(Fields!USCATVLS_1.Value)
> > the Value "External" returns to the report.
> >
> > The documentaion indicates this should work.
> > Pls Help.
> > --
> > Mick
>
> Are you sure that Fields!BNFITAMT_5.Value is not nothing? The
> expression seems to be fine otherwise.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
>|||Even though Fields!USCATVLS_1.Value returns "External", or appears to, in
the report, is it possible that there are actually some trailing spaces?
IOW, what if you used the following condition in your expression:
Fields!USCATVLS_1.Value.Trim() = "External"
... or something like that?
>L<
"Mick Egan" <MickEgan@.discussions.microsoft.com> wrote in message
news:25B7A52C-877A-4C7D-9AC9-817A179ECFB0@.microsoft.com...
>I added this expression to the Text Box on the Report and it returns the
> Nothing Value, yet the value does exist.
> =Iif(Fields!USCATVLS_1.Value => "External",(Fields!Billing_Amount.Value*Fields!BNFITAMT_5.Value/100),Nothing
> )
> If I add:
> =(Fields!USCATVLS_1.Value)
> the Value "External" returns to the report.
> The documentaion indicates this should work.
> Pls Help.
> --
> Mick|||Lisa / Enrique,
The .Trim() isn't recogonised, so I tried (Trim(Fields!USCATVLS_1.Value) ="External", this didn't work either.
So I tried:
Setting the RTRIM on the Dataset and this worked, I needed to add the field
as an expression.
i.e. RTRIM(IV00101.USCATVLS_2) AS CAT2
Thanks heaps for pointing me in the right direction.
Mick
Mick
"Lisa Slater Nicholls" wrote:
> Even though Fields!USCATVLS_1.Value returns "External", or appears to, in
> the report, is it possible that there are actually some trailing spaces?
> IOW, what if you used the following condition in your expression:
> Fields!USCATVLS_1.Value.Trim() = "External"
> ... or something like that?
> >L<
> "Mick Egan" <MickEgan@.discussions.microsoft.com> wrote in message
> news:25B7A52C-877A-4C7D-9AC9-817A179ECFB0@.microsoft.com...
> >I added this expression to the Text Box on the Report and it returns the
> > Nothing Value, yet the value does exist.
> >
> > =Iif(Fields!USCATVLS_1.Value => > "External",(Fields!Billing_Amount.Value*Fields!BNFITAMT_5.Value/100),Nothing
> > )
> >
> > If I add:
> > =(Fields!USCATVLS_1.Value)
> > the Value "External" returns to the report.
> >
> > The documentaion indicates this should work.
> > Pls Help.
> > --
> > Mick
>