July 14, 2012 at 4:13 am
Can you please help me to get the median using the cursor
How I wrote the following code using the cursor
Do I really need help please
UPDATE tblGas01G4C40Total
SET Median Consumption =
(((SELECT Max( DailyConsumption ) FROM
(SELECT TOP 50 PERCENT DailyConsumption
FROM participation_cntInformation_Archieve90 INNER JOIN
tblGas01G4C40Total ON participation_cntInformation_Archieve90.ID_NO= tblGas01G4C40Total.ID_No
ORDER BY DailyConsumption)As h1)
+
(SELECT MIN( DailyConsumption )
FROM(SELECT TOP 50 PERCENT DailyConsumption
FROM participation_cntInformation_Archieve90 INNER JOIN
tblGas01G4C40Total ON participation_cntInformation_Archieve90.ID_NO= tblGas01G4C40Total.ID_No
ORDER BY DailyConsumption DESC) AS h2))/2)
July 14, 2012 at 10:22 am
I suggest you visit this site, and there you may find a solution to your problem.
Or read this article on SSC
http://www.sqlservercentral.com/articles/SS2K5+-+CLR+Integration/3208/
July 14, 2012 at 11:56 am
I'm not sure what you're trying to do because there's no cursor in there.
You're updating a table in the worst way possible (well, there might be something worse).
To update the field with the median for the dailyconsumption I would do something like this:
DECLARE@Mediandecimal( 18, 8)
SELECT@Median = (MAX(DailyConsumption) + MIN(DailyConsumption)) / 2
FROMparticipation_cntInformation_Archieve90 p
INNER JOIN tblGas01G4C40Total g ON p.ID_NO= g.ID_No
GROUP BY CUSTNUM_CH
UPDATE tblGas01G4C40Total SET MedianConsumption = @Median
However, this is not a best practice. If you explain with more detail and post your DDL, sample data and expected results you can receive better help. Take a look at this article http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 14, 2012 at 2:00 pm
I do not like to be a kill joy but the MEDIAN is defined as:
median of a distribution with a discrete random variable depends on whether the number of terms in the distribution is even or odd.
If the number of terms is odd, then the median is the value of the term in the middle.
This is the value such that the number of terms having values greater than or equal to it is the same as the number of terms having values less than or equal to it.
If the number of terms is even, then the median is the average of the two terms in the middle,
July 14, 2012 at 2:15 pm
You're right, I got confused with the term as I haven't used it in a long time.
July 14, 2012 at 2:27 pm
Luis Cazares (7/14/2012)
You're right, I got confused with the term as I haven't used it in a long time.
Don't feel bad, I had to Google it to be reminded of how it was determined.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply