none
Query to get just one record based on the multiple date fields RRS feed

  • Question

  • Hi All,

    I have table with following table structure and currently I'm using SQL Server 2008 

    Store_ID  Person_Id Submit_Startdate Submit_Enddate Estimate_Startdate Estimate _Enddate

    100002    100017     2018-01-25         2018-07-13        2018-01-23            2018-07-12 

    100002    100017     2018-01-25         2018-07-13        2018-08-23            2019-02-28

    100014    100133     2018-08-30         2019-02-28        2018-01-23            2018-07-12

    100014    100133     2018-08-30         2019-02-28        2018-08-23            2019-02-28

    I want to do here is I need to get the one record for store_ID and person_id. Same store_id,same person_id, same submitted start date and same submitted end date has different scenarios. The only problem here is for one store_id, person_id, Submitted Startdate, Submitted Enddate combination has two records because estimated startdate and estimated end date has different values.

    Here I need to look for store_id & person_id whose estimate_startdate closer to submit_startdate and then only that combination record needs to pulled up. Technically I need to compare the estimate_startdate closer to submit_startdate and also possibily the second check would be the estimate_enddate is closer to submit_enddate 

    Scenario1:

    Store_ID  Person_Id Submit_Startdate Submit_Enddate Estimate_Startdate Estimate _Enddate

    100002    100017     2018-01-25         2018-07-13       2018-01-23            2018-07-12 

    100002    100017     2018-01-25         2018-07-13       2018-08-23            2019-02-28

    Output:

    Store_ID  Person_Id Submit_Startdate Submit_Enddate Estimate_Startdate Estimate _Enddate

    100002    100017     2018-01-25         2018-07-13       2018-01-23             2018-07-12 

    Scenario2:

    Store_ID  Person_Id Submit_Startdate Submit_Enddate Estimate_Startdate Estimate _Enddate

    100014  100133      2018-08-30      2019-02-28      2018-01-23          2018-07-12

    100014  100133      2018-08-30      2019-02-28      2018-08-23          2019-02-28

    Output:

    Store_ID  Person_Id Submit_Startdate Submit_Enddate Estimate_Startdate Estimate _Enddate

    100014   100133      2018-08-30         2019-02-28        2018-08-23           2019-02-28

    Thanks for your all help!


    • Edited by Red987 Wednesday, September 11, 2019 3:59 AM
    Wednesday, September 11, 2019 2:54 AM

All replies

  • Hi,

    1. Please post queries to create the sample table and insert the sample dtaa instead posting text in the message.

    2. please clarify why in the first scenario you take the row with the value Estimated Enddate=2018-07-12, but in the second scenario you take the row with the date 2019-02-28


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, September 11, 2019 3:22 AM
    Moderator
  • Thanks for responding.

    Create table Temp (

    store_id int,

    person_id int,

    submit_startdate date,

    submite_enddate date,

    estimate_startdate date,

    estimate_enddate date

    )

    Insert into Temp

    values (100002,100017,2018-01-25,2018-07-13,2018-01-23,2018-07-12), 

    (100002,100017,2018-01-25,2018-07-13,2018-08-23,2019-02-28),

    (100014,100133,2018-08-30, 2019-02-28,2018-01-23,2018-07-12),

    (100014,100133,2018-08-30,2019-02-28,2018-08-23,2019-02-28),

    Note: 

    In first scenario: it would pick estimate_startdate value (i.e., 2018-01-23) because it closest to submit_startdate (i.e., 2018-01-25compared to second estimate_startdate (i.e., 2018-08-23) 

    Scenario1:

    Store_ID  Person_Id Submit_Startdate Submit_Enddate Estimate_Startdate Estimate _Enddate

    100002    100017     2018-01-25         2018-07-13       2018-01-23            2018-07-12 

    100002    100017     2018-01-25         2018-07-13       2018-08-23            2019-02-28

    Output:

    Store_ID  Person_Id Submit_Startdate Submit_Enddate Estimate_Startdate Estimate _Enddate

    100002    100017     2018-01-25         2018-07-13       2018-01-23             2018-07-12 

    In second scenario: it would pick estimate_startdate value (i.e., 2018-08-23) because it is closest to submit_startdate (i.e., 2018-08-30compared to first estimate_startdate (i.e., 2018-01-23)    

    Scenario2:

    Store_ID  Person_Id Submit_Startdate Submit_Enddate Estimate_Startdate Estimate _Enddate

    100014  100133      2018-08-30      2019-02-28      2018-01-23          2018-07-12

    100014  100133      2018-08-30      2019-02-28      2018-08-23          2019-02-28

    Output:

    Store_ID  Person_Id Submit_Startdate Submit_Enddate Estimate_Startdate Estimate _Enddate

    100014   100133      2018-08-30         2019-02-28        2018-08-23           2019-02-28

    To be precise: I need get only one record by looking at the estimate_startdate which is nearest to submit_startdate and estimate_enddate nearest to submit_enddate. That said, I need to exclude the other record which has huge gap between estimate_startdate and submit_startdate

    Thanks!  


    • Edited by Red987 Wednesday, September 11, 2019 4:21 AM
    Wednesday, September 11, 2019 4:18 AM
  • Running your script I got

    Msg 213, Level 16, State 1, Line 17
    Column name or number of supplied values does not match table definition.


    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

    Wednesday, September 11, 2019 4:46 AM
    Answerer
  • Hi Red987,

    Please try the below code:

    Create table Temp (
    store_id int,
    person_id int,
    submit_startdate date,
    submite_enddate date,
    estimate_startdate date,
    estimate_enddate date
    )
    
    Insert into Temp (store_id ,
    person_id ,
    submit_startdate ,
    submite_enddate ,
    estimate_startdate ,
    estimate_enddate )
    values 
    (100002,100017,'2018-01-25','2018-07-13','2018-01-23','2018-07-12'), 
    (100002,100017,'2018-01-25','2018-07-13','2018-08-23','2019-02-28'),
    (100014,100133,'2018-08-30', '2019-02-28','2018-01-23','2018-07-12'),
    (100014,100133,'2018-08-30','2019-02-28','2018-08-23','2019-02-28');
    
    select t.store_id ,
    t.person_id ,
    t.submit_startdate ,
    t.submite_enddate ,
    t.estimate_startdate,
    t.estimate_enddate
    from
    (
    select store_id ,
    person_id ,
    submit_startdate ,
    submite_enddate ,
    estimate_startdate,
    estimate_enddate,
    row_number() over (partition by store_id ,person_id ,submit_startdate ,submite_enddate 
    				   order by (abs(datediff(dd,submit_startdate,estimate_startdate))) asc) as r_no 
    from temp
    ) t where t.r_no=1;
    
    ----drop table Temp;

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi

    Wednesday, September 11, 2019 4:46 AM
  • Hi,

    Thanks for providing the missing information. Let's see if I understood your needs :-)

    Please check if the following query solve your need:

    ;With MyCTE AS (
    	select 
    		store_id,
    		person_id,
    		submit_startdate,
    		submite_enddate,
    		estimate_startdate,
    		estimate_enddate,
    		RN = ROW_NUMBER() OVER(
    			PARTITION BY store_id, person_id, submit_startdate, submite_enddate
    			ORDER BY ABS(DATEDIFF(DAY, submit_startdate ,estimate_startdate ))
    		)
    	from Temp
    )
    SELECT * FROM MyCTE
    where RN = 1
    GO


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, September 11, 2019 4:58 AM
    Moderator
  • The dates in the sample code are not between '' which lead to this error

    Create table Temp (
    store_id int,
    person_id int,
    submit_startdate date,
    submite_enddate date,
    estimate_startdate date,
    estimate_enddate date
    )
    Insert into Temp
    values 
    (100002,100017,'2018-01-25','2018-07-13','2018-01-23','2018-07-12'), 
    (100002,100017,'2018-01-25','2018-07-13','2018-08-23','2019-02-28'),
    (100014,100133,'2018-08-30','2019-02-28','2018-01-23','2018-07-12'),
    (100014,100133,'2018-08-30','2019-02-28','2018-08-23','2019-02-28')
    
    select * from Temp
    go


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, September 11, 2019 4:59 AM
    Moderator
  • Hi Red,

    You could also try this one: 

    ;with cte as (
    select store_id,min(startgap)as mingap from (
    select *, abs(datediff(day,estimate_startdate,submit_startdate))as startgap
    from temp)a
    group by store_id)
    
    select a.* from temp a 
    inner join cte b 
    on abs(datediff(day,a.estimate_startdate,a.submit_startdate))=b.mingap
    
    /*
    store_id    person_id   submit_startdate submite_enddate estimate_startdate estimate_enddate
    ----------- ----------- ---------------- --------------- ------------------ ----------------
    100002      100017      2018-01-25       2018-07-13      2018-01-23         2018-07-12
    100014      100133      2018-08-30       2019-02-28      2018-08-23         2019-02-28
    */

    Sabrina


    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.

    Wednesday, September 11, 2019 6:38 AM
  • Hi,

    I recommend to check Execution Plan.
    In this solution you scan the table twice: one for the CTE and one for the outer part or the query
    While in Arulmouzhi's solution (which is also the exact solution which I posted... but he posted it before me which is why I proposed his message) you only scan the table once.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Wednesday, September 11, 2019 7:00 AM
    Moderator
  • Hi Red

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Regards,

    Sabrina


    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, September 13, 2019 8:32 AM