To get data in the second format, based on one table and one column we need to write
scalar-valued function[
^]:
USE [DATABASE1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetNextStatusDate]
(
@MemID INT,
@cDate DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @Result DATETIME
SELECT @Result = MIN([DATE])
FROM [DATABASE1].[dbo].[TABLE1]
WHERE [MEMBER_ID] = @MemID AND [DATE] > @cDate
SET @RESULT = ISNULL(@RESULT,'2099-12-31')
RETURN @Result
END
Than, query like this:
SELECT [MEMBER_ID], [STATUS], [DATE] AS [START_DATE], [DATABASE1].[dbo].[GetNextStatusDate] ([MEMBER_ID], [DATE]) AS [END_DATE]
FROM [DATABASE1].[dbo].[MemberWork]
Results:
MEMBER_ID | STATUS | START_DATE | END_DATE |
---|
1 | A | 2012-01-01 | 2012-01-09 |
1 | B | 2012-01-09 | 2012-01-10 |
1 | A | 2012-01-10 | 2099-12-31 |
2 | A | 2012-01-01 | 2012-01-03 |
2 | C | 2012-01-03 | 2012-01-13 |
2 | A | 2012-01-13 | 2099-12-31 |
If you woukd like to change the final format of date, use different parameter for
CONVERT[
^] function.