다음 이벤트 날짜를 표시합니다

이벤트 이벤트 메타 데이터를 저장하기위한 UI의 디자인은

SQL TABLE 디자인이다

CREATE TABLE [dbo].[EVENTS]
([ID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](255) NOT NULL)

과

CREATE TABLE [dbo].[EVENTS_META](
[ID] [int] IDENTITY(1,1) NOT NULL,
[event_id] [int] NOT NULL,
[meta_key] [varchar](255) NOT NULL,
[meta_value] [bigint] NOT NULL)

이벤트 데이터는

이벤트 메타 데이터는

나는 캘린더 이벤트 및 최종적인 약간의 수학을 반복 이어 나는 쿼리 아래를 썼다

주어진 종료일 전에 LIST 모든 이벤트 날짜

SELECT EV.*
FROM events AS EV
RIGHT JOIN events_meta AS EM1 ON EM1.event_id = EV.id
RIGHT JOIN events_meta AS EM2 ON EM2.meta_key = 'repeat_interval_'+ CAST(EM1.id as Varchar(100))
WHERE EM1.meta_key = 'repeat_start'
AND ((1391040000 - EM1.meta_value ) % EM2.meta_value) = 0

난 아무것도 못하고 있어요. 나는 주어진 간격으로 repeat_start 후 모든 날짜를 표시합니다.

(3 차 년 1 월 2014 10 AM) 위해 UnixTimeStamp = 1,388,743,200에 예 여기에 1 차 이벤트가 시작되고 매주 금요일 (7 일), 우리는 또한 시작의 토요일에 첫 번째 이벤트 (Jan04, 2014) 1388858400 일정을 계속하고 계속 매 7 일 (토요일 번 )

한 달에 1 회 / 일 / 등이 될 수 있습니다. 우리는 초로 정의 간격이 그래서.

전 30 일월 2014처럼 어떤 입력을 제공하는 경우, 즉 = 1,391,040,000 (30 일월 2014 0시 0분 0초)

예상 결과

BILLA 방문 2014 년 10 A.M 1월 3일

BILLA 방문 2014 년 10 A.M 1월 4일

BILLA 방문 2014 년 10 A.M 10 년 1 월

BILLA 방문 2014 년 10 A.M 1월 11일

BILLA 방문 2014 년 10 A.M 1월 17일

BILLA 방문 2014 년 10 A.M 1월 18일

BILLA 방문 2014 년 10 A.M 1월 24일

BILLA 방문 2014 년 10 A.M 1월 25일

SQL 뿐인 LINK

해결법

==============================

1.첫 번째 단계는 이벤트가 사용할 수있는이 작업을 수행하기 위해, 각 이벤트 및 반복 간격으로 날짜를 빨리 시작하는 것입니다 :

첫 번째 단계는 이벤트가 사용할 수있는이 작업을 수행하기 위해, 각 이벤트 및 반복 간격으로 날짜를 빨리 시작하는 것입니다 :

SELECT  EventID = e.ID, 
        e.Name, 
        StartDateTime = DATEADD(SECOND, rs.Meta_Value, '19700101'),
        RepeatInterval = ri.Meta_Value
FROM    dbo.Events e
        INNER JOIN dbo.Events_Meta rs
            ON rs.Event_ID = e.ID
            AND rs.Meta_Key = 'repeat_start'
        INNER JOIN dbo.Events_Meta ri
            ON ri.Event_ID = e.ID
            AND ri.Meta_Key = 'repeat_interval_' + CAST(e.ID AS VARCHAR(10));

이것은 제공합니다 :

EventID | Name         | StartDateTime       | RepeatInterval
--------+--------------+---------------------+-----------------
   1    | Billa Vist   | 2014-01-03 10:00:00 |     604800
   1    | Billa Vist   | 2014-01-04 18:00:00 |     604800

이것은 당신이없는 경우에 가입 교차하는 숫자 테이블이 필요합니다 반복 얻으려면 내가 사용하는 단순 이유로 즉석에서 키를 생성하는 방법의 숫자가 있습니다 :

WITH Numbers AS
(   SELECT  Number = ROW_NUMBER() OVER(ORDER BY a.object_id) - 1
    FROM    sys.all_objects a
)
SELECT  Number
FROM    Numbers;

추가 읽기를 들어, 아론 버트 랜드는 숫자의 순차적 인 목록을 생성하는 깊이 비교의 방법으로 몇 가지 일을했다 :

우리는 0으로 우리의 번호 테이블을 제한하는 경우 - 5 만 첫 번째 이벤트 봐 줄 것 두 가지를 결합 교차 :

EventID | Name         | StartDateTime       | RepeatInterval | Number
--------+--------------+---------------------+----------------+---------
   1    | Billa Vist   | 2014-01-03 10:00:00 |     604800     |    0
   1    | Billa Vist   | 2014-01-03 10:00:00 |     604800     |    1
   1    | Billa Vist   | 2014-01-03 10:00:00 |     604800     |    2
   1    | Billa Vist   | 2014-01-03 10:00:00 |     604800     |    3
   1    | Billa Vist   | 2014-01-03 10:00:00 |     604800     |    4
   1    | Billa Vist   | 2014-01-03 10:00:00 |     604800     |    5

그런 다음 이벤트 시작 시간에 반복 간격 * 번호를 추가하여 발생을 얻을 수 있습니다 :

DECLARE @EndDate DATETIME = '20140130';

WITH EventData AS
(   SELECT  EventID = e.ID, 
            e.Name, 
            StartDateTime = DATEADD(SECOND, rs.Meta_Value, '19700101'),
            RepeatInterval = ri.Meta_Value
    FROM    dbo.Events e
            INNER JOIN dbo.Events_Meta rs
                ON rs.Event_ID = e.ID
                AND rs.Meta_Key = 'repeat_start'
            INNER JOIN dbo.Events_Meta ri
                ON ri.Event_ID = e.ID
                AND ri.Meta_Key = 'repeat_interval_' + CAST(rs.ID AS VARCHAR(10))
), Numbers AS
(   SELECT  Number = ROW_NUMBER() OVER(ORDER BY a.object_id) - 1
    FROM    sys.all_objects a
)
SELECT  e.EventID,
        e.Name,
        EventDate = DATEADD(SECOND, n.Number * e.RepeatInterval, e.StartDateTime)
FROM    EventData e
        CROSS JOIN Numbers n
WHERE   DATEADD(SECOND, n.Number * e.RepeatInterval, e.StartDateTime) < @EndDate
ORDER BY e.EventID, EventDate;

이것은 당신의 예상 출력을 제공합니다 :

EVENTID | NAME          | EVENTDATE
--------+---------------+--------------------------------
   1    | Billa Vist    | January, 03 2014 10:00:00+0000
   1    | Billa Vist    | January, 04 2014 18:00:00+0000
   1    | Billa Vist    | January, 10 2014 10:00:00+0000
   1    | Billa Vist    | January, 11 2014 18:00:00+0000
   1    | Billa Vist    | January, 17 2014 10:00:00+0000
   1    | Billa Vist    | January, 18 2014 18:00:00+0000
   1    | Billa Vist    | January, 24 2014 10:00:00+0000
   1    | Billa Vist    | January, 25 2014 18:00:00+0000

SQL 바이올린에 예

나는 당신이 가지고있는 스키마가 의심 그래도이 조인 생각 :

Meta_Key = 'repeat_interval_' + CAST(rs.ID AS VARCHAR(10))

최고의 어설픈입니다. 난 당신이 더 나은 시작 날짜와 함께 그와 관련된 반복 간격을 저장 떨어져있을 거라고 생각 :

CREATE TABLE dbo.Events_Meta
(       ID INT IDENTITY(1, 1) NOT NULL,
        Event_ID INT NOT NULL,
        StartDateTime DATETIME2 NOT NULL,
        IntervalRepeat INT NULL, -- NULLABLE FOR SINGLE EVENTS
        RepeatEndDate DATETIME2 NULL, -- NULLABLE FOR EVENTS THAT NEVER END
    CONSTRAINT PK_Events_Meta__ID PRIMARY KEY (ID),
    CONSTRAINT FK_Events_Meta__Event_ID FOREIGN KEY (Event_ID) REFERENCES dbo.Events (ID)
);

이 데이터를하기 간단합니다 :

EventID | StartDateTime       | RepeatInterval | RepeatEndDate
--------+---------------------+----------------+---------------
   1    | 2014-01-03 10:00:00 |    604800      |     NULL
   1    | 2014-01-04 18:00:00 |    604800      |     NULL

그것은 또한 당신이 단지 그것을 일주일 동안 반복하려는 경우 즉 당신의 반복에 종료 날짜를 추가 할 수 있습니다. 이것은 다음 쿼리에 simlifies :

DECLARE @EndDate DATETIME = '20140130';
WITH Numbers AS
(   SELECT  Number = ROW_NUMBER() OVER(ORDER BY a.object_id) - 1
    FROM    sys.all_objects a
)
SELECT  e.ID,
        e.Name,
        EventDate = DATEADD(SECOND, n.Number * em.IntervalRepeat, em.StartDateTime) 
FROM    Events e
        INNER JOIN Events_Meta em
            ON em.Event_ID = e.ID
        CROSS JOIN Numbers n
WHERE   DATEADD(SECOND, n.Number * em.IntervalRepeat, em.StartDateTime) <= @EndDate
AND (   DATEADD(SECOND, n.Number * em.IntervalRepeat, em.StartDateTime) <= em.RepeatEndDate 
    OR  em.RepeatEndDate IS NULL
    )
ORDER BY EventDate;

SQL 바이올린에 예

나는 당신에게 내가 과거에 이것을 달성 방법에 대한 내 전체 스키마를 제공하지 않습니다,하지만 난 당신이 희망이 자신을 구축 할 수있는 예를 아래로 매우 컷을 줄 것이다. 난 단지 월 - 금 매주 발생하는 이벤트에 대한 예를 추가합니다 :

ER RepeatEvent 위가 반복 이벤트에 대한 기본 정보를 저장에서 다음 반복 유형 (매일, 매주, 매월)를 따라 또는 그 이상의 다른 테이블의 채워집니다. 이 테이블 RepeatDay에에서 반복하는 주간 이벤트의 예에서는 일주일의 모든 일을 저장하는 것입니다. 이 특정 개월로 제한하는 데 필요한 경우 등 RepeatMonth에이 달을 저장하고있다.

그런 다음 첫 번째 날짜 이후 모든 가능한 날짜를 얻고, 년 등의 / 월 요일을 일치 만 날짜에이를 제한 할 수 있습니다 달력 테이블을 사용 :

WITH RepeatingEvents AS
(   SELECT  e.Name,
            re.StartDateTime,
            re.EndDateTime,
            re.TimesToRepeat,
            RepeatEventDate = CAST(c.DateKey AS DATETIME) + CAST(re.StartTime AS DATETIME),
            RepeatNumber = ROW_NUMBER() OVER(PARTITION BY re.RepeatEventID ORDER BY c.Datekey)
    FROM    dbo.Event e
            INNER JOIN dbo.RepeatEvent re
                ON e.EventID = re.EventID
            INNER JOIN dbo.RepeatType rt
                ON rt.RepeatTypeID = re.RepeatTypeID
            INNER JOIN dbo.Calendar c
                ON c.DateKey >= re.StartDate
            INNER JOIN dbo.RepeatDayOfWeek rdw
                ON rdw.RepeatEventID = re.RepeatEventID
                AND rdw.DayNumberOfWeek = c.DayNumberOfWeek
    WHERE   rt.Name = 'Weekly'
)
SELECT  Name, StartDateTime, RepeatEventDate, RepeatNumber
FROM    RepeatingEvents
WHERE   (TimesToRepeat IS NULL OR RepeatNumber <= TimesToRepeat)
AND     (EndDateTime IS NULL OR RepeatEventDate <= EndDateTime);

SQL 바이올린에 예

이것은 단지 내가 그것을 구현하는 방법의 아주 기본적인 표현, 예를 들어 실제로 완전히 사용 RepeatDayOfWeek에 항목 어떤 이벤트가 아니라 결코보다, 매일 반복으로 간주 될 수 있도록 반복 데이터에 대한 모든 쿼리를 본다. 이것과 다른 답변의 다른 모든 세부 사항과 함께, 당신은 희망보다 충분히 당신이 시작하는 있어야한다.

==============================

2.다음은 CTE와 StartEvent와 메타 설명에 따라 이벤트를 생성합니다.

다음은 CTE와 StartEvent와 메타 설명에 따라 이벤트를 생성합니다.

매개 변수 값에 따라 MaxDate 및 오며 MaxEvents의 값을 변경합니다.

declare @MaxDate datetime = convert(datetime,'12/2/2014', 101);
declare @MaxEvents integer=  200;

; With
    -- number generator by power of 2
    n2(n) as ( select 1 as n union all select 1),
    n4(n) as ( select 1 from n2 t1 cross join n2 t2 ),
    n16(n) as ( select 1 from n4 t1 cross join n4 t2 ),
    n256(n) as ( select 1 from n16 t1 cross join n16 t2 ),
    n65k(n) as ( select 1 from n256 t1 cross join n256 t2 ),
   Numbers (n) as (select row_number() over( order by n) from n65k ),

    -- Start of events 
    StartEvents as 
    ( SELECT 1 as EventNo, EV.Name, EM.ID, EM.Event_Id, EM.Meta_key, dateAdd(second,EM.meta_value,convert(datetime,'01/01/1970', 101)) as EventDate
        FROM events AS EV
        INNER JOIN events_meta  EM 
          ON EM.event_id = EV.id
          AND EM.meta_key = 'repeat_start'),
    -- Repeating events N times
    NextEvents AS
    ( SELECT Numbers.N+1 asEventNo, StartEvents.Name, EM.ID, EM.Event_Id, EM.Meta_key, dateAdd(second,EM.meta_value*Numbers.n,StartEvents.EventDate) as EventDate
        FROM StartEvents 
        INNER JOIN  events_meta EM 
            ON EM.event_id = StartEvents.event_id
            AND  EM.meta_key = 'repeat_interval_'+ ltrim(rtrim(str(StartEvents.ID )))
            AND ((1391040000- EM.meta_value ) % EM.meta_value) = 0 
     cross join Numbers 
     -- not to overflow (dateadd parameter is int type)
     Where Numbers.N < 3000
    )
   -- startEvents union nextEvents
  select EventNo, Name, Meta_key, EventDate
  FROM (
        Select * from StartEvents
        union all select * from NextEvents ) AllEvents
  where EventDate < @MaxDate
   and  EventNo < @MaxEvents
        order by ID ;

==============================

3.이를 달성하는 한 가지 방법은 임시 테이블에 기록 루프를 사용하여 삽입된다. 그럼 당신은 단순히 당신의 임시 테이블을 선택할 수 있습니다. 그 값이 날짜로 변환으로 예에서 나는뿐만 아니라 위해 UnixTimeStamp 값 모두를 저장 아래.

이를 달성하는 한 가지 방법은 임시 테이블에 기록 루프를 사용하여 삽입된다. 그럼 당신은 단순히 당신의 임시 테이블을 선택할 수 있습니다. 그 값이 날짜로 변환으로 예에서 나는뿐만 아니라 위해 UnixTimeStamp 값 모두를 저장 아래.

declare @enddate bigint, @intervalFactor int, @rowresult int

set @enddate = 1391040000

create table #Results
(
  eventid int,
  eventdate bigint,
  eventdatedate datetime
)

set @rowresult = 1
set @intervalFactor = 0

WHILE (@rowresult > 0)
BEGIN
  Insert #Results (eventid, eventdate, eventdatedate)
  Select events.id, date.meta_value + (intrvl.meta_value * @intervalFactor)
        ,DATEADD(ss,date.meta_value + (intrvl.meta_value * @intervalFactor), CAST('1970-01-01 00:00:00' AS datetime))
      from events
      inner join events_meta date
        on events.id = date.event_id
        AND date.meta_key = 'repeat_start'
      inner join events_meta intrvl
        on events.id = intrvl.event_id
        and intrvl.meta_key = 'repeat_interval_'+ CAST(date.id as Varchar(100))
  where ((@enddate - date.meta_value ) % intrvl.meta_value) >= 0
  and date.meta_value + (intrvl.meta_value * @intervalFactor) <= @enddate


  set @rowresult = @@rowcount
  set @intervalFactor = @intervalFactor + 1

END  

select * from #Results

이 문제에 대한 또 다른 가능한 해결책은 재귀 CTE를 활용하는 것.

==============================

4.이 그것을 할 것입니다!

이 그것을 할 것입니다!

WITH mycte AS
(
    SELECT A.ID, A.Name, A.StartDate, A.StartDate AS [IntervalDate], A.Interval, A.[repeat_startID], A.[repeat_intervalID]
    FROM (
            -- this is your provided query
            -- can you run this derived table only and make sure it return what you expect?
        SELECT
             EV.*
            ,EM1.id AS [repeat_startID]
            ,EM2.id AS [repeat_intervalID]
            -- I convert to datetime becuase Im more familiar with datatype=time manipulations
            ,DATEADD(SECOND,EM1.meta_value,'1970-01-01') AS [StartDate]
            ,EM2.meta_value AS [Interval]
        FROM [EVENTS] AS EV
            -- I used inner joins, please modify that as needed.
            INNER JOIN [EVENTS_META] AS EM1 ON EM1.meta_key = 'repeat_start' AND EM1.event_id = EV.id
            INNER JOIN [EVENTS_META] AS EM2 ON EM2.meta_key = 'repeat_interval_'+ CAST(EM1.id as Varchar(100))
    ) AS A
    UNION ALL
    SELECT  ID, Name, StartDate, DATEADD(SECOND,Interval,[IntervalDate]), Interval, [repeat_startID], [repeat_intervalID]
    FROM    mycte   
    WHERE   DATEADD(SECOND,1,[IntervalDate]) < '2014-01-30 00:00:00.000' -- this is your epoch timestamp
)
SELECT * FROM mycte 
-- it is unclear if the "cutoff" date is for the Last Interval's Start Date or the next one
-- examining the results shows there are 2 records after your"cutoff" date
-- add a WHERE statement to fix this if needed?
-- WHERE [IntervalDate] < '2014-01-30 00:00:00.000' -- this is your epoch timestamp
ORDER BY [repeat_startID], StartDate;

-- produces: (Column #4 is what you are interested in)
1   Billy Visit 2014-01-03 10:00:00.000 2014-01-03 10:00:00.000 604800  1   2
1   Billy Visit 2014-01-03 10:00:00.000 2014-01-10 10:00:00.000 604800  1   2
1   Billy Visit 2014-01-03 10:00:00.000 2014-01-17 10:00:00.000 604800  1   2
1   Billy Visit 2014-01-03 10:00:00.000 2014-01-24 10:00:00.000 604800  1   2
1   Billy Visit 2014-01-03 10:00:00.000 2014-01-31 10:00:00.000 604800  1   2 -- occurs after '2014-01-30 00:00:00.000'
1   Billy Visit 2014-01-04 18:00:00.000 2014-01-04 18:00:00.000 604800  3   4
1   Billy Visit 2014-01-04 18:00:00.000 2014-01-11 18:00:00.000 604800  3   4
1   Billy Visit 2014-01-04 18:00:00.000 2014-01-18 18:00:00.000 604800  3   4
1   Billy Visit 2014-01-04 18:00:00.000 2014-01-25 18:00:00.000 604800  3   4
1   Billy Visit 2014-01-04 18:00:00.000 2014-02-01 18:00:00.000 604800  3   4 -- occurs after '2014-01-30 00:00:00.000'

==============================

5.모든 필드 이름이 SQL 뿐인, 출력은 사용자 정의 시간 형식을 보여주기 위해 아주 약간 불통 필요에 맞게 원하는대로이 코드는 작동합니다. 나는 당신의 형식에 대한 기본 변환을 찾을 수 없습니다.

모든 필드 이름이 SQL 뿐인, 출력은 사용자 정의 시간 형식을 보여주기 위해 아주 약간 불통 필요에 맞게 원하는대로이 코드는 작동합니다. 나는 당신의 형식에 대한 기본 변환을 찾을 수 없습니다.

http://www.sqlfiddle.com/#!3/057fe/1

이 용액을 재귀 새로운 데이터 열을 생성하는 CTE를 사용하는, 각 시점에서 시작하고 종료 날짜를 충족 할 때까지 "간격"의 증가되는. 이 CTE 테이블이 작성되면, 데이터를 추출하는 간단한 쿼리가 필요합니다.

; -- previous statement must terminate w/ semicolon
WITH mycte AS
(
    SELECT A.ID, A.Name, A.StartDate, A.StartDate AS [IntervalDate], A.[Interval]
    FROM (
            -- this is your provided query
            SELECT EV.*
                -- I added two derived fields to simplify the recursion process.
                ,DATEADD(SECOND,EM1.meta_value,'1970-01-01') AS StartDate
                ,EM2.meta_value AS [Interval]
            FROM events AS EV
            RIGHT JOIN events_meta AS EM1 ON EM1.event_id = EV.id
            RIGHT JOIN events_meta AS EM2 ON EM2.meta_key = 'repeat_interval_'+ CAST(EM1.id as Varchar(100))
            WHERE EM1.meta_key = 'repeat_start'
    ) AS A
    UNION ALL
    SELECT  ID, Name, StartDate, DATEADD(SECOND,Interval,[IntervalDate]), [Interval]
    FROM    mycte   
    WHERE   DATEADD(SECOND,1,[IntervalDate]) < '2014-01-30 00:00:00.000' -- this is your epoch timestamp
)
SELECT Name + ', ' + CONVERT(VARCHAR,[IntervalDate],113)-- format your custom date as needed.
FROM mycte 
WHERE [IntervalDate] < '2014-01-30 00:00:00.000' -- this is your epoch timestamp
ORDER BY StartDate
OPTION (Maxrecursion 1000); -- default is 100 if not stated

from https://stackoverflow.com/questions/20286332/display-next-event-date by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 왜 내 SQL 키워드를 대문자로해야합니까? [복제] (0)	2020.06.04
[SQL] SSRS보고에 어떻게 형식의 날짜와 시간을합니까? (0)	2020.06.04
[SQL] SQL의 모든 조합을 생성 (0)	2020.06.04
[SQL] PHP / MySQL의 우편 번호의 근접 검색 (0)	2020.06.04
[SQL] SQL 서버 : SUM () 여기서 조항을 포함하여 여러 행의 (0)	2020.06.04

복붙노트

[SQL] 다음 이벤트 날짜를 표시합니다

다음 이벤트 날짜를 표시합니다

해결법

1.첫 번째 단계는 이벤트가 사용할 수있는이 작업을 수행하기 위해, 각 이벤트 및 반복 간격으로 날짜를 빨리 시작하는 것입니다 :

2.다음은 CTE와 StartEvent와 메타 설명에 따라 이벤트를 생성합니다.

4.이 그것을 할 것입니다!

5.모든 필드 이름이 SQL 뿐인, 출력은 사용자 정의 시간 형식을 보여주기 위해 아주 약간 불통 필요에 맞게 원하는대로이 코드는 작동합니다. 나는 당신의 형식에 대한 기본 변환을 찾을 수 없습니다.

'SQL' 카테고리의 다른 글

티스토리툴바