How do I get the desired result in T-SQL like [closed]

Closed. This question needs to be more focused. It is not currently accepting answers.

---

Want to improve this question? Update the question so it focuses on one problem only by editing this post.

Closed 8 years ago.

Improve this question

How do I开发者_JS百科 get the desired result in T-SQL like .... like I have a Record like

UseriD    InDate    outDate
1         3/12/2010   3/12/2010
1         3/12/2010   3/13/2010
1         3/19/2010   3/30/2010   
2         3/2/2010    3/3/2010  
2         3/3/2010    3/4/2010
2         3/4/2010    3/29/2010 
3         2/2/2010    2/28/2010

so our result must be like this

UseriD    InDate    outDate
1         3/12/2010   3/13/2010
1         3/19/2010   3/30/2010   
2         3/2/2010    3/29/2010 
3         2/2/2010    2/28/2010

How can we do this is T-Sql

---

I would like to share my solution to help anyone who comes accross the same problem:

/****** Object:  StoredProcedure [dbo].[MergeSeqDates]    Script Date: 06/12/2010 18:18:26 ******/
/************************************************************************************************
THIS STORED PROCEDURE CAN BE USED FOR AN INPUT TABLE LIKE THE FOLLOWING 
tablename: [_dateSeq]
UserId  InDate                      OutDate id          Record number
1       3/12/2010 12:00:00 AM   3/12/2010 12:00:00 AM   1 
1       3/12/2010 12:00:00 AM   3/13/2010 12:00:00 AM   2 
1       3/19/2010 12:00:00 AM   3/30/2010 12:00:00 AM   3 
2       3/2/2010 12:00:00 AM    3/3/2010 12:00:00 AM    4 
2       3/3/2010 12:00:00 AM    3/4/2010 12:00:00 AM    5 
2       3/4/2010 12:00:00 AM    3/9/2010 12:00:00 AM    6 
3       2/2/2010 12:00:00 AM    2/28/2010 12:00:00 AM   7 


TO GIVE AN OUTPUT TABLE LIKE:
tablename: [mergeddateseq]
UserId  InDate                  OutDate                 Unique_Id 
1       3/12/2010 12:00:00 AM   3/13/2010 12:00:00 AM   1
1       3/19/2010 12:00:00 AM   3/30/2010 12:00:00 AM   2
2       3/2/2010 12:00:00 AM    3/9/2010 12:00:00 AM    3
3       2/2/2010 12:00:00 AM    2/28/2010 12:00:00 AM   4


Table Structures used:
(1)
CREATE TABLE [dbo].[_dateSeq](
    [UserId] [bigint] NULL,
    [InDate] [datetime] NULL,
    [OutDate] [datetime] NULL,
    [id] [bigint] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK__dateSeq] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



(2)
CREATE TABLE [dbo].[MergedDateSeq](
    [Unique_Id] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [bigint] NULL,
    [InDate] [datetime] NULL,
    [OutDate] [datetime] NULL,
 CONSTRAINT [PK_MergedDateSeq] PRIMARY KEY CLUSTERED 
(
    [Unique_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


************************************************************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER Procedure [dbo].[MergeSeqDates]
(
@StartDate datetime,
@EndDate datetime
)
AS
BEGIN
/*Clear the output table before new data is put into it*/
    DROP TABLE mergeddateseq;
    CREATE TABLE [dbo].[MergedDateSeq](
    [Unique_Id] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [bigint] NULL,
    [InDate] [datetime] NULL,
    [OutDate] [datetime] NULL,
 CONSTRAINT [PK_MergedDateSeq] PRIMARY KEY CLUSTERED 
(
    [Unique_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

/*For every record in the original table, check if the user's next start date is consequent to the user's previous end date*/
/*If yes, update the earlier record*/
/*If not, add a new record record*/

DECLARE rec_cursor CURSOR FOR
SELECT UserId,InDate,OutDate FROM [_dateSeq] WHERE InDate>=@StartDate and ((OutDate<=@EndDate) or (OutDate is null)) order by InDate;


OPEN rec_cursor

Declare @DateFrom DateTime
Declare @DateTo DateTime
Declare @CardId bigint

Declare @mrgDateFrom DateTime
Declare @mrgDateTo DateTime
Declare @mrgCardId bigint
Declare @Unque_Id bigint

FETCH NEXT FROM rec_cursor
INTO @CardId,@DateFrom, @DateTo

WHILE @@FETCH_STATUS = 0
BEGIN
    set @Unque_Id=0;
    SELECT @Unque_Id=Unique_Id,@mrgCardId=UserId,@mrgDateFrom=InDate,@mrgDateTo=OutDate FROM mergeddateseq where UserId=@CardId order by OutDate desc;
    if @@rowcount>0
        BEGIN
        --check dates
        --update record if date is same
        if (@DateFrom=@mrgDateTo)
            Update mergeddateseq set OutDate=@DateTo where Unique_Id=@Unque_Id;
        --update record if dates are sequential
        else if (@DateFrom=DATEADD(day,+1,@mrgDateTo))
            Update mergeddateseq set OutDate=@DateTo where Unique_Id=@Unque_Id;
        else
        --insert new record
        Insert into mergeddateseq (UserId,InDate,OutDate) values (@CardId,@DateFrom,@DateTo);
        END
    else
        BEGIN
        --insert new record
        Insert into mergeddateseq (UserId,InDate,OutDate) values (@CardId,@DateFrom,@DateTo);
        END

    FETCH NEXT FROM rec_cursor
    INTO @CardId,@DateFrom, @DateTo
END

CLOSE rec_cursor
DEALLOCATE rec_cursor

    Select * from mergeddateseq;
END

--exec [MergeSeqDates] @StartDate='1-1-2010', @EndDate='1-1-2011'

---

Probably a two step process. First create a temporary work table with UserId, InDat, OutDate. Then insert worktable(userid, inDate) select UserId, InDate from InputTable group by UserId, InDate

Then the next step would be

update worktable set outdate = t.outdate from (select userid, OutDate from InputTable group by userid, outDate) as t where UserId = t.UserId

I have't tried the last statement but you should be able to create a virtual table t on the fly and update the main table. If it doesn't work then create a second worktable for the OutDate per user per date and then create a join between the two work tables.

---

Maybe something like this:

SELECT UseriD, InDate, MAX(outDate)
FROM TABLE
GROUP BY UseriD, InDate
ORDER BY UseriD, InDate

this will select all the unique UseriD, InDate combinations and display the latest outDate matching that combination. e.g. there are 2 rows that have UseriD = 1 and InDate = 3/12/2010, but the outDates are 3/12/2010 and 3/13/2010 so max(outDate) for that UseriD, InDate grouping will show 3/13/2010.