none
SQL Query RRS feed

  • Question

  • This should be easy but I am running into all types of trouble trying to figure this one out. I need to be able to select records from a table based on the min(date) field. Lets say I have the following fields Store ID, Person_id, type_id, Type,created_at,start_date.

    Store ID             Person_id            type_id   Type              created_at                start_date

    10000003458     10001146402            1      Reported       12/18/07 2:39 PM      12/18/2007

    10000003458      10001146402           2      Accepted       3/14/08 7:15 PM        3/14/2008

    10000003458      10001146402           7      Closed            4/3/08 5:18 PM        4/3/2008

    10000003458      10001146402           1      Reported       4/3/08 6:36 PM          4/3/2008

    10000003458      10001146402           3      Review           4/3/08 6:36 PM         4/3/2008

    10000003458      10001146402            7     Closed             12/9/09 5:00 AM      12/9/2009

    10000003458      10001146402            1     Reported        2/3/10 8:54 PM          2/3/2010

    10000003458      10001146402            3     Review            2/3/10 8:54 PM         2/3/2010

    10000003458      10001146402            9     PreClosed       11/18/10 8:54 PM       11/18/2010

    10000003458      10001146402            7      Closed             11/18/10 8:54 PM    11/18/2010

    10000003458      10001146402            3      Review            4/7/11 1:02 PM         4/7/2011

    10000003458      10001146402            7      Closed             6/16/16 4:34 PM       6/13/2016

    Same store I’d and same person I’d but different scenarios. Here I need all the records which source type = 'Closed' but their source start date should be start date of that respective episode.

    Note: Here we cant take the minimum of source type id because they are not assigned in orderly manner.

    For example first episode source type = 'Closed' on 4/3/2008 but the episode started on 12/18/2007. In similar way I need to get all the closed source type records where the source start date should be their respective episode star date.

    The final output  from this example should be four records with source type = 'closed' with start date of  their respective episode start date

    Final ouptut:

    10000003458   10001146402    7     Closed  4/3/08 5:18 PM    4/3/2008 --->episode start date:12/18/2007

    10000003458   10001146402   7      Closed  12/9/09 5:00 AM  12/9/2009 --> episode start date:4/3/2008

    10000003458   10001146402   7      Closed  11/18/10 8:54 PM  11/18/2010 -->episode start date:2/3/2010

    10000003458   10001146402   7      Closed  6/16/16 4:34 PM    6/13/2016---> episode start date:4/7/2011

    Wednesday, December 6, 2017 9:30 PM

All replies

  • Simplest way is to use CROSS APPLY to get the top row for the date preceding closed date.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, December 6, 2017 9:43 PM
    Moderator
  • CREATE TABLE mytable(
       StoreID    bigint  NOT NULL  
      ,Person_id  bigint  NOT NULL
      ,type_id    INTEGER  NOT NULL
      ,Type       VARCHAR(9) NOT NULL
      ,created_at DATETIME  NOT NULL
      ,start_date date
      
    );
    INSERT INTO mytable(StoreID,Person_id,type_id,Type,created_at,start_date  ) 
    VALUES (10000003458,10001146402,1,'Reported','12/18/07 2:39 PM','12/18/2007')
    ,(10000003458,10001146402,2,'Accepted','3/14/08 7:15 PM','3/14/2008') 
    , (10000003458,10001146402,7,'Closed','4/3/08 5:18 PM','4/3/2008')
    , (10000003458,10001146402,1,'Reported','4/3/08 6:36 PM','4/3/2008')
    ,(10000003458,10001146402,3,'Review','4/3/08 6:36 PM','4/3/2008') 
    , (10000003458,10001146402,7,'Closed','12/9/09 5:00 AM','12/9/2009') 
    ,  (10000003458,10001146402,1,'Reported','2/3/10 8:54 PM','2/3/2010') 
    ,  (10000003458,10001146402,3,'Review','2/3/10 8:54 PM','2/3/2010')  
    ,  (10000003458,10001146402,9,'PreClosed','11/18/10 8:54 PM','11/18/2010') 
    ,  (10000003458,10001146402,7,'Closed','11/18/10 8:54 PM','11/18/2010') 
    ,  (10000003458,10001146402,3,'Review','4/7/11 1:02 PM','4/7/2011') 
    , (10000003458,10001146402,7,'Closed','6/16/16 4:34 PM','6/13/2016');
    
    ;with mycte as (
     SELECT * 
     , Sum(case when type='Closed' then 1 else 0 end) Over(Order by created_at DESC ) grp
      FROM  mytable
      )
    
      ,mycte2 as (
      Select StoreID,Person_id,type_id,Type,created_at,start_date, grp
      , min(start_date) Over(Partition by grp) [episode start date]  
      from mycte)
    
      Select StoreID,Person_id,type_id,Type,created_at,start_date, [episode start date] 
       from mycte2
      WHERE type='Closed'
     Order by created_at 
    
    
     DROP TABLE mytable

    Wednesday, December 6, 2017 10:06 PM
    Moderator
  • Thank you for responding.

    I'm getting the error at below line and the error it displays is "Incorrect syntax near 'order' "

    , Sum(case when type='Closed' then 1 else 0 end) Over(Order by created_at DESC ) grp

    Currently I'm running this in  SQL Server Management studio 2008R2.

    Could you please help me in figuring out this issue?

    Thanks in advance.

     




    • Edited by Red987 Thursday, December 7, 2017 1:18 AM
    Thursday, December 7, 2017 1:07 AM
  • What version of sql server are you using?
    Thursday, December 7, 2017 1:43 AM
  • I'm using SSMS 2008R2.

    Thanks!

    Thursday, December 7, 2017 1:55 AM
  • In this case the above query will not work as it requires SQL 2012 and up.

    Did you try the idea I suggested?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 7, 2017 2:02 AM
    Moderator
  • I tried using the cross-apply function but it didn't work for me. 

    I have tried in SSMS v17.1 too but it didn't work.

    Thanks in advance!

    • Marked as answer by Red987 Thursday, December 7, 2017 5:52 AM
    • Unmarked as answer by Red987 Thursday, December 7, 2017 5:52 AM
    Thursday, December 7, 2017 2:09 AM
  • >> This should be easy but I am running into all types of trouble trying to figure this one out. I need to be able to select records [sic] from a table based on the min(date) field [sic]. Lets say I have the following fields [sic] Store ID, Person_id, type_id, Type, created_at, start_date. <<

    please read a book on basic data modeling and RDBMS. Rows are nothing whatsoever like records, and fields don’t mean the same thing as columns in SQL. The only format allowed for temporal data in ANSI-ISO standard SQL is based on ISO 8601, not for I think might be American dialect. 

    Then read the basic netiquette at the start of this forum; every table should have a key. We now have to guess about the data types, constraints and everything else that you fail to post out of rudeness.

    There is no such absurdity as a “type_id” in RDBMS. The terms “type” and “id” are what ISO calls an attribute property. They have to be used as “<something in particular>_type” or“<something in particular>_id” to conform to the ISO standards. Likewise, we need “<something in particular>_start_date” to be up to standards. 

    We never put audit data like creation dates in the table being audited. Just think about it; what happens when that row (not a record!) Is deleted or updated? You’ve just destroyed your audit trail! Then on top of that you got the text of this nameless generic code along with the numeric encoding. The whole point of all databases, even those before RDBMS,was to remove redundancy and not increase it the way you but you’re doing.

    Finally, by definition, a table must have a key. Where’s the key? 

    >> Same store_id and same person_id I’d but different scenarios. Here I need all the records [sic] which source type = 'Closed' but their source start date should be start date of that respective episode. <<

    What is an episode? You never define it. You have a start date of something (this is for the episodes?) but you don’t have an ending date. I’m going to make a guess that your episodes are actually state transitions. The model for that looks like this:

    https://www.red-gate.com/simple-talk/sql/t-sql-programming/state-transition-constraints/

    CREATE TABLE Something_Episodes 
    (store_duns CHAR(10) NOT NULL, 
     person_id CHAR(11) NOT NULL, 
     event_start_date DATE NOT NULL,
     event_end_date DATE,
     CHECK (event_start_date <= event_end_date),
    DATE NOT NULL,
    current_event_type CHAR(9) NOT NULL,
    previous_event_type CHAR(9) NOT NULL,
     FOREIGN KEY (current_event_type, previous_event_type) 
     REFERENCES Something_Transitions (current_event_type, previous_event_type), 

    PRIMARY KEY (store_duns, person_id, current_event_type)

    Now your query is simply:

    SELECT store_duns, person_id, event_end_date 
      FROM Something_Episodes 
     WHERE current_event_type = ‘closed’;

    when you’ve been at this for a few years (were few decades like me :-)), your realize that most of the work in SQL is done in the DDL and not in the DML.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, December 7, 2017 2:38 AM
  • Hi sat987,

    Welcome to Transact-SQL community forum.

    Since you are using SQL Server 2008R2. You may use ROW_NUMBER function and CROSS APPLY to achieve your requirements.

    CREATE TABLE mytable1(
       StoreID    bigint  NOT NULL  
      ,Person_id  bigint  NOT NULL
      ,type_id    INTEGER  NOT NULL
      ,Type       VARCHAR(9) NOT NULL
      ,created_at DATETIME  NOT NULL
      ,start_date date
    );
    
    INSERT INTO mytable1(StoreID,Person_id,type_id,Type,created_at,start_date  ) 
    VALUES (10000003458,10001146402,1,'Reported','12/18/07 2:39 PM','12/18/2007')
    ,(10000003458,10001146402,2,'Accepted','3/14/08 7:15 PM','3/14/2008') 
    , (10000003458,10001146402,7,'Closed','4/3/08 5:18 PM','4/3/2008')
    , (10000003458,10001146402,1,'Reported','4/3/08 6:36 PM','4/3/2008')
    ,(10000003458,10001146402,3,'Review','4/3/08 6:36 PM','4/3/2008') 
    , (10000003458,10001146402,7,'Closed','12/9/09 5:00 AM','12/9/2009') 
    ,  (10000003458,10001146402,1,'Reported','2/3/10 8:54 PM','2/3/2010') 
    ,  (10000003458,10001146402,3,'Review','2/3/10 8:54 PM','2/3/2010')  
    ,  (10000003458,10001146402,9,'PreClosed','11/18/10 8:54 PM','11/18/2010') 
    ,  (10000003458,10001146402,7,'Closed','11/18/10 8:54 PM','11/18/2010') 
    ,  (10000003458,10001146402,3,'Review','4/7/11 1:02 PM','4/7/2011') 
    , (10000003458,10001146402,7,'Closed','6/16/16 4:34 PM','6/13/2016');
    
    
    ;with cte as 
    (
    select
    ROW_NUMBER() over (order by created_at) as rn,
    StoreID,
    Person_id,
    [type_id],
    [Type],
    created_at,
    [start_date]
    from mytable1
    where [type]='Closed'
    )
    select T.StoreID,T.Person_id,T.[type_id],T.[Type],T.created_at,T.[start_date],T2.[start_date] as [episode start date]
    from cte T
    left join cte T1 on T.rn=T1.rn+1
    cross apply ( select min([start_date]) as [start_date] from mytable1 
    where created_at<T.created_at and created_at>isnull(T1.created_at,'1900-1-1' )) T2

    Best Regards,

    Will


    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.

    Thursday, December 7, 2017 3:50 AM
    Moderator
  • I tried using the cross-apply function but it didn't work for me. 

    I have tried in SSMS v17.1 too but it didn't work.

    Thanks in advance!

    Hi sat987,

    I think the issue is caused by the tool SSMS, and it has nothing to do with the version of SQL Server. The APPLY operator is available at the beginning of SQL Server 2005.

    Could you please upgrade v17.1 to v17.3 based on this article?

    Download SQL Server Management Studio (SSMS)

    Best Regards,

    Will


    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.

    Thursday, December 7, 2017 6:17 AM
    Moderator
  • What exactly did you try? CROSS APPLY is not a function, it's an operator.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 7, 2017 1:09 PM
    Moderator
  • Thank you so much for your response. 

    This query worked almost but only issue I have here is....For different store_id and person_id for difference episodes I'm unable to get respective values.

    If the pass the static values in first CTE (store_id and person_id) this works fine but if I give the store_id and person_id at the last this wont work. 

    Scenario 1: Works fine...

    ;with mycte as (
     SELECT * 
     , Sum(case when type='Closed' then 1 else 0 end) Over(Order by created_at DESC ) grp
      FROM mytable where store_id = 10000003458 and person_id =10001146402 

      )
    ,mycte2 as (Select StoreID,Person_id,type_id,Type,created_at,start_date, grp
                      ,min(start_date) Over(Partition by grp) [episode start date]  
                      from mycte)
    Select StoreID,Person_id,type_id,Type,created_at,start_date, [episode start date] 
     from mycte2
     WHERE type='Closed'
     Order by created_at

    output:

    10000003458   10001146402    7  Closed  4/3/08 5:18 PM    4/3/2008 --->episode date:12/18/2007

    10000003458   10001146402   7   Closed  12/9/09 5:00 AM  12/9/2009 --> episode date:4/3/2008

    10000003458   10001146402   7   Closed  11/18/10 8:54 PM 11/18/2010 -->episode date:2/3/2010

    10000003458   10001146402   7   Closed  6/16/16 4:34 PM    6/13/2016---> episode date:4/7/2011

    Scenario 2: Returns someother date value instead of the episode start date.

    ;with mycte as (
     SELECT * 
     , Sum(case when type='Closed' then 1 else 0 end) Over(Order by created_at DESC ) grp
      FROM mytable 

      )
    ,mycte2 as (Select StoreID,Person_id,type_id,Type,created_at,start_date, grp
                      ,min(start_date) Over(Partition by grp) [episode start date]  
                      from mycte)
    Select StoreID,Person_id,type_id,Type,created_at,start_date, [episode start date] 
     from mycte2
     WHERE type='Closed' and store_id = 10000003458 and person_id =10001146402 
     Order by created_at

    output:                                                     

    10000003458   10001146402    7  Closed  4/3/08 5:18 PM    4/3/2008 --->episode date:2004-10-13

    10000003458   10001146402   7   Closed  12/9/09 5:00 AM  12/9/2009 --> episode date:0112-05-01

    10000003458   10001146402   7   Closed  11/18/10 8:54 PM 11/18/2010 -->episode date:1994-08-27

    10000003458   10001146402   7   Closed  6/16/16 4:34 PM    6/13/2016---> episode date:1900-01-01

    Here my question how can I dynamically give values to this logic for other store_id and person_id. I have source table all_store_info in which all the information is stored (i.e., case_id, child_id, type_id, type, created_at, start_date and episode_date).

    For below query I need to know how can I pass the input values (i.e., case_id, child_id) from table all_store_info to below logic.

    ;with mycte as (
     SELECT * 
     , Sum(case when type='Closed' then 1 else 0 end) Over(Order by created_at DESC ) grp
      FROM mytable where store_id = @store_id and person_id = @person_id 

      )
    ,mycte2 as (Select StoreID,Person_id,type_id,Type,created_at,start_date, grp
                      ,min(start_date) Over(Partition by grp) [episode start date]  
                      from mycte)
    Select StoreID,Person_id,type_id,Type,created_at,start_date, [episode start date] 
     from mycte2
     WHERE type='Closed'
     Order by created_at

    Could you please assist me in passing the input values from different table to the whole logic that is copied above?

    Thanks in advance.





    • Edited by Red987 Friday, December 8, 2017 4:09 AM
    Friday, December 8, 2017 3:39 AM
  • Hi sat987,

    You could add the column "store_id","person_id" to the partition by clause. Please refer: 

    CREATE TABLE mytable(
       StoreID    bigint  NOT NULL  
      ,Person_id  bigint  NOT NULL
      ,type_id    INTEGER  NOT NULL
      ,Type       VARCHAR(9) NOT NULL
      ,created_at DATETIME  NOT NULL
      ,start_date date
      
    );
    INSERT INTO mytable(StoreID,Person_id,type_id,Type,created_at,start_date  ) 
    VALUES (10000003458,10001146402,1,'Reported','12/18/07 2:39 PM','12/18/2007')
    ,(10000003458,10001146402,2,'Accepted','3/14/08 7:15 PM','3/14/2008') 
    , (10000003458,10001146402,7,'Closed','4/3/08 5:18 PM','4/3/2008')
    , (10000003458,10001146402,1,'Reported','4/3/08 6:36 PM','4/3/2008')
    ,(10000003458,10001146402,3,'Review','4/3/08 6:36 PM','4/3/2008') 
    , (10000003458,10001146402,7,'Closed','12/9/09 5:00 AM','12/9/2009') 
    ,  (10000003458,10001146402,1,'Reported','2/3/10 8:54 PM','2/3/2010') 
    ,  (10000003458,10001146402,3,'Review','2/3/10 8:54 PM','2/3/2010')  
    ,  (10000003458,10001146402,9,'PreClosed','11/18/10 8:54 PM','11/18/2010') 
    ,  (10000003458,10001146402,7,'Closed','11/18/10 8:54 PM','11/18/2010') 
    ,  (10000003458,10001146402,3,'Review','4/7/11 1:02 PM','4/7/2011') 
    , (10000003458,10001146402,7,'Closed','6/16/16 4:34 PM','6/13/2016')
    ,(10000003459,10001146403,1,'Reported','12/18/07 2:39 PM','12/18/2007')
    ,(10000003459,10001146403,2,'Accepted','3/14/08 7:15 PM','3/14/2008') 
    , (10000003459,10001146403,7,'Closed','4/3/08 5:18 PM','4/3/2008')
    , (10000003459,10001146403,1,'Reported','4/3/08 6:36 PM','4/3/2008')
    ,(10000003459,10001146403,3,'Review','4/3/08 6:36 PM','4/3/2008') 
    , (10000003459,10001146403,7,'Closed','12/9/09 5:00 AM','12/9/2009') 
    ,  (10000003459,10001146403,1,'Reported','2/3/10 8:54 PM','2/3/2010') 
    ,  (10000003459,10001146403,3,'Review','2/3/10 8:54 PM','2/3/2010')  
    ,  (10000003459,10001146403,9,'PreClosed','11/18/10 8:54 PM','11/18/2010') 
    ,  (10000003459,10001146403,7,'Closed','11/18/10 8:54 PM','11/18/2010') 
    ,  (10000003459,10001146403,3,'Review','4/7/11 1:02 PM','4/7/2011') 
    , (10000003459,10001146403,7,'Closed','6/16/16 4:34 PM','6/13/2016');
    
    
    ;with mycte as (
     SELECT * 
     , Sum(case when [type]='Closed' then 1 else 0 end) Over(partition by StoreID,Person_id Order by created_at DESC ) grp
      FROM  mytable
      )
      ,mycte2 as (
      Select StoreID,Person_id,type_id,Type,created_at,start_date, grp
      , min(start_date) Over(Partition by StoreID,Person_id,grp) [episode start date]  
      from mycte)
    
      Select StoreID,Person_id,type_id,Type,created_at,start_date, [episode start date] 
       from mycte2
      WHERE type='Closed'
     Order by StoreID,Person_id,created_at 
    
     DROP TABLE mytable

    Best Regards,

    Will


    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.

    Friday, December 8, 2017 12:46 PM
    Moderator