none
How to judge whether the two time periods are repeated? RRS feed

  • Question

  • Hi,

       I want  to add the data to the table ,but  can not add data with repetitive time periods,so I must to judge it .

       thank you 

    Tuesday, November 21, 2017 6:49 AM

Answers

All replies

  • if not exists (select * from tbl where dt.....)

    insert into tbl values (.....)

    Please provide sample data along with desired result


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 21, 2017 6:52 AM
    Answerer
  • From your very brief description, I feel you need either of these

    1. If you actual data is one row per period then you would need a logic like

    INSERT Table (...)
    SELECT...
    FROM Source
    WHERE NOT EXISTS 
    (
    SELECT 1
    FROM table
    WHERE timeperiod = s.Timeperiod)

    to avoid them from being duplicated

    if you want overwrite in case of existing period you need to make it into update like

    UPDATE t
    SET col1 = ...,
    col2 = ..
    FROM table t
    JOIN Source s
    ON s.Timeperiod = t.Timeperiod

    2. In case your data is at more detailed level but you want it at time period level then you need to apply aggregation like below to avoid duplicate rows per period

    INSERT table (...)
    SELECT TimePEriod,
    SUM(Col1) AS...,
    SUM(col2) AS...,
    AVG(Col3) AS ..
    FROM Source
    GROUP BY TimePeriod

    hope this was what you're after

    In case you meant something different, please revert with more detail including some sample data and required output


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 21, 2017 7:02 AM
  • I don't know how  to  judge  the current time period overlaps with the existing time period
    Tuesday, November 21, 2017 7:10 AM
  • Simple check to see if two time periods overlap:

    (
        (@Start > StartDate and @Start < EndDate)
        OR (@End > StartDate and @End < EndDate)
        OR (@Start < StartDate and @End >EndDate)
    )

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by silverting Tuesday, November 21, 2017 8:18 AM
    Tuesday, November 21, 2017 7:22 AM
    Answerer
  • I don't know how  to  judge  the current time period overlaps with the existing time period

    I think you need this

    WHERE @YourStart < PeriodEnd
    AND @YourEnd > PeriodStart

    here's an illustration on the same

    declare @strt datetime = '1 Jun 2017'
    declare @end datetime = '13 Aug 2017'
    
    declare @t table
    (
    startdate datetime,
    enddate datetime
    )
    insert @t
    values('1 Jan 2017','23 Sep 2017'),
    ('13 Feb 2017','18 Apr 2017'),
    ('15 Jun 2017','3 Sep 2017'),
    ('1 Jan 2017','20 Jul 2017'),
    ('19 Aug 2017','25 Nov 2017')
    
    select *
    from @t
    where startdate < @end
    and enddate > @strt


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Tuesday, November 21, 2017 7:31 AM
    Tuesday, November 21, 2017 7:26 AM
  • Hi,

    If you mean that you want to get the current time period, you can do this:

    Select CAST(GETDATE() as time) --Current Time period

    Then use the same way to get the existing time period and make a comparison. Something like:

    Select *
    from YouTable
    where CAST(TimeColumn as time) <> CAST(GETDATE() as time)
    

    Since your description is too simple , please share us more detailed information like some sample data and its corresponding expected result.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 21, 2017 7:26 AM