November 28, 2005 at 4:01 am
Hello,
I am working on a project for a company that needs a report. The report is using a table with the following data structure:
TempTable
patient varchar(10)
datebegin datetime
dateend datetime
This is using SQL Server 2000. The data would be as follows:
Patient DateBegin DateEnd
A 1/1/05 1/31/05
A 4/1/05 6/30/05
B 2/1/05 2/28/05
B 3/1/05 5/27/05
C 5/2/05 7/1/05
C 7/2/05 8/15/05
Somehow I need to write a query that returns:
Patient DateBegin DateEnd
A 1/1/05 1/31/05
A 4/1/05 6/30/05
B 2/1/05 5/27/05
C 5/2/05 8/15/05
I should be able to group on Patient and return min(datebegin) and max(dateEnd) for that patient.
The catch is if the datebegin in the second row is not the day following the dateend in the first row, then the query needs to return a record for that row. We're dealing with policies expiring and renewing.
Currently, this is being done with a Cursor and looking at each record, putting the data in variables and then fetch next record and looking at it. This is not the way I want to go on a production box with over 10 million records.
Is there a set based solution to this? Is the best alternative a While Loop?
Thanks for your help on this one!! There is no identity field on this table. I am only a developer and can't change the data structure.
Tony
Things will work out. Get back up, change some parameters and recode.
November 28, 2005 at 6:13 am
SET NOCOUNT ON
DECLARE @TempTable TABLE
(
patient varchar(10),
datebegin datetime,
dateend datetime
)
INSERT @TempTable
SELECT 'A', '1/1/05', '1/31/05' UNION
SELECT 'A', '4/1/05', '6/30/05' UNION
SELECT 'B', '2/1/05', '2/28/05' UNION
SELECT 'B', '3/1/05', '5/27/05' UNION
SELECT 'C', '5/2/05', '7/1/05' UNION
SELECT 'C', '7/2/05', '8/15/05'
SELECT patient, MIN(datebegin) datebegin, MAX(dateend) dateend
FROM
@TempTable
GROUP BY patient
--OR
SELECT patient, CONVERT(VARCHAR, MIN(datebegin), 101) datebegin, CONVERT(VARCHAR, MAX(dateend), 101) dateend
FROM
@TempTable
GROUP BY patient
Regards,
gova
November 28, 2005 at 6:53 am
I appreciate the fast response.
However, this does not answer the question. These are methods I had already considered.
The problem is that you will get one record per patient for the minimum begindate and maximum endDate.
There is a clause that if the next begindate is greater than one day, that record should show. If the next begindte is one day, then you can group the records.
So for my example you should come up with two records for Patient A.
Any suggestions?
Tony
Things will work out. Get back up, change some parameters and recode.
November 29, 2005 at 4:38 am
Thanks govinn for the DDL, here is one way:
SET NOCOUNT ON
DECLARE @TempTable TABLE
(
patient varchar(10),
datebegin datetime,
dateend datetime
)
DECLARE @Results TABLE
(
patient varchar(10),
datebegin datetime,
dateend datetime
)
INSERT @TempTable
SELECT 'A', '1/1/05', '1/31/05' UNION
SELECT 'A', '4/1/05', '6/30/05' UNION
SELECT 'B', '2/1/05', '2/28/05' UNION
SELECT 'B', '3/1/05', '5/27/05' UNION
SELECT 'C', '5/2/05', '7/1/05' UNION
SELECT 'C', '7/2/05', '8/15/05'
INSERT @Results
SELECT t.patient, t.datebegin, t.dateend
FROM @TempTable AS t
LEFT JOIN @TempTable AS t1 ON t.patient = t1.patient
AND DATEADD(d,1,t.dateend) = t1.datebegin
WHERE t1.patient IS NULL
UPDATE @Results
SET datebegin = u.datebegin
FROM @Results r
INNER JOIN (SELECT t.patient, MIN(t.datebegin) AS datebegin
FROM @TempTable AS t
INNER JOIN @TempTable AS t1 ON t.patient = t1.patient
AND DATEADD(d,1,t.dateend) = t1.datebegin
GROUP BY t.patient) AS u ON r.patient = u.patient
SELECT * FROM @Results
Andy
January 6, 2006 at 7:23 am
David,
Thanks for your help. I was taken off that project for awhile. Now I am back on the project. Life of a consultant.
I tried your solution and it works great for the dataset posted.
However, we never know how many user records a user will have. So I added a few more user records for Member A.
INSERT @TempTable
SELECT 'A', '1/1/05', '1/31/05' UNION
SELECT 'A', '2/1/05', '2/20/05' UNION
SELECT 'A', '2/21/05', '3/15/05' UNION
SELECT 'A', '4/1/05', '6/30/05' UNION
SELECT 'B', '2/1/05', '2/28/05' UNION
SELECT 'B', '3/1/05', '5/27/05' UNION
SELECT 'C', '5/2/05', '7/1/05' UNION
SELECT 'C', '7/2/05', '8/15/05'
This caused the query to not get the correct results.
I got:
Member Date Begin Date End
A 2005-01-01 00:00:00.000 2005-01-31 00:00:00.000
A 2005-01-01 00:00:00.000 2005-06-30 00:00:00.000
B 2005-02-01 00:00:00.000 2005-05-27 00:00:00.000
C 2005-05-02 00:00:00.000 2005-08-15 00:00:00.000
It should read (based on data above):
A 2005-01-01 00:00:00.000 2005-03-15 00:00:00.000
A 2005-04-01 00:00:00.000 2005-06-30 00:00:00.000
B 2005-02-01 00:00:00.000 2005-05-27 00:00:00.000
C 2005-05-02 00:00:00.000 2005-08-15 00:00:00.000
Any suggestions? Thanks for the help.
Things will work out. Get back up, change some parameters and recode.
January 6, 2006 at 9:00 am
Does this get you what you are looking for?
SET NOCOUNT ON
DECLARE @TempTable TABLE
(
patient varchar(10),
datebegin datetime,
dateend datetime
)
INSERT @TempTable
SELECT 'A', '1/1/05', '1/31/05' UNION
SELECT 'A', '2/1/05', '2/20/05' UNION
SELECT 'A', '2/21/05', '3/15/05' UNION
SELECT 'A', '3/18/05', '3/21/05' UNION
SELECT 'A', '4/1/05', '6/30/05' UNION
SELECT 'B', '1/1/05', '1/15/05' UNION
SELECT 'B', '2/1/05', '2/28/05' UNION
SELECT 'B', '3/1/05', '5/27/05' UNION
SELECT 'C', '5/2/05', '7/1/05' UNION
SELECT 'C', '7/2/05', '8/15/05'
select a.patient, min(a.datebegin), max(b.dateend)
from @temptable b where b.patient = a.patient and b.datebegin = dateadd(d, -1, a.dateend))
from @temptable a inner join @temptable b on b.patient = a.patient and b.datebegin = dateadd(d, 1, a.dateend)
group by a.patient
union
select patient, datebegin, dateend
from @temptable a
where not exists (select 1 from @temptable b where b.patient = a.patient and (b.datebegin = dateadd(d, 1, a.dateend) or b.dateend = dateadd(d, -1, a.datebegin)))
January 6, 2006 at 9:21 am
Chris,
Yes it does!!!!!!!!
Thanks so much. Now I just need to go through query to understand it and see how you came up with it.
Thanks again.
Things will work out. Get back up, change some parameters and recode.
January 6, 2006 at 9:37 am
Here is the basic thought. There are two types of rows. Those with another one that starts right after and needs to be combined in the results. The other are rows that need to be in the results just like they are.
So we use a union to combine the groups. The first sql statement takes care of combining the rows that need to be merged. The second query takes care of the rows that are fine the way they are. Run either half of the union to see how this query really works.
There is one potential issue with this query. If you have a row that is within the dates of another row for the same patient, you get that row in the results. This case wasn't in your requirements, so I didn't code for it.
Chris
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply