Median Calculation Wrong

Mar 4, 2008



I used the Median() function in a calculated measure in a cube. All seemed well until one of my users, a statistician, pointed out that the displayed values were incorrect. I investigated this and finally built the simplest cube with three records and the displayed median does not make sense, i.e. equal what a median value should be.

The 3 records have loan amounts as a measure with these values: $102,500, $168,400, and $172,181 and loan number keys of 1, 2, and 3. That's it.

The median should be $168,400 since the number of items is odd and that is the middle value.

But the cube calculated measure displays $170,290.50. It appears to be taking the average of the middle row and the next value.

When I increase the number of records I get the same odd behavior but it not only alternates (of course) with odd and even (since they use different formulats for odd and even) but the odd wrong results alternate within themselves. The apparent calculations for 5 or 7 records are different compared to 3, 9, 11, 13, 15, 17 records. The first set seems to be calculating odd number of items median by averaging the middle value + the value BEFORE the middle value while the second set of odd rows (3, 9, etc) seem to be calculating median by averaging the middle value + the value AFTER the middel value. The even numbers result in the larger of the two middle values being selected instead of the average (financial median) or the lower number (as one poster claimed for statistical median).

My calculated measure MDX is very simple:


MEDIAN( [Test Fact].[Loan Number].Members, [Measures].[Loan Amount])

Ideas anyone? Am I missing something? Is this a bug?

Thanks!

- Grant

View 6 Replies


ADVERTISEMENT

Analysis :: Median Calculation Using MDX

May 28, 2015

 I have a fact table fct_line_details having two columns mtid, productivity

  mtid     productivity
  1           400
1           200
1          600
2          700
3         900

I want to calculate the median for each mtid in SSAS . (median for mtid 1=400 )

View 2 Replies View Related

Transact SQL :: Multiple Columns Needs Median Calculation

Oct 27, 2015

I need to get the median for the 10 columns in my table. For the sake of example, I've reduced it to 2 columns. 

The code below works perfectly if i compute for only 1 column and certainly doesn't for multiple columns.

Is there a way to better handle the median computation in one pass, if multiple columns are involved?

DECLARE @tbl as table (id_n int, col1 int, col2 int)
insert into @tbl
values (1, 1, 2), (1,3, 4), (1, 5, 7), (2, 4, 7), (2, 7, 7), (2, 3, 5), (2,5, 5), (3, 1, 2), (3, 3, 5), (3, NULL,11)
select *
from @tbl
order by id_n, col1

[Code] .....

View 6 Replies View Related

Analysis :: SSAS Calculation With Division Combined With A Time Calculation?

Sep 17, 2015

I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.

(so I can use measure security on calculations

as explained here  )

SCOPE [Measures].[C];

THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);

View 2 Replies View Related

Converting Oracle Calculation To Sql Server 2005 Calculation

Jul 19, 2007

Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the

calculations.

Oracle

TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')



this is a sfar as I have got with the sql version

SQLSERVER2005

= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )



visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.



using:

VS 2005 BI Tools

SQLServer 2005



View 5 Replies View Related

Sql Server 2005 Inserting Prbblem..wrong SQL? Wrong Parameter?

Feb 19, 2006

Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code:        Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _        ByVal Email As String, ByVal Gender As Integer, _        ByVal FirstName As String, ByVal LastName As String, _        ByVal CellPhone As String, ByVal Street As String, _        ByVal StreetNumber As String, ByVal StreetAddon As String, _        ByVal Zipcode As String, ByVal City As String, _        ByVal Organization As String _        ) As Boolean            'returns true with success, false with failure            Dim MyConnection As SqlConnection = GetConnection()            Dim bResult As Boolean            Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection)            MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName))            MyCommand.Parameters.Add(New SqlParameter("@Password", Password))            MyCommand.Parameters.Add(New SqlParameter("@Email", Email))            MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender))            MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName))            MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName))            MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone))            MyCommand.Parameters.Add(New SqlParameter("@Street", Street))            MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber))            MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon))            MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode))            MyCommand.Parameters.Add(New SqlParameter("@City", City))            MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization))            Try                MyConnection.Open()                MyCommand.ExecuteNonQuery()                bResult = True            Catch ex As Exception                bResult = False            Finally                MyConnection.Close()            End Try            Return bResult        End FunctionThanks!

View 1 Replies View Related

Better Median??

Dec 7, 2002

I recently had to use my own little median technique again on a report here at work, and had posted it before, but wasn't sure if anyone had seen it. I have read Celko's and others techniques for generating a median and haven't seen one more efficent.

Does anyone have a better way they can think of? I think this bad boy is pretty short & efficient.

First, if you want to return the middle number or the higher one next to the middle if there is an even number:

SELECT x.Value AS median
FROM Vals x
CROSS JOIN Vals y
GROUP BY x.Value
HAVING SUM(SIGN(x.Value-y.Value)) IN (1,0)

Change the " IN (1,0)" to "IN (-1,0)" to get the lower value if there is an even # of values.

Basically, we are saying compare each number to all possible numbers, and add up values of 1,0 or -1 depending if the first number is less, equal or higher than the second. The number that returns 0 is right in the middle ... If there is no middle, a -1 or 1 is returned. There will never be a 0 and (-1 or 1) at the same time returned.

To get the financial median (avg of the 2 values middle values if there is an even number), you need to encapsulate the results of the above into a subquery, allow for not just (-1,0) but all three (-1,0,1) and then take the AVG of the values returned.

That is,

SELECT Avg(Median) as Median FROM
(
SELECT x.Value AS median
FROM Vals x
CROSS JOIN Vals y
GROUP BY x.Value
HAVING SUM(SIGN(x.Value-y.Value)) IN (1,0,-1)
) A

If there is an even number of values, the lower and higher middle ones are averaged. If there is an odd number, only the middle value is returned and averaged (which of course has no effect).

Most other techniques used several COUNT(*) subqueries which this one avoids.

Critique and enjoy!

- Jeff

View 7 Replies View Related

New To SQL, Need To Get The Median Value

Sep 27, 2007

I have a table that contains the following:
customer (ID of customers)
product (Product description numeric value)
UOM (unit of measure like each or pak)
avgprice (Avg proce that this product and uom was sold)


I need to find the median value for a product, uom. Then I need create a table that shows product,uom,avgprice,median grouped by product and uom
I have been at this for two days with no luck

Thanks in advance for any help




View 6 Replies View Related

Calculating Median In SQL

Aug 25, 2004

Hi All,
I have a table that of server names and their execution times that run in to hundreds of thousands of records. What i need is some SQL that gives me the median execution times for each of these different servers. At the moment i have some SQL that only gives me the median for all the records in the table not the median execution time for every different server name. For example my tables looks something like this;

ServerName | ExecTime
-----------------------
server1 | 0.07
server2 | 0.17
server1 | 0.27
server1 | 0.37
server2 | 0.47
server1 | 0.57
server1 | 0.67
server2 | 0.77

My SQL below gives me

ServerName | ExecTime
-----------------------
server1 | 0.37

Where as i want

ServerName | ExecTime
-----------------------
server1 | 0.37
server2 | 0.47

Here is my SQL, hope someone can modify it and thanks in advance.


Code:


SELECT DISTINCT instance, exec_time AS median
FROM (SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time) sub_a
ORDER BY 1 DESC) sub_1
UNION ALL
SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time DESC) sub_b
ORDER BY 1) sub_2)

View 4 Replies View Related

How To Find The Median?

Jan 28, 2006

I've noticed that SQL Server (and other DBMSs I've looked at) doesn't seem to have a built-in function for finding the median of a range of numbers.

Gack!

View 14 Replies View Related

Median Function

Sep 28, 2006

I have a long term need for a median function, I was wondering if anyone has or knows of some code or user defined functions somewhere that would do this. Ideally you could use it just liket the rest of the aggregate functions like AVG, etc.

View 1 Replies View Related

Finding Median Value

Mar 12, 2007

Hi,

I have looked all over the web to try to find some very basic / simple explanations of how to get a median value from a group of records in a table but with no luck

the problem i am having is that all the information i find is always centered around getting a median using every single row in the table. except i have groups of data in the table and want to work out a median for each group. the group is identified by 4 different columns (the 5th column is what i want to get the median on but for each group not the entire table) and i want to produce a resulting table that has 1 row for each group and therefore contains the median value for the group instead of the individual numbers that it currently has. e.g. the current table is like this

column1 column2 column3 column 4 column5(median of this)

value 1 value 2 value 3 value 4 1.2
value 1 value 2 value 3 value 4 1.0
value 1 value 2 value 3 value 4 1.5
value 2 value 3 value 4 value 5 0.2
value 2 value 3 value 4 value 5 0.4
etc...

and i need a query to get the results to show like this

column1 column2 column3 column 4 column5

value 1 value 2 value 3 value 4 1.0
value 2 value 3 value 4 value 5 0.3
etc...

This is driving me crazy and i will be very helpful if anyone can help

the statement i need to add it to is:

select pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate, avg(srtot)as meansrtot, avg(sreff)as meansreff, avg(BFRaw)as BF_Rawmean, avg(BFTGV)as BF_TGVmean, count(srtot)as countsrtot, count(sreff)as countsreff
from bodyparametersf
where (srtot is not null) OR (sreff is not null)
group by pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate

thanks very much

View 16 Replies View Related

Calculate Median!!!

May 29, 2007

I need to calculate Median on each calculated result from the query below. There is one Median function available in SQL2K but it is not working. Can anyone help me in this regard.

------------------------------------------------------------------------
SELECT INS.Code As [code],INS.FinYr as [YEAR], ' ' As [FIRE BUSINESS], (INSRev.FI_NetPremLessIns / INSRev.FI_GrPremium) * 100 As [Rention Ratio],
(INSRev.FI_NetClaimPaid/INSRev.FI_AdjNetPremium)*100 As [Claim Ratio], ((INSRev.FI_AgencyCommPaid+INSRev.FI_ReInsCommPaid+INSRev.FI_MgmtExpenses+INSRev.FI_OthExpenses)/INSRev.FI_AdjNetPremium)*100 As [Expense Ratio],
((INSRev.FI_NetClaimPaid/INSRev.FI_AdjNetPremium)*100)+(((INSRev.FI_AgencyCommPaid+INSRev.FI_ReInsCommPaid+INSRev.FI_MgmtExpe nses+INSRev.FI_OthExpenses)/INSRev.FI_AdjNetPremium)*100) As [Combine Ratio],
(INSRev.FI_ClosingBal/INSRev.FI_NetClaimPaid) As [Unexpired Risk Reserve to Net Claim(x)],(INSRev.FI_MgmtExpenses/INSRev.FI_AdjNetPremium)*100 As [Management Expenses to Adj. Net Premium],
(INSRev.FI_AgencyCommPaid/INSRev.FI_AdjNetPremium)*100 As [Agency Commissioned to Adj. Net Premium]

FROM
(InsuranceGen As INS LEFT JOIN INSURANCEGen As INS1 ON (INS1.FinYr=INS.FinYr-1 AND INS.Code=INS1.Code))
LEFT JOIN INSRevGen as INSRev ON (INS.Code=INSRev.Code AND INS.FinYr=INSRev.FinYr) WHERE INS.Code IN ('ABC1','ABC2','ABC3') AND INS.FinYr=2005 ORDER BY INS.Code, INS.FinYr
----------------------------------------------------------------------

Thanks

View 5 Replies View Related

Median Function

Sep 7, 2007

Im trying to find a funtion for median's if there is one. can anyone help?

View 3 Replies View Related

Calculate Median LTM

Apr 22, 2008

Using SQL Server 2005, I have the following query to calculate the median sales of each quarter over the past 5 years:


WITH CompMedian AS

(

SELECT SoldDate, SoldPrice, ROW_NUMBER() OVER(PARTITION BY Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2)) ORDER BY SoldPrice) AS RowNum, COUNT(*) OVER(PARTITION BY Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2))) AS Cnt FROM tbl_Orders
WHERE Status = 'Sold'

AND SoldDate >= DATEADD(Year, -5, Convert(DateTime, Convert(Varchar(5),Month(GetDate())) + Convert(Varchar(5), '/1/') + Convert(Varchar(5), YEAR(GetDate()))))

AND SoldDate < DATEADD(Year, 0, Convert(DateTime, Convert(Varchar(5),Month(GetDate())) + Convert(Varchar(5), '/1/') + Convert(Varchar(5), YEAR(GetDate()))))
)

SELECT Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2)) AS CompDate, AVG(SoldPrice) AS CompMedian

FROM CompMedian

WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2)

GROUP BY Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2))

ORDER BY CompDate;


Now my client would like me to change the query so that each quarter would represent the median for the past 12 months ending with that quarter. I've been looking at this for hours and I'm at a loss. Anyone have any thoughts?

Thanks in advance,
Russ

View 2 Replies View Related

Calculate Median Value

Dec 15, 2005

I am converting a report created using Crystal Reports 10 to Reporting Services.  The report contains a list of items with dollar values.  The original report displays both the Average and Median value.  I can easily ( using avg(Field1.Value!) ) determine the average but cannot find a function to determine the median.

How can I add the Median to the report?

 

Thanks in advance,

Greg

View 21 Replies View Related

Median Query Without Using Function

Feb 24, 2006

I have a table (cars) with 3 fields:VIN, Class, sell_price101, sports, 10000102, sports, 11000103, luxury, 9000104, sports, 11000105, sports, 11000106, luxury, 5000107, sports, 11000108, sports, 11000109, luxury, 9000i need to write a query that WITHOUT USING A FUNCTION will return themedian selling price for each class of car. result should look like:Class, Med_Priceluxury, 9000sports, 11000thanks to all u SQLers

View 4 Replies View Related

The Median Function In SQL Server

Jul 20, 2005

I read the follow query about calculating median posted by Daivd Portaon 10/8/03.CREATE TABLE SomeValues (keyx CHAR(1) PRIMARY KEY, valuex INTEGER NOTNULL)INSERT INTO SomeValues VALUES ('A',1)INSERT INTO SomeValues VALUES ('B',2)INSERT INTO SomeValues VALUES ('C',3)INSERT INTO SomeValues VALUES ('D',4)INSERT INTO SomeValues VALUES ('E',5)SELECT S1.valuex AS medianFROM SomeValues AS S1, SomeValues AS S2GROUP BY S1.valuexHAVING SUM(CASE WHEN S2.valuex <= S1.valuexTHEN 1 ELSE 0 END)[color=blue]>= ((COUNT(*) + 1) / 2)[/color]AND SUM(CASE WHEN S2.valuex >= S1.valuexTHEN 1 ELSE 0 END)[color=blue]>= (COUNT(*)/2 + 1)[/color]I have difficulty to understand the having clause. If S1 and S2 arethe same table, what it means by S2.valuex >= S1.valuex? Could somegive me a help?Also, if I have a table structured as:classID field1 field2 field3c1 1 2 3c1 4 5 6c1 7 8 9c2 9 8 7c2 6 5 4c2 3 2 1Is there a way to create a user-defined function that can get themedian for each field as easy as the average function. Such asselect distinct classID,median(field1),median(field2),median(field3)from [tablename]group by classIDThanks in advance

View 2 Replies View Related

Median Function In T-SQL For SS2005?

Oct 13, 2006

Hello!

I have been trying to find a T-SQL function that would calculates a Median statistical value for me. I am runnnig on SS 2005? Any examples of using this function would be greatly appreciated.

Thanks for any responses!





View 7 Replies View Related

Using Report Builder 3.0 To Calculate Median?

Sep 14, 2015

I'm using custom code in an expression to calculate the median of a column. It works fine up to a point. Like if there are 35 rows in the result set (or up to some number) but when it gets bigger results, like 42 rows or more it doesn't work, the median is -1. This is the custom code I'm using (found online).

I don't see anything limiting the count, but it comes back with a -1 median so I think that means the count is not > 0.

I have a column in the report called arrival_to_complete which is like: 53 min

I create a column with expression: =MAX(Code.AddValue(Val(Fields!Arrival_to_complete.Value))) to fill the array using just the number part of the column. Then in the report I have 'Median =' <expression>, where the expression is: =Code.GetMedian() I run the report with 2 parameters a begin date and an end date. I don't see where any of this should be limiting the median calculation so I don't get why it works sometimes and not other times.

Dim values As New System.Collections.ArrayList
Function AddValue(newValue As Decimal)
If values is Nothing Then
values = New System.Collections.ArrayList
End If
values.Add(newValue)

[code]....

View 0 Replies View Related

Calculating Median Value From Measures And Dimensions

May 24, 2007

I am facing some problem in calculating Median

I am trying to calculate the median value using one of the measures and a dimension value.



Time is a measure in my cube and OpId is one of the dimensions.The result is as follows:



opid time median

1 55

2 23

3 23

Total 23



The Time here for Op Id 1 is the aggregation for all the rows whose OpId is 1.I want the median of the values whose OpId is 1 which is not showing at the moment.



What I am getting here is the median for all of the OpId but what I really want is the median for each of the individual Opid's as well.



I am using a calculated field Median with the following expression.



MEDIAN

( [Dim Operation].[Dim Operation].currentmember.children ,[Measures].[Elapsed Time])



Thanks

View 1 Replies View Related

Calculate Median Of Difference In Days Between Records

Jan 24, 2006

I have a table of sample data

Samples(sample_no, sample_date..)

I have no idea how to do the following in sql server or if its even possible:

1. Calculate the difference in days between all samples.
2. Select the median result

Any trick to get this done would be really helpful

thanks,

DB

View 3 Replies View Related

Calculate Median Of Difference In Days Between Records

Jan 24, 2006

I have a table of sample data

Samples(sample_no, sample_date..)

I have no idea how to do the following in sql server or if its even possible:

1. Calculate the difference in days between all samples.
2. Select the median result

Any trick to get this done would be really helpful

thanks,

DB

View 10 Replies View Related

Calculating Median Values On Column In A Table

Feb 19, 2012

I need to calculate a median on a column in a table. The code I have is:

Code:
Select gender,
CASE
when gender = 'F' then 'Female'
when gender = 'M' then 'Male'
else 'Unknown'
end as test,
datediff(day, [admit_date], getdate()) as 'datediffcal',
from [tbl_record]
How do I calculate the median on the datediffcal column?

It doesn't matter if the resultset only shows the median result. So if the output shows:

median
15

that's fine. Minimally, I need the median value.

View 5 Replies View Related

Simulating Median On Table Columns In SQLserver2000

Apr 22, 2004

Folks,

I have a an sql table having 10,000 Rows.

The table has 5 columns. They are S1,S2,S3,S4 and M

I need the best way to calculate the MEDIAN for each row and store it in M. i.e. M=median(S1,S2,S3,S4)

I've come across articles where they've calculated medians on table rows, not table columns. But my requirement involves table columns. I guess transposing the columns into rows and then calculating median should be possible, but if I do that for 10000 rows using a cursor, then it would take a loooooong time.

Please suggest the best way to counter this

Thanx

Kiran

View 1 Replies View Related

SQL Server 2008 :: Calculating Median Over 2 Columns?

Aug 13, 2015

I have a database with 1million+ records in and i'm trying to collect the median values of column(2) for all distinct values in column (1)

Example DB:

Column 1 Column 2
978555 500
978555 502
978555 480
978555 490
978324 1111
978324 1102
978311 122
978311 120
978994 804
978320 359

and I need it to display on SELECT as

column 1 column 2
978555 495
978324 1106
978311 121
978994 804
978320 359

Is this possible on 2008 R2?

View 7 Replies View Related

T-SQL (SS2K8) :: How To Reduce Execution Time Of Median Calculations

Apr 7, 2015

I wrote a procedure to calculate median:

============================
ALTER proc [dbo].[sp_CalculateMedianTimeInDepartmentMinutes]

@StartDate date
,@EndDate date
as
--== Check if count is even or odd
declare @modulo int
select @modulo = (Select COUNT(*)%2 from ED_data where AdmitDateTime between @StartDate and @EndDate )
--=== Get Median

[Code] ....

My fellow developer is using this code to calcuate a madians in many columns (see below). The problem is that it takes about 2 minutes to execute this code. Is there a way to reduce the time of execution?

I attach also a sample of the view

==============
ALTER PROCEDURE [dbo].[sp_ED_Measures]
@StartDate date,
@EndDate date,
@Hospital varchar(5)
AS
BEGIN
SET NOCOUNT ON;

[Code] ......

View 5 Replies View Related

Analysis :: Calculating A Rolling Median Over A Period Of 3 Years?

Jun 17, 2015

calculating a rolling median over a period of 3 years.

I already calculate median and I've tried to calculate rolling median over a period of 3 years as below.

 MEDIAN([Date].[Year].CurrentMember.Lag(3):[Date].[Year].CurrentMember,[Measures].[median])

What this does is, it calculates the median of the medians over the period of 3 years. But, what I'm looking for is the overall median of the underlying measure over a period of 3 years.

What I have now:

Year1 - 41,52,73;  Median1 - 52
Year2 - 6,9,12;  Median2- 9
Year3 - 24,68,89; Median3 - 68
Overall Median of 9,52,68 - 52

What I need:

Year1 - 41,52,73;  Median1 - 52
Year2 - 6,9,12;  Median2- 9
Year3 - 24,68,89; Median3 - 68

Overall Median of 41,52,73,6,9,12,24,68,89 is 41 

View 4 Replies View Related

Where To Do The Calculation In VB.NET Or In SQL?

May 4, 2008



Hi,

My predicament is - where do I do these calculations - in my vb.net code or in an SQL stored procedure?

My manager has handed me a task of converting an excel file she uses in to a web aplication.

While it has been easy to devise what should be the screens and how to capture data, I am struggling over how to code the calculations.


The calculations in excel are pretty simple. These are just sequential calculations (about a 150 calculation for average 500 rows). Mathametical operations include sum, average, max min - regular excel stuff. Some calculations involve vlookup (equvalent to calculation based on value derived from a reference table).


So I am stil wondering - where do I do these calculations - in my vb.net code or in an SQL stored procedure?


Since these calculations are required a produce a result in an online environment, what will be faster?


I tried to do a proof of concept by creating a sample calculation in a .NET class and an in a stored procedure. The choice is still not clear. SQL code execution time was not bad. But SQL code tended to be very messy.VB.net code seemed to be a little slow. But seemed a more organised to look at.


Any views that you can offer will be very helpful.

Thanks in advance.

PMA

View 6 Replies View Related

Help W/ Calculation

Aug 3, 2007

I need to calculate the overall GPA for a student in a particular class.


YEAR SCHOOL STUDENT IDENT GRADE TEACHER CLASS GPA
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 1.6700
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.3300
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 1.0000


The problem I'm having is that a student may not taken the class for four terms (as in the Elementary Algebra example above). So I can't hard code it to sum the gpa and divide by 4; it needs to be the number of terms the student took the class.


Here's my sql:


select
trnscrpt.schyear as [Year],
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as suniq,
stugrp_active.graden as Grade,
trnscrpt.teachname as Teacher,
trnscrpt.descript as Class,
gpamarks.gpavallvl0 AS GPA

from
dbo.trnscrpt
inner join dbo.stugrp_active on trnscrpt.suniq = stugrp_active.suniq INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark

where
trnscrpt.graden >= 6 and
trnscrpt.markawd1 not in ('NC','NG','P','W','WA','WF','WI','WP') and
trnscrpt.subjectc in ('LA', 'MA', 'CP', 'CB') and
trnscrpt.schyear = 2007 and
stugrp_active.schoolc = 725

order by
school.schname,
student,
grade,
class

View 3 Replies View Related

MDX Calculation Help

May 28, 2008

Dimensions:

DimPeriod/Year-Quarter-Month
DimProduct/Category-Product
Fact:
FactInventory/PeriodKey-ProductKey-StatusCode


Period = 1
Product = 1
Status Code = InStock


Period = 1
Product = 2
Status Code = InStock



Period = 2
Product = 1
Status Code = OutOfStock

Period = 2
Product = 2
Status Code = InStock


In period = 2, status code change from InStock to OutOfStock: Product 1 (Count=1)
In period = 1, number of products with status code = InStock: product 1 and product 2 (Count=2)


The measure = 1 / 2 or 50%. TIA

View 2 Replies View Related

Calculation

Oct 9, 2007



This is a smple data in table1

sector RefDate price
pharm 22 august 2007 100.21
gap 15 august 2007 10.32
pharm 21 august 2007 99.99
pharm 9 oct 2007 100.99
pharm 2 oct 2007 98.34
pharm 8 oct 2007 96.34
...

I would like to have the result as follows:
sector RefDate price priceChangeSinceYesterday priceChangeSinceLastWeek priceChangeSinceLastMonth
pharm 9 oct 2007 100.99 100.99-96.34 100.99-98.34 100.99-lastmonth's price value

select
sector,
RefDate,
price,
priceChangeSinceYesterday??,
priceChangeSinceLastWeek???,
priceChangeSinceLastMonth??
from
table1

thanks

View 3 Replies View Related

Calculation

Apr 2, 2008



My aim is to do something like what I have explained below and I was planning on building this logic at the Database level only rather than on the frontend code.

There are certain allocations(transactions) that happen on a periodic basis and I am storing these transactions in the PurchaseTranMaster and PurchaseTranDetail table. These transactions are categorized as 'Main' type and the amount could be allocated for one or many categories in a single transaction. Below is how it will be saved in the 2 table


PurchaseTranMaster


TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 1 9-March-2008 Main


PurchaseTranDetail



TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C


Now what happens is users of my application can make purchases under all these categories only until the Balance under these categories is > than purchase amount. The Balance is calculated as sum of all transactions. It means that w.r.t the above data the balances for each category is(this is not stored in the database)

A 1300
B 3000
C 400

So lets say a user does make a purchase(Trantype is 'SUB') of 300 under A and 400 under B in a single transaction. The data would then be stored in the tables as


PurchaseTranMaster


TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 19-March-2008 Main
4 20-March-2008 SUB


PurchaseTranDetail


TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
4 300 A 1
4 400 B 1


In the PurchaseTranDetail the Debit_TranId value means that the amount has been marked against the TranID 1. This TranId is not handpicked by the user and the system should allocate it accordingly based on the amount available for a particualar category for a Main Transaction. It means that before TranId 4 was saved in the database then the system would first check whether the Total available balance for A >=300 and B>=400 (in our example above it is 1300 and 3000 resp)
Then if the Balance is > than the puchase amount then the allocation would be done by the system and this would be done against the TranID whose TranDate was the earliest, so thats why the Debit_TranId column has 1 as TranId 1 was the earliest.so logically now the balance for the categories would be (this is not saved in the database)

A 1000
B 2600
C 400

So next time again when a user would make a purchase(transaction) under A for 800 and under B for 1000 then if the balance is greater than the purchase amount(which in this case it is) the allocation would happen according to the earliest TranId and this time amount would be partly marked against TranId 1 , TranID 2 and TranID 3. The data would look like this


PurchaseTranMaster


TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 19-March-2008 Main
4 20-March-2008 SUB
5 21-March-2008 SUB



TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
4 300 A 1
4 400 B 1
5 700 A 1
5 100 A 3
5 600 B 1
5 400 B 2


I need to do the above taking into consideration that there could be multiple users making purchases(concurrency).
Also I was building my logic on doing the above whether to use cursors or loops. I just need to know how do I write my stored procedure and what would be the most efficeint way of doing the above.

The design for creating the above sample tables is below

/*CREATE TABLE PurchaseTranMaster
(
TranID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
TranDate Datetime,
TranType varchar(30)
)


CREATE TABLE PurchaseTranDetail
(
TranID int,
Amount int,
Category Varchar(20),
Debit_TranId int
)

insert into PurchaseTranMaster values(convert(datetime,' 14-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 17-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 19-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 20-March-2008',103),'SUB')
insert into PurchaseTranMaster values(convert(datetime,' 21-March-2008',103),'SUB')


insert into PurchaseTranDetail values(1,1000,'A',0)
insert into PurchaseTranDetail values(1,1000,'B',0)
insert into PurchaseTranDetail values(2,2000,'B',0)
insert into PurchaseTranDetail values(3,300,'A',0)
insert into PurchaseTranDetail values(3,400,'C',0)
insert into PurchaseTranDetail values(4,300,'A',1)
insert into PurchaseTranDetail values(4,400,'B',1)
insert into PurchaseTranDetail values(5,700,'A',1)
insert into PurchaseTranDetail values(5,100,'A',3)
insert into PurchaseTranDetail values(5,600,'B',1)
insert into PurchaseTranDetail values(5,400,'B',2)*/

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved