locked
Help in date query please RRS feed

  • Question

  • Hello, I want to know those records of CEMP which have wrong date_2. If any CEMP has more than ONE records then previous record's date_2 should not be NULL. It should be one day before of latest record's date_1.

    Create table #addp (Cemp char(10),  city char(10), prov char(10), country char(10), date_1 datetime, date_2 datetime)


    insert into #addp values ('9393','NY','AL','US','2008-09-15','2016-10-27')

    insert into #addp values ('9393','NY','AL','US','2016-10-28','2016-10-27')

    insert into #addp values ('9393','NY','AL','US','2016-10-28',NULL)

    insert into #addp values ('9394','NY','AL','US','2014-06-31','2016-07-10')

    insert into #addp values ('9394','NY','AL','US','2016-07-11',NULL)

    insert into #addp values ('9395','NY','AL','US','2012-06-31',NULL)  /* Date_2 should be 2016-07-10 but it is blank*/

    insert into #addp values ('9395','AS','MS','US','2016-07-11',NULL)

    /*Result*/

    9395        NY   AL   US   2012-06-31    NULL

    9395        AS   MS   US   2016-07-11    NULL

    Tuesday, January 3, 2017 7:25 PM

Answers

  • Create table #addp (Cemp char(10),  city char(10), prov char(10), country char(10), date_1 datetime, date_2 datetime)
    
    
    insert into #addp values ('9393','NY','AL','US','2008-09-15','2016-10-27')
    insert into #addp values ('9393','NY','AL','US','2016-10-28','2016-10-27')
    insert into #addp values ('9393','NY','AL','US','2016-10-28',NULL)
    insert into #addp values ('9394','NY','AL','US','2014-06-30','2016-07-10')
    insert into #addp values ('9394','NY','AL','US','2016-07-11',NULL)
    insert into #addp values ('9395','NY','AL','US','2012-06-30',NULL)  /* Date_2 should be 2016-07-10 but it is blank*/
    insert into #addp values ('9395','AS','MS','US','2016-07-11',NULL)
    
    
    ;with mycte as (
    select * , row_number() Over(Partition by Cemp Order by date_1 ) rn 
    from #addp )
    Select * from #addp a Where exists (
    Select 1 from mycte m1 left join mycte m2 on m1.Cemp=m2.Cemp and m1.rn=m2.rn-1 and m1.rn=1
    Where (m1.date_2  is null or datediff(day,m1.date_2,m2.date_1)<>1) and m2.rn=2 and a.Cemp=m1.Cemp
    )
     
    
    drop table #addp

    • Marked as answer by Khan_K Tuesday, January 3, 2017 10:38 PM
    Tuesday, January 3, 2017 8:57 PM
  • Create table #addp (Cemp char(10),  city char(10), prov char(10), country char(10), date_1 datetime, date_2 datetime)
    
    
    insert into #addp values ('9393','NY','AL','US','2008-09-15','2016-10-27')
    insert into #addp values ('9393','NY','AL','US','2016-10-28','2016-10-27')
    insert into #addp values ('9393','NY','AL','US','2016-10-28',NULL)
    insert into #addp values ('9394','NY','AL','US','2014-06-30','2016-07-10')
    insert into #addp values ('9394','NY','AL','US','2016-07-11',NULL)
    insert into #addp values ('9395','NY','AL','US','2012-06-30',NULL)  /* Date_2 should be 2016-07-10 but it is blank*/
    insert into #addp values ('9395','AS','MS','US','2016-07-11',NULL)
    Insert into #addp values ('9396','NY','AL','US','2006-06-05','2015-10-28')
    Insert into #addp values ('9396','NY','AL','US','2015-10-29','2015-11-11')
    Insert into #addp values ('9396','NY','AL','US','2015-11-12',NULL)
    
    
    Insert into #addp values ('9399','NY','AL','US','2006-06-05','2015-10-29')
    Insert into #addp values ('9399','NY','AL','US','2015-10-30','2015-11-12') /* Date_2 is wrong. It should be 2015-11-11 and it should pick in the result*/
    Insert into #addp values ('9399','NY','AL','US','2015-11-12',NULL)
    
    
     
     
     ;with mycte as (
    select * , Lead(date_1)OVER(Partition by Cemp Order by date_1   )  Lead_date_1 
    ,  row_number() Over(Partition by Cemp Order by date_1 DESC) rn 
    ,datediff(day, date_2,Lead(date_1)OVER(Partition by Cemp Order by date_1   )) diff
    from #addp  )
     
     
    Select  Cemp,city,prov,country,date_1,date_2 from mycte m
    WHERE (diff is null or diff<>1)  and rn<>1   
     
    
     
    
    drop table #addp

    • Marked as answer by Khan_K Wednesday, January 4, 2017 4:51 PM
    Wednesday, January 4, 2017 3:58 PM

All replies

  • Hello, I want to know those records of CEMP which have wrong date_2. If any CEMP has more than ONE records then previous record's date_2 should not be NULL. It should be one day before of latest record's date_1.

    Create table #addp (Cemp char(10),  city char(10), prov char(10), country char(10), date_1 datetime, date_2 datetime)


    insert into #addp values ('9393','NY','AL','US','2008-09-15','2016-10-27')

    insert into #addp values ('9393','NY','AL','US','2016-10-28','2016-10-27')

    insert into #addp values ('9393','NY','AL','US','2016-10-28',NULL)

    insert into #addp values ('9394','NY','AL','US','2014-06-31','2016-07-10')

    insert into #addp values ('9394','NY','AL','US','2016-07-11',NULL)

    insert into #addp values ('9395','NY','AL','US','2012-06-31',NULL)  /* Date_2 should be 2016-07-10 but it is blank*/

    insert into #addp values ('9395','AS','MS','US','2016-07-11',NULL)

    /*Result*/

    9395        NY   AL   US   2012-06-31    NULL

    9395        AS   MS   US   2016-07-11    NULL

    May be a better approach but this will show  the record that is incorrect.

    select a.* 
    from #addp a
    inner join (	SELECT Cemp, MAX(Date_1) as Dt 
    		FROM #addp 
    		GROUP BY Cemp) b on (a.Cemp = b.Cemp) 
    				and (isnull(a.Date_2, '1900-01-01') < dateadd(d, -1, b.dt)) 
    				and (a.date_1 != b.dt)

    For the larger discussion, I'd suggest modifying the data model. At a minimum, you should have a column that shows which record is current. Possibly another column for some type of uniqueness as well. The data model could be improved quite a bit.

    I hope that helps!


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Tuesday, January 3, 2017 7:53 PM
  • Hi, I have added the following rows. These rows are correct and should not be picked in the results. 

    Insert into #addp values ('9396','NY,'AL','US','2006-06-05','2015-10-28')

    Insert into #addp values ('9396','NY,'AL','US','2015-10-29','2015-11-11')

    Insert into #addp values ('9396','NY','AL','US','2015-11-12',NULL

    Tuesday, January 3, 2017 8:45 PM
  • Create table #addp (Cemp char(10),  city char(10), prov char(10), country char(10), date_1 datetime, date_2 datetime)
    
    
    insert into #addp values ('9393','NY','AL','US','2008-09-15','2016-10-27')
    insert into #addp values ('9393','NY','AL','US','2016-10-28','2016-10-27')
    insert into #addp values ('9393','NY','AL','US','2016-10-28',NULL)
    insert into #addp values ('9394','NY','AL','US','2014-06-30','2016-07-10')
    insert into #addp values ('9394','NY','AL','US','2016-07-11',NULL)
    insert into #addp values ('9395','NY','AL','US','2012-06-30',NULL)  /* Date_2 should be 2016-07-10 but it is blank*/
    insert into #addp values ('9395','AS','MS','US','2016-07-11',NULL)
    
    
    ;with mycte as (
    select * , row_number() Over(Partition by Cemp Order by date_1 ) rn 
    from #addp )
    Select * from #addp a Where exists (
    Select 1 from mycte m1 left join mycte m2 on m1.Cemp=m2.Cemp and m1.rn=m2.rn-1 and m1.rn=1
    Where (m1.date_2  is null or datediff(day,m1.date_2,m2.date_1)<>1) and m2.rn=2 and a.Cemp=m1.Cemp
    )
     
    
    drop table #addp

    • Marked as answer by Khan_K Tuesday, January 3, 2017 10:38 PM
    Tuesday, January 3, 2017 8:57 PM
  • Create table #addp (Cemp char(10),  city char(10), prov char(10), country char(10), date_1 datetime, date_2 datetime)
    
    
    insert into #addp values ('9393','NY','AL','US','2008-09-15','2016-10-27')
    insert into #addp values ('9393','NY','AL','US','2016-10-28','2016-10-27')
    insert into #addp values ('9393','NY','AL','US','2016-10-28',NULL)
    insert into #addp values ('9394','NY','AL','US','2014-06-30','2016-07-10')
    insert into #addp values ('9394','NY','AL','US','2016-07-11',NULL)
    insert into #addp values ('9395','NY','AL','US','2012-06-30',NULL)  /* Date_2 should be 2016-07-10 but it is blank*/
    insert into #addp values ('9395','AS','MS','US','2016-07-11',NULL)
    Insert into #addp values ('9396','NY','AL','US','2006-06-05','2015-10-28')
    Insert into #addp values ('9396','NY','AL','US','2015-10-29','2015-11-11')
    Insert into #addp values ('9396','NY','AL','US','2015-11-12',NULL)
    
    
    ;with mycte as (
    select * , row_number() Over(Partition by Cemp Order by date_1 ) rn 
    from #addp )
    Select * from #addp a Where exists (
    Select 1 from mycte m1 left join mycte m2 on m1.Cemp=m2.Cemp and m1.rn=m2.rn-1 and m1.rn=1
    Where (m1.date_2  is null or datediff(day,m1.date_2,m2.date_1)<>1) and m2.rn=2 and a.Cemp=m1.Cemp
    )
     
    
    drop table #addp

    • Marked as answer by Khan_K Tuesday, January 3, 2017 10:38 PM
    • Unmarked as answer by Khan_K Wednesday, January 4, 2017 3:34 PM
    Tuesday, January 3, 2017 8:58 PM
  • Thank you so much.
    Tuesday, January 3, 2017 10:39 PM
  • Hi, I added the following rows and these rows are not showing in the results.

    Insert into #addp values ('9399','NY,'AL','US','2006-06-05','2015-10-29')

    Insert into #addp values ('9399','NY,'AL','US','2015-10-30','2015-11-12') /* Date_2 is wrong. It should be 2015-11-11 and it should pick in the result*/

    Insert into #addp values ('9399','NY','AL','US','2015-11-12',NULL)

    Wednesday, January 4, 2017 3:38 PM
  • Create table #addp (Cemp char(10),  city char(10), prov char(10), country char(10), date_1 datetime, date_2 datetime)
    
    
    insert into #addp values ('9393','NY','AL','US','2008-09-15','2016-10-27')
    insert into #addp values ('9393','NY','AL','US','2016-10-28','2016-10-27')
    insert into #addp values ('9393','NY','AL','US','2016-10-28',NULL)
    insert into #addp values ('9394','NY','AL','US','2014-06-30','2016-07-10')
    insert into #addp values ('9394','NY','AL','US','2016-07-11',NULL)
    insert into #addp values ('9395','NY','AL','US','2012-06-30',NULL)  /* Date_2 should be 2016-07-10 but it is blank*/
    insert into #addp values ('9395','AS','MS','US','2016-07-11',NULL)
    Insert into #addp values ('9396','NY','AL','US','2006-06-05','2015-10-28')
    Insert into #addp values ('9396','NY','AL','US','2015-10-29','2015-11-11')
    Insert into #addp values ('9396','NY','AL','US','2015-11-12',NULL)
    
    
    Insert into #addp values ('9399','NY','AL','US','2006-06-05','2015-10-29')
    Insert into #addp values ('9399','NY','AL','US','2015-10-30','2015-11-12') /* Date_2 is wrong. It should be 2015-11-11 and it should pick in the result*/
    Insert into #addp values ('9399','NY','AL','US','2015-11-12',NULL)
    
    
     
     
     ;with mycte as (
    select * , Lead(date_1)OVER(Partition by Cemp Order by date_1   )  Lead_date_1 
    ,  row_number() Over(Partition by Cemp Order by date_1 DESC) rn 
    ,datediff(day, date_2,Lead(date_1)OVER(Partition by Cemp Order by date_1   )) diff
    from #addp  )
     
     
    Select  Cemp,city,prov,country,date_1,date_2 from mycte m
    WHERE (diff is null or diff<>1)  and rn<>1   
     
    
     
    
    drop table #addp

    • Marked as answer by Khan_K Wednesday, January 4, 2017 4:51 PM
    Wednesday, January 4, 2017 3:58 PM