クエリのエラー

Question

お世話になります．
http://social.technet.microsoft.com/Forums/ja-JP/sqlserverja/thread/350a1569-91e4-499a-ac0e-1266cafc4c9b
このスレッドの舌の根も乾かないうちに質問します．．．

下記の３つのテーブルがあります．これらのテーブルから加重平均を求めたいのですが，集約関数のネストとON句の集計の2点で引っ掛かっています．どうしたらよいでしょうか？よろしくお願いします．

CREATE TABLE [dbo].[T_PU_Detail](
 [Pt_ID] [int] NOT NULL,
 [PU_Location_Code] [bigint] NOT NULL,
 [PU_StartDate] [datetime] NOT NULL,
 [PU_AssessmentDate] [datetime] NOT NULL,
 [PU_NextAssessmentDate] [datetime] NOT NULL,
 [guideline_version] [nvarchar](10) NOT NULL,
 [Depth_Status] [nvarchar](2) NOT NULL,
 [Exudate_Status] [nvarchar](3) NOT NULL,
 [Size_MajorAxis] [decimal](5, 2) NOT NULL,
 [Size_MaxDiameter] [decimal](5, 2) NOT NULL,
 [Inflammation_Infection_Status] [nvarchar](3) NOT NULL,
 [Granuloma_Status] [nvarchar](3) NOT NULL,
 [Necrosis_Status] [nvarchar](3) NOT NULL,
 [Pocket_MajorAxis] [decimal](5, 2) NOT NULL,
 [Pocket_MaxDiameter] [decimal](5, 2) NOT NULL,
 [PU_picture] [varbinary](max) NULL,
 [PU_picture_path] [nvarchar](max) NULL,
 CONSTRAINT [PK_T_PU_Detail] PRIMARY KEY CLUSTERED
(
 [Pt_ID] ASC,
 [PU_StartDate] ASC,
 [PU_Location_Code] ASC,
 [PU_AssessmentDate] ASC

CREATE TABLE [dbo].[T_PU_Care_Detail](
 [Pt_ID] [int] NOT NULL,
 [PU_Location_Code] [bigint] NOT NULL,
 [PU_StartDate] [datetime] NOT NULL,
 [PU_Care_Date] [datetime] NOT NULL,
 [PU_Care_Detail_Number] [int] NOT NULL,
 [Stuff_ID] [int] NOT NULL,
 CONSTRAINT [PK_T_PU_Care_Detail] PRIMARY KEY CLUSTERED
(
 [Pt_ID] ASC,
 [PU_Location_Code] ASC,
 [PU_StartDate] ASC,
 [PU_Care_Date] ASC,
 [PU_Care_Detail_Number] ASC

CREATE TABLE [dbo].[T_DESIGNR_Exudate](
 [DESIGNR_Exudate_Status] [nvarchar](3) NOT NULL,
 [DESIGNR_Exudate_Description] [nvarchar](50) NOT NULL,
 [LowerFrequency] [float] NOT NULL,
 [UpperFrequency] [float] NOT NULL,
 [Exudate_Score] [int] NOT NULL,
 [guideline_version] [nvarchar](10) NOT NULL,
 [DESIGN_Exudate_Status] [nchar](2) NOT NULL,
 CONSTRAINT [PK_T_DESIGNR_Exudate] PRIMARY KEY CLUSTERED
(
 [DESIGNR_Exudate_Status] ASC

  SELECT E2.DESIGNR_Exudate_Status
    FROM T_PU_Detail AS PUD1
 INNER JOIN T_PU_Detail AS PUD2
   ON PUD1.Pt_ID = PUD2.Pt_ID
  AND PUD1.PU_Location_Code = PUD2.PU_Location_Code
  AND PUD1.PU_StartDate = PUD2.PU_StartDate
  AND PUD2.PU_AssessmentDate = (SELECT MAX(PU_AssessmentDate)
          FROM T_PU_Detail AS PUD3
            WHERE (PUD1.PU_AssessmentDate > PU_AssessmentDate)) --greatest lower bound
 INNER JOIN T_PU_Care_Detail AS PUCD1
   ON PUCD1.Pt_ID = PUD1.Pt_ID
  AND PUCD1.PU_Location_Code = PUD1.PU_Location_Code
  AND PUCD1.PU_StartDate  = PUD1.PU_StartDate
  AND PUCD1.PU_Care_Date <= PUD1.PU_AssessmentDate
  AND PUCD1.PU_Care_Date >  PUD2.PU_AssessmentDate
 INNER JOIN T_DESIGNR_Exudate AS E2
   ON E2.LowerFrequency <=((SUM(COUNT(*) * DATEDIFF(day, PUD2.PU_AssessmentDate, PUCD1.PU_Care_date))) * 2)
        / ((DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate))
         * (DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate) + 1))
  AND E2.UpperFrequency > ((SUM(COUNT(*) * DATEDIFF(day, PUD2.PU_AssessmentDate, PUCD1.PU_Care_date))) * 2)
        / ((DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate))
         * (DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate) + 1));
--集計やサブクエリを含む式に対して集計関数を実行することはできません。
--HAVING 句または選択リストに含まれるサブクエリの場合、および集計される列が外部参照の場合にだけ、ON 句に集計を含めることができます。

Answer 1

分かりにくいので追記です．例を示します．

PUD1.PU_AssessmentDate
2009/8/8
PUD2.PU_AssessmentDate
2009/8/1

PUCD1.PU_Care_Date    PU_Care_Detail_Number
2009/8/7                       1
2009/8/6                       1
2009/8/6                       2
2009/8/4                       1
2009/8/4                       2
2009/8/4                       3
2009/8/4                       4

求めたい式は以下です．PU_Care_Detail_NumberにMAX関数をかけるのもありかと思いますが，PUCD1.PU_Care_Dateごとのレコード数を求めた方がよいと思っています．
(4 * ('2009/8/4' - '2009/8/1') + 2 * ('2009/8/6' - '2009/8/1') + 1 * ('2009/8/7' - '2009/8/1')) / (('2009/8/8' - '2009/8/1') * ('2009/8/8' - '2009/8/1' + 1) / 2)

Answer 2

(4 * ('2009/8/4' - '2009/8/1') + 2 * ('2009/8/6' - '2009/8/1') + 1 * ('2009/8/7' - '2009/8/1')) / (('2009/8/8' - '2009/8/1') * ('2009/8/8' - '2009/8/1' + 1) / 2)

検証していませんが、T_DESIGNR_Exudate を JOIN するのがクエリを複雑化し
エラーの遠因となっているようにおもえます。
上記式の乗算の「4」「2」「1」のところをサブクエリにしてみてはいかがでしょう。

Answer 3

お世話になります．

> 上記式の乗算の「4」「2」「1」のところをサブクエリにしてみてはいかがでしょう

今そのあたりを書いてみています．問題は結果が集合で返ることです．これを集約したいのですが，何とも．．．

--(1)On first day of admission with pressure ulcers with unknown progress, a count of care records on the admission day is 'Exudate'.
--(2)Weighted count of care records is product of count care records and coefficient which is calculated 'DATEDIFF' function.

     SELECT    CASE WHEN NOT EXISTS (SELECT *                                                            --(1)
                                       FROM T_PU_Detail AS PUD1
                                 INNER JOIN T_PU_Detail AS PUD2
                                         ON PUD1.Pt_ID = PUD2.Pt_ID
                                        AND PUD1.PU_Location_Code = PUD2.PU_Location_Code
                                        AND PUD1.PU_StartDate = PUD2.PU_StartDate
                                        AND PUD1.PU_AssessmentDate > PUD2.PU_AssessmentDate)
            THEN    (SELECT COUNT(*)
                       FROM T_PU_Detail AS PUD1
                 INNER JOIN T_PU_Care_Detail AS PUCD1
                         ON PUD1.Pt_ID = PUCD1.Pt_ID
                        AND PUD1.PU_Location_Code = PUCD1.PU_Location_Code
                        AND PUD1.PU_StartDate = PUCD1.PU_StartDate
                        AND PUD1.PU_AssessmentDate = PUCD1.PU_Care_Date   
                   GROUP BY PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate)
            ELSE    (SELECT COUNT(*) * (DATEDIFF(day, PUD2.PU_AssessmentDate, PUCD1.PU_Care_Date) + 1)    --(2)
                        FROM T_PU_Detail AS PUD1
                 INNER JOIN T_PU_Detail AS PUD2
                         ON PUD1.Pt_ID = PUD2.Pt_ID
                        AND PUD1.PU_Location_Code = PUD2.PU_Location_Code
                        AND PUD1.PU_StartDate = PUD2.PU_StartDate
                        AND PUD2.PU_AssessmentDate = (SELECT MAX(PU_AssessmentDate)
                                                        FROM T_PU_Detail AS PUD3
                                                       WHERE PUD1.PU_AssessmentDate > PUD3.PU_AssessmentDate)
                 INNER JOIN T_PU_Care_Detail AS PUCD1
                         ON PUCD1.Pt_ID = PUD1.Pt_ID
                        AND PUCD1.PU_Location_Code = PUD1.PU_Location_Code
                        AND PUCD1.PU_StartDate  = PUD1.PU_StartDate
                        AND PUCD1.PU_Care_Date BETWEEN PUD1.PU_AssessmentDate AND PUD2.PU_AssessmentDate
                   GROUP BY PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate, PUCD1.PU_Care_Date, PUD2.PU_AssessmentDate)
            END AS WeightedExudateCount;

Answer 4

お世話になります．COUNT(*)を求めるクエリを書いてみました．この後どう展開すべきか？とりあえず中間報告です．これにDATEDIFF(day, PUCD1.PU_Care_Date, PUD2.PU_AssessmentDate)をかけてみたところ，マルチパートPUD2.PU_AssessmentDateをバインドできない旨のアラートが出てクエリが失敗します．どうもうまく行きません．．．

  SELECT COUNT(*)
 FROM T_PU_Care_Detail AS PUCD1
   WHERE PU_Care_Date BETWEEN (SELECT MAX(PU_AssessmentDate)
         FROM T_PU_Detail AS PUD2
        WHERE PUCD1.Pt_ID = PUD2.Pt_ID
          AND PUCD1.PU_Location_Code = PUD2.PU_Location_Code
          AND PUCD1.PU_StartDate = PUD2.PU_StartDate
          AND PUCD1.PU_Care_Date > PUD2.PU_AssessmentDate)
        AND (SELECT PU_AssessmentDate
         FROM T_PU_Detail AS PUD1
           WHERE PUCD1.Pt_ID = PUD1.Pt_ID
          AND PUCD1.PU_Location_Code = PUD1.PU_Location_Code
          AND PUCD1.PU_StartDate = PUD1.PU_StartDate)
GROUP BY PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate, PUCD1.PU_Care_Date ;

Answer 5

お世話になります．DATEDIFF関数にすぐ上で求めたクエリを掛け算したものですが．．．これ，結局は直積を作ってしまうんじゃないかという気がします．
結合で求めようと，サブクエリで求めようと得られる結果は同じではないでしょうか．

結局，COUNT関数にSUM関数をかぶせる処理を何とかしないと解決しないわけで．．．よろしくお願いします．

  SELECT (DATEDIFF(day, PUD1.PU_AssessmentDate, PUD2.PU_AssessmentDate) + 1)
  * (SELECT COUNT(*)
      FROM T_PU_Care_Detail AS PUCD1
     WHERE PU_Care_Date BETWEEN (SELECT MAX(PU_AssessmentDate)
           FROM T_PU_Detail AS PUD2
             WHERE PUCD1.Pt_ID = PUD2.Pt_ID
            AND PUCD1.PU_Location_Code = PUD2.PU_Location_Code
            AND PUCD1.PU_StartDate = PUD2.PU_StartDate
            AND PUCD1.PU_Care_Date > PUD2.PU_AssessmentDate)
          AND (SELECT PU_AssessmentDate
           FROM T_PU_Detail AS PUD1
             WHERE PUCD1.Pt_ID = PUD1.Pt_ID
            AND PUCD1.PU_Location_Code = PUD1.PU_Location_Code
            AND PUCD1.PU_StartDate = PUD1.PU_StartDate)
   GROUP BY PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate, PUCD1.PU_Care_Date)
    FROM T_PU_Detail AS PUD1
 INNER JOIN T_PU_Detail AS PUD2
   ON PUD1.Pt_ID = PUD2.Pt_ID
  AND PUD1.PU_Location_Code = PUD2.PU_Location_Code
  AND PUD1.PU_StartDate = PUD2.PU_StartDate
  AND PUD2.PU_AssessmentDate = (SELECT MAX(PU_AssessmentDate)
          FROM T_PU_Detail AS PUD3
            WHERE PUD1.PU_AssessmentDate > PUD3.PU_AssessmentDate) ;

Answer 6

仕様がよく理解できていないので
なんとなくこんな書き方もありますねというご参考程度に。。→肝心の select 文は下のほうにあります。

（テーブル名、カラム名などだいぶ変形しています。。）

IF OBJECT_ID(N'[dbo].[A]', N'U') IS NOT NULL
 DROP TABLE [dbo].[A];
IF OBJECT_ID(N'[dbo].[B]', N'U') IS NOT NULL
 DROP TABLE [dbo].[B];
/*DROP TABLE [dbo].[C];*/

CREATE TABLE [dbo].[A](
 [ID] [int] NOT NULL,
 [Date] [datetime] NOT NULL,
 [Status] [nvarchar](3) NOT NULL,
 CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
 [ID] ASC,
 [Date] ASC))

CREATE TABLE [dbo].[B](
 [ID] [int] NOT NULL,
 [CDate] [datetime] NOT NULL,
 [Code] [nvarchar](3) NOT NULL,
 CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED
(
 [ID] ASC,
 [CDate] ASC,
 [Code] ASC))

/*CREATE TABLE [dbo].[C](
 [EStatus] [nvarchar](3) NOT NULL,
 [Lower] [float] NOT NULL,
 [Upper] [float] NOT NULL,
 CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED
(
 [EStatus] ASC))*/

insert A values (1, '2009/8/1', 'b')
insert A values (1, '2009/8/2', 'c')
insert A values (1, '2009/8/3', 'd')
insert A values (1, '2009/8/4', 'e')
insert A values (1, '2009/8/5', 'f')
insert A values (1, '2009/8/6', 'g')
insert A values (1, '2009/8/7', 'h')
insert A values (1, '2009/8/8', 'i')
insert B values (1, '2009/8/4', '1')
insert B values (1, '2009/8/4', '2')
insert B values (1, '2009/8/4', '3')
insert B values (1, '2009/8/4', '4')
insert B values (1, '2009/8/6', '5')
insert B values (1, '2009/8/6', '6')
insert B values (1, '2009/8/7', '7')

select CNT, MinDate, B.CDate, sum( CNT * datediff(day, MinDate, B.CDate))
from B
INNER JOIN (SELECT min(Date) MinDate FROM A) AS AA ON 1 = 1
INNER JOIN (SELECT ID, CDate, count(*) CNT FROM B group by ID, CDate) AS BB ON BB.ID = B.ID and BB.CDate = B.CDate
group by B.CDate, CNT, MinDate

Answer 7

お世話になります．

非常に示唆に富んだ回答で，ショックを受けています．
(1)結合の相手がテーブルの操作の結果のテーブルであること
(2)集約関数の結果の相関名を引数としてさらに集約できること

一つ分からないのですが，
ON 1 = 1
とは何と何を比較しているのでしょうか？
B.ID = AA.ID
B.CDate = AA.Date
など書き換えてみましたがいずれも列名が無効で失敗します．

Answer 8

ON 1 = 1

↑見たまんま、定数1 と定数1 の比較です。

結果が常に true になる式です。

とりあえず全件つなげようとしてこんな書き方になってしまいました。

列名が無効な件は、、、

たとえば AA.ID が無効なのは

テーブルAA にカラムID が無いためです。

つまり、

INNER JOIN (SELECT min(Date) MinDate FROM A) AS AA ON 1 = 1

↓

INNER JOIN (SELECT ID, min(Date) MinDate FROM A) AS AA ON B.ID = AA.ID

とすれば有効です。

ただし、

列 'A.ID' は選択リスト内では無効です。この列は集計関数または GROUP BY 句に含まれていません。

というエラーが出てしまいます。

IDが一意に決まらないためです。

そこでさらに、

INNER JOIN (SELECT ID, min(Date) MinDate FROM A group by ID) AS AA ON B.ID = AA.ID

とすれば大丈夫です。

Answer 9

お世話になります．よく見返してみると，戻り値が期待していたものと若干異なります．

     SELECT MinDate, 
            B.CDate, 
            CNT, 
            '*', 
            DATEDIFF(day, MinDate, B.CDate), 
            '=', 
            CNT * DATEDIFF(day, MinDate, B.CDate), 
            SUM(CNT * DATEDIFF(day, MinDate, B.CDate))
     FROM B
 INNER JOIN (SELECT ID, MIN(Date) AS MinDate 
       FROM A 
   GROUP BY ID) AS AA 
         ON B.ID = AA.ID
 INNER JOIN (SELECT ID, CDate, COUNT(*) AS CNT 
       FROM B 
   GROUP BY ID, CDate) AS BB 
         ON BB.ID = B.ID 
        AND BB.CDate = B.CDate
   GROUP BY MinDate, B.CDate, CNT;

としてみると

2009-08-01 2009-08-04 4 * 3 = 12 48 (=4*4*3?)
2009-08-01 2009-08-06 2 * 5 = 10 20 (=2*2*5?)
2009-08-01 2009-08-07 1 * 6 = 6 6 (=1*1*6?)

が返ります．期待しているのは

2009-08-01 2009-08-04 4 * 3 = 12 12
2009-08-01 2009-08-06 2 * 5 = 10 22 (=12 + 10)
2009-08-01 2009-08-07 1 * 6 = 6 28 (=12 + 10 + 6)

(といった同心円的再帰集合なのです．どうすればよいでしょうか？)

 

追記：．．．えと，少し訂正．

 SUM(CNT * DATEDIFF(day, MinDate, B.CDate))

この場合，求めるのは12+10+6=28だけですね．失礼しました．

Answer 10

ひとつの SQL で作るとややこしくなりそうでしょうか。

 ・ビューにしておく。

 ・一時テーブルを使用する。

といった方法はいかがでしょう。

たとえば、いったん

12

10

6

を求めたところまでで一時テーブルにしておいて

さらに、select文で最終的に求めたい結果を得ます。

ほかに、再帰クエリを使ってもできるかもしれません。

Answer 11

お世話になります．
一時テーブルですか．．．初めて聞きます．成書を当たってみましたが，なかなかよさげですね．

例に示していただいたコードでは，日付の範囲を求める部分でグループ化するためのキーが不足しているために，それ以上の集約ができないのではないかと思います．

再帰クエリは，今読んでいる書籍には詳細な説明がなくてまだ分かりません．

当初のクエリを書きなおしてみたところです．

    SELECT PUCD1.Pt_ID, 
           PUCD1.PU_Location_Code, 
           PUCD1.PU_StartDate, 
           PUD2.PU_AssessmentDate, 
           PUCD1.PU_Care_Date, 
           PUD1.PU_AssessmentDate, 
           CNT, 
           CNT * (DATEDIFF(day, PUD2.PU_AssessmentDate, PUCD1.PU_Care_Date) + 1) * 2 
               /((DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate) + 1) 
               * (DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate) + 2)) AS WCNT
      FROM T_PU_Detail AS PUD1 
INNER JOIN T_PU_Detail AS PUD2 
        ON PUD1.Pt_ID = PUD2.Pt_ID 
       AND PUD1.PU_Location_Code = PUD2.PU_Location_Code 
       AND PUD1.PU_StartDate = PUD2.PU_StartDate 
       AND PUD2.PU_AssessmentDate = (SELECT MAX(PU_AssessmentDate) 
                                       FROM T_PU_Detail AS PUD3 
                                      WHERE PUD1.PU_AssessmentDate > PUD3.PU_AssessmentDate)
INNER JOIN (SELECT Pt_ID, PU_Location_Code, PU_StartDate, PU_Care_Date, COUNT(*) AS CNT
              FROM T_PU_Care_Detail
          GROUP BY Pt_ID, PU_Location_Code, PU_StartDate, PU_Care_Date) AS PUCD1
        ON PUCD1.Pt_ID = PUD1.Pt_ID 
       AND PUCD1.PU_Location_Code = PUD1.PU_Location_Code 
       AND PUCD1.PU_StartDate  = PUD1.PU_StartDate 
       AND PUCD1.PU_Care_Date BETWEEN PUD1.PU_AssessmentDate AND PUD2.PU_AssessmentDate 
  GROUP BY PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate, 
    PUD2.PU_AssessmentDate, PUCD1.PU_Care_Date, PUD1.PU_AssessmentDate, CNT; 

上記の場合，下記のキーで集約できる余地があるのではないでしょうか．
PUCD1.Pt_ID,
PUCD1.PU_Location_Code, 
PUCD1.PU_StartDate,
PUD2.PU_AssessmentDate,
PUD1.PU_AssessmentDate

Answer 12

お世話になります．前回，集約できそうと思ってやってみたんですが，GROUP付近の不適切な構文があるとのエラーでうまく行っていません．

　

      SELECT SUM(WCNT) AS WAVG
      SELECT PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate, 
	  PUD2.PU_AssessmentDate, PUCD1.PU_Care_Date, PUD1.PU_AssessmentDate, CNT, 
	  CNT * (DATEDIFF(day, PUD2.PU_AssessmentDate, PUCD1.PU_Care_Date) + 1) * 2 
	  /((DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate) + 1) 
	  * (DATEDIFF(day, PUD2.PU_AssessmentDate, PUD1.PU_AssessmentDate) + 2)) AS WCNT, 
        FROM T_PU_Detail AS PUD1 
  INNER JOIN T_PU_Detail AS PUD2 
          ON PUD1.Pt_ID = PUD2.Pt_ID 
         AND PUD1.PU_Location_Code = PUD2.PU_Location_Code 
         AND PUD1.PU_StartDate = PUD2.PU_StartDate 
         AND PUD2.PU_AssessmentDate = (SELECT MAX(PU_AssessmentDate) 
					 FROM T_PU_Detail AS PUD3 
					WHERE PUD1.PU_AssessmentDate > PUD3.PU_AssessmentDate)
  INNER JOIN (SELECT Pt_ID, PU_Location_Code, PU_StartDate, PU_Care_Date, COUNT(*) AS CNT
	     FROM T_PU_Care_Detail
	 GROUP BY Pt_ID, PU_Location_Code, PU_StartDate, PU_Care_Date) AS PUCD1
          ON PUCD1.Pt_ID = PUD1.Pt_ID 
         AND PUCD1.PU_Location_Code = PUD1.PU_Location_Code 
         AND PUCD1.PU_StartDate  = PUD1.PU_StartDate 
         AND PUCD1.PU_Care_Date BETWEEN PUD1.PU_AssessmentDate AND PUD2.PU_AssessmentDate 
    GROUP BY PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate, 
			 PUD2.PU_AssessmentDate, PUCD1.PU_Care_Date, PUD1.PU_AssessmentDate, CNT;
    GROUP BY PUCD1.Pt_ID, PUCD1.PU_Location_Code, PUCD1.PU_StartDate, 
			 PUD2.PU_AssessmentDate, /*PUCD1.PU_Care_Date, */PUD1.PU_AssessmentDate, CNT;

Answer 13

お世話になります．その後も二つ前のクエリ全体をテーブル式にして試行していますが，構文エラーが解決しません．
集約のためのキーは存在するのに，何故できないのかが分かりません．

     SELECT MinDate, 
            B.CDate, 
            CNT, 
            '*', 
            DATEDIFF(day, MinDate, B.CDate), 
            '=', 
            CNT * DATEDIFF(day, MinDate, B.CDate), 
            SUM(CNT * DATEDIFF(day, MinDate, B.CDate)) AS SUMCNT, 
            COUNT(CNT * DATEDIFF(day, MinDate, B.CDate)) AS CNTCNT
     FROM B
 INNER JOIN (SELECT ID, MIN(Date) AS MinDate 
               FROM A 
           GROUP BY ID) AS AA 
         ON B.ID = AA.ID
 INNER JOIN (SELECT ID, CDate, COUNT(*) AS CNT 
       FROM B 
   GROUP BY ID, CDate) AS BB 
         ON BB.ID = B.ID 
        AND BB.CDate = B.CDate
   GROUP BY MinDate, B.CDate, CNT;

の結果は
2009-08-01 2009-08-04 4 * 3 = 12 48 4
2009-08-01 2009-08-06 2 * 5 = 10 20 2
2009-08-01 2009-08-07 1 * 6 = 6 6 1

と返ります．SELECT句の最後のCOUNT関数の結果CNTCNTが1にならない理由も分かりません．よろしくお願いします．

Answer 14

お世話になります．その後試行錯誤して下記コードで求めたい値にたどり着きました．

IF OBJECT_ID(N'[dbo].[Care]') IS NOT NULL
	DROP TABLE [dbo].[Care];
IF OBJECT_ID(N'[dbo].[Detail]') IS NOT NULL
	DROP TABLE [dbo].[Detail];

CREATE TABLE [dbo].[Detail](
 [Pt_ID] [int] NOT NULL,
 [AssessmentDate] [datetime] NOT NULL,
 [Status] [nvarchar](3) NOT NULL,
 CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED 
(
 [Pt_ID] ASC,
 [AssessmentDate] ASC))

CREATE TABLE [dbo].[Care](
 [Pt_ID] [int] NOT NULL,
 [Care_Date] [datetime] NOT NULL,
 [Code] [nvarchar](3) NOT NULL,
 CONSTRAINT [PK_Care] PRIMARY KEY CLUSTERED 
(
 [Pt_ID] ASC,
 [Care_Date] ASC,
 [Code] ASC))

insert Detail values (1, '2009/7/30', 'a')
insert Detail values (1, '2009/8/1', 'b')
insert Detail values (1, '2009/8/8', 'c')
insert Care values (1, '2009/8/4', '1')
insert Care values (1, '2009/8/4', '2')
insert Care values (1, '2009/8/4', '3')
insert Care values (1, '2009/8/4', '4')
insert Care values (1, '2009/8/6', '1')
insert Care values (1, '2009/8/6', '2')
insert Care values (1, '2009/8/7', '1')

	 SELECT TMP.Pt_ID, TMP.MaxDate, TMP.MinDate, 
			SUM(TMP.WCNT) AS WSUM 
	   FROM (																
	 SELECT	D1.Pt_ID, 
			D1.AssessmentDate AS MaxDate, 
			D2.AssessmentDate AS MinDate, 
			C1.Care_Date, 
			CNT, 
			DATEDIFF(day, D2.AssessmentDate, C1.Care_Date) AS DIFF, 
			CNT * DATEDIFF(day, D2.AssessmentDate, C1.Care_Date) AS WCNT 
	   FROM	Detail AS D1
 INNER JOIN	Detail AS D2
		 ON	D1.Pt_ID = D2.Pt_ID 
		AND	D1.AssessmentDate > D2.AssessmentDate 
		AND D2.AssessmentDate = (SELECT MAX(AssessmentDate) 
								   FROM	Detail AS D3 
								  WHERE	D1.AssessmentDate > D3.AssessmentDate) 
 INNER JOIN	(SELECT Pt_ID, Care_Date, COUNT(*) AS CNT 
			   FROM Care 
		   GROUP BY Pt_ID, Care_date) AS C1 
		 ON	C1.Pt_ID = D1.Pt_ID 
		AND	C1.Care_Date BETWEEN D2.AssessmentDate AND D1.AssessmentDate 
   GROUP BY	D1.Pt_ID, D1.AssessmentDate, D2.AssessmentDate, C1.Care_Date, CNT
			) AS TMP
   GROUP BY	TMP.Pt_ID, TMP.MaxDate, TMP.MinDate;

レイアウトがぐちゃぐちゃですが．．．テーブル式によるクエリということで，いかがでしょうか．

Answer 15

FROM句をSELECT句にしたんですね。

いいアイデアなのではないでしょうか。

Answer 16

お世話になります．
間違ってはいないようで，ありがとうございました．
ちょっと感情的になったりしてすみませんでした．

ここからはご意見を伺いたいのですか．．．

> いったん

> 12

> 10

> 6

> を求めたところまでで一時テーブルにしておいて

> さらに、select文で最終的に求めたい結果を得ます。

一時テーブルではないにしても似た方針で結果は得られたかなと思います．
ただ，このコードはかなり頻繁に実行される可能性があるためパフォーマンス上の懸念があります．
推定実行プランを表示してみると，最後あたりのソートに46％コストがかかっているようです．
仮に一時テーブルを使う場合は，最もコストのかかる部分を含めておいた方がよいのでしょうか？

> ほかに、再帰クエリを使ってもできるかもしれません

こちらに関しては，ちょっと思いつきません．何かヒントだけでも．．．

Answer 17

ちょっと感情的になったりしてすみませんでした．

返信が遅くなったことを心配されたのでしたら

遅くなったのは私的都合だけですので心配なさらないでください。

（また、ほかのかたの意見も伺いたかったため、

意図的に頻繁に返信しなかったときもあります。）

ただ，このコードはかなり頻繁に実行される可能性があるためパフォーマンス上の懸念があります．
推定実行プランを表示してみると，最後あたりのソートに46％コストがかかっているようです．
仮に一時テーブルを使う場合は，最もコストのかかる部分を含めておいた方がよいのでしょうか？

それについてはデータ量、更新量などの要素もあり一概になにがいいとはいえないかと思います。

蛇足ですが、、、

実行コストがわかるのであれば、とりあえず今の状態で比較してみたり、

本番環境に近いテスト環境で実際のパフォーマンスを確かめてみることを検討されてはいかがでしょうか。

> ほかに、再帰クエリを使ってもできるかもしれません

こちらに関しては，ちょっと思いつきません．何かヒントだけでも．．．

再帰クエリを実際に使ったことがないのでヒントは無理ですが、、、

以前、じふ様が求めているようなSQLを書こうとしたときに

再帰クエリを書けたらスッキリ書けるんじゃないかなと思ったことがあります。

その頃SQLには再帰クエリの構文はなかったので諦めました。

ただ、いったん推薦しておいてなんですが、

まだ使用例が少なすぎるかもしれないので

仕事で使うのであればお勧めはしかねます。

ちなみに参考にさせていただいたのは以下のようなページです。

↓taediumの日記　SQL Server 2005で再帰クエリ

http://d.hatena.ne.jp/taedium/20060207/p1

Answer 18

お世話になります．

> 実行コストがわかるのであれば、とりあえず今の状態で比較してみたり、

> 本番環境に近いテスト環境で実際のパフォーマンスを確かめてみることを検討されてはいかがでしょうか。

そうですね．とりあえず一旦今の状態で様子を見ます．

> 以前、じふ様が求めているようなSQLを書こうとしたときに

> 再帰クエリを書けたらスッキリ書けるんじゃないかなと思ったことがあります。

手続き型に近いやり方に見えますね．せっかくSQLを使うなら，集合指向で行きたいという気もします．

ともあれ，今回はこれで解決とさせていただきます．ありがとうございました．