ansaurus

Question

How do I get the desired result in T-SQL like ....

Answer 1

A:

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.

krock 2010-06-12 09:01:41

ya but what about the result of UserId 2 ??

Azhar 2010-06-12 09:12:10

@Azhar I would guess that you need something in your WHERE clause, possibly looking at columns that are not in the select list. This WHERE clause is most likely filtering out the other 2 rows where UseriD = 2. I think we need to know more about what you are trying to achieve and data in other columns/tables that this query needs to look at.

krock 2010-06-12 09:34:13

no we have this much data and we have to filter it accordingly

Azhar 2010-06-12 10:24:38

Well you could use WHERE InDate < '3/3/2010' OR InDate > '3/4/2010' and that may work but may not be what you need. Why do you not to select the other 2 UseriD = 2 rows?

krock 2010-06-12 10:42:43

Actually its the requirement to get the result of report with this date pattern and there could be other thousands of records like this.

Azhar 2010-06-12 12:20:54

But what pattern? Why do you need to have 3/29/2010 in the result but not 3/3/2010 or 3/4/2010?

krock 2010-06-12 23:15:47

Answer 2

A:

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.

Raj 2010-06-12 09:02:58

ya but what about the result of UserId 2 .. it would not be handle by this way :(

Azhar 2010-06-12 09:22:00

Answer 3

+1 A:

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'

Fiza 2010-06-13 07:39:52

ansaurus

tags:

views:

answers:

How do I get the desired result in T-SQL like ....

related questions