April 16, 2012 at 2:05 pm
SELECT SUM(I1) FROM TABLE_THAT_HAS_I1_AS_A_COLUMN
April 16, 2012 at 2:06 pm
hbtkp (4/16/2012)
i dont know what is cumulative formula.but this is how i am getting my final data in report.
so based on how they calculate , i need to do implement that formula in sql.
this result is accurate.i need to figure out formula based on that
Well how do you expect somebody else to help with a formula when you can't explain how to calculate it?
Let me try to paraphrase in extremely clear english what your questions is.
The chemical billfold yields to a crazy accent. Behind the fan waits my dress. The infant mortal pilots an ace near a stretch wash. A farewell word sneaks with the exit.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2012 at 2:07 pm
hbtkp (4/16/2012)
i dont know what is cumulative formula.but this is how i am getting my final data in report.
so based on how they calculate , i need to do implement that formula in sql.
this result is accurate.i need to figure out formula based on that
Then the first thing you need to do is find out from you colleagues how that is calculated (because we sure as hell don't know) and work from there.
Whatever it is, it's not a standard calculation and there is no way we can guess what your colleagues may have done.
For the nth time, speak to your boss/manager/colleagues and ask them for help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2012 at 2:11 pm
hbtkp (4/16/2012)
i dont know what is cumulative formula.but this is how i am getting my final data in report.
so based on how they calculate , i need to do implement that formula in sql.
this result is accurate.i need to figure out formula based on that
Here is the values you originally posted.
NUMBER CUMULATIVE
0.00
2.78 2.78
1.01 3.82
2.07 5.97
1.71 7.78
2.21 10.16
0.23 10.41
0.69 11.17
-0.01 11.16
-1.70 9.27
5.51 15.29
-2.47 12.44
-4.28 7.63
2.97 10.82
-2.50 8.06
2.38 10.62
You are telling me you can't tell me how to calculate the value in the second column based on the first value and the values that precede the row? If you can't tell us that, how do you expect us to tell you how to code it?
Please get real. You have got to start helping us to understand what it is you are trying to accomplish. We can not read your mind, nor can we see what you are doing, nor can we access your systems to figure out what you need to do.
Answer us this, have you even bothered to read this article: http://qa.sqlservercentral.com/articles/Best+Practices/61537/?
If not, why not? If so, why haven't you bothered to provide us with what we have been asking you to provide?
April 16, 2012 at 2:15 pm
this is what i have to figure out ,how they calculate it.
April 16, 2012 at 2:17 pm
Well figure that out and get back to us. We can help you recreate a formula in sql. We will not be able to help you figure out what that formula is. That formula is a business rule not a sql thing. We are experts at SQL not your business.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2012 at 2:18 pm
hbtkp (4/16/2012)
this is what i have to figure out ,how they calculate it.
Ask the people you work with. If they can't tell you, you are straight out of luck. Sorry.
April 17, 2012 at 9:16 am
How about
SELECT *
FROM
(
VALUES
( 0, NULL ),
( 2.78, 2.78 ),
( 1.01, 3.82 ),
( 2.07, 5.97 ),
( 1.71, 7.78 ),
( 2.21, 10.16 ),
( 0.23, 10.41 ),
( 0.69, 11.17 ),
( -0.01, 11.16 ),
( -1.7, 9.27 ),
( 5.51, 15.29 ),
( -2.47, 12.44 ),
( -4.28, 7.63 ),
( 2.97, 10.82 ),
( -2.5, 8.06 ),
( 2.38, 10.62 )
) AS UnexplicableCumulative (item2, item3)
That should do the trick.
Sorry, couldn't help it 😀
April 17, 2012 at 9:21 am
hbtkp (4/13/2012)
i dont need to change field, i have filedCREATE FUNCTION item1(@Data [varbinary](max))
RETURNS TABLE (
item2 varchar,
item3 int)
now i need to add more rows in item3, and i need to implement in another sp.
I especially like the varbinary(max) parameter. What is it you are passing in there? The contents of an entire FaceBook NoSQL database?
April 17, 2012 at 9:25 am
Jan Van der Eecken (4/17/2012)
How about
SELECT *
FROM
(
VALUES
( 0, NULL ),
( 2.78, 2.78 ),
( 1.01, 3.82 ),
( 2.07, 5.97 ),
( 1.71, 7.78 ),
( 2.21, 10.16 ),
( 0.23, 10.41 ),
( 0.69, 11.17 ),
( -0.01, 11.16 ),
( -1.7, 9.27 ),
( 5.51, 15.29 ),
( -2.47, 12.44 ),
( -4.28, 7.63 ),
( 2.97, 10.82 ),
( -2.5, 8.06 ),
( 2.38, 10.62 )
) AS UnexplicableCumulative (item2, item3)
That should do the trick.
Sorry, couldn't help it 😀
Personally, I attribute the values in the second column to poor math skills. If that column is correct, I would like to see the formula used in the calculation because I can't decern a pattern.
April 17, 2012 at 9:30 am
Looks like OP need just a running total.
My be sample provide is not very exact 🙂
April 17, 2012 at 9:33 am
Eugene Elutin (4/17/2012)
Looks like OP need just a running total.My be sample provide is not very exact 🙂
Already suggested that and was told no, it wasn't a running total. Still waiting for the OP to explain what it is.
April 17, 2012 at 9:58 am
hbtkp (4/16/2012)
it snot running total,if you see it carefully,its not just adding values. sometimes its adding 1 ,sometimes it minus 1
Can you tell please which rows of provided samples do you refer to? Where it does add 1 and wher it does -1?
If you do the following:
SELECT IDENTITY(int,1,1) as RN,*
into #t
FROM
(
VALUES
( 0, NULL ),
( 2.78, 2.78 ),
( 1.01, 3.82 ),
( 2.07, 5.97 ),
( 1.71, 7.78 ),
( 2.21, 10.16 ),
( 0.23, 10.41 ),
( 0.69, 11.17 ),
( -0.01, 11.16 ),
( -1.7, 9.27 ),
( 5.51, 15.29 ),
( -2.47, 12.44 ),
( -4.28, 7.63 ),
( 2.97, 10.82 ),
( -2.5, 8.06 ),
( 2.38, 10.62 )
) AS UnexplicableCumulative (item2, item3)
alter table #t add rt decimal(10,2)
create unique clustered index ix_#t on #t(rn)
declare @rt decimal(10,2)
set @rt= 0
update #t
from #t
option (maxdop 1)
select *, rt-item3 as [Diff to running total] from #t
You can see:
RNitem2item3rtDiff to running total
10.00NULL0.00NULL
22.782.782.780.00
31.013.823.79-0.03
42.075.975.86-0.11
51.717.787.57-0.21
62.2110.169.78-0.38
70.2310.4110.01-0.40
80.6911.1710.70-0.47
9-0.0111.1610.69-0.47
10-1.709.278.99-0.28
115.5115.2914.50-0.79
12-2.4712.4412.03-0.41
13-4.287.637.750.12
142.9710.8210.72-0.10
15-2.508.068.220.16
162.3810.6210.60-0.02
The fluctuations in bold, means:
1. your sample is not exact as should be
or
2. there are another data involved in calculation
or
3. Your formula is based on complex differential equation
If your case is No 1 then you just need a running total
If your case is No 2 then you need to find what other data involved
If your case is No 3 then you need to find what formula is used or consult top mathematician/quant engineer to build one, but he will ask you for much larger sample and it will cost, most likely, your annual salary without guaranteed solution
April 17, 2012 at 10:23 am
ok. i already posted my formula for cumulative.
i hv another prov
i have delcare variable
declare @abd datetime
now i need to get 1 date from function
like
select @abd = fromdate from item2(@rdfd)
fromdate hace same value in all filed
so i just want 1 value
can i use like this
select @abd =distinct fromdate from item2(@rdfd)
i cannot use where ,this is udf
April 17, 2012 at 10:36 am
hbtkp (4/17/2012)
ok. i already posted my formula for cumulative.i hv another prov
i have delcare variable
declare @abd datetime
now i need to get 1 date from function
like
select @abd = fromdate from item2(@rdfd)
fromdate hace same value in all filed
so i just want 1 value
can i use like this
select @abd =distinct fromdate from item2(@rdfd)
i cannot use where ,this is udf
You didn't provide any formula in this post! Here is your words:
i dont know what is cumulative formula.
but this is how i am getting my final data in report.
so based on how they calculate , i need to do implement that formula in sql.
this result is accurate.i need to figure out formula based on that
And for your another problem:
Selecting value into variable will guarantee that you will have only one of it regardless of using WHERE or DISTINCT as single non-table type variable may only hold single value.
The problem is you never know which value is taken from range, but if in your case the function returns list of dates and all of them the same, you shouldn't care: the following will work just fine:
select @abd = fromdate from item2(@rdfd)
BTW, if your UDF is table-valued and it is table valued as you select from it, you can use WHERE!
I've seen you claiming to know the SQL basics... I'm afraid you're wrong mate.
Viewing 15 posts - 46 through 60 (of 85 total)
You must be logged in to reply to this topic. Login to reply