Hi ,
I have the following table
| id | SHC | member | start_dt | end_dt |
| 1 | 100-002-304 | LOZANO M.D.,MARTHA E | 1/1/2004 0:00 | 8/22/2004 0:00 |
| 2 | 100-002-304 | LOZANO M.D.,MARTHA E | 8/23/2004 0:00 | 9/30/2004 0:00 |
| 3 | 100-002-304 | LOZANO M.D.,MARTHA E | 10/1/2004 0:00 | 10/31/2004 0:00 |
| 4 | 100-002-304 | WALTER M.D.,SHERI L | 4/17/2001 0:00 | 12/31/2003 0:00 |
| 5 | 100-002-304 | WALTER M.D.,SHERI L | 1/1/2004 0:00 | 8/22/2004 0:00 |
| 6 | 100-002-304 | WALTER M.D.,SHERI L | 8/23/2004 0:00 | 9/30/2004 0:00 |
| 7 | 100-002-304 | WALTER M.D.,SHERI L | 10/1/2004 0:00 | 10/31/2004 0:00 |
| 8 | 100-002-304 | WALTER M.D.,SHERI L | 11/1/2004 0:00 | NULL |
| 9 | 100-002-304 | ZZANTHONY D.O.,JEFFREY | 10/1/2000 0:00 | 4/16/2001 0:00 |
| 10 | 103-259-196 | A-OUTSIDE,PCP | 12/1/2000 0:00 | 8/31/2001 0:00 |
| 11 | 103-259-196 | A-OUTSIDE,PCP | 1/1/2005 0:00 | 1/1/2005 0:00 |
| 12 | 103-259-196 | DILL M.D.,DONALD M | 11/1/2000 0:00 | 11/30/2000 0:00 |
| 13 | 103-259-196 | DILL M.D.,DONALD M | 9/1/2001 0:00 | 8/30/2002 0:00 |
| 14 | 103-259-196 | DILL M.D.,DONALD M | 8/31/2002 0:00 | 8/31/2002 0:00 |
| 15 | 103-259-196 | DILL M.D.,DONALD M | 9/1/2002 0:00 | 10/30/2004 0:00 |
| 16 | 103-259-196 | DILL M.D.,DONALD M | 10/31/2004 0:00 | 10/31/2004 0:00 |
| 17 | 103-259-196 | DILL M.D.,DONALD M | 11/1/2004 0:00 | 12/30/2004 0:00 |
| 18 | 103-259-196 | DILL M.D.,DONALD M | 12/31/2004 0:00 | 12/31/2004 0:00 |
| 19 | 103-259-196 | DILL M.D.,DONALD M | 1/1/2005 0:00 | 1/1/2005 0:00 |
| 20 | 103-259-196 | DILL M.D.,DONALD M | 1/2/2005 0:00 | 7/30/2005 0:00 |
| 21 | 103-259-196 | DILL M.D.,DONALD M | 7/31/2005 0:00 | 7/31/2005 0:00 |
| 22 | 103-259-196 | DILL M.D.,DONALD M | 10/1/2000 0:00 | 10/31/2000 0:00 |
I need to create a query that will return the results like Below
| id | SHC | name | start_dt | end_dt |
| 1 | 100-002-304 | LOZANO M.D.,MARTHA E | 1/1/2004 0:00 | 10/31/2004 0:00 |
| 4 | 100-002-304 | WALTER M.D.,SHERI L | 4/17/2001 0:00 | 1/1/1900 0:00 |
| 9 | 100-002-304 | ZZANTHONY D.O.,JEFFREY | 10/1/2000 0:00 | 4/16/2001 0:00 |
| 10 | 103-259-196 | A-OUTSIDE,PCP | 12/1/2000 0:00 | 8/31/2001 0:00 |
| 11 | 103-259-196 | A-OUTSIDE,PCP | 1/1/2005 0:00 | 1/1/2005 0:00 |
| 22 | 103-259-196 | DILL M.D.,DONALD M | 10/1/2000 0:00 | 11/30/2000 0:00 |
| 13 | 103-259-196 | DILL M.D.,DONALD M | 9/1/2001 0:00 | 7/31/2005 0:00 |
Thanks in Advance
R
Try this query:
Select Min(ID) as ID, Min(SHC) as SHC, Member, Min(Start_dt) as Start_dt, Max(End_dt) as End_dt
From MemberTable
Group By Member
Best regards,
Sami Samir|||select *
from yourtable t
where id = (select top 1 id from yourtable x where x.SHC = t.SHC order by start_dt)|||
Here is a recursive Common Table Expression and SQL which produces the result you want:
WITH MemberList ([ID], SHC, Member, Start_dt, End_dt)
AS (
SELECT [ID], SHC, Member, Start_dt, End_dt
FROM dbo.MemberTable
UNION ALL
SELECT ML.[ID], ML.SHC, ML.Member, ML.Start_dt, MB.End_dt
FROM dbo.MemberTable MB
INNER JOIN MemberList ML
ON (MB.SHC = ML.SHC) AND
(MB.Member = ML.Member) AND
(MB.Start_dt = DATEADD(day, 1, ML.End_dt))
)
SELECT MB.[ID], MB.SHC, [Name] = MB.Member, MB.Start_dt,
CASE
WHEN (ML.End_dt = CAST('31DEC3000' AS DATETIME)) THEN
CAST('1JAN1900' AS DATETIME)
ELSE
ML.End_dt
END
FROM (
SELECT SHC, Member, MIN(Start_dt) AS Start_dt, End_dt
FROM (
SELECT SHC, Member, Start_dt,
MAX(COALESCE(End_dt, CAST('31DEC3000' AS DATETIME))) AS End_dt
FROM MemberList
GROUP BY SHC, Member, Start_dt
) GotEnd
GROUP BY SHC, Member, End_dt
) ML
INNER JOIN dbo.MemberTable MB
ON (MB.SHC = ML.SHC) AND
(MB.Member = ML.Member) AND
(MB.Start_dt =ML.Start_dt)
ORDER BY MB.SHC, MB.Member, MB.Start_dt
I have given the table the name MemberTable as I did not know what name you had used. I think I got all the column names correct from your data.
The CTE itself (yellow background) produces a record set which has a record for each possible chain (starting and ending date with a continuous chain of records (where start = end + 1 day for the same SHC and Member).
The inner query (light blue background) finds the highest/maximum end date for each start date (the nulls are converted to 31 Dec 3000 at this point so they always are considered the last date), by SHC and Member.
The next query out (light grey background) finds the minimum start date for each of the end dates found before (again by SHC and Member). This means we now have the desired list of time spans.
The final outer query simply joins back to the source table to find the correct ID, converts any 31 Dec 3000 dates to 1 Jan 1900 dates, and orders by SHC, Member and Start Date.
There are a couple of assumptions about the source data:
There are no overlaps in time ranges for the same SHC and Member (not sure how this would affect the output).|||
Thanks Samir
but the result is not right. THat is the first thought, but not that easy.,. if you look closely to the results you will see why..
R
|||
Thank you KH
But the result is not right. THat is the first thought, but not that easy.,. if you look closely to the results you will see why..
R
|||
Thank you Dhericean
I think that you are heading on the right direction, but I cannot completely tell you because the script is giving me an error at the WITH clause..
Any idea,,
Thank you in advance..
R
|||
how about something like:
select min(id), SHC, name, min(isnull(start_dt, getdate())), max(isnull(end_dt, getdate()))
from [table name]
group by SHC, name
Is this what you need? Does it need to say 1/1/1900? If it does, how about something like:
select min(id), SHC, name, start_dt, replace(convert(varchar(256), max(isnull(end_dt, getdate()))), convert(varchar(256), getdate(), '1/1/1900')
from [table name]
group by SHC, name
|||
| 1 | 100-002-304 | LOZANO M.D.,MARTHA E | 1/1/2004 0:00 | 10/31/2004 0:00 |
| 4 | 100-002-304 | WALTER M.D.,SHERI L | 4/17/2001 0:00 | 1/1/1900 0:00 |
| 9 | 100-002-304 | ZZANTHONY D.O.,JEFFREY | 10/1/2000 0:00 | 4/16/2001 0:00 |
| 10 | 103-259-196 | A-OUTSIDE,PCP | 12/1/2000 0:00 | 8/31/2001 0:00 |
| 11 | 103-259-196 | A-OUTSIDE,PCP | 1/1/2005 0:00 | 1/1/2005 0:00 |
| 22 | 103-259-196 | DILL M.D.,DONALD M | 10/1/2000 0:00 | 11/30/2000 0:00 |
| 13 | 103-259-196 | DILL M.D.,DONALD M | 9/1/2001 0:00 | 7/31/2005 0:00 |
Above is what it should be
below are your results
| ID | SHC | MEMBER | START_DT | END_T |
| 10 | 103-259-196 | A-OUTSIDE,PCP | 12/1/2000 0:00 | 1/1/2005 0:00 |
| 12 | 103-259-196 | DILL M.D.,DONALD M | 10/1/2000 0:00 | 7/31/2005 0:00 |
| 1 | 100-002-304 | LOZANO M.D.,MARTHA E | 1/1/2004 0:00 | 10/31/2004 0:00 |
| 4 | 100-002-304 | WALTER M.D.,SHERI L | 4/17/2001 0:00 | 3/19/2007 14:03 |
| 9 | 100-002-304 | ZZANTHONY D.O.,JEFFREY | 10/1/2000 0:00 | 4/16/2001 0:00 |
I wish it was that simple though...Thank you in advance. I really appreciate your help...
|||
Your looking for windows but you are counting a START_DT that is chronologically the next day as an END_DT as being within the same window.
Something like that will need a procedure in t-sql, or some other langauge to figure out.
If you can live with it, just group it by the START_DT...
|||
Possibly,
My co-worker has done using MS Access queries...
I am sure we can do this in SQL,
The objective is to see the the time which member was enrolled continuously.For that, the end dt is start_dt-1 of the next line.
if that is not true than another row would start for the same member..
Thanks
R
|||
Instead of a table like this, please provide the DDL for your table and inserts or we are just going to keep guessing the query without getting your result. Your result expectation is excellent, but it is tedious work to do all of the DDL and DML to build the data ourselves when you have so much.
Thanks!
|||and most importantly PLEASE EXPLAIN THE LOGIC OR HOW THE RESULT IS OBTAIN ? ? ? ?|||
I need to see the error.
I put your data into a table and ran the SQL I gave you against it. The only thing I changed was the table name and a couple of columns (would really suggest not using things like ID (and Value, Name, Member etc.) as column names (avoid reserved words or things that look that they might become reserved words). Here is the complete Repro SQL set (table create, populate, and CTE query).
-- Create the test table
CREATE TABLE [dbo].[CTE_Test](
[Rec_ID] [int] NOT NULL,
[SHC] [nchar](11) COLLATE Latin1_General_CI_AS NOT NULL,
[MemberName] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[DATE_Start] [datetime] NULL,
[DATE_End] [datetime] NULL,
CONSTRAINT [PK_CTE_Test] PRIMARY KEY CLUSTERED
([Rec_ID] ASC)
)
-- Insert the test data
INSERT INTO [dbo].[CTE_Test]
VALUES(1, '100-002-304', 'LOZANO', 'Jan 1 2004', 'Aug 22 2004')
INSERT INTO [dbo].[CTE_Test]
VALUES(2, '100-002-304', 'LOZANO', 'Aug 23 2004', 'Sep 30 2004')
INSERT INTO [dbo].[CTE_Test]
VALUES(3, '100-002-304', 'LOZANO', 'Oct 1 2004', 'Oct 31 2004')
INSERT INTO [dbo].[CTE_Test]
VALUES(4, '100-002-304', 'WALTER', 'Apr 17 2001', 'Dec 31 2003')
INSERT INTO [dbo].[CTE_Test]
VALUES(5, '100-002-304', 'WALTER', 'Jan 1 2004', 'Aug 22 2004')
INSERT INTO [dbo].[CTE_Test]
VALUES(6, '100-002-304', 'WALTER', 'Aug 23 2004', 'Sep 30 2004')
INSERT INTO [dbo].[CTE_Test]
VALUES(7, '100-002-304', 'WALTER', 'Oct 1 2004', 'Oct 31 2004')
INSERT INTO [dbo].[CTE_Test]
VALUES(8, '100-002-304', 'WALTER', 'Nov 1 2004', NULL)
INSERT INTO [dbo].[CTE_Test]
VALUES(9, '100-002-304', 'ZZANTHONY', 'Oct 1 2000', 'Apr 16 2001')
INSERT INTO [dbo].[CTE_Test]
VALUES(10, '103-259-196', 'A-OUTSIDE', 'Dec 1 2000', 'Aug 31 2000')
INSERT INTO [dbo].[CTE_Test]
VALUES(11, '103-259-196', 'A-OUTSIDE', 'Jan 1 2005', 'Jan 1 2005')
INSERT INTO [dbo].[CTE_Test]
VALUES(12, '103-259-196', 'DILL', 'Nov 1 2000', 'Nov 30 2000')
INSERT INTO [dbo].[CTE_Test]
VALUES(13, '103-259-196', 'DILL', 'Sep 1 2001', 'Aug 30 2002')
INSERT INTO [dbo].[CTE_Test]
VALUES(14, '103-259-196', 'DILL', 'Aug 31 2002', 'Aug 31 2002')
INSERT INTO [dbo].[CTE_Test]
VALUES(15, '103-259-196', 'DILL', 'Sep 1 2002', 'Oct 30 2004')
INSERT INTO [dbo].[CTE_Test]
VALUES(16, '103-259-196', 'DILL', 'Oct 31 2004', 'Oct 31 2004')
INSERT INTO [dbo].[CTE_Test]
VALUES(17, '103-259-196', 'DILL', 'Nov 1 2004', 'Dec 30 2004')
INSERT INTO [dbo].[CTE_Test]
VALUES(18, '103-259-196', 'DILL', 'Dec 31 2004', 'Dec 31 2004')
INSERT INTO [dbo].[CTE_Test]
VALUES(19, '103-259-196', 'DILL', 'Jan 1 2005', 'Jan 1 2005')
INSERT INTO [dbo].[CTE_Test]
VALUES(20, '103-259-196', 'DILL', 'Jan 2 2005', 'Jul 30 2005')
INSERT INTO [dbo].[CTE_Test]
VALUES(21, '103-259-196', 'DILL', 'Jul 31 2005', 'Jul 31 2005')
INSERT INTO [dbo].[CTE_Test]
VALUES(22, '103-259-196', 'DILL', 'Oct 1 2000', 'Oct 31 2000')
-- SELECT * FROM CTE_Test ORDER BY Rec_ID
-- The sample CTE query
WITH MemberList (Rec_ID, SHC, MemberName, DATE_Start, DATE_End)
AS (
SELECT Rec_ID, SHC, MemberName, DATE_Start, DATE_End
FROM dbo.CTE_Test
UNION ALL
SELECT ML.Rec_ID, ML.SHC, ML.MemberName, ML.DATE_Start, MB.DATE_End
FROM dbo.CTE_Test MB
INNER JOIN MemberList ML
ON (MB.SHC = ML.SHC) AND
(MB.MemberName = ML.MemberName) AND
(MB.DATE_Start = DATEADD(day, 1, ML.DATE_End))
)
SELECT MB.Rec_ID, MB.SHC, MB.MemberName, MB.DATE_Start,
CASE
WHEN (ML.DATE_End = CAST('31DEC3000' AS DATETIME)) THEN
CAST('1JAN1900' AS DATETIME)
ELSE
ML.DATE_End
END
FROM (
SELECT SHC, MemberName, MIN(DATE_Start) AS DATE_Start, DATE_End
FROM (
SELECT SHC, MemberName, DATE_Start,
MAX(COALESCE(DATE_End, CAST('31DEC3000' AS DATETIME))) AS DATE_End
FROM MemberList
GROUP BY SHC, MemberName, DATE_Start
) GotEnd
GROUP BY SHC, MemberName, DATE_End
) ML
INNER JOIN dbo.CTE_Test MB
ON (MB.SHC = ML.SHC) AND
(MB.MemberName = ML.MemberName) AND
(MB.DATE_Start =ML.DATE_Start)
ORDER BY MB.SHC, MB.MemberName, MB.DATE_Start
This should work as advertised (ran it on my system), and it produced the 7 records you requested. If there are any problem please include any error messages.
This is a basic CTE and could be refined. One example is only including records in the anchor query where there is not another record (for the same SHC, Name) with an EndDate equal to the Start date of the included record +1 day.
Just occurred to me - This functionality will only work if you have SQL Server 2005 as Common Table Expressions are a new feature. If not then it will be a much more complex proposition.
|||
Thank you very much for your reply. I am sure it works. and you are also right about the SQL 2005 script.
I am using SQL 2000...
R
