ホチキス先生の「プログラマーと呼ばれたい」

InfoPath & SQL Server !

Archive for 2月 2011

Roland JS-8のBallad:Light Ballad(Key:C)をバックにGR-20とSteinberger Spirit GT-PROでギターを弾く

leave a comment »

今日は趣味の音楽ネタである。とはいえ、コンピュータやディジタル技術と関連した話題である。

RolandのJS-8を手に入れてから、ギターのソロ練習がとても楽しくなった。今日はJS-8のBallad:Light Ballad(Key:C)をバックに、RolandのギターシンセサイザーGR-20を使ってキーボード風ソロを加えてみた。録音はJS-8でやり、USBでパソコンに取り込む。演奏はそのままパソコンで聴いてもいいし、ディジタルオーディオプレイヤーに転送してもいい。ディジタル機器の発展で、こんなことがいとも簡単にできる時代になったのだ。

まずRolandのギターシンセサイザーGR-20だが、これは最近新機種が発売になりギタリストの間で評判になっているGR-55の前機種にあたる。ギターからの出力は専用のピックアップGK-3によって取り出し、コントローラーを経てGR-20へ接続する。専用ピックアップGK-3をSteinberger Spirit GT-PROに取り付けたところはこうだ。

IMG_1217
<Fig.1 Steinberger Spirit GT-PROにGK-3を取り付けたところ>

写真のとおりGK-3ピックアップにはネジで固定するための穴が開いているが、とりあえずは両面テープでSteinberger Spirits GT-PROのボディに貼り付けることにした。Steinberger Spirit GT-PROには現行で基本的に2つのモデルがあり、ピックアップがSSHのStandardとピックアップがHSHSのDeluxeである。値段は同じで特にDeluxeが上等だというわけではない。

ギターによってはボディと弦高の間が狭く、取り付けられないこともあるらしい。Steinberger Spirit GT-PROの場合は弦高は大丈夫だった。むしろ低いくらいである。しかし問題は、ピックアップのポール間隔である。Steinbergerの弦の間が一般的なギターよりも狭くなっているのか、GK-3のピックアップが左右にはみ出すようになる。これでは1弦や6弦の音が正確に捉えられないのではないかと思われる。実際、弾いた感じでも1弦と6間は感度が極端に悪いように感じる。

ストラトキャスターモデルに、斜めに取り付けるのがよいように思われる。

もうひとつの問題は、GK-3のコントローラーだ。これはギターのボディに取り付けるのだが、Steinbergerは極端にボディの面積が狭い。そこで通常取り付けるだろう位置に固定すると、次の写真のようになった。これも両面テープで貼り付けている。

IMG_1215
<Fig.2 Steinberger Spirit GT-PROのボディに固定したGK-3コントローラー>

狭いボディにはみ出すように取り付けたが、この位置は実に演奏時に具合が悪い。ひじが当たるのだ。これでは弾きにくいので、さて、どこに固定するのが良いだろう、と考えて、裏側に取り付けることにしてみた。ボタンやボリュームの操作性は悪くなるが、これだとボディと体の間に収まって邪魔にならない。

IMG_1222
<Fig.3 Steinberger Spirit GT-PROの裏側に取り付けたGK-3コントローラー>

次はお気に入りのJS-8の準備をする。今日はシンセサイザーの艶やかな音を使ってみたいので、落ち着いたバラードにする。選んだバッキングパターンはBallad:Light Ballad(Kye:C)だ。

IMG_1219
<Fig.4 Roland JS-8でBallad:Light Ballad(Key C)を選んだ>

そしてギターシンセサイザーGR-20では、Strings/Orchバンクの34番を選んだ。そしてAttackとReleaseを少し長めにし、Reverbを最大にかけた。

IMG_1218
<Fig.5 RolandのギターシンセサイザーGR-20のパネル>

演奏はそのままJS-8のREC機能で録音する。録音されたものはそのままJS-8で再生できるが、JS-8をUSBケーブルでパソコンにつなぐと、専用のアプリケーション「Roland JS-8 Song List Editor」を使ってWAV形式に変換して取り込むことができる。変換している画面はこうだ。

JS-8_GR-20_001_edit
<Fig.6 Roland JS-8に録音されたトラックをコンピュータに取り込む>

これと似たようなことをするために、学生時代には専用のリズムマシン、ベースマシンを使ってバッキングデータを作り、ミキサーで音を加え、テープレコーダーに多重録音をしたものだ。懐かしい。今はコンピュータや電子楽器を使っていとも簡単に面白いことができる。

Windows LiveのSky Driveに置いたので聴いてみてくれ。

http://cid-dfec2d26b603327d.office.live.com/richupload.aspx/.Public/JS-8%5E_Ballad%5E_with%5E_GR-20

広告

Written by Yoshio Matsumoto

2011年2月25日 at 1:33 AM

正しく作られたシステムでも、運用を誤り破綻する恐れをゼロにできない

leave a comment »

いささか大上段に構えたタイトルにしたが、実際におこったことは些細なことである。しかし、今日はあらためてシステムを作ることの難しさを考えさせられた。

今日のテーマはシステム構築における純粋な技術の話ではなく、人の思考や行動といったヒューマンな側面についてである。しかしシステム開発は技術的に完全であればよいものではなく、ヒューマンな要素を十分に考慮しなければならない。また狭い意味でのコンピュータシステムだけを考えるのではなく、データ入力の帳票のあり方、作業の方法なども見直さなければならないケースもある。

私は勤務校でSQL Serverをデータベースにし、InfoPathとAccessを組み合わせた、いわゆる「OBA開発」の手法でクライアントサーバー型の「校務システム」を構築し、運用している。このシステムの基本は、単位制高校である本校の講座編成、時間割、履修登録、出欠、考査点、成績、修得単位など、教務処理を行うものである。それに加えて、通知表などを家庭に発送するための住所管理、職員の勤務時間を集計する従事時間集計、学校評価のアンケート集計など校内の情報管理を一元的に行うものへ発展させている。「OBA開発」の利点は、運用しながらシステムを改良することがやりやすいところだ。

このシステムに今年度から生徒の保健情報も扱うことにした。身長、体重などの健康記録に加えて、内科検診など検査結果も処理できなければならない。これらのデータをどのようにデータベース化するかについては、養護教諭つまり保健の先生と相談しながら設計し、実際のデータに対応できるものにした。このあたりの詳細は、また別にblogにまとめるつもりだ。

さて前置きが長くなったが、このシステムに「結核検診結果」を入力することになった。結核検診について、SQL Serverのテーブル構造は次のようになっている。

<SQL Serverのテーブル構造>
学籍番号 char(7)
年度 char(2)
結核検診 char(2)
結核検診詳細 varchar(50)

「結核検診」フィールドはコード管理し、00が「未受診」、01が「異常なし」、02が「異常あり」とし、所見があったときは「結核検診詳細」フィールドに自由記述することとした。

これにデータを入力するためのInfoPathフォームは次のようなものである。

脊柱側弯_001_edit
<Fig.1 結核検診結果を入力するInfoPathフォーム>

SQL Serverで「結核検診」フィールドのデフォルト値を00にしておき、ボタンで01または02に変更できるようにする。「結核検診詳細」テキストボックスは、「結核検診」フィールドが02でなければグレーアウトし、読み取り専用になるようにしておく。これはInfoPathのテキストボックスのプロパティで「条件付き書式」で設定する。結核検診の結果が「異常あり」でなければ、詳細は入力できないようにしておくのだ。入力間違いを少なくする仕掛けだ。

さて、入力作業をしているところに、ふと、立ち寄って後ろから見ていると、なにかおかしいことに気づいた。次のような入力画面が見えたのだ。

脊柱側弯_002_edit
<Fig.2 目にとまったおかしな入力画面>

結核検診の結果を入力しているのに、詳細が「脊柱側湾」となっている。入力担当は若い男性教員だ。どうやら養護教諭に頼まれてかわりに入力しているらしい。

「脊柱側湾」って、結核と関係ないんじゃない」「はあ。」「それは内科検診の項目だから、入力フォームを間違っていると思うよ」「はあ。でも結核検診の結果に書いてあるんです。養護教諭の先生がとりあえずそこに入力しておいて、って言ったので」

そこで入力のために使っている検診結果の表を見ると、確かに次のように書かれている。氏名はもちろん仮名である。

脊柱側弯_003_edit
<Fig.3 データ入力に使った結核検診の結果表>

「結核検診」の記録のはずなのに、結核と関係ない「脊柱側湾」の所見が書かれている。これはおかしいのではないか。そこで養護教諭に事情を問いただした。すると、こういうことである。

結核検診はレントゲンなので、結核の疑いのあるなしだけでなく、脊柱側湾つまり脊柱が曲がっている症状もわかることが多いのだ。そこで慣習として、いわばサービスみたいなものとして、脊柱側湾の症状がレントゲンからわかれば、検査機関が所見に書いてくれるということなのだ。

養護教諭の立場からすると、少しでも多くの症状が早く発見できればよいのだろうが、データ入力上は間違いの原因になる。この生徒は、脊柱側湾であるが、結核の異常はないのである。しかし、上のような入力では、結核検診で異常が発見されたことに集計されてしまう。

何が問題なのか。まず入力に使う結果用紙の様式が問題である。脊柱側湾を結核検診で所見に書くなら、所見の欄を2つに分け、まず結核検診の結果を書き、それとは別にその他の所見を書くべきである。検査結果の用紙を見直したい。

もし検査用紙の見直しができないならば、データ入力において、やはりそのデータに関する、ある程度の知識を持っていること、データがどのように集計されるべきなのかという意味を理解していることが必要である。書式がデータ入力に即していなかったり、記入の仕方があいまいであっても、きちんと判断できる人間が入力するなら問題ない。

今回のケースは些細なこと、また結核検診という、まず全員が異常なしとなるだろう記録であったので、このまま間違い入力をしてしまっても、後で間違いが発見されただろう。しかしこのようなケースが他の例でも起こりうることであり、いかに正しく設計されたシステムであっても、間違ったデータ入力が見過ごされて信頼性のないデータで汚染され、全体として機能しないシステムに陥る危険を垣間見た気がする。

SQL Serverのユーザー定義関数を使おう~(11)任意の日における生徒の年齢を知るユーザー定義関数

leave a comment »

生徒の年齢を知りたい場合がある。それも任意の日における年齢を知る必要がある。というのも、学校における各種統計において、「その日の年齢」であったり「ある基準日における年齢」であったり、年齢を算出する基準日が異なるためだ。これに対応するユーザー定義関数を作ってみた。

生徒は学籍番号で管理されており、「生徒」テーブルに生年月日が保存されている。この生年月日から年齢を算出するユーザー定義関数だ。

使い方はこうだ。

select dbo.fx生徒年齢(学籍番号,基準日)

たとえば2010年4月1日における学籍番号0101001の生徒の年齢を知るには、実際にはこんなかんじ。

select dbo.fx生徒年齢(‘0101001′,’2010/04/01’)

——————(ここからユーザー定義関数)——————

create function [dbo].[fx生徒年齢]

    (
    @p_学籍番号 char(7),
    @p_基準日 smalldatetime
    )
returns int
AS
begin
declare @判定 int
declare @生年月日 smalldatetime
set @生年月日 = (select 生年月日 from 生徒 where 学籍番号 = @p_学籍番号)
set @判定 = datediff(dd
,cast(‘2000/’+cast(month(@生年月日) as varchar(2))+’/’+cast(day(@生年月日) as varchar(2)) as smalldatetime)
,cast(‘2000/’+cast(month(@p_基準日) as varchar(2))+’/’+cast(day(@p_基準日) as varchar(2))as smalldatetime))
return (select case when @判定 < 0 then datediff(yy,@生年月日,@p_基準日)-1
when @判定 >= 0 then datediff(yy,@生年月日,@p_基準日) end)
return null
end

——————(ここまでユーザー定義関数)——————

Written by Yoshio Matsumoto

2011年2月16日 at 12:11 AM

InfoPathとSQL Serverで「学校評価」の集計をする

leave a comment »

年度末に近づき、多くの学校では一年間のまとめをする時期になったと思う。本校でもこの一年の取り組みのまとめとして、学校評価を行うことになった。

本校の学校評価では、いくつかの大項目の下に、小項目として50弱の項目を目標として設定した。これらの小項目に対して、それぞれ教員が1から4までの評価を与えることにしている。このようなアンケート式のデータ集計は、InfoPathとSQL Serverが最も得意とするところである。しかしテーブル構造や集計については、少し工夫を要するところがある。

InfoPathフォームはSQL Serverのテーブルに対して1対1の対応が得意である。そこでやや強引ではあるが、テーブル構造として次のようなものを作成した。

———————-(ここからテーブル作成SQL)———————-

CREATE TABLE gakkohyoka_t_hyoka (
    [shokuinbango] [char] (6) ,
    [inputtime] [datetime] ,
    [01] [char] (2) ,
    [02] [char] (2) ,
    [03] [char] (2) ,
    [04] [char] (2) ,
    [05] [char] (2) ,
    [06] [char] (2) ,
    [07] [char] (2) ,
    [08] [char] (2) ,
    [09] [char] (2) ,
    [10] [char] (2) ,
    [11] [char] (2) ,
    [12] [char] (2) ,
    [13] [char] (2) ,
    [14] [char] (2) ,
    [15] [char] (2) ,
    [16] [char] (2) ,
    [17] [char] (2) ,
    [18] [char] (2) ,
    [19] [char] (2) ,
    [20] [char] (2) ,
    [21] [char] (2) ,
    [22] [char] (2) ,
    [23] [char] (2) ,
    [24] [char] (2) ,
    [25] [char] (2) ,
    [26] [char] (2) ,
    [27] [char] (2) ,
    [28] [char] (2) ,
    [29] [char] (2) ,
    [30] [char] (2) ,
    [31] [char] (2) ,
    [32] [char] (2) ,
    [33] [char] (2) ,
    [34] [char] (2) ,
    [35] [char] (2) ,
    [36] [char] (2) ,
    [37] [char] (2) ,
    [38] [char] (2) ,
    [39] [char] (2) ,
    [40] [char] (2) ,
    [41] [char] (2) ,
    [42] [char] (2) ,
    [43] [char] (2) ,
    [44] [char] (2) ,
    [45] [char] (2) ,
    [46] [char] (2) ,
    [47] [char] (2) ,
    [48] [char] (2) ,
    [49] [char] (2) ,
    [50] [char] (2) ,
    [hyokaiken] [varchar] (2000) ,
    [unneiiken] [varchar] (2000) 
)

———————-(ここまでテーブル作成SQL)———————-

フィールド[shokuinbango]に職員番号を生成しておき、[inputtime]にInfoPathフォームで入力したときの日付を入れる、フィールド[01]から[50]までにそれぞれの項目の1から4評価をストアし、最後の[hyokaiken]と[unneiiken]には自由記述で文を入力できるようにする。このようなテーブルにすると、InfoPathフォームは作りやすい。

しかし問題は集計の方法だ。これがExcelの表ならば、各列の最後にcountif()文を使って各評価の個数を集計するところだが、SQL Serverではそうはいかない。

そこで、まず、このテーブルの値を正規化するビューを作る。次のようなビューだ。

———————-(ここからビューのSQL)———————-

SELECT                  ’01’ AS 項目, [01] AS 評価, COUNT(01) AS 回答数
FROM                     gakkohyoka_t_hyoka
GROUP BY          [01]
UNION
SELECT                  ’02’ AS 項目, [02] AS 評価, COUNT(02) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_02
GROUP BY          [02]
UNION
SELECT                  ’03’ AS 項目, [03] AS 評価, COUNT(03) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_03
GROUP BY          [03]
UNION
SELECT                  ’04’ AS 項目, [04] AS 評価, COUNT(04) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_04
GROUP BY          [04]
UNION
SELECT                  ’05’ AS 項目, [05] AS 評価, COUNT(05) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_05
GROUP BY          [05]
UNION
SELECT                  ’06’ AS 項目, [06] AS 評価, COUNT(06) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_06
GROUP BY          [06]
UNION
SELECT                  ’07’ AS 項目, [07] AS 評価, COUNT(07) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_07
GROUP BY          [07]
UNION
SELECT                  ’08’ AS 項目, [08] AS 評価, COUNT(08) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_08
GROUP BY          [08]
UNION
SELECT                  ’09’ AS 項目, [09] AS 評価, COUNT(09) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_09
GROUP BY          [09]
UNION
SELECT                  ’10’ AS 項目, [10] AS 評価, COUNT(10) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_10
GROUP BY          [10]
UNION
SELECT                  ’11’ AS 項目, [11] AS 評価, COUNT(11) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_11
GROUP BY          [11]
UNION
SELECT                  ’12’ AS 項目, [12] AS 評価, COUNT(12) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_12
GROUP BY          [12]
UNION
SELECT                  ’13’ AS 項目, [13] AS 評価, COUNT(13) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_13
GROUP BY          [13]
UNION
SELECT                  ’14’ AS 項目, [14] AS 評価, COUNT(14) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_14
GROUP BY          [14]
UNION
SELECT                  ’15’ AS 項目, [15] AS 評価, COUNT(15) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_15
GROUP BY          [15]
UNION
SELECT                  ’16’ AS 項目, [16] AS 評価, COUNT(16) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_16
GROUP BY          [16]
UNION
SELECT                  ’17’ AS 項目, [17] AS 評価, COUNT(17) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_17
GROUP BY          [17]
UNION
SELECT                  ’18’ AS 項目, [18] AS 評価, COUNT(18) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_18
GROUP BY          [18]
UNION
SELECT                  ’19’ AS 項目, [19] AS 評価, COUNT(19) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_19
GROUP BY          [19]
UNION
SELECT                  ’20’ AS 項目, [20] AS 評価, COUNT(20) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_20
GROUP BY          [20]
UNION
SELECT                  ’21’ AS 項目, [21] AS 評価, COUNT(21) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_21
GROUP BY          [21]
UNION
SELECT                  ’22’ AS 項目, [22] AS 評価, COUNT(22) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_22
GROUP BY          [22]
UNION
SELECT                  ’23’ AS 項目, [23] AS 評価, COUNT(23) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_23
GROUP BY          [23]
UNION
SELECT                  ’24’ AS 項目, [24] AS 評価, COUNT(24) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_24
GROUP BY          [24]
UNION
SELECT                  ’25’ AS 項目, [25] AS 評価, COUNT(25) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_25
GROUP BY          [25]
UNION
SELECT                  ’26’ AS 項目, [26] AS 評価, COUNT(26) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_26
GROUP BY          [26]
UNION
SELECT                  ’27’ AS 項目, [27] AS 評価, COUNT(27) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_27
GROUP BY          [27]
UNION
SELECT                  ’28’ AS 項目, [28] AS 評価, COUNT(28) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_28
GROUP BY          [28]
UNION
SELECT                  ’29’ AS 項目, [29] AS 評価, COUNT(29) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_29
GROUP BY          [29]
UNION
SELECT                  ’30’ AS 項目, [30] AS 評価, COUNT(30) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_30
GROUP BY          [30]
UNION
SELECT                  ’31’ AS 項目, [31] AS 評価, COUNT(31) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_31
GROUP BY          [31]
UNION
SELECT                  ’32’ AS 項目, [32] AS 評価, COUNT(32) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_32
GROUP BY          [32]
UNION
SELECT                  ’33’ AS 項目, [33] AS 評価, COUNT(33) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_33
GROUP BY          [33]
UNION
SELECT                  ’34’ AS 項目, [34] AS 評価, COUNT(34) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_34
GROUP BY          [34]
UNION
SELECT                  ’35’ AS 項目, [35] AS 評価, COUNT(35) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_35
GROUP BY          [35]
UNION
SELECT                  ’36’ AS 項目, [36] AS 評価, COUNT(36) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_36
GROUP BY          [36]
UNION
SELECT                  ’37’ AS 項目, [37] AS 評価, COUNT(37) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_37
GROUP BY          [37]
UNION
SELECT                  ’38’ AS 項目, [38] AS 評価, COUNT(38) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_38
GROUP BY          [38]
UNION
SELECT                  ’39’ AS 項目, [39] AS 評価, COUNT(39) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_39
GROUP BY          [39]
UNION
SELECT                  ’40’ AS 項目, [40] AS 評価, COUNT(40) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_40
GROUP BY          [40]
UNION
SELECT                  ’41’ AS 項目, [41] AS 評価, COUNT(41) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_41
GROUP BY          [41]
UNION
SELECT                  ’42’ AS 項目, [42] AS 評価, COUNT(42) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_42
GROUP BY          [42]
UNION
SELECT                  ’43’ AS 項目, [43] AS 評価, COUNT(43) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_43
GROUP BY          [43]
UNION
SELECT                  ’44’ AS 項目, [44] AS 評価, COUNT(44) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_44
GROUP BY          [44]
UNION
SELECT                  ’45’ AS 項目, [45] AS 評価, COUNT(45) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_45
GROUP BY          [45]
UNION
SELECT                  ’46’ AS 項目, [46] AS 評価, COUNT(46) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_46
GROUP BY          [46]
UNION
SELECT                  ’47’ AS 項目, [47] AS 評価, COUNT(47) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_47
GROUP BY          [47]
UNION
SELECT                  ’48’ AS 項目, [48] AS 評価, COUNT(48) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_48
GROUP BY          [48]
UNION
SELECT                  ’49’ AS 項目, [49] AS 評価, COUNT(49) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_49
GROUP BY          [49]
UNION
SELECT                  ’50’ AS 項目, [50] AS 評価, COUNT(50) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_50
GROUP BY          [50]

———————-(ここまでビューのSQL)———————-

ひたすら長いSQL文だが、構造は簡単なUNIONクエリの繰り返しなので、機械的に作ることができるはずだ。このSQL文によって、「項目」、「評価」、「回答数」というビューが得られる。結果はSQL Server Management Studioの編集画面を見てもらうと一目瞭然だろう。

学校評価SQL工夫_005_edit
<Fig.1 : UNIONクエリによるデータの途中集計>

この後はPIVOTによる集計を行う。クエリは以下のとおり。

———————-(ここからPIVOTのSQL)———————-

SELECT 項目
    ,SUM(CASE WHEN 評価 = ‘A’ THEN 回答数 ELSE 0 END) AS 評価A,
    ,SUM(CASE WHEN 評価 = ‘B’ THEN 回答数 ELSE 0 END) AS 評価B,
    ,SUM(CASE WHEN 評価 = ‘C’ THEN 回答数 ELSE 0 END) AS 評価C,
    ,SUM(CASE WHEN 評価 = ‘D’ THEN 回答数 ELSE 0 END) AS 評価D
FROM v_gakkohyoka_shu
GROUP BY 項目

———————-(ここまでPIVOTのSQL)———————-

これもSQL Server Management Studioの編集画面を見てもらうと直感的にわかるだろう。

学校評価SQL工夫_006_edit
<Fig.2 : PIVOT集計のビュー>

あとはこのビューに対して、画面に表示する単純なInfoPathフォームを作るだけだ。こうしておけば、アンケートの入力が終わった時点で、即集計結果を出すことができる。

SQL ServerのようなデータベースとExcelを単純に比べることは意味がないが、このような集計をするにはExcelは得意であるが、SQL Serverでやろうとすると手間がかかることは間違いない。しかし一度作っておけば、毎年同じ処理をこのテーブルとビューでできるので、省力化できる。項目がいくら増えても、選択肢が増えても、職員数が変わっても、ほとんど変更することなく集計ができる。

Written by Yoshio Matsumoto

2011年2月13日 at 11:53 PM

SQL Server 2000のデータをSQL Server 2005にインポートするにはAccess Projectを使おう

leave a comment »

つい先日のことだが、古いサーバーでSQL Server 2000で管理されていたデータを、新しいサーバーのSQL Server 2005へ移行する作業を行った。このとき、データ移行でエラーとなり、試行錯誤した。

データ移行には、DTSを使った。DTS(Data Transformation Services)である。旧サーバーのSQL Server 2000からDTSを使ってデータをExcel形式で書き出し、SQL Server 2005のDTSでインポートしようとした。このとき「エラー0xc02020f6:データフロータスク : 列****では、Unicod形式の文字列データ型とUnicode以外の形式の文字列データ型を変換できません」といったエラーが出てインポートができない現象がおきた。

学校評価データ移行_020
<Fig.1 : DTSのインポートウィザードが停止した状態>

学校評価データ移行_021
<Fig.2 : DTS停止のレポート表示>

もとのSQL Server 2000のテーブルは、単純なvarchar型のテーブルであり、なんら文字コードに特殊な仕掛けをしていない。さて困ったところで、DTSでデータを書き出すためにExcel形式はやめてCSVにしたり文字コードを決め打ちしたりいろいろとやっても解決できない。かなり行き詰まった状況になり、焦燥感が充満した。

Excel形式でもCSVでもだめだったので、データをAccessファイルに書き出すことを考えた。しかしSQL Server 2000のDTSで書き出したAccessファイルをSQL Server 2005のDTSで読み込んでもエラーで止まってしまう。

しばらく頭を冷やし、何か方法がないか、と考え、Access Projectを使うことを思いついた。つまり、こうするのだ。

1.移転先のSQL Server 2005に対してAccess Projectを作成する。
2.そのAccess Projectでデータのインポートを実行する。
3.インポートするファイルは、SQL Server 2000のDTSで書き出したExcelファイルでもCSVファイルでもよい。
4.念のため新しいテーブルにインポートし、その後SQL文でインポートしたいテーブルにINSERTする。

上記のような手順で、うまくデータをインポートすることができた。つまり、データを書き出すにはSQL ServerのDTSでよいが、インポートするにはDTSよりもAccess Projectのインポートウィザードを使った方がよかった、ということだ。これがなぜなのか、といった追求までしていないが、DTSでうまくいかない場合はAccessを間に介することで解決できることもある、ということはTipsとして知っておいていいだろう。

Written by Yoshio Matsumoto

2011年2月13日 at 10:12 PM

Roland JS-8 e-BandにCDから音を取り込むには専用のソフトJS-8 Song List Editorを使う

leave a comment »

RolandのJS-8は本格的なバッキングフレーズを多数デフォルトで持っているが、CDから音を取り込んで使うこともできる。それには専用のソフト、JS-8 Song List Editorを使う。

まず取り込みたいCDをPCのドライブに装填する。そしてJS-8をUSB接続し、JS-8 Song List Editorを起動する。

js-8_011
<Fig.1 : JS-8をPCに接続してJS-8 Song List Editorを起動したところ>

JS-8 Song List Editorの画面に表示されるのは、標準で用意されたバッキングトラックのリストだ。ここで「CD Ripping」のボタンをクリックする。するとCD Rippingのウィンドウが表示され、CDの曲を選択することができる。このとき、インターネットのデータベースから曲名などが自動的に検索されている。

js-8_012
<Fig.2 : CD Rippingで曲を選択する>

このときリッピングされたデータは、PCではなくJS-8にストアされる。リッピングが終了したらソングリストに戻るが、このときリスト上にリッピングされた曲データが表示されているのがわかる。

js-8_013
<Fig.3 : リッピングが終了したときのSongリスト>

リッピングデータがSongリストに表示されることからもわかるように、CDからリッピングした曲はJS-8のSong Listから選んで再生することができる。

IMG_1111
<Fig.4 : JS-8でリッピングした曲を再生する>

上のFig.4はJS-8でリッピングした曲を再生しているところ。あとは自分のギターを思う存分弾きまくるだけだ。

Written by Yoshio Matsumoto

2011年2月13日 at 1:51 AM

SQL Serverのユーザー定義関数を使おう~(10)月曜から始まる年度週番号ユーザー定義関数

leave a comment »

以前、「年度週番号」を返すT-SQLのユーザー定義関数を作ったことを書いた。ある日付が、その年度の何週目にあたるかを求める必要があったからだ。以前に書いたユーザー定義関数はこのようなものだった。

———————-(ここから)———————-

create function fx年度週番号
    (
    @p_日付 datetime
    )
returns int
as

begin
declare @thisyear41 datetime
declare @overyear41 datetime
set @thisyear41 = cast(cast(datepart(year,@p_日付) as varchar) + ‘/’ +’4/1′ as datetime)
set @overyear41 = cast(cast(datepart(year,@p_日付)-1 as varchar) + ‘/’ +’4/1′ as datetime)
    if @p_日付 > @thisyear41
    begin
        return datediff(week,@thisyear41,@p_日付)+1
    end
    if @p_日付 < @thisyear41
    begin
        return datediff(week,@overyear41,@p_日付)+1
    end
    if @p_日付 = @thisyear41
    begin
        return 1
    end
return null
end

———————-(ここまで)———————-

引数にする日付の年の4月1日を求め、日付が4月1日よりも後だったらその日付は4月1日から12月31日までであり、4月1日よりも前だったら年が変わっているので1月1日から3月31日までであると判定し、週の番号を求める仕組みだった。

このユーザー定義関数では、日曜日が週の最初に来る値を返す。しかし欲しかったのは日曜日が週の最後に来る値を返すユーザー定義関数だった。そこでこのユーザー定義関数を修正し、月曜で始まる週として年度週番号を返すユーザー定義関数に変更した。変更は力技的であり、日付が日曜ならば年度週番号を1マイナスする、という仕組みにした。

———————-(ここから)———————-

create function fx年度週番号
    (
    @p_日付 datetime
    )
returns int
as

/*
日曜はじまりの年度週番号を返すように変更
*/

begin
declare @thisyear41 datetime
declare @overyear41 datetime
set @thisyear41 = cast(cast(datepart(year,@p_日付) as varchar) + ‘/’ +’4/1′ as datetime)
set @overyear41 = cast(cast(datepart(year,@p_日付)-1 as varchar) + ‘/’ +’4/1′ as datetime)
declare @sunday_minus int
set @sunday_minus = 0
if datepart(weekday,@p_日付) = 1
set @sunday_minus = 1
    if @p_日付 > @thisyear41
    begin
        return datediff(week,@thisyear41,@p_日付)+1-@sunday_minus
    end
    if @p_日付 < @thisyear41
    begin
        return datediff(week,@overyear41,@p_日付)+1-@sunday_minus
    end
    if @p_日付 = @thisyear41
    begin
        return 1
    end
return null
end

———————-(ここまで)———————-

Written by Yoshio Matsumoto

2011年2月13日 at 12:53 AM