私は次のデータセットを持っています:
これは、各施設のタスクのサイクルを指定し、「cycleperiod」列はサイクルの開始日と終了日を指定します。
タスクは、開始日と完了ステータスを指定する個々の列で指定されます(date〜status)、
ステータスの「0」は未完了を意味し、「1」は完了を意味します。
次に、施設ごとに次のように行をフィルタリングします。
現在のサイクル
少なくとも1つのタスクが不完全な前の1つのサイクル
次のサイクルが現在の日付の3日以内に開始する場合は、次の1サイクル
これまでのところ、各施設の現在のサイクルを取得するためにこれを実行しましたが、のロジックを理解できません
select * from ##finalTempTable1
where Cast(LTRIM(RTRIM(SUBSTRING(CyclePeriod, 0, 11))) as date) >= cast(getdate() as date)
and cast(LTRIM(RTRIM(SUBSTRING(CyclePeriod, CHARINDEX(''o'',CyclePeriod,0) + 2, 11))) as date) <= cast(getdate() as date);
この点での助けは大歓迎です。
私はあなたが何をしようとしているのか100%確信していませんが、私があなたを正しく理解しているなら、ここにスターターがあります:
select
cycleperiod
from
##finalTempTable1
where
Cast(LTRIM(RTRIM(SUBSTRING(CyclePeriod, 0, 11))) as date) >= cast(getdate() as date)
and cast(LTRIM(RTRIM(SUBSTRING(CyclePeriod, CHARINDEX(''o'',CyclePeriod,0) + 2, 11))) as date) <= cast(getdate() as date)
and right(cycleperiod) = '0'
UNION
select
max(b.cycleperiod)
from
##finalTempTable1 as a
inner join (
select
b.CyclePeriod
from
##finalTempTable1 as b
WHERE
and right(cycleperiod) = '0'
)as b
ON Cast(LTRIM(RTRIM(SUBSTRING(b.CyclePeriod, 0, 11))) as date) < Cast(LTRIM(RTRIM(SUBSTRING(b.CyclePeriod, 0, 11))) as date)
where
Cast(LTRIM(RTRIM(SUBSTRING(CyclePeriod, 0, 11))) as date) >= cast(getdate() as date)
and cast(LTRIM(RTRIM(SUBSTRING(CyclePeriod, CHARINDEX(''o'',CyclePeriod,0) + 2, 11))) as date) <= cast(getdate() as date)
UNION
select
min(b.cycleperiod)
from
##finalTempTable1 as a
inner join (
select
b.CyclePeriod
from
##finalTempTable1 as b
WHERE
and right(cycleperiod) = '0'
)as b
ON Cast(LTRIM(RTRIM(SUBSTRING(b.CyclePeriod, 0, 11))) as date) > Cast(LTRIM(RTRIM(SUBSTRING(b.CyclePeriod, 0, 11))) as date)
where
Cast(LTRIM(RTRIM(SUBSTRING(CyclePeriod, 0, 11))) as date) >= cast(getdate() as date)
and cast(LTRIM(RTRIM(SUBSTRING(CyclePeriod, CHARINDEX(''o'',CyclePeriod,0) + 2, 11))) as date) <= cast(getdate() as date)
and Cast(LTRIM(RTRIM(SUBSTRING(b.CyclePeriod, 0, 11))) as date) <= <= dateadd(d,3,getdate() )
基本的に、必要な3つのデータセットを結合します。
次と前のサイクル期間を取得するには、テーブルをそれ自体に結合します
結果を1つだけ取得し、最大と最小を表示どおりに使用できるようにすると、重複するピリオドはなく、日付はアルファベット順の並べ替えが機能するようにフォーマットされます。
サイクル期間の列以外のものが必要な場合は、別の一時テーブルを使用するか、次のようにして、この結果をテーブルに再度結合します。
select
d.*
from
##finalTempTable1 as d
inner join(
select
cycleperiod
from
##finalTempTable1
where
Cast(LTRIM(RTRIM(SUBSTRING(CyclePeriod, 0, 11))) as date) >= cast(getdate() as date)
and cast(LTRIM(RTRIM(SUBSTRING(CyclePeriod, CHARINDEX(''o'',CyclePeriod,0) + 2, 11))) as date) <= cast(getdate() as date)
and right(cycleperiod) = '0'
UNION
select
max(b.cycleperiod)
from
##finalTempTable1 as a
inner join (
select
b.CyclePeriod
from
##finalTempTable1 as b
WHERE
and right(cycleperiod) = '0'
)as b
ON Cast(LTRIM(RTRIM(SUBSTRING(b.CyclePeriod, 0, 11))) as date) < Cast(LTRIM(RTRIM(SUBSTRING(b.CyclePeriod, 0, 11))) as date)
where
Cast(LTRIM(RTRIM(SUBSTRING(CyclePeriod, 0, 11))) as date) >= cast(getdate() as date)
and cast(LTRIM(RTRIM(SUBSTRING(CyclePeriod, CHARINDEX(''o'',CyclePeriod,0) + 2, 11))) as date) <= cast(getdate() as date)
UNION
select
min(b.cycleperiod)
from
##finalTempTable1 as a
inner join (
select
b.CyclePeriod
from
##finalTempTable1 as b
WHERE
and right(cycleperiod) = '0'
)as b
ON Cast(LTRIM(RTRIM(SUBSTRING(b.CyclePeriod, 0, 11))) as date) > Cast(LTRIM(RTRIM(SUBSTRING(b.CyclePeriod, 0, 11))) as date)
where
Cast(LTRIM(RTRIM(SUBSTRING(CyclePeriod, 0, 11))) as date) >= cast(getdate() as date)
and cast(LTRIM(RTRIM(SUBSTRING(CyclePeriod, CHARINDEX(''o'',CyclePeriod,0) + 2, 11))) as date) <= cast(getdate() as date)
and Cast(LTRIM(RTRIM(SUBSTRING(b.CyclePeriod, 0, 11))) as date) <= <= dateadd(d,3,getdate() )
) as c
on d.cycleperiod = c.CyclePeriod
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加