October 26, 2011 at 5:25 am
Hi, im looking for help with a query that im writing. Im not that great with math and I need to populate a temporary table by multiplying or dividing a value in one row, by a value in another row
Ive got the multiplcation sorted with some help from google, but can i do similar for division?
my multiplication involves
SELECT EXP(sum(log(abs(nullif(VALUE, 0))))) * (1+2*(cast(sum(sign(VALUE)-1)/2 as int) % 2)) * min(abs(sign(VALUE)))
Thanks in advance
October 26, 2011 at 5:30 am
strawb2 (10/26/2011)
Hi, im looking for help with a query that im writing. Im not that great with math and I need to populate a temporary table by multiplying or dividing a value in one row, by a value in another rowIve got the multiplcation sorted with some help from google, but can i do similar for division?
my multiplication involves
SELECT EXP(sum(log(abs(nullif(VALUE, 0))))) * (1+2*(cast(sum(sign(VALUE)-1)/2 as int) % 2)) * min(abs(sign(VALUE)))
Thanks in advance
Hello and welcome to SSC!
Unfortunately your DDL script has become detached from your post, or perhaps you were unaware of the benefits of providing one.
When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
Without a clear indication of what you're trying to do, along with readily consumable sample data, the only answer I can offer to your question is "Yes, you can divide in the same way that you can multiply data). Which I'm sure is as unsatisfactory to you as it is to me.
Thanks!
October 26, 2011 at 8:38 am
Thanks for the heads up...
Test Data and query
---------------------------
CREATE TABLE #SourceData
(
ReadingDateTimedatetime,
ChannelDatadecimal(18,4),
UnitAggregationvarchar(50),
IsMultiplyChannelbit,
IsDivideChannelbit
)
INSERT INTO #SourceData
(ReadingDateTime, ChannelData, UnitAggregation, IsMultiplyChannel, IsDivideChannel)
SELECT '2011/01/01 00:00','2','RAW','0','1'
INSERT INTO #SourceData
(ReadingDateTime, ChannelData, UnitAggregation, IsMultiplyChannel, IsDivideChannel)
SELECT '2011/01/01 00:00','4','RAW','0','1'
INSERT INTO #SourceData
(ReadingDateTime, ChannelData, UnitAggregation, IsMultiplyChannel, IsDivideChannel)
SELECT '2011/01/01 00:00','6','RAW','0','1'
INSERT INTO #SourceData
(ReadingDateTime, ChannelData, UnitAggregation, IsMultiplyChannel, IsDivideChannel)
SELECT '2011/01/01 00:30','8','RAW','0','1'
INSERT INTO #SourceData
(ReadingDateTime, ChannelData, UnitAggregation, IsMultiplyChannel, IsDivideChannel)
SELECT '2011/01/01 00:30','10','RAW','0','1'
INSERT INTO #SourceData
(ReadingDateTime, ChannelData, UnitAggregation, IsMultiplyChannel, IsDivideChannel)
SELECT '2011/01/01 00:30','12','RAW','0','1'
CREATE TABLE #VirtualData
(
ReadingDateTimedatetime,
ChannelDatadecimal(18,4),
UnitAggregationvarchar(50),
IsMultiplyChannel bit,
IsDivideChannelbit
)
INSERT INTO #VirtualData
SELECT
ReadingDateTime,
EXP(SUM(lOG(abs(nullif(ChannelData, 0))))) * (1+2*(cast(sum(sign(ChannelData)-1)/2 as int) % 2)) * min(abs(sign(ChannelData))),
UnitAggregation,
IsMultiplyChannel,
IsDivideChannel
FROM #SourceData
GROUP BY
ReadingDateTime,
UnitAggregation,
IsMultiplyChannel,
IsDivideChannel
Select * from #VirtualData
order by ReadingDateTime
DROP TABLE #VirtualData
DROP TABLE #SourceData
---------------------------
From that test script, id expect to find 2 rows of data in my destination table "#VirtualData" where it has multiplied ChannelData by channeldata from each row
What Id like help with is to do similar, but this time by dividing. Id expect to replace "EXP(SUM(lOG(abs(nullif(ChannelData, 0))))) * (1+2*(cast(sum(sign(ChannelData)-1)/2 as int) % 2)) * min(abs(sign(ChannelData))),"
but as i have mentioned my math is quite poor and im quite new to writing SQL sprocs
Any help is appreciated
October 26, 2011 at 8:48 am
Whoa... Can you give me some context or business rules? I don't quite understand what is being multiplied and divided...
Thanks,
Jared
Jared
CE - Microsoft
October 26, 2011 at 9:04 am
I am getting two rows from your test script, with the values of 48 and 959.999
Is that correct? It's hard to know what's not working. Division operations work the same as multiplication, though you do have to be careful of divide by zero operations.
October 26, 2011 at 9:05 am
basically we have a system that logs data from various devices called channels. Each channel logs data every 30 minutes on the hour and on the half hour.
occaisionally we want to perform calculations on the channel data
so for a given date range id like to be able to multiply or divide channel readings by each other.
e.g. for 01/01/2011 00:00 id like to multiply the column "ChannelData" for channel1 by "ChannelData" from channel2 by "ChannelData" from channel3
so in my test data above for 01/01/2011 00:00 id have to get the product of the ChannelData, in this case it would be 2 * 4 * 6
my output would merge my 3 rows of source data where "readingdatetime" = 01/01/2011 00:00 into a single row output with the "CHannelData" value equals 48
I would like to do the same for division so for 01/01/2011 00:00 my calculation should be 2 / 4 / 6
Can you follow? Channeldata can be positive, negative or zero
THanks
October 26, 2011 at 9:07 am
Steve Jones - SSC Editor (10/26/2011)
I am getting two rows from your test script, with the values of 48 and 959.999Is that correct? It's hard to know what's not working. Division operations work the same as multiplication, though you do have to be careful of divide by zero operations.
THanks for the response Steve, youre correct with 2 rows of output data.
I pretty much need someone to write the divide calculation for me as i dont really understand how to achieve it
October 26, 2011 at 9:09 am
So, you have something similar to a running total. Anyone thinking pseudo-cursor and quirky update?
Jared
Jared
CE - Microsoft
October 26, 2011 at 9:13 am
o you have a fixed number of channels? I don't see a channelId or something to identify which channel is feeding the data. If it is fixed, you can crosstab it, which may be useful as well. If it is not fixed, then maybe a running product is what you need?
Thanks,
Jared
Jared
CE - Microsoft
October 26, 2011 at 9:18 am
Theres no fixed number of channels, it could be anything upwards of 2
I do have fixed channelIDs which would be in my Insert-Select Statement but for ease of testing Ive not added them and they are not used in my output. I just pass my output to a chart.
Im taking the data from 2 or more real channels and making 1 pseudo / virtual channel
October 26, 2011 at 9:20 am
Yes, I think some running total is needed. Something like this: http://www.sqlservercentral.com/articles/T-SQL/68467/
The problem is that you cannot do the division in the same way as you're doing the multiplication since the subtraction in an aggregate isn't the same as the addition (SUM).
The easiest way is with a cursor, but if this is running in any kind of scale or load or with many users, it will not perform well. The quirky update, explained in the article, does a better performance job, but it takes a little to work through.
Ultimately what you have to do is work through the table, comparing groups of rows with other groups of rows for the same time ID.
My apologies, I don't have time to dig in and write the code, but it's a good exercise to take that article and rework it for your situation. Work with a single set of IDs, then adding some grouping to it.
October 26, 2011 at 9:30 am
Steve Jones - SSC Editor (10/26/2011)
Yes, I think some running total is needed. Something like this: http://www.sqlservercentral.com/articles/T-SQL/68467/The problem is that you cannot do the division in the same way as you're doing the multiplication since the subtraction in an aggregate isn't the same as the addition (SUM).
The easiest way is with a cursor, but if this is running in any kind of scale or load or with many users, it will not perform well. The quirky update, explained in the article, does a better performance job, but it takes a little to work through.
Ultimately what you have to do is work through the table, comparing groups of rows with other groups of rows for the same time ID.
My apologies, I don't have time to dig in and write the code, but it's a good exercise to take that article and rework it for your situation. Work with a single set of IDs, then adding some grouping to it.
Is there a function to multiply the values in a grouping in SSRS? If there is, and if this is a plausible route for the OP, it may be easier...
Jared
Jared
CE - Microsoft
October 27, 2011 at 7:19 am
Steve Jones - SSC Editor (10/26/2011)
Yes, I think some running total is needed. Something like this: http://www.sqlservercentral.com/articles/T-SQL/68467/The problem is that you cannot do the division in the same way as you're doing the multiplication since the subtraction in an aggregate isn't the same as the addition (SUM).
The easiest way is with a cursor, but if this is running in any kind of scale or load or with many users, it will not perform well. The quirky update, explained in the article, does a better performance job, but it takes a little to work through.
Ultimately what you have to do is work through the table, comparing groups of rows with other groups of rows for the same time ID.
My apologies, I don't have time to dig in and write the code, but it's a good exercise to take that article and rework it for your situation. Work with a single set of IDs, then adding some grouping to it.
Would i perhaps be able to divide by multiplication by using reciprocal or inverse values?
kind of like what this achieves...
DECLARE @value1 Decimal(10,4), @value2 Decimal(10,4), @value3 Decimal(10,4), @powerof Decimal(10,4), @Result1 Decimal(10,4), @Result2 Decimal(10,4), @Result3 Decimal(10,4);
SET @value1 =2.000;
SET @value2 = 10.000;
SET @value3 = 20.000;
SET @powerof = -1.000;
SET @Result1 = POWER(@value1, @powerof);
SET @Result2 = POWER(@value2, @powerof);
SET @Result3 = POWER(@value3, @powerof);
--SELECT (@Result1);
--SELECT (@Result2);
--SELECT (@Result3);
SELECT (@value1 * @Result2 * @result3);
SELECT (@value1 / @value2 / @value3);
November 1, 2011 at 12:02 pm
I would like to do the same for division so for 01/01/2011 00:00 my calculation should be 2 / 4 / 6
You must have some kind of sequencer, right? That tells you that you need 2/4/6 rather than 6/4/2? The result of multiplication is not affected by the order of the operands, but the result of division is.
For example:
select '2*4*6', 2.00*4*6
union select '4*2*6',4.00*2*6
union select '6*4.2', 6.00*4*2
union select '2/4/6', 2.00/4/6
union select '4/2/6', 4.00/2/6
union select '6/4/2', 6.00/4/2
All the multiplications are the same, but all the divisions are different.
If you can identify the initial number that you want to divide, you should be able to get the log of the divisor as the sum of the logs of the inverses.
November 1, 2011 at 12:12 pm
mthurber (11/1/2011)
I would like to do the same for division so for 01/01/2011 00:00 my calculation should be 2 / 4 / 6You must have some kind of sequencer, right? That tells you that you need 2/4/6 rather than 6/4/2? The result of multiplication is not affected by the order of the operands, but the result of division is.
For example:
select '2*4*6', 2.00*4*6
union select '4*2*6',4.00*2*6
union select '6*4.2', 6.00*4*2
union select '2/4/6', 2.00/4/6
union select '4/2/6', 4.00/2/6
union select '6/4/2', 6.00/4/2
All the multiplications are the same, but all the divisions are different.
If you can identify the initial number that you want to divide, you should be able to get the log of the divisor as the sum of the logs of the inverses.
You are correct in that division, order of operations matters. How do YOU know which order to divide? I have to be honest, the reason that there is not a lot of help here is because this seems like an analysis that has no meaning. I cannot think of any statistical relevance to what is being done here. If you can explain the ultimate goal maybe we could suggest an alternative approach that will be better; i.e. what question is being answered by the mathematical operations? Personally, I have 3 full years of learning/teaching scientific and business statistics under my belt and cannot figure out what is being done here. Doesn't make it wrong or stupid, it would just be helpful to know.
Thanks,
Jared
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply