家の状態を報告する必要のあるシステムがあります。家は、「一次」要素に失敗した場合、または2つの二次要素を蓄積した場合に、住宅基準に失敗します。これは、Management Studioに貼り付けて実行できる例です。これには、私が達成しようとしていることを説明するコメントが付いています。
WITH T1 AS
( SELECT
1 propertyid,
'Principal roof covering' text,
0 isprimary,
CAST('1 Apr 2016' AS datetime) date,
'This should not appear because it is secondary and
there are no subsequent secondary elements for this property' comment
UNION SELECT
1,
'Chimney stacks',
0,
CAST('1 Apr 2015' AS datetime),
'This should appear unadjusted because it is secondary and there is a
preceding secondary element for the same property' UNION SELECT
1,
'Bathroom condition',
1,
CAST('1 Apr 2014' AS datetime),
'This should appear unadjusted because it is primary' UNION SELECT
2,
'Damp proof course',
0,
CAST('1 Apr 2016' AS datetime),
'This should not appear because it is secondary and there are no
subsequent secondary elements for this property'
UNION SELECT
1,
'External wall finish',
0,
CAST('1 Apr 2014' AS datetime),
'This should get it''s date adjusted to 2015 because it is secondary
and the next secondary element is in 2015' UNION SELECT
1,
'Flashings',
0,
CAST('1 Jun 2015' AS datetime),
'This should get it''s date adjusted to Sep 2015 because it is secondary
and the next secondary element is in Sep 2015' UNION SELECT
1,
'Underground drainage',
0,
CAST('1 Sep 2015' AS datetime),
'This should appear unadjusted because it is secondary and there is a
preceding secondary element for the same property'
)
SELECT
*
FROM t1
ORDER BY date
後続のセカンダリ要素がないセカンダリ要素を除外し、後続の要素がある日付を後続の要素の日付に調整するにはどうすればよいですか？
次のクエリは、必要なものを返す必要があります。
WITH T1 AS
(
SELECT *
, ROW_NUMBER() OVER (PARTITION BY propertyid, isprimary ORDER BY date) AS PropNo
, COUNT(*) OVER (PARTITION BY propertyid, isprimary) AS PropCount
FROM
-- Replace below with your source data table
(VALUES(1,'Bathroom condition',1,'2014-04-01')
,(1,'External wall finish',0,'2014-04-01')
,(1,'Chimney stacks',0,'2015-04-01')
,(1,'Principal roof covering',0,'2016-04-01')
,(2,'Damp proof course',0,'2016-04-01')) T(propertyid, text, isprimary, date)
)
SELECT
T1.propertyid
, T1.text
, T1.isprimary
, CASE
WHEN T1.isprimary = 1 OR T1.PropNo = T1.PropCount - 1 THEN T1.date
ELSE ISNULL(T1Next.date, T1.date) END AS [date]
FROM T1
LEFT JOIN T1 AS T1Next ON T1.propertyid = T1Next.propertyid
AND T1.isprimary = T1Next.isprimary
AND T1.PropNo = T1Next.PropNo - 1
WHERE T1.isprimary = 1
OR (T1.PropNo < T1.PropCount)
ROW_NUMBER（）関数とCOUNT（*）関数を使用して、後続の行があるかどうかを判別します。次の行の日付を適用するには、LEFTJOINを使用します。
編集左結合をこれに変更すると、結合は2次要素でのみ発生し、2つおきの要素でのみ発生します。
LEFT JOIN T1 AS T1Next ON T1.propertyid = T1Next.propertyid
AND T1.isprimary = 0
AND T1Next.isprimary = 0
AND T1.PropNo = T1Next.PropNo - 1
AND T1Next.PropNo % 2 = 0
つまり、caseステートメントは必要ありません。これだけです。
ISNULL(T1Next.date, T1.date) AS [date]
しかし、whereステートメントは完全に正しくありません。これは機能します：
WHERE T1.isprimary = 1
OR (T1.PropNo % 2 = 0) --every 2nd one
OR T1Next.date IS NOT NULL --and the 1st if there is a 2nd
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加