derriving record start and end dates from a single date field

Question

0.00/5 (No votes)

See more:

Hi,

I have a table on SQL Server that looks like this:

MEMBER_ID , STATUS , DATE
1 , A , 1/1/2012
1 , B , 9/1/2012
1 , A , 10/1/2012
2 , A , 1/1/2012
2 , C , 3/1/2012
2 , A , 13/1/2012

Each member only has ONE status recorded per day, it is only recorded when it changes.

I need to be able to determine the Members with any given status
(eg 'A') on a particular day. (remember that I don't have a status record for each day, just the days when it has changed)

Can I query the table directly? Should I create a view something like this:
MEMBER_ID , STATUS , START DATE , END DATE ,
1 , A , 1/1/2012 , 9/1/2012
1 , B , 9/1/2012 , 10/1/2012
1 , A , 10/1/2012 , 31/12/2999 (or blank)
2 , A , 1/1/2012 , 3/1/2999
2 , C , 3/1/2012 , 13/1/2999
2 , A , 13/1/2012 , 31/12/2999 (or Blank)

How do I transform the data from first format into the second above? (using just a query or stored procedure)

Help much appreciated.

Posted 15-May-12 21:04pm

FrancisOC

Add a Solution

Comments

Maciej Los 16-May-12 11:37am

To answer this question we need to know the relations between fields. Based on what codition do we know which date is starting date and which is ending date? Is this a [Status] {A, B, C}?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

VJ Reddy · Answer 1 · 2012-05-15T22:46:00

If I understood the question correctly then I think one record per each member has to be picked up whose satus is as specified and the date is either less than or equal to the entered date. If it is so then I think the following query can be used.

SQL

SELECT MEMBER_ID, MAX(STATUS), MAX([DATE]) FROM MEMBERSTATUS
WHERE [DATE] <= '2012/01/10' AND STATUS = 'A'
GROUP BY MEMBER_ID
--Output for the given data
--1	A	2012-01-10
--2	A	2012-01-01

This query groups the records by MEMBER_ID where the Date is less than or equal to the given date then it picks up the record corresponding the maximum date.

Maciej Los · Answer 2 · 2012-05-16T05:51:00

To get data in the second format, based on one table and one column we need to write scalar-valued function[^]:

SQL

USE [DATABASE1]
GO
/****** Object:  UserDefinedFunction [dbo].[GetNextStatusDate]    Script Date: 05/17/2012 16:21:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetNextStatusDate] 
(
	-- parameters:
	@MemID INT,
	@cDate DATETIME
)
RETURNS DATETIME
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result DATETIME

	-- Add the T-SQL statements to compute the return value here
	SELECT @Result = MIN([DATE])
	FROM [DATABASE1].[dbo].[TABLE1]
	WHERE [MEMBER_ID] = @MemID AND [DATE] > @cDate

	SET @RESULT = ISNULL(@RESULT,'2099-12-31')
		
	-- Return the result of the function
	RETURN @Result

END

Than, query like this:

SQL

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.

derriving record start and end dates from a single date field

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0