none
Need a solution

    Question

  • Hi Experts,

    have a table which contains the data as shown below

    ID   StartDate    EndDate

    1    01-Jan-2011  31-Dec-2011 
    2    01-Jan-2012  31-Dec-2012
    3    01-Jan-2013 NULL


    here, the requirement is, need to check few things before inserting a row in to this table

    1) date range should not be repeated (ex: there should not be another "01-Jan-2011 to 31-Dec-2011" range)

    2) if EndDate is NULL for the latest StartDate, there should not be any future dates from that StartDate (Ex: For "01-Jan-2013" EndDate is null, therefore no future dates from that date in any new row to be inserted)

    can we write an instead of trigger or is there any proper way? and also, need some help with code.

    Thanks,

    Shiva




    • Edited by ShivaGS Wednesday, July 10, 2013 10:12 PM
    Wednesday, July 10, 2013 10:08 PM

Answers

  • Thank you Sarat!

    "01-Jan-2011 to 31-aug-2011"  is not acceptable since there is another date range starts with "01-Jan-2011"

    EndDate NULL means that is the latest one. there should not be any date range after that. but, if we want to start new date range, this EndDate should be updated.

    Ex :  for "01-Jan-2013", EndDate is null. if we are going to start a new range "01-Jan-2014", then this EndDate should be updated with "31-Dec-2013"

    In such a case validation with start date alone is needed, try this.

    create table #temp(id int, startDate datetime, enddate datetime)
    insert into #temp
    select 1,    '01-Jan-2011',  '31-Dec-2011' 
    union
    select 2,    '01-Jan-2012',  '31-Dec-2012'
    union
    select 3,    '01-Jan-2013', NULL
    go
    ---New values to be inserted (could be a procedure)
    declare @id int=4,@startdate date='01-Jan-2014',@enddate date=NULL
    
    If exists(select 1 from #temp where startDate=@startdate )
    		print 'cannot insert data'
    else
    BEGIN
    			insert into #temp values (@id,@startdate,@enddate)
    			update #temp set enddate= cast('31-Dec-'+ cast(DATEPART(year,'01-Jan-2013') as varchar(10))as date) where enddate is null
    			
    			
    END 

    Thursday, July 11, 2013 3:55 PM

All replies

  • Why don't you verify the things in your DML instead of creating a trigger?

    You could verify the things before inserting the row into the table. Please go through with the query below for getting some idea:

    create table #temp(id int, startDate datetime, enddate datetime)
    insert into #temp
    select 1,    '01-Jan-2011',  '31-Dec-2011' 
    union
    select 2,    '01-Jan-2012',  '31-Dec-2012'
    union
    select 3,    '01-Jan-2013', NULL
    if not exists(select 1 from #temp 
    				where (startdate='01-Jan-2011' and enddate='31-Dec-2011') 
    					or (startdate='01-Jan-2013' and enddate is null))
    					begin
    					insert into #temp values (4,'02-jan-2013','03-jan-2013')
    					end
    select * from #temp
    drop table #temp

    Please Mark As Answer if the post answers your query.

    Cheers


    Amar Deep Singh

    Wednesday, July 10, 2013 10:35 PM
  • Against your point 1, that means "01-Jan-2011 to 31-aug-2011"  is acceptible?

    Also, what if the newly inserted values contains end date as NULL?

    If not, check with the start date alone will do, otherwise we may need to compare both start and end dates.


    • Edited by Sarat Babu (SS) Thursday, July 11, 2013 1:09 AM added one more ques
    Thursday, July 11, 2013 1:03 AM
  • If the end date comparison is not need that can be removed from OR condition,

    create table #temp(id int, startDate datetime, enddate datetime)
    insert into #temp
    select 1,    '01-Jan-2011',  '31-Dec-2011' 
    union
    select 2,    '01-Jan-2012',  '31-Dec-2012'
    union
    select 3,    '01-Jan-2013', NULL
    go
    ---New values to be inserted (could be a procedure)
    declare @id int=4,@startdate date='01-feb-2013',@enddate date='28 feb 2013'
    
    If exists(select 1 from #temp where startDate=@startdate and (enddate=@enddate or enddate is null or @enddate is null))
    		print 'cannot insert data'
    else
    			insert into #temp values (@id,@startdate,@enddate)

    Thursday, July 11, 2013 1:11 AM
  • Thank you Sarat!

    "01-Jan-2011 to 31-aug-2011"  is not acceptable since there is another date range starts with "01-Jan-2011"

    EndDate NULL means that is the latest one. there should not be any date range after that. but, if we want to start new date range, this EndDate should be updated.

    Ex :  for "01-Jan-2013", EndDate is null. if we are going to start a new range "01-Jan-2014", then this EndDate should be updated with "31-Dec-2013"

    Thursday, July 11, 2013 5:48 AM
  • Have you tried my query?

    you only have to create a SP and paste the query and pass the Start Date and End Date parameters instead of passing dates directly in the query.

    Cheers,


    Amar Deep Singh

    Thursday, July 11, 2013 3:47 PM
  • Thank you Sarat!

    "01-Jan-2011 to 31-aug-2011"  is not acceptable since there is another date range starts with "01-Jan-2011"

    EndDate NULL means that is the latest one. there should not be any date range after that. but, if we want to start new date range, this EndDate should be updated.

    Ex :  for "01-Jan-2013", EndDate is null. if we are going to start a new range "01-Jan-2014", then this EndDate should be updated with "31-Dec-2013"

    In such a case validation with start date alone is needed, try this.

    create table #temp(id int, startDate datetime, enddate datetime)
    insert into #temp
    select 1,    '01-Jan-2011',  '31-Dec-2011' 
    union
    select 2,    '01-Jan-2012',  '31-Dec-2012'
    union
    select 3,    '01-Jan-2013', NULL
    go
    ---New values to be inserted (could be a procedure)
    declare @id int=4,@startdate date='01-Jan-2014',@enddate date=NULL
    
    If exists(select 1 from #temp where startDate=@startdate )
    		print 'cannot insert data'
    else
    BEGIN
    			insert into #temp values (@id,@startdate,@enddate)
    			update #temp set enddate= cast('31-Dec-'+ cast(DATEPART(year,'01-Jan-2013') as varchar(10))as date) where enddate is null
    			
    			
    END 

    Thursday, July 11, 2013 3:55 PM