Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    17

    Unanswered: Find start and end date strings

    Hello,

    I am having some probelms getting script to give me the first and last date a customer had an outstanding item. The data is as follows:

    Customer StartDate EndDate
    A 4/1/04 4/15/04
    A 4/15/04 5/1/04
    A 5/1/04 5/15/04
    A 5/16/04 5/28/04
    A 5/28/04 6/5/04
    B 5/1/04 5/15/04
    B 5/16/04 5/20/04

    The results I am looking for would be as follows:

    Customer A : Outstanding 4/1/04 - 5/15/04
    Customer A : Outstanding 5/16/04 - 5/28/04 (Theres a one day gap between prior sting, so this would be a new string )
    Customer B : OUtstanding 5/1/04 - 5/15/04
    Customer B : Outstanding 5/16/04 - 5/20/04

    I want to include any strings where the start of one item and the end of another are on the same day as one continuis string. Any ideas on how to do this??

    Thanks in advance!!

  2. #2
    Join Date
    Oct 2004
    Posts
    17
    bump..any one have ideas??

  3. #3
    Join Date
    Aug 2004
    Posts
    54
    How about something like this?

    Code:
    Declare @sCustomer as varchar(5)
    Declare @dtStartDate as datetime
    Declare @dtEndDate as Datetime
    Declare @sSaveCustomer as varchar(5)
    Declare @dtSaveStartDate as DateTime
    Declare @dtLinkDate as DateTime
    
    DECLARE Test CURSOR FOR
    	SELECT * FROM Test ORDER BY Customer, StartDate, EndDate
    
    CREATE table #tmp (Customer varchar(5), StartDate DateTime, EndDate DateTime)
    
    OPEN TEST
    
    Fetch next from Test into @sCustomer, @dtStartDate, @dtEndDate
    
    SET @sSaveCustomer = @sCustomer
    SET @dtSaveStartDate = @dtStartDate
    SET @dtLinkDate = @dtEndDate
    
    if (@@FETCH_STATUS = 0)
    BEGIN
    	Fetch next from Test into @sCustomer, @dtStartDate, @dtEndDate
    END
    
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    	if ((@sSaveCustomer = @sCustomer) AND (@dtStartDate = @dtLinkDate))
    	BEGIN
    		SET @dtLinkDate = @dtEndDate
    	END
    	ELSE
    	BEGIN
    		INSERT INTO #tmp VALUES(@sSaveCustomer, @dtSaveStartDate, @dtLinkDate)
    		SET @sSaveCustomer = @sCustomer
    		SET @dtSaveStartDate = @dtStartDate
    		SET @dtLinkDate = @dtEndDate
    	END
    
    	Fetch next from Test into @sCustomer, @dtStartDate, @dtEndDate		
    END
    
    INSERT INTO #tmp VALUES(@sSaveCustomer, @dtSaveStartDate, @dtLinkDate)
    
    select * from #tmp
    
    Drop table #tmp
    
    Close Test
    Deallocate Test
    Have some fun.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    A similar question was asked in this thread.

    http://www.dbforums.com/t1005647.html

  5. #5
    Join Date
    Oct 2004
    Posts
    17
    Thanks!! Both options worked!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •