uweziegenhagen.de

CREATE TABLE #test(datum DATE, VALUE INT)
 
INSERT INTO #test VALUES('2010-01-01',10)
INSERT INTO #test VALUES('2010-01-02',20)
INSERT INTO #test VALUES('2010-01-02',30)
 
SELECT t1.*, (SELECT SUM(VALUE) FROM #test t2 WHERE t2.datum<=t1.datum) [cumulative amount]
FROM #test t1
ORDER BY t1.datum
DROP TABLE #test

DROP TABLE #data
CREATE TABLE #data(NUMBER FLOAT)
 
INSERT INTO #data VALUES(1.0);INSERT INTO #data VALUES(2.0);
INSERT INTO #data VALUES(3.0);INSERT INTO #data VALUES(4.0);
INSERT INTO #data VALUES(5.0);INSERT INTO #data VALUES(6.0);
INSERT INTO #data VALUES(7.0);INSERT INTO #data VALUES(8.0);
INSERT INTO #data VALUES(9.0);INSERT INTO #data VALUES(10.0);
INSERT INTO #data VALUES(11.0);INSERT INTO #data VALUES(12.0);
INSERT INTO #data VALUES(13.0);INSERT INTO #data VALUES(14.0);
INSERT INTO #data VALUES(15.0);INSERT INTO #data VALUES(16.0);
INSERT INTO #data VALUES(17.0);INSERT INTO #data VALUES(18.0);
INSERT INTO #data VALUES(19.0);INSERT INTO #data VALUES(20.0);
INSERT INTO #data VALUES(21.0);INSERT INTO #data VALUES(22.0);
INSERT INTO #data VALUES(23.0);INSERT INTO #data VALUES(24.0);
INSERT INTO #data VALUES(25.0);INSERT INTO #data VALUES(26.0);
INSERT INTO #data VALUES(27.0);INSERT INTO #data VALUES(28.0);
INSERT INTO #data VALUES(29.0);INSERT INTO #data VALUES(30.0);
INSERT INTO #data VALUES(31.0);INSERT INTO #data VALUES(32.0);
INSERT INTO #data VALUES(33.0);INSERT INTO #data VALUES(34.0);
INSERT INTO #data VALUES(35.0);INSERT INTO #data VALUES(36.0);
INSERT INTO #data VALUES(37.0);INSERT INTO #data VALUES(38.0);
INSERT INTO #data VALUES(39.0);INSERT INTO #data VALUES(40.0);
INSERT INTO #data VALUES(-100.0);INSERT INTO #data VALUES(100.0);
 
DECLARE @median FLOAT
DECLARE @perc25 FLOAT
DECLARE @perc75 FLOAT
DECLARE @iqr FLOAT
 
SET @median = (SELECT   MAX(NUMBER) AS Median FROM (SELECT TOP 50 PERCENT NUMBER FROM #data ORDER BY NUMBER) a)
SET @perc25 = (SELECT   MAX(NUMBER) AS Median FROM (SELECT TOP 25 PERCENT NUMBER FROM #data ORDER BY    NUMBER) a)
SET @perc75 = (SELECT   MAX(NUMBER) AS Median FROM (SELECT TOP 75 PERCENT NUMBER FROM #data ORDER BY    NUMBER) a)
 
print @median
print @perc25
print @perc75
 
SET @iqr = @perc75-@perc25
print 'IQR'
print @iqr
 
SELECT * FROM #data WHERE NUMBER <  (@median + 1.5* @iqr) AND 
NUMBER >  (@median - 1.5* @iqr)
 
print 'number of outliers'
SELECT COUNT(*) FROM #data WHERE NUMBER <  (@median - 1.5* @iqr)
OR NUMBER >  (@median + 1.5* @iqr)

# open connection to the server
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=localhost; Initial Catalog=Posh; Integrated Security=SSPI")
$conn.Open()
 
# create command object
$cmd = $conn.CreateCommand()
# create statement
$cmd.CommandText ="INSERT myTable VALUES ('Hello', 'World', 123)"
# execute command
$cmd.ExecuteNonQuery()
# close connection
$conn.Close()

# open connection to the server
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=localhost; Initial Catalog=Posh; Integrated Security=SSPI")
$conn.Open()
 
# create command object
$cmd = $conn.CreateCommand()
# create statement
$cmd.CommandText ="INSERT myTable VALUES ('Hello', 'World', 123)"
# execute command
$cmd.ExecuteNonQuery()
# close connection
$conn.Close()