none
How to Group discontinued time periods

    Question

  • Hi All:

    I have an issue which costs me bunch of time to develop. Basically I am trying to use discounted time in a select query but feel  like get nowhere to start.

    the scenarios is like this:

    I have many rows of data of this and I would like to have a solution I can select time period from just like what I can do for integer and text:

    Select *

    From Table

    Where Group = B

    and Open_Date in (select the all group A's time interval)

    ----------------------------------

    to explain this problem in plain text, I would like to select all records from Group B whose Open_Date is within the interval between Group A's Open_Date and Close_Date. Thanks all in advance.

    Record Open_Date Close_Date GROUP
    1 9/12/2013 9:25 9/12/2013 10:44 A
    2 9/12/2013 10:19 9/12/2013 10:39 A
    3 9/12/2013 10:22 9/12/2013 10:22 A
    4 9/12/2013 10:56 9/12/2013 12:41 B
    5 9/12/2013 12:00 9/12/2013 12:00 B
    6 9/12/2013 12:00 9/12/2013 12:01 A
    7 A+E301:H318 9/12/2013 12:03 A
    8 9/12/2013 13:44 9/12/2013 13:46 C
    9 9/12/2013 13:46 9/12/2013 13:46 C
    10 9/12/2013 13:46 9/12/2013 14:33 C
    11 9/12/2013 14:04 9/12/2013 14:09 A
    12 9/12/2013 14:23 9/12/2013 17:05 A
    13 9/12/2013 15:54 9/12/2013 15:54 A
    14 9/12/2013 15:54 9/12/2013 15:55 B
    15 9/12/2013 15:55 9/12/2013 15:55 B
    16 9/12/2013 15:55 9/12/2013 15:58 A
    17 9/12/2013 16:02 9/12/2013 16:02 A

     

    Sunday, March 30, 2014 7:55 AM

Answers

  • If I understand this right:

    SELECT ...
    FROM   tbl b
    WHERE  b.grp = 'B'
      AND  EXISTS (SELECT *
                   FROM   tbl a
                   WHERE  a.grp = 'A'
                     AND  b.open_date BETWEEN a.open_date AND a.close_date)

    Tip: for questions of this type it is always a good idea include the CREATE TABLE statement for you table and INSERT statements with sample data, together with the expected result given the sample. This makes is easy to copy and paste and developed a tested solution. The solution above is untested.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by RSingh() Sunday, March 30, 2014 10:38 AM
    • Marked as answer by JohnnyKahWang Sunday, March 30, 2014 11:07 AM
    Sunday, March 30, 2014 9:47 AM

All replies

  • Sorry cannot test it right now

    Select *

    From Table

    Where Group = B

    AND Open_Date in (SELECT OpenDate FROM Table WHERE  Group = A

    AND  OpenDate  BETWEEN OpenDate  AND CloseDate )


    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

    Sunday, March 30, 2014 8:09 AM
  • If I understand this right:

    SELECT ...
    FROM   tbl b
    WHERE  b.grp = 'B'
      AND  EXISTS (SELECT *
                   FROM   tbl a
                   WHERE  a.grp = 'A'
                     AND  b.open_date BETWEEN a.open_date AND a.close_date)

    Tip: for questions of this type it is always a good idea include the CREATE TABLE statement for you table and INSERT statements with sample data, together with the expected result given the sample. This makes is easy to copy and paste and developed a tested solution. The solution above is untested.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by RSingh() Sunday, March 30, 2014 10:38 AM
    • Marked as answer by JohnnyKahWang Sunday, March 30, 2014 11:07 AM
    Sunday, March 30, 2014 9:47 AM
  • Thanks a lot Uri, I am bit confused aobut this part:

    AND Open_Date in (SELECT OpenDate FROM Table WHERE  Group = A

    AND  OpenDate  BETWEEN OpenDate  AND CloseDate )

    as the as I need the A's open_date between any interval of B's Open_Date and Close_Date, not exactly match B's Open_Date. Please correct me if I was wrong about your code. Thanks again for your great share...

    Sunday, March 30, 2014 9:50 AM
  • Hi Erland:

    Thanks and it worked. Really appreciated your help.

    Cheers

    Johnny

    Sunday, March 30, 2014 10:25 AM