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

InfoPath & SQL Server !

Posts Tagged ‘T-SQL

SQL Server : サーバーサイドでデータの変更日付をテーブルに自動的に記録するために、AFTER UPDATEトリガを利用する。変更データの絞込みにはサブクエリではなく in 句を使う。

leave a comment »

「SQL Server : サーバーサイドでデータの作成日付をテーブルに自動的に記録するために、フィールドの列のプロパティで規定値を設定し、INSTEAD OF INSERTトリガを利用する」では、テーブルのフィールドに「作成日」のフィールドを作り、データの生成日と時間を自動的に記録する方法を説明した。データの生成日と時間をサーバー側で記録するには、フィールドのプロパティに既定値を設定する方法と、INSTEAD OF INSERTトリガで強制的にデータを与える方法があった。では、既に生成しているデータの「更新日」をSQL Server側で自動的に記録するにはどうすればよいだろう。これもテーブルに「トリガ」を設定することで実現できるが、少し工夫が必要だ。

まず次のようなテーブルを作ろう。

——————————————————

フィールド名 データ型 備考
ID int 主キー、IDENTITY(はい)、シード(1)、増分(1)
データ int
作成日 datetime
更新日 datetime

——————————————————

SQLSV_blog_update_datetime_001_mid_640

<Fig.1 : サンプルの「テスト」テーブルを作る>

テーブルができたら、いくつかサンプルのデータを作っておこう。

SQLSV_blog_update_datetime_003_mid_640

<Fig.2 : 「テスト」テーブルにサンプルのデータを作っておく>

次に、この「テスト」テーブルに次のクエリでAFTER UPDATEトリガを設定する。

——————————————————

CREATE trigger trig_02 on テスト after update

as

update テスト set 更新日 = getdate() where ID = (select ID from inserted)

——————————————————

SQLSV_blog_update_datetime_002_mid_640

<Fig.3 : 「テスト」テーブルにトリガを設定する>

SQLSV_blog_update_datetime_004_mid_640

<FIg.4 : 「テスト」テーブルにトリガを設定した>

このトリガは、「テスト」テーブルにUPDATE文が発行されたとき、変更されたデータが入っている「inserted」一時テーブルのIDを調べ、そのIDと同じIDのデータに対して「更新日」のフィールド値を gerdate() 関数で現在の日付と時間を取得して更新するというものだ。

では、このトリガを設定した「テスト」テーブルのデータを更新してみよう。次のクエリを試してみる。

——————————————————

UPDATE テスト SET データ = 101 where データ = 100

——————————————————

SQLSV_blog_update_datetime_005_mid_640

<Fig.5 : トリガを設定した「テスト」テーブルに UPDATE 文を実行する>

UPDATE 文は1件のデータを更新するものだが、処理結果のメッセージが2行表示されている。トリガが動いたのだ。ではテーブルのデータを確認してみよう。

SQLSV_blog_update_datetime_006_mid_640

<Fig.6 : データを更新した「テスト」テーブルのデータを確認する>

トリガによって「更新日」のデータが自動的に記録されたことがわかる。だが、実はこのトリガには問題がある。試しに、次のクエリを実行してみよう。このクエリでは、データが 800 のデータを更新しているが、サンプルデータには 800 のデータが2件あるのだ。

——————————————————

UPDATE テスト SET データ = 801 where データ = 800

——————————————————

SQLSV_blog_update_datetime_007_mid_640

<Fig.7 : 「テスト」テーブルの2件のデータを更新しようとしてエラーになった>

トリガに設定した UPDATE 文はサブクエリだ。サブクエリでは複数の値を処理することができない。これでは困るので、トリガを変更する。変更するには、テーブルを展開してトリガを表示し、右クリックで「変更」をクリックする。

SQLSV_blog_update_datetime_010_mid_640

<Fig.8 : 「テスト」テーブルに設定したトリガを変更する>

——————————————————

update テスト set 更新日 = getdate() where ID in (select ID from inserted)

——————————————————

= でつないだサブクエリを in 句に変更するのだ。

SQLSV_blog_update_datetime_011_mid_640

<Fig.9 : トリガのサブクエリを in 句に変更する>

変更できたら、もういちどデータを2件変更する UPDATE 文を実行してみよう。うまくいくはずだ。

SQLSV_blog_update_datetime_012_mid_640

<Fig.10 : データを2件変更する UPDATE 文を実行する>

変更したデータを確認しよう。

SQLSV_blog_update_datetime_013_mid_640

<Fig.10 : UPDATE 文を変更したテーブルを確認する>

このように更新日の日付と時間をサーバー側で自動的に記録するには、テーブルにトリガを設定すればよい。そのとき、変更をうけたデータを絞り込むにサブクエリではなくて in 句を利用する。サブクエリでは複数のデータを更新する UPDATE 文でエラーがおこるためだ。

広告

SQL Server : サーバーサイドでデータの作成日付をテーブルに自動的に記録するために、フィールドの列のプロパティで規定値を設定し、INSTEAD OF INSERTトリガを利用する

leave a comment »

データを管理する際に、そのデータがいつ作られ、いつ更新されたかを記録しておくことが求められる場合がある。これらの日付を記録するため、次のようにテーブルに「作成日」と「更新日」のフィールドを持たせておくことにする。どちらのフィールドも「datetime型」としておこう。

SQL_storedatetime_001_mid_640

<fig.1 : テーブル「Table_3」に「作成日」と「更新日」のフィールドを作成した>

このテーブルに新しいデータを生成し、「作成日」の値を記録するとき、クライアント側で明示的に処理を行うなら次のようなSQL文になるだろう。このとき、getdate()はSQL Serverから日付を取得する関数だ。

————————————————————————

insert into Table_3(サンプル値,作成日) values(‘ほげほげ’,getdate())

————————————————————————

SQL_storedatetime_002_mid_640

<fig.2 : 明示的にSQL文によってデータ作成日を登録した>

SQL_storedatetime_003_mid_640

<fig.3 : Table_3に作成日が登録された>

すでに生成したデータを更新し、「更新日」の値を記録するとき、クライアント側で明示的に処理を行うなら次のようなSQL文になるだろう。

————————————————————————

update Table_3 set サンプル値 = ‘ほにゃらら’,更新日 = getdate() where サンプル値 = ‘ほげほげ’

————————————————————————

SQL_storedatetime_004_mid_640

<fig.4 : Table_3のデータ「ほげほげ」を「ほにゃらら」に更新し、明示的にSQL文によってデータ作成日を登録した>

SQL_storedatetime_005_mid_640

<fig.5 : Table_3のデータを更新し、更新日が登録された>

新しいデータを作成したときに自動的に「作成日」が記録されるようにするには、フィールドのプロパティで「規定値」を設定する方法がある。規定値に「getdate()」関数を設定しておけばいい。規定値を設定すれば、SQL文で明示的に日付を与えなくてもテーブルには自動的に日付が入るようになる。

SQL_storedatetime_006_mid_640

<fig.6 : Table_3の「作成日」フィールドのプロパティで「規定値」を「getdate()」に設定した>

フィールドに規定値を設定すると、次のSQL文で新しいデータを生成すると「作成日」の値が自動的に記録される。

————————————————————————

insert into Table_3(サンプル値) values(‘規定値でほげ’)

————————————————————————

SQL_storedatetime_007_mid_640

<fig.7 : Table_3に「作成日」を明示的に指定せずにデータを生成した>

SQL_storedatetime_008_mid_640

<fig.8 : Table_3に「作成日」を明示的に指定せずにデータを生成し、規定値が記録された>

このように規定値を使って自動的に値を与えることは一般的だが、この方法ではうまくいかない場合がある。いくら規定値を指定していても、規定値はあくまでも値が示されないときのものであって、ユーザーが明示的に値を与えると規定値は無視されユーザーの指定した値が記録されるのだ。たとえばユーザーが次のSQL文を使うと「記録日」が期待どおりの「規定値」にならない。

————————————————————————

insert into Table_3(サンプル値,作成日) values(‘昔の日付で出ています’,’1961/8/13′)

————————————————————————

SQL_storedatetime_009_mid_640

<fig.9 : 「作成日」に規定値を設定したTable_3にSQLで明示的に日付を指定してデータを生成するSQL文を実行した>

SQL_storedatetime_010_mid_640

<fig.10 : 「作成日」に規定値を設定したTable_3にSQLで明示的に日付を指定して過去の日付でデータを生成した>

データを管理するためには、ユーザーが「作成日」に勝手な値を指定できないようにしたほうがいい。そこでユーザーが「作成日」に明示的な日付を設定しても、それを無効にしてサーバーサイドで日付を強制的に設定される方法を考えてみよう。それにはテーブルにINSERTトリガを設定する方法がある。INSERTトリガは、テーブルにデータがINSERTされたときに自動的に実行されるストアドプロシージャだ。

INSERTトリガが実行されるタイミングは2つある。ひとつはINSERTが実行された後にトリガが実行されるAFTER INSERT、もうひとつはINSERTが実行される前にトリガが実行されるINSTEAD OF INSERTだ。INSTEAD OF INSERTはその名前からわかるように、実際のINSERT文に代わって実行されるトリガだ。ためしに次のSQL文でトリガをテーブルに設定してみよう。トリガを作成するには、create triggerを使う。

————————————————————————

create trigger trig_01 on Table_3 instead of insert
as
insert into Table_3(サンプル値,作成日) values(‘トリガでほげ’,GETDATE())

————————————————————————

このトリガを設定するSQL文で、create triggerの次にくる「trig_01」はトリガ名だ。わかりやすい名前を考えて自分で考えてつけることになる。その後onに続いてどのテーブルに対して設定するのかの「テーブル名」を記述している。そしてinstead of insertがトリガの種類だ。次にasに続けて、INSERTに代わって実行させたいSQL文を記述している。トリガを作成したら、左のペイン「オブジェクトエクスプローラー」の「Table_3」を展開すると「トリガ」のところに「trig_01」という名前のトリガができていることがわかる。

SQL_storedatetime_014_mid_640

<fig.11 : create trigger文でTable_3にINSTEAD OF INSERTトリガを作成した>

これでTable_3に何か値をinsertしようとしても、代わりにINSTEAD OF INSERTトリガが働き、あらかじめ決められた内容のデータしか生成しなくなる。たとえば次のようなSQL文をTable_3に実行してみよう。

————————————————————————

insert into Table_3(サンプル値,作成日) values(‘どうなる’,’1961/8/13′)

————————————————————————

SQL_storedatetime_015_mid_640

<fig.12 : INSTEAD OF INSERTトリガを設定したTable_3に明示的にinsert文を実行する>

ひとつのinsert文しか実行していないのに、処理結果が2つあらわれている。トリガが動いたのだ。

SQL_storedatetime_016_mid_640

<fig.13 : INSTEAD OF INSERTトリガを設定したTable_3に明示的にinsert文を実行した結果>

Table_3を見ると、明示的に実行されたINSERT文は無視され、トリガによって強制的にデータがinsertされたことがわかる。「作成日」はgetdate()で得られた日付になっている。

ここで「作成日」が強制的にgetdate()の値になったことはよいが、「サンプル値」が決まった値では困る。「サンプル値」はもとのinsert文で示された値でなければいけない。このときINSERTトリガでは、もとのinsert文によって与えられたデータは、一時的なテーブル「INSERTED」に保存されている。トリガを変更し、INSERTEDテーブルの値を利用して「サンプル値」を取得しよう。トリガを変更するには、ALTER TRIGGERを使う。

————————————————————————

alter trigger trig_01 on Table_3 instead of insert
as
insert into Table_3(サンプル値,作成日) select サンプル値,getdate() from inserted

————————————————————————

このINSTEAD OF INSERTトリガで、「サンプル値」はinsert文によって一時テーブルinsertedに記録された「サンプル値」を得、「作成日」はgetdate()で与えている。

SQL_storedatetime_017_mid_640

<fig.14 : alter triggetによってINSTEAD OF INSERTトリガを変更した>

SQL_storedatetime_018_mid_640

<fig.15 : INSTEAD OF INSERTトリガを変更したテーブルにINSERT文を実行した>

SQL_storedatetime_019_mid_640

<fig.16 : 「サンプル値」をinserted一時テーブルから取得し「作成日」はgetdate()で明示的に値を与えた結果>

このように、テーブルにデータを生成するとき「作成日」を与えたいとき、ひとつはフィールドのプロパティで「規定値」を設定する方法があり、さらにユーザーによる「作成日」の明示的な指定を避けたいときは、INSTEAD OF INSERTトリガを設定し、insertされた値を強制的に変更する手法を使うことができる。INSTEAD OF INSERTトリガだけでなく、トリガは他にもいろいろな自動化処理をするために使えるので、データの整合性をとったり履歴を記録したりすることに使うなど、工夫のしがいがあるところだ。

SQL Server : フィールドにIDENTITYを設定して自動的に連番が設定されるようにしたテーブルでIDENTITY_INSERTをONにして明示的に数値を指定したデータをINSERTする

with one comment

データを管理するとき、自動的に連番が設定されるようにしたい場合がある。これは例えばMicrosoft Accessでいう「オートナンバー型」のフィールドに相当する。Microsoft Accessの「オートナンバー型」は、データを生成するときに何も値を入れず、自動的に連番が設定されるフィールドだ。テーブルを構成するとき、このように自動的に連番が割り当てられるフィールドを作っておくと、その連番値自体に特段の意味はなくても、データを管理する場面で有効になる。システムの運用をはじめると、さまざまな想定外のことがおこる。間違ったデータを大量に作ってしまい特定の範囲のデータを全部まとめて修正しなければならない、重複したデータを作ってしまい後から作ったデータを削除しなければならない、ある一定期間に行われた処理に誤りがあり一括してデータを修正しなければならない、といったようなこことだ。これらの運用時に、データが生成された順番がわかっていると処理しなければならないデータの範囲を絞り込む重要な手掛かりになる。

Microsoft Accessには次のように「オートナンバー型」というデータ型がある。

SQL_identity_002_mid_640

<Fig.1 : Miceosoft Access 2013でフィールドのデータ型をオートナンバー型にする>

しかしSQL ServerにはAccessのようなオートナンバー型というデータ型はない。SQL ServerでAccessのオートナンバー型のように、数値が連番で自動的に設定されるようにするには、データ型を「bigint」、「decimal」、「int」、「numeric」、「smallint」、「tinyint」のような整数の性質を持っているものとし、列のプロパティで「IDENTITYの指定」の「(IDである)」を「はい」にし、「IDの増分」と「IDENTITYシード」を設定する。「IDENTITYシード」は最初に割り当てられる数値を意味し、「IDの増分」はデータが増えるたびに加えられる値を意味する。次の例は「IDENTITYシード」を10、「IDの増分」を3として作ったテーブルにデータをいくつか追加した例だ。

SQL_identity_004_mid_640

<fig.2 : IDフィールドのIDENTITYを指定し、IDENTITYシードを10、IDの増分を3と設定する>

SQL_identity_006_mid_640

<fig.3 : IDフィールドにIDENTITYを指定したテーブルにデータを3件追加した>

なおIDENTITYの設定は「float」、「real」のような実数型では指定することができない。また「money」、「smallmoney」でも指定することができない。

IDENTITYを指定したフィールドには明示的に値を指定することはできず、自動的に値が割り当てられるだけになる。例えば次のようなINSERT文は実行できない。

——————————————————————

insert into Table_1(ID,[values]) values(20,’testinsert’)

——————————————————————

SQL_identity_007_mid_640

<fig.4 : IDENTITYを指定したフィールドに明示的に値を指定したINSERT文を実行しエラーとなった>

IDENTITYを指定したフィールドは、たとえば次のように明示的に値を指定せずにINSERT文を実行することになるだろう。

——————————————————————

insert into Table_1([values]) values(‘testinsert’)

——————————————————————

SQL_identity_008_mid_640

<fig.5 : IDENTITYを指定したフィールドに明示的に値を指定せずINSERT文を実行した>

SQL_identity_009_mid_640

<fig.6 : IDENTITYを指定したフィールドに明示的に値を指定せずINSERT文を実行した結果>

INSERTされたデータのIDは、フィールドに指定した「IDの増分」だけ増えた値が自動的に割り当てられている。もちろんこれは期待どおりの結果だが、データの保守管理上はこのままでは困る場合が出てくる。たとえばあるテーブルのデータを別のテーブルに一時的に退避し、後に戻したいといった場合だ。データを退避するため「table_1」と同じフィールド構造をもつ「Table_2」を作っておくが、「Table_2」の「ID」フィールドのIDENTITYは設定しない。そして次のクエリのように、「Table_1」のデータを「Table_2」にコピーし、「Table_1」のデータを消去するが、そのあと、「Table_2」のデータを「Table_1」の戻すときにはエラーとなる。

——————————————————————

insert into Table_2(ID,[values]) select ID,[values] from Table_1
delete Table_1
insert into Table_1(ID,[values]) select ID,[values] from Table_2 –(エラーになる)

——————————————————————

SQL_identity_010_mid_640

<fig.7 : IDENTITYが設定されているフィールドに明示的に値を指定してINSERTしようとしてエラーになった>

このエラーを回避する最もわかりやすい方法は、「Table_1」の「ID」列のIDENTITY指定を一時的に解除することだ。しかし一時的ではあれテーブルのデザインを変更すると、そのあいだに他のユーザーが正しい方法でデータを挿入しようとしたときにエラーになるだろう。自分だけが使うデータベースであるとか、システムが小規模で他に与える影響がないと考えられる場合はいいかもしれないが、望ましいことではない。

このようにIDENTITYが設定されたフィールドに明示的に値を与えたいとき、IDENTITY_INSERTをONにする方法がある。先のエラーになったINSERT文の前後にIDENTITY_INSERT文を使おう。例えば次のクエリはエラーにならず成功する。

——————————————————————

set identity_insert Table_1 on
insert into Table_1(ID,[values]) select ID,[values] from Table_2
set identity_insert Table_1 off

——————————————————————

SQL_identity_011_mid_640

<fig.8 : IDENTITY_INSERTの設定によりINSERTが成功した>

set identity_insert Table_1 onによってIDENTITYを設定したフィールドにも明示的に値を与えることができるようになり、set identity_insert Table_1 offでデフォルトの状態に戻る。

たとえば次のようにIDに「20」という値を明示的に挿入するクエリも実行することができる。

——————————————————————

set identity_insert Table_1 on
insert into Table_1(ID,[values]) values(20,’ID20testdata’)
set identity_insert Table_1 off

——————————————————————

SQL_identity_012_mid_640

<fig.9 : IDENTITYを設定したIDフィールドに明示的に「20」の値を設定してINSERTした>

SQL_identity_013_mid_640

<fig.10 : IDENTITYを設定したIDフィールドに明示的に「20」の値を設定してINSERTした結果>

このように、テーブルにIDENTITYを設定したIDフィールドを持たせることはよくやることだが、IDENTITYを指定したフィールドには通常は明示的な値を与えることはないし、それはできないのだが、identity_insertをon、offすることによって明示的に値を与えることができ、これはデータの管理の手法として知っておくとよい。

SQL Server – 「基準値」テーブルでコードを一括管理する

leave a comment »

SQL Serverによるシステムがある程度になると、いろいろな処理をするためのコードを効率よく処理することが必要になる。学校の場合は、たとえば「教科科目コード」や「時限コード」のような時間割に関するコード、「在籍コード」や「異動コード」など生徒の在籍状態に関するコード、などだ。

これらのコードをシステム上どのようにして処理するかだが、単純に考えると「教科科目コード」や「時限コード」といった名前のテーブルを作りコードを記録することだが、それぞれのコードに対してテーブルを作ると、テーブルの数が増えてシステム全体の見通しが悪くなる。ちなみに、このようにテーブルを作る場合でも、テーブル名は「コード教科科目」や「コード時限」のように「コード」といった共通の名前を前にするとテーブル名を一覧表示したときに見通しがよくなる。もちろん「C_教科科目コード」や「C_時限コード」のようにシンボリックな文字を前につけてもよい。

システムが大きくなると、このようなコードを記録する必要も大きくなる。そこでコードを一括して「基準値」テーブルにストアして管理する方法がよい。そのためにコードはすべて二桁の文字char(2)か、または日付smalldatetimeと統一し、次のようなテーブルを作る。

——————————————————————————-

テーブル名:基準値
フィールド:
基準値管理番号 int IDENTITY(1,1) NOT NULL
      基準分類 varchar(50)
      基準内容 varchar(50)
      基準値 char(2)
      基準日 smalldatetime
      表示順 int

——————————————————————————-

このテーブルで、「基準分類」によってコードの分類をし、コードの内容は「基準内容」に記述する。たとえば次のようにデータをストアする。

——————————————————————————-

基準分類    基準内容    基準値    基準日    表示順
性別          男              01          NULL      1
性別          女              02          NULL      2
異動         入学           01          NULL      1
異動         休学           02          NULL      2
異動         復学           03          NULL      3
異動         留学           04          NULL      4
異動         転学           05          NULL      5
異動         退学           06          NULL      6
異動         卒業           07          NULL      7
教科         国語           01          NULL      1
教科         地歴           02          NULL      2
教科         数学           03          NULL      3
教科         理科           04          NULL      4
教科         保健体育    05          NULL      5
教科         芸術           06          NULL     6
教科         外国語        07          NULL     7
教科         家庭           08          NULL     8
教科         情報           09          NULL     9
教科         商業           10          NULL    10
日程    前期開始日  NULL  2011/04/01   1
日程    前期終了日  NULL  2011/09/30   2
日程    後期開始日  NULL  2011/10/01   3
日程    後期終了日  NULL  2012/03/31   4

——————————————————————————-

この「基準値」テーブルを使って必要なコードを取得する。たとえば「性別」のコードを使うときは、

select 基準内容,基準値 from 基準値 where 基準分類=’性別’ order by 表示順

といったクエリを使う。これを「v_性別コード」のようなビューにしておくのもよいだろう。

「日程」データを取得するには、

select 基準内容,基準日 from 基準値 where 基準分類 = ‘日程’ order by 表示順

とする。

このように、コードを一元管理することで「あのコードを格納したのはなんというテーブルだったっけ」というように探し出す必要がなくなるのだ。

Written by Yoshio Matsumoto

2011年11月10日 at 10:55 PM

SQL Server – ストアドプロシージャの雛形を作成した

leave a comment »

SQL Serverでストアドプロシージャを作成する時間が多くなった。管理のための作業をストアドプロシージャにする作業が多くなったからだ。そこで新しいストアドプロシージャを作るときに、雛形となるものを作ってみた。

このストアドプロシージャでは、引数として「対象」と「年度」を設定している。もちろん引数の意味を変更することや増やすことも可能だ。また処理の履歴を「処理記録」テーブルにストアすることにしている。そのとき、システムから「年度コード」、「日付」、「処理者」を取得し、パラメーターとして一緒にストアしている。これにより処理の履歴を追跡しやすくしている。参考になれば幸いである。

———————————————————————————————————————————-

create procedure ストアドプロシージャ雛形

    @P_対象 as varchar(50),
    @P_年度 as char(2)

as

/*
使い方

<実行例> execute ストアドプロシージャ雛形 ‘男性’,’10’

*/

–処理定数の取得
    declare @今年度 as char(2)
    declare @日付時間 as smalldatetime
    declare @処理者 as varchar(50)

set @今年度 = (select nendo_cd from const_nendo where now_cd = 1)
set @日付時間 = getdate()
set @処理者 = substring(suser_sname(),6,6)

–実際の処理
if @P_対象 = ‘男性’
begin

insert into 処理記録(日付,職員番号,備考)
values (@日付時間,@処理者,’「ストアドプロシージャ雛形」を実行した。引数は、’ + ‘引数1(対象):’ + @P_対象
        + ‘ 引数2(年度):’ + @P_年度 + ‘ 今年度 :’ + @今年度)

end

if @P_対象 = ‘女性’
begin

insert into 処理記録(日付,職員番号,備考)
values (@日付時間,@処理者,’「ストアドプロシージャ雛形」を実行した。引数は、’ + ‘引数1(対象):’ + @P_対象
        + ‘ 引数2(年度):’ + @P_年度 + ‘ 今年度 :’ + @今年度)

end

———————————————————————————————————————————-

Written by Yoshio Matsumoto

2011年7月5日 at 12:54 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