There is a limit on how long a varchar datatype variable can be. It is 8,000. It obviously not enough since this kind of variable generally used dynamical SQL, especially when use with loop statement to add up with the query statement.
The solution? Don't concatenate it. Instead, execute it often and save into a WIP temporary tables. Sure, you would need to programmatically delete these temporary tables at the end of your procedure.
http://www.fotia.co.uk/fotia/DY.13.VarCharMax.aspx
Tutorial: JavaScript：Defining Array
Array in JavaScript is class derived from Object. There are several ways of defining an array:
1. Regular array. Pass an optional integer argument to control array's size. However, if it is defined, no more element can be defined exceeding this number.
； var myfriends = new Array(optionalElementNumber)
； myfriends[0] = "John"
； myfriends[1] = "Bob"
； myfriends[2] = "Sue"
2. Condensed array:
； var myfriends = new Array("John", "Bob", "Sue")
3. Literal array：
； var myfriends = ["John", "Bob", "Sue"]
4. Empty array:
; var myArray = []
; myArray.push("John")
; myArray.push("Bob")
; myArray.push("Sue")
The elements/values of an array can be numeric, string, another array and object, or reference points to these:
； var var_0 = "John"
； var var_1 = "Bob"
； var var_2 = "Sue"
； var myfriends = new Array(var_0, var_1, var_2)
or
； var myfriends = [var_0, var_1, var_2]
When an element is another array, it becomes multidimensional array.
1. Regular array. Pass an optional integer argument to control array's size. However, if it is defined, no more element can be defined exceeding this number.
； var myfriends = new Array(optionalElementNumber)
； myfriends[0] = "John"
； myfriends[1] = "Bob"
； myfriends[2] = "Sue"
2. Condensed array:
； var myfriends = new Array("John", "Bob", "Sue")
3. Literal array：
； var myfriends = ["John", "Bob", "Sue"]
4. Empty array:
; var myArray = []
; myArray.push("John")
; myArray.push("Bob")
; myArray.push("Sue")
The elements/values of an array can be numeric, string, another array and object, or reference points to these:
； var var_0 = "John"
； var var_1 = "Bob"
； var var_2 = "Sue"
； var myfriends = new Array(var_0, var_1, var_2)
or
； var myfriends = [var_0, var_1, var_2]
When an element is another array, it becomes multidimensional array.
The Calculation of Chute Time
Chute time is the period from crew accepts the task to the engine first started before actual departure in emergency services field. In terms of reviewing historic data, the point of actual departure usually is quite clear. However, the point of crew accepts is ambiguous. It is norm in aviation industry there are several accepts and declines co-existing for a trip. The following algorithm may help to determine this point:
The oldest accept event which is newer than newest decline event.
However, if the working system does flag the last accepting event being the true acceptance, for which the crew being occupied, then the last event should be used.
Following is a real example in T-SQL how to calculate chute time based on IQM and based on first approach:
USE ArchiveOPTIMAS
GO
IF EXISTS( SELECT *
FROM QM.SYS.TABLES
WHERE NAME = 'SSRS_Program_Temp_Legs'
)
DROP TABLE QM.dbo.SSRS_Program_Temp_Legs
IF EXISTS( SELECT *
FROM QM.SYS.TABLES
WHERE NAME = 'SSRS_Program_Temp_CrewTime'
)
DROP TABLE QM.dbo.SSRS_Program_Temp_CrewTime
DECLARE @Type VARCHAR(4)
DECLARE @Con VARCHAR(1)
SET @Type = 'URG'
SET @Con = 'D'
SELECT DISTINCT trip.trip_id
, t.transport_id
, tl.actual_departure AS Patient_Leg_Departure
, tl.actual_arrival AS Patient_Leg_Arrival
INTO QM.dbo.SSRS_Program_Temp_Legs
FROM dbo.transport AS t
INNER JOIN dbo.trip_leg_patient AS tlp ON tlp.transport_id = t.transport_id
INNER JOIN dbo.trip_leg AS tl ON tl.trip_leg_id = tlp.trip_leg_id
INNER JOIN dbo.trip ON trip.trip_id = tl.trip_id
INNER JOIN QM.dbo.dw_Legs AS dl on dl.Patient_id = t.transport_id
WHERE tl.trip_leg_status_cd = 'COMP'
AND t.transport_status_cd = 'COMP'
AND tl.actual_departure IS NOT NULL
AND tl.actual_arrival IS NOT NULL
AND t.transport_priority_cd = @Type
AND dl.Contract = @Con
SELECT Rest.trip_id AS Trip_Id
, CASE WHEN DATEDIFF(MI, Chute_Time.Crew_Accepts, Rest.Prior_Departure)>0 THEN DATEDIFF(MI, Chute_Time.Crew_Accepts, Rest.Prior_Departure) ELSE NULL END AS S1_2
, CASE WHEN DATEDIFF(MI, Rest.Prior_Departure, Rest.Prior_Arrival)>0 THEN DATEDIFF(MI, Rest.Prior_Departure, Rest.Prior_Arrival) ELSE NULL END AS S2_3
, CASE WHEN DATEDIFF(MI, Rest.Prior_Arrival, Rest.Patient_Leg_Departure)>0 THEN DATEDIFF(MI, Rest.Prior_Arrival, Rest.Patient_Leg_Departure) ELSE NULL END AS S3_4
, CASE WHEN DATEDIFF(MI, Rest.Patient_Leg_Departure, Rest.Patient_Leg_Arrival)>0 THEN DATEDIFF(MI, Rest.Patient_Leg_Departure, Rest.Patient_Leg_Arrival) ELSE NULL END AS S4_5
, CASE WHEN DATEDIFF(MI, Rest.Patient_Leg_Arrival, Rest.After_Departure)>0 THEN DATEDIFF(MI, Rest.Patient_Leg_Arrival, Rest.After_Departure) ELSE NULL END AS S5_6
, CASE WHEN DATEDIFF(MI, Chute_Time.Crew_Accepts, Rest.After_Departure)>0 THEN DATEDIFF(MI, Rest.Prior_Departure, Rest.After_Departure) ELSE NULL END AS S1_6
INTO QM.dbo.SSRS_Program_Temp_CrewTime
FROM (
SELECT sptl.trip_id
, MIN(th.add_datetime) AS Crew_Accepts
FROM QM.dbo.SSRS_Program_Temp_Legs AS sptl
INNER JOIN dbo.transport_history AS th ON th.transport_id = sptl.transport_id
INNER JOIN dbo.transport_event AS te ON te.transport_event_id = th.transport_event_id
INNER JOIN
( SELECT sptl.trip_id
, MAX( CASE WHEN th.add_datetime IS NOT NULL AND te.transport_event_id = 129
THEN th.add_datetime
ELSE DATEADD(DAY, 1, '1900-01-01')
END
) AS Crew_Decline
FROM QM.dbo.SSRS_Program_Temp_Legs AS sptl
LEFT JOIN dbo.transport_history AS th ON th.transport_id = sptl.transport_id
LEFT JOIN dbo.transport_event AS te ON te.transport_event_id = th.transport_event_id
GROUP BY sptl.trip_id
) AS Decline_Events
ON Decline_Events.trip_id = sptl.trip_id
WHERE th.add_datetime >= Decline_Events.Crew_Decline
AND te.transport_event_id = 130
GROUP BY sptl.trip_id
) AS Chute_Time
FULL JOIN
( SELECT sptl.trip_id
, Before_After.Prior_Departure
, Before_After.Prior_Arrival
, sptl.Patient_Leg_Departure
, sptl.Patient_Leg_Arrival
, Before_After.After_Departure
FROM ( SELECT sptl.trip_id
, MAX(CASE WHEN tl.actual_departure < sptl.Patient_Leg_Departure THEN tl.actual_departure END) AS Prior_Departure
, MAX(CASE WHEN tl.actual_arrival < sptl.Patient_Leg_Departure THEN tl.actual_arrival END) AS Prior_Arrival
, MIN(CASE WHEN tl.actual_departure > sptl.Patient_Leg_Arrival THEN tl.actual_departure END) AS After_Departure
FROM QM.dbo.SSRS_Program_Temp_Legs AS sptl
INNER JOIN dbo.trip_leg AS tl ON tl.trip_id = sptl.trip_id
GROUP BY sptl.trip_id
) AS Before_After
INNER JOIN QM.dbo.SSRS_Program_Temp_Legs AS sptl
ON sptl.trip_id = Before_After.trip_id
) AS Rest
ON Rest.trip_id = Chute_Time.trip_id
SELECT SUM(IQM_Sep) AS IQM
, SUM(Stdevp_Sep) AS Stdevp
FROM ( SELECT AVG(S1_2) AS IQM_Sep
, NULL AS Stdevp_Sep
FROM QM.dbo.SSRS_Program_Temp_CrewTime AS Minut
WHERE S1_2 IS NOT NULL
AND Minut.Trip_Id NOT IN (SELECT TOP 25 PERCENT Trip_Id FROM QM.dbo.SSRS_Program_Temp_CrewTime WHERE S1_2 IS NOT NULL ORDER BY S1_2)
AND Minut.Trip_Id NOT IN (SELECT TOP 25 PERCENT Trip_Id FROM QM.dbo.SSRS_Program_Temp_CrewTime WHERE S1_2 IS NOT NULL ORDER BY S1_2 DESC)
UNION
SELECT NULL AS IQM_Sep
, STDEVP(S1_2) AS Stdevp_Sep
FROM QM.dbo.SSRS_Program_Temp_CrewTime
) AS Colum
http://www.math.carleton.ca/CanQueue-08/canq08Alanis.pdf
The oldest accept event which is newer than newest decline event.
However, if the working system does flag the last accepting event being the true acceptance, for which the crew being occupied, then the last event should be used.
Following is a real example in T-SQL how to calculate chute time based on IQM and based on first approach:
USE ArchiveOPTIMAS
GO
IF EXISTS( SELECT *
FROM QM.SYS.TABLES
WHERE NAME = 'SSRS_Program_Temp_Legs'
)
DROP TABLE QM.dbo.SSRS_Program_Temp_Legs
IF EXISTS( SELECT *
FROM QM.SYS.TABLES
WHERE NAME = 'SSRS_Program_Temp_CrewTime'
)
DROP TABLE QM.dbo.SSRS_Program_Temp_CrewTime
DECLARE @Type VARCHAR(4)
DECLARE @Con VARCHAR(1)
SET @Type = 'URG'
SET @Con = 'D'
SELECT DISTINCT trip.trip_id
, t.transport_id
, tl.actual_departure AS Patient_Leg_Departure
, tl.actual_arrival AS Patient_Leg_Arrival
INTO QM.dbo.SSRS_Program_Temp_Legs
FROM dbo.transport AS t
INNER JOIN dbo.trip_leg_patient AS tlp ON tlp.transport_id = t.transport_id
INNER JOIN dbo.trip_leg AS tl ON tl.trip_leg_id = tlp.trip_leg_id
INNER JOIN dbo.trip ON trip.trip_id = tl.trip_id
INNER JOIN QM.dbo.dw_Legs AS dl on dl.Patient_id = t.transport_id
WHERE tl.trip_leg_status_cd = 'COMP'
AND t.transport_status_cd = 'COMP'
AND tl.actual_departure IS NOT NULL
AND tl.actual_arrival IS NOT NULL
AND t.transport_priority_cd = @Type
AND dl.Contract = @Con
SELECT Rest.trip_id AS Trip_Id
, CASE WHEN DATEDIFF(MI, Chute_Time.Crew_Accepts, Rest.Prior_Departure)>0 THEN DATEDIFF(MI, Chute_Time.Crew_Accepts, Rest.Prior_Departure) ELSE NULL END AS S1_2
, CASE WHEN DATEDIFF(MI, Rest.Prior_Departure, Rest.Prior_Arrival)>0 THEN DATEDIFF(MI, Rest.Prior_Departure, Rest.Prior_Arrival) ELSE NULL END AS S2_3
, CASE WHEN DATEDIFF(MI, Rest.Prior_Arrival, Rest.Patient_Leg_Departure)>0 THEN DATEDIFF(MI, Rest.Prior_Arrival, Rest.Patient_Leg_Departure) ELSE NULL END AS S3_4
, CASE WHEN DATEDIFF(MI, Rest.Patient_Leg_Departure, Rest.Patient_Leg_Arrival)>0 THEN DATEDIFF(MI, Rest.Patient_Leg_Departure, Rest.Patient_Leg_Arrival) ELSE NULL END AS S4_5
, CASE WHEN DATEDIFF(MI, Rest.Patient_Leg_Arrival, Rest.After_Departure)>0 THEN DATEDIFF(MI, Rest.Patient_Leg_Arrival, Rest.After_Departure) ELSE NULL END AS S5_6
, CASE WHEN DATEDIFF(MI, Chute_Time.Crew_Accepts, Rest.After_Departure)>0 THEN DATEDIFF(MI, Rest.Prior_Departure, Rest.After_Departure) ELSE NULL END AS S1_6
INTO QM.dbo.SSRS_Program_Temp_CrewTime
FROM (
SELECT sptl.trip_id
, MIN(th.add_datetime) AS Crew_Accepts
FROM QM.dbo.SSRS_Program_Temp_Legs AS sptl
INNER JOIN dbo.transport_history AS th ON th.transport_id = sptl.transport_id
INNER JOIN dbo.transport_event AS te ON te.transport_event_id = th.transport_event_id
INNER JOIN
( SELECT sptl.trip_id
, MAX( CASE WHEN th.add_datetime IS NOT NULL AND te.transport_event_id = 129
THEN th.add_datetime
ELSE DATEADD(DAY, 1, '1900-01-01')
END
) AS Crew_Decline
FROM QM.dbo.SSRS_Program_Temp_Legs AS sptl
LEFT JOIN dbo.transport_history AS th ON th.transport_id = sptl.transport_id
LEFT JOIN dbo.transport_event AS te ON te.transport_event_id = th.transport_event_id
GROUP BY sptl.trip_id
) AS Decline_Events
ON Decline_Events.trip_id = sptl.trip_id
WHERE th.add_datetime >= Decline_Events.Crew_Decline
AND te.transport_event_id = 130
GROUP BY sptl.trip_id
) AS Chute_Time
FULL JOIN
( SELECT sptl.trip_id
, Before_After.Prior_Departure
, Before_After.Prior_Arrival
, sptl.Patient_Leg_Departure
, sptl.Patient_Leg_Arrival
, Before_After.After_Departure
FROM ( SELECT sptl.trip_id
, MAX(CASE WHEN tl.actual_departure < sptl.Patient_Leg_Departure THEN tl.actual_departure END) AS Prior_Departure
, MAX(CASE WHEN tl.actual_arrival < sptl.Patient_Leg_Departure THEN tl.actual_arrival END) AS Prior_Arrival
, MIN(CASE WHEN tl.actual_departure > sptl.Patient_Leg_Arrival THEN tl.actual_departure END) AS After_Departure
FROM QM.dbo.SSRS_Program_Temp_Legs AS sptl
INNER JOIN dbo.trip_leg AS tl ON tl.trip_id = sptl.trip_id
GROUP BY sptl.trip_id
) AS Before_After
INNER JOIN QM.dbo.SSRS_Program_Temp_Legs AS sptl
ON sptl.trip_id = Before_After.trip_id
) AS Rest
ON Rest.trip_id = Chute_Time.trip_id
SELECT SUM(IQM_Sep) AS IQM
, SUM(Stdevp_Sep) AS Stdevp
FROM ( SELECT AVG(S1_2) AS IQM_Sep
, NULL AS Stdevp_Sep
FROM QM.dbo.SSRS_Program_Temp_CrewTime AS Minut
WHERE S1_2 IS NOT NULL
AND Minut.Trip_Id NOT IN (SELECT TOP 25 PERCENT Trip_Id FROM QM.dbo.SSRS_Program_Temp_CrewTime WHERE S1_2 IS NOT NULL ORDER BY S1_2)
AND Minut.Trip_Id NOT IN (SELECT TOP 25 PERCENT Trip_Id FROM QM.dbo.SSRS_Program_Temp_CrewTime WHERE S1_2 IS NOT NULL ORDER BY S1_2 DESC)
UNION
SELECT NULL AS IQM_Sep
, STDEVP(S1_2) AS Stdevp_Sep
FROM QM.dbo.SSRS_Program_Temp_CrewTime
) AS Colum
http://www.math.carleton.ca/CanQueue-08/canq08Alanis.pdf
Stop Using for…in Loop Statement to Iterate Array In JavaScript
for…in loop statement is working fine. The problem is it does not work with Prototype. So standard loop statement is encouraged:
; for (var index=0; index
// Your code working on item here...
}
In addition, if the array is so large, each time to check the length will be so costly. Use following statement instead:
; for (var index=0, len=myArray.length; index
// Your code working on item here...
}
http://www.prototypejs.org/api/array
; for (var index=0; index
// Your code working on item here...
}
In addition, if the array is so large, each time to check the length will be so costly. Use following statement instead:
; for (var index=0, len=myArray.length; index
// Your code working on item here...
}
http://www.prototypejs.org/api/array
Get Selected Item in JavaScript Form
Somehow JavaScript mixed objects with attributes. For instance:
document.formName.listBoxName[i].selected
formName and listBoxName are references to two bojects. It might be good, but it just crosses the boundary of reference to object and the attributes. Don't know if this the the reason, to get selected item's value in form seems not easy. Google search shows there are so many people ask the question and not get right answers. Here is the easier way:
; function getValueOfSelectedItem(obj)
{ ; var val = obj.options[obj.selectedIndex].value
; if (val) (use val here)
}
To call this function, put the caller on onchange in HTML codes:
onChange="getValueOfSelectedItem(this)"
The beauty of it is there is no mixture of reference and attribute and you can pass anything of it without worry of crossing boundary issue.
http://www.java2s.com/Code/JavaScriptReference/Javascript-Properties/selectedIndexExample.htm
document.formName.listBoxName[i].selected
formName and listBoxName are references to two bojects. It might be good, but it just crosses the boundary of reference to object and the attributes. Don't know if this the the reason, to get selected item's value in form seems not easy. Google search shows there are so many people ask the question and not get right answers. Here is the easier way:
; function getValueOfSelectedItem(obj)
{ ; var val = obj.options[obj.selectedIndex].value
; if (val) (use val here)
}
To call this function, put the caller on onchange in HTML codes:
onChange="getValueOfSelectedItem(this)"
The beauty of it is there is no mixture of reference and attribute and you can pass anything of it without worry of crossing boundary issue.
http://www.java2s.com/Code/JavaScriptReference/Javascript-Properties/selectedIndexExample.htm
Turning SSRS Report Into Form
The difference between report and form is report pulls data out from database and the form is used to feed data into database. SQL Server Reporting Services is of the report. However, sometimes the two-way interactions are really needed. Here is an example. A parameter need to be input by user; however, if the user had input previously, it should be stored in database and no longer require user to input again.
The logic of the solution is:
1. Get value A of the subject from database according to other parameters.
2. If (Null!=A) then show report based on A, and exit.
3. Else if user input parameter B, then insert B into database, then show report based on B, and exit.
4. Else show the blank report with notice that user needs to key-in the B.
However, this logic does not allow front end to alter the what was already in database. Any change will be manully operated by back end. Alternatively, following logic can be applied:
1. If user input parameter B, then insert or alter B in database, then show report based on B, and exit.
2. Else get value A of the subject from database according to other parameters.
3. If (Null!=A) then show report based on A, and exit.
4. Else show the blank report with notice that user needs to key-in the B.
As a standard procedure, the user therefore is not required to key-in the parameter unless is notified.
INSERT and SET query can be as text or as stored procedure. However, INSERT query should not be stand along as text, or an error message will be generated. At the end, SSRS is of report.
The logic of the solution is:
1. Get value A of the subject from database according to other parameters.
2. If (Null!=A) then show report based on A, and exit.
3. Else if user input parameter B, then insert B into database, then show report based on B, and exit.
4. Else show the blank report with notice that user needs to key-in the B.
However, this logic does not allow front end to alter the what was already in database. Any change will be manully operated by back end. Alternatively, following logic can be applied:
1. If user input parameter B, then insert or alter B in database, then show report based on B, and exit.
2. Else get value A of the subject from database according to other parameters.
3. If (Null!=A) then show report based on A, and exit.
4. Else show the blank report with notice that user needs to key-in the B.
As a standard procedure, the user therefore is not required to key-in the parameter unless is notified.
INSERT and SET query can be as text or as stored procedure. However, INSERT query should not be stand along as text, or an error message will be generated. At the end, SSRS is of report.
Conservative Forecast Based On Historic Data
You can always use mean on the historic data as your forecast, which is reasonable. However, this is radical, especially when not consider the probability distribution. To be conservative, you may need to add a standard deviation (σ):
Mean + σ
Or
Mean - σ
In general, 1 standard deviation is good enough. In normally distributed data, 1σ means the side coverage of 34.1%, accounts for 84.1% of total population.
If there is no noise data within the dataset, arithmetic mean (average) can be used. However, if there is noise data, interquartile mean (IQM) is recommended. Accordingly, the standard deviation should be also based on the interquartile range (middle 50%).
In MySQL, the built-in function is STD(expr). In Oracle, it is STDDEV(expr) and in T-SQL, it is STDEVP(expr).
http://en.wikipedia.org/wiki/Standard_deviation
http://koncordpartners.blogspot.com/2010/11/practical-mean.html
http://oreilly.com/catalog/sqlnut/chapter/ch04.html
http://www.devguru.com/technologies/t-sql/7097.asp
Mean + σ
Or
Mean - σ
In general, 1 standard deviation is good enough. In normally distributed data, 1σ means the side coverage of 34.1%, accounts for 84.1% of total population.
If there is no noise data within the dataset, arithmetic mean (average) can be used. However, if there is noise data, interquartile mean (IQM) is recommended. Accordingly, the standard deviation should be also based on the interquartile range (middle 50%).
In MySQL, the built-in function is STD(expr). In Oracle, it is STDDEV(expr) and in T-SQL, it is STDEVP(expr).
http://en.wikipedia.org/wiki/Standard_deviation
http://koncordpartners.blogspot.com/2010/11/practical-mean.html
http://oreilly.com/catalog/sqlnut/chapter/ch04.html
http://www.devguru.com/technologies/t-sql/7097.asp
Practical Mean
In statistics, mean has two related meanings:
- the arithmetic mean.
- the expected value of a random variable, which is also called the population mean.
To get the mean for a column in database, in general built-in aggregation function AVG() is good enough. However, one much understand this function is of arithmetic mean. If your database contains noise data, this is not good enough, simply because this mean does not have noise data prune mechanism. While different means would have their precise meaning, if you do not know which to use, use interquartile mean (IQM).
Following is the some notes related to T-SQL’s mean functions:
Arithmetic mean (AM)
There is built-in function – Never use DISTINCT, and no need to use ALL because it is default:
AVG ( [ ALL | DISTINCT ] expression )
Median
Median is not mean, but it is included here anyway. There is no such function in T-SQL. But you can get from here (credits to Itzik Ben-Gan):
SELECT(
(SELECT MAX(Value) FROM
(SELECT TOP 50 PERCENT Value FROM dbo.VOrders ORDER BY Value) AS H1)
+
(SELECT MIN(Value) FROM
(SELECT TOP 50 PERCENT Value FROM dbo.VOrders ORDER BY Value DESC) AS H2)
) / 2 AS Median;
Interquartile mean (IQM)
There is no such function in T-SQL. But you can get from here:
SELECT AVG(S1_2) AS P1_2
FROM Minut
WHERE S1_2 IS NOT NULL
AND Minut.Trip_Id NOT IN
(SELECT TOP 25 PERCENT Trip_Id FROM Minut WHERE S1_2 IS NOT NULL ORDER BY S1_2)
AND Minut.Trip_Id NOT IN
(SELECT TOP 25 PERCENT Trip_Id FROM Minut WHERE S1_2 IS NOT NULL ORDER BY S1_2 DESC)
If the dataset is so small, such as only a few records exist, SELECT TOP 25 PERCENT won't work. Instead, use following solution:
SELECT AVG(S1_2) AS P1_2
FROM Minut
WHERE S1_2 IS NOT NULL
AND Minut.Trip_Id NOT IN
(
SELECT TOP
(
SELECT CONVERT(INT, COUNT(*)/4)
FROM Minut
WHERE S1_2 IS NOT NULL
) Trip_Id
FROM Minut
WHERE S1_2 IS NOT NULL
ORDER BY S1_2
)
AND Minut.Trip_Id NOT IN
(
SELECT TOP
(
SELECT CONVERT(INT, COUNT(*)/4)
FROM Minut
WHERE S1_2 IS NOT NULL
) Trip_Id
FROM Minut
WHERE S1_2 IS NOT NULL
ORDER BY S1_2 DESC
)
http://en.wikipedia.org/wiki/Mean
http://blogical.se/blogs/mikael_sand/archive/2009/07/24/t-sql-is-mean-there-is-no-median.aspx
http://www.sqlmag.com/article/tsql3/calculating-the-median-gets-simpler-in-sql-server-2005.aspx
http://koncordpartners.blogspot.com/2010/12/rounding-issue-in-select-top-n-percent.html
- the arithmetic mean.
- the expected value of a random variable, which is also called the population mean.
To get the mean for a column in database, in general built-in aggregation function AVG() is good enough. However, one much understand this function is of arithmetic mean. If your database contains noise data, this is not good enough, simply because this mean does not have noise data prune mechanism. While different means would have their precise meaning, if you do not know which to use, use interquartile mean (IQM).
Following is the some notes related to T-SQL’s mean functions:
Arithmetic mean (AM)
There is built-in function – Never use DISTINCT, and no need to use ALL because it is default:
AVG ( [ ALL | DISTINCT ] expression )
Median
Median is not mean, but it is included here anyway. There is no such function in T-SQL. But you can get from here (credits to Itzik Ben-Gan):
SELECT(
(SELECT MAX(Value) FROM
(SELECT TOP 50 PERCENT Value FROM dbo.VOrders ORDER BY Value) AS H1)
+
(SELECT MIN(Value) FROM
(SELECT TOP 50 PERCENT Value FROM dbo.VOrders ORDER BY Value DESC) AS H2)
) / 2 AS Median;
Interquartile mean (IQM)
There is no such function in T-SQL. But you can get from here:
SELECT AVG(S1_2) AS P1_2
FROM Minut
WHERE S1_2 IS NOT NULL
AND Minut.Trip_Id NOT IN
(SELECT TOP 25 PERCENT Trip_Id FROM Minut WHERE S1_2 IS NOT NULL ORDER BY S1_2)
AND Minut.Trip_Id NOT IN
(SELECT TOP 25 PERCENT Trip_Id FROM Minut WHERE S1_2 IS NOT NULL ORDER BY S1_2 DESC)
If the dataset is so small, such as only a few records exist, SELECT TOP 25 PERCENT won't work. Instead, use following solution:
SELECT AVG(S1_2) AS P1_2
FROM Minut
WHERE S1_2 IS NOT NULL
AND Minut.Trip_Id NOT IN
(
SELECT TOP
(
SELECT CONVERT(INT, COUNT(*)/4)
FROM Minut
WHERE S1_2 IS NOT NULL
) Trip_Id
FROM Minut
WHERE S1_2 IS NOT NULL
ORDER BY S1_2
)
AND Minut.Trip_Id NOT IN
(
SELECT TOP
(
SELECT CONVERT(INT, COUNT(*)/4)
FROM Minut
WHERE S1_2 IS NOT NULL
) Trip_Id
FROM Minut
WHERE S1_2 IS NOT NULL
ORDER BY S1_2 DESC
)
http://en.wikipedia.org/wiki/Mean
http://blogical.se/blogs/mikael_sand/archive/2009/07/24/t-sql-is-mean-there-is-no-median.aspx
http://www.sqlmag.com/article/tsql3/calculating-the-median-gets-simpler-in-sql-server-2005.aspx
http://koncordpartners.blogspot.com/2010/12/rounding-issue-in-select-top-n-percent.html
Can JavaScript Codes Be Added To SSRS Report?
The SQL Server Reporting Services HTML is poorly constructed so that the tags you'd most want to customize don't have Id's or Classes assigned. In addition, the ASPX pages just reference compiled assemblies, so the only real way to modify them is via CSS. Someone tried to edit the ReportingServices.js file, but it is neither the concept of customization nor standard functionality.
Conclusion: you could not add JavaScript block onto SSRS.
http://geekswithblogs.net/mnf/archive/2007/11/25/sql-server-reporting-services-notes.aspx
http://geekswithblogs.net/mnf/archive/2007/11/25/sql-server-reporting-services-notes.aspx
http://stackoverflow.com/questions/789303/is-it-possible-to-embed-javascript-into-an-ssrs-report
Conclusion: you could not add JavaScript block onto SSRS.
http://geekswithblogs.net/mnf/archive/2007/11/25/sql-server-reporting-services-notes.aspx
http://geekswithblogs.net/mnf/archive/2007/11/25/sql-server-reporting-services-notes.aspx
http://stackoverflow.com/questions/789303/is-it-possible-to-embed-javascript-into-an-ssrs-report
Schedule A Database Job In MySQL
As of MySQL 5.1, one can use event to schedule a job:
CREATE EVENT event_name
ON SCHEDULE schedule
DO event_body
;
schedule can be: AT timestamp [+ INTERVAL interval] ...
or EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval can be: quantity followed by unit
unit can be: {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
event_body can be SQL query or EXECUTE sp_name.
For instance:
CREATE EVENT event_name
ON SCHEDULE EVERY 5 MINUTE
DO SELECT * FROM tab
;
ALTER, DROP and SET GLOBAL event_name = 1 to switch on the event, etc.
http://dev.mysql.com/doc/refman/5.1/en/create-event.html
http://answers.oreilly.com/topic/177-how-to-use-events-to-schedule-database-actions-in-mysql/
http://rpbouman.blogspot.com/2005/10/scheduling-procedure-execution-in.html
CREATE EVENT event_name
ON SCHEDULE schedule
DO event_body
;
schedule can be: AT timestamp [+ INTERVAL interval] ...
or EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval can be: quantity followed by unit
unit can be: {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
event_body can be SQL query or EXECUTE sp_name.
For instance:
CREATE EVENT event_name
ON SCHEDULE EVERY 5 MINUTE
DO SELECT * FROM tab
;
ALTER, DROP and SET GLOBAL event_name = 1 to switch on the event, etc.
http://dev.mysql.com/doc/refman/5.1/en/create-event.html
http://answers.oreilly.com/topic/177-how-to-use-events-to-schedule-database-actions-in-mysql/
http://rpbouman.blogspot.com/2005/10/scheduling-procedure-execution-in.html
Subscribe to:
Posts (Atom)
Posts
