先週の月曜日から日曜日までのデータを常に返す

tsql
先週の月曜日から日曜日までのデータを常に返す

先週の月曜日から先週の日曜日まで常にデータを返すSQLステートメントを記述する方法 どんなガイダンスもいただければ幸いです。

ありがとう。

  4  1


ベストアンサー

t-clausen.dk’s answerは機能しますが、明確でない場合があります*なぜ*動作するのか(あなたにも、あなたの後に来る開発者にも)。 追加された明瞭さは簡潔さとパフォーマンスを犠牲にすることがあるため、その短い構文を使用したい場合になぜそれが機能するのかを説明したいと思います。

SELECT t.*
FROM  t CROSS JOIN
(SELECT DATEDIFF(day, 0, getdate() - DATEDIFF(day, 0, getdate()) %7) lastmonday) a
WHERE t.yourdate >= a.lastmonday - 7 and yourdate < a.lastmonday

SQL Serverが内部的に「datetime」を保存する方法

SQL Serverは、「datetime」を2つの4バイト整数として保存します。最初の4バイトは1/1/1900(または負の数の場合は1/1/1900より前)以降の日数を表し、次の4バイトは真夜中からのミリ秒数を表します。

「int」または「decimal」と「datetime」を使用する

「datetime」は2つの4バイト整数として格納されるため、T-SQLで数値データ型と日付データ型の間を簡単に移動できます。 たとえば、「SELECT GETDATE()+ 1」は「SELECT DATEADD(day、1、GETDATE())」と同じを返し、「CAST(40777.44281 AS datetime)」は「2011-08-24 10」と同じです。 37:38.783`。

1/1/1900

前述のように、datetimeの最初の整数部分は1/1/1900(SQL Server Epochとも呼ばれます)以降の日数なので、 CAST(0 as datetime)`は定義により `1900-01と同等です-01 00:00:00

DATEDIFF(日、0、GETDATE())

ここから、物事がトリッキーで楽しいものになり始めます。 最初に、 0`が日付として扱われる場合、1/1/1900と同じであるため、 DATEDIFF(day、 ‘1/1/1900’、GETDATE()) `は同じであることをすでに確立しています。 `DATEDIFF(day、0、GETDATE())`として—両方とも1/1/1900以降の現在の日数を返します。 しかし、ちょっと待ってください。現在の日数は、datetimeの最初の4バイトで表されるものです。 1つのステートメントで、2つのことを行いました。1) `GETDATE()`によって返される「時間」部分を削除し、最新の日曜日を見つけるための計算に使用できる整数値を取得しました。前の月曜日が少し楽になりました。

月曜日のモジュロ7

DATEDIFF(day、0、GETDATE())%7`は、 DATEPART(day、0) (ポイントを過度に強調するリスクがあるが、 DATEPART(day、 ‘1 / 1/1900 ‘) )は 2`(月曜日)を返します。^ * 1 * ^したがって、 `DATEDIFF(day、0、GETDATE())%7`は現在の日付の月曜日からの日数を常に生成します。

^ 1 ^ `DATEFIRST`を使用してデフォルトの動作を変更していない限り。

最新の月曜日

それ自体の見出しにはあまりにも簡単ですが、この時点で、これまでに得たすべてをまとめることができます。

  • `GETDATE()-DATEDIFF(day、0、GETDATE())%7`は最新のものを提供します
    月曜

  • `DATEDIFF(day、0、GETDATE()-DATEDIFF(day、0、GETDATE())%7)`は
    日付のない最新の月曜日で、時間部分はありません。

しかし、ポスターは月曜日から日曜日までのすべてを望んでいた…​

BETWEEN演算子(包括的)を使用する代わりに、投稿された回答は最終日を除外したため、正しい日付範囲を取得するためにシフトや計算を行う必要はありませんでした。

  • `t.yourdate> = a.lastmonday-7`は、(または
    含む)2番目に新しい月曜日の真夜中

  • `t.yourdate <a.lastmonday`は日付が前のレコードを返します(ただし、
    最新の月曜日の深夜。

私は、あなたと、1年後にあなたの後に来る開発者(あなたかもしれません)の両方にとって、理解しやすいコードを書くことを強く支持しています。 以前に投稿した回答は、初心者のT-SQLプログラマーでも理解できるはずです。 しかし、t-clausen.dkの答えは簡潔で、うまく機能し、実動コードで遭遇する可能性があるので、将来の訪問者がそれがなぜ機能するかを理解するのに役立つ説明をしたかったのです。

13


コードが複雑すぎることに気付いたので、これに対する答えを変えました。 getdate()の後のマイナス1は、今週の月曜日(今日が日曜日の場合は明日)ではなく、日曜日の最後の月曜日を返すように修正します。

SELECT t.*
FROM  t CROSS JOIN
(SELECT DATEDIFF(week, 0, getdate() - 1) * 7 lastmonday) a
WHERE t.yourdate >= a.lastmonday - 7 and yourdate < a.lastmonday

旧コード

SELECT t.*
FROM  t CROSS JOIN
(SELECT DATEDIFF(day, 0, getdate() - DATEDIFF(day, 0, getdate()) %7) lastmonday) a
WHERE t.yourdate >= a.lastmonday - 7 and yourdate < a.lastmonday

4


どのSQL方言を指定しなかったので、私はT-SQLに答えます。これは私が最もよく知っていることであり、 `tsql`タグを使用しました。

T-SQLでは、http://msdn.microsoft.com/en-us/library/ms174420.aspx [DATEPART]関数を使用して曜日を見つけます。 現在の曜日がわかれば、最新の日曜日とその前の月曜日の日付を取得できます。

ストアドプロシージャでは、直近の日曜日と前の月曜日の値を計算し、変数に値を格納する方が簡単です(少なくとも、より読みやすく、保守しやすいと思います)。 その後、これらの変数は、手順の後半の計算で使用できます。

CREATE PROCEDURE SomeProcedure
AS
  DECLARE @CurrentDayOfWeek int, @LastSunday date, @LastMonday date
  SET @CurrentWeekday = DATEPART(weekday, GETDATE())
  -- Count backwards from today to get to the most recent Sunday.
  -- (@CurrentWeekday % 7) - 1 will give the number of days since Sunday;
  -- -1 negates for subtraction.
  SET @LastSunday = DATEADD(day,  -1 * (( @CurrentWeekday % 7) - 1), GETDATE())
  -- Preceding Monday is obviously six days before last Sunday.
  SET @LastMonday = DATEADD(day, -6, @LastSunday)

  SELECT ReportColumn1, ReportColumn2
  FROM ReportTable
  WHERE DateColumn BETWEEN @LastMonday AND @LastSunday

SELECTステートメントまたはビューで計算を実行できるようにする必要がある場合は、クエリ自体は少し面倒ですが、手順を実行したので簡単になりました。

  SELECT ReportColumn1, ReportColumn2
  FROM ReportTable
  WHERE DateColumn
  BETWEEN
  (
      -- Last Monday is six days before...
      DATEADD(day, -6,
         -- ... last Sunday.
         DATEADD(day,  -1 * (( DATEPART(weekday, GETDATE()) % 7) - 1), GETDATE())
      )
  )
  AND
  (
      -- Last Sunday has to be calculated again each time it is used inline.
      DATEADD(day,  -1 * (( DATEPART(weekday, GETDATE()) % 7) - 1), GETDATE())
  )

追加したかっこは必要ありませんが、WHERE句がどのように構築されているかを確認するのに役立ちます。

最後に、これらはSQL 2008の `date`データ型を使用していることに注意してください。 「datetime」データ型の場合、日付と時刻の値の代わりに日付全体を比較するために、独自の変換/切り捨てを実行する必要がある場合があります。

3


@ t-clausen.dkの回答の短いコードは日曜日には機能せず、Googleで最初に見つかった結果も機能しません。 それらをテストするには、試してください。

DECLARE @date as DATETIME;
SET @date = '2014-11-23 10:00:00'; -- Sunday at 10a

SELECT DATEADD(wk, DATEDIFF(wk,0,@date), 0) -- 2014-11-24 00:00:00
SELECT CAST(DATEDIFF(week, 0, @date)*7 as datetime) -- 2014-11-24 00:00:00
SELECT CAST(DATEDIFF(day, 0, CAST(@date as DATETIME) - DATEDIFF(day, 0,@date) %7) as DATETIME) --2014-11-17 00:00:00
SELECT DATEADD(wk, DATEDIFF(wk,0,@date-1), 0) --2014-11-17 00:00:00

したがって、元の回答の長いコードまたは修正された短いコードを使用する必要があります。

SELECT t.*
FROM  t CROSS JOIN
(SELECT DATEDIFF(week, 0, getdate() - 1) * 7 lastmonday) a
WHERE t.yourdate >= a.lastmonday - 7 and yourdate < a.lastmonday

1


タイトルとURLをコピーしました