cleaning different formats

I have a column "currentDate" with approx 4 million rows.
The rows are composed of dates , but with a variety of formats.
For example:
"14-05-2005 14:56:32"
"20/07/2005 10:26:43"
"2006-03-26 11:21:42"
I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
hh:mm:ss, and then convert the column into a datetime
The problem I have is that I am using DATEPART , which is fine , except that
I am losing the leading zero.
Taking "02/03/2005 10:26:43" as an example ,
when I do the following:
CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to "02"
,
How can I retain the leading "0" , for any DATEPART.?>I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
Bravo! But that is very far from a standard format!
Why wasn't this a datetime in the first place? What application is storing
all these different formats? Are you allowing end users to just enter
whatever format they want?
Assuming these are the only three formats present, this might give you some
ideas:
SELECT CONVERT(CHAR(10), d, 120)+'T'+CONVERT(CHAR(8), d, 108)
FROM
(
SELECT d = CASE WHEN ISDate(d)=1 THEN CONVERT(DATETIME, d)
ELSE CONVERT(DATETIME, d, 103) END
FROM
(
SELECT d = '14-05-2005 14:56:32'
UNION ALL SELECT '20/07/2005 10:26:43'
UNION ALL SELECT '2006-03-26 11:21:42'
) a
) b
However, I am betting there are twenty other formats you're not mentioning.
A|||You may try this:
select right('0' + CAST(DATEPART(dd, myday) AS VARCHAR(2)), 2)
Perayu
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:dtv3e8$kdf$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
> hh:mm:ss, and then convert the column into a datetime
> The problem I have is that I am using DATEPART , which is fine , except
> that
> I am losing the leading zero.
> Taking "02/03/2005 10:26:43" as an example ,
> when I do the following:
> CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to
> "02"
> ,
> How can I retain the leading "0" , for any DATEPART.?
>|||Jack Vamvas (delete_this_bit_jack@.ciquery.com_delete) writes:
> I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e
> dd-mm-yyyy hh:mm:ss, and then convert the column into a datetime The
> problem I have is that I am using DATEPART , which is fine , except that
> I am losing the leading zero.
> Taking "02/03/2005 10:26:43" as an example ,
> when I do the following:
> CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to
> "02" ,
> How can I retain the leading "0" , for any DATEPART.?
There are a couple of variations on that theme, but it does not seem to
address your real issue anyway.
Obviously you have dateformat of dmy, in which case the format 2006-03-26
is not likely to convert to datetime.
I would suggest that you add a new column to the table, nullable. Then
you would do something like:
UPDATE tbl
SET newcol = convert(datetime, oldcol)
WHERE isdate(oldcol) = 1
go
SET DATEFORMAT ymd
go
UPDATE tbl
SET newcol = convert(datetime, oldcol)
WHERE isdate(oldcol) = 1
AND newcol IS NULL
go
SELECT * FROM tbl WHERE newcol IS NULL
-- Manuallly fix the rest?
go
ALTER TABLE tbl DROP oldcol
go
-- If column should not permit NULL.
ALTER TABLE col ALTER newcol datetime NOT NULL
One thing you would have to make an extra check for are completely far-out
date formats like MM/DD/YY (yes, there are odd corners of the world where
they use this). Not talking of a string like 05/03/02 that has a number of
interpretations.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The Convert function will do the conversion to DateTime for you. However,
depending on the format, you will need to specify the Style parameter.
Lookup the Convert function in SQL Server Books Online. For example:
-- Italian dd-mm-yy
print convert(datetime,'14-05-2005 14:56:32',105)
May 14 2005 2:56PM
print convert(datetime,'14/05/2005 14:56:32',105)
May 14 2005 2:56PM
-- ANSI
print convert(datetime,'2005-05-14 14:56:32',102)
May 14 2005 2:56PM
Also, going forward, you will need to constrain user input into the
application to a consistent format.
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:dtv3e8$kdf$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>I have a column "currentDate" with approx 4 million rows.
> The rows are composed of dates , but with a variety of formats.
> For example:
> "14-05-2005 14:56:32"
> "20/07/2005 10:26:43"
> "2006-03-26 11:21:42"
> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
> hh:mm:ss, and then convert the column into a datetime
> The problem I have is that I am using DATEPART , which is fine , except
> that
> I am losing the leading zero.
> Taking "02/03/2005 10:26:43" as an example ,
> when I do the following:
> CAST(DATEPART(dd,myday) AS VARCHAR(2)) the result = "2" as opposed to
> "02"
> ,
> How can I retain the leading "0" , for any DATEPART.?
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#yCoM86OGHA.2912@.tk2msftngp13.phx.gbl...
dd-mm-yyyy
> Bravo! But that is very far from a standard format!
> Why wasn't this a datetime in the first place? What application is
storing
> all these different formats? Are you allowing end users to just enter
> whatever format they want?
> Assuming these are the only three formats present, this might give you
some
> ideas:
> SELECT CONVERT(CHAR(10), d, 120)+'T'+CONVERT(CHAR(8), d, 108)
> FROM
> (
> SELECT d = CASE WHEN ISDate(d)=1 THEN CONVERT(DATETIME, d)
> ELSE CONVERT(DATETIME, d, 103) END
> FROM
> (
> SELECT d = '14-05-2005 14:56:32'
> UNION ALL SELECT '20/07/2005 10:26:43'
> UNION ALL SELECT '2006-03-26 11:21:42'
> ) a
> ) b
> However, I am betting there are twenty other formats you're not
mentioning.
> A
>
>
>
Thanks for the response.
Just to clarify , this data is inherited from a client . It is historical
data , and the different formats represent different pahases of their data
recording.
They dumped everything into a varchar column and now are requesting
different queries based on the dates.Hence the need to sort this problem
out.
The 3 formats I've presented are the only formats. The code you've presented
has givem me some ideas.|||> I've decided to convert all the rows into a standard format i.e dd-mm-yyyy
> hh:mm:ss, and then convert the column into a datetime
I think you can eliminate this intermediate step. Just convert from the
existing values into DateTime.|||Thanks
That sorted the problem out
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:uZ#VY86OGHA.1132@.TK2MSFTNGP10.phx.gbl...
> You may try this:
> select right('0' + CAST(DATEPART(dd, myday) AS VARCHAR(2)), 2)
> Perayu
> "Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
> news:dtv3e8$kdf$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
dd-mm-yyyy
>|||I was having a similar issue and this fixed my problem as well! Good tip!

Checking for NULL dates in Report

Hi,
I have a date field in my report dataset. I want to check for NULL date
before displaying it in the report. I have tried using the exp
=IIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day(Fields!Date.Value)&"/"&Year(Fields!Date.Value)).
But this doesnt work. I see #error in my report where ever date value is
NULL or blank. I have also tried using Len and IsDate functions but they too
dont seem to work. Can anyone help me ?
Thanks,
RKHi,
Tue and False part are evaluated in a IIF function. That's why you have an
#error.
You can use Visibility property.
--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"ramakrishna" <ramakrishna@.translogicsys.com> wrote in message
news:O2Cm3S%23ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a date field in my report dataset. I want to check for NULL date
> before displaying it in the report. I have tried using the exp
> =IIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day(Fields!Date.Value)&"/"&Year(Fields!Date.Value)).
> But this doesnt work. I see #error in my report where ever date value is
> NULL or blank. I have also tried using Len and IsDate functions but they
> too dont seem to work. Can anyone help me ?
> Thanks,
> RK
>
>
>|||Hi,
Thanks for the reply. I want to display the date only when its not null. So
I have used the IIF expression. But the checking using the function
IsNothing or IsDate doesnt seem to work.
--
RK
"Jean-Pierre Riehl" <jean-pierre.riehl@.bewise.fr> wrote in message
news:19928F8F-CDF8-4F4F-8C6A-C8B50D13C5C7@.microsoft.com...
> Hi,
> Tue and False part are evaluated in a IIF function. That's why you have an
> #error.
> You can use Visibility property.
> --
> Jean-Pierre Riehl
> http://blog.djeepy1.net
> http://www.bewise.fr
>
> "ramakrishna" <ramakrishna@.translogicsys.com> wrote in message
> news:O2Cm3S%23ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have a date field in my report dataset. I want to check for NULL date
>> before displaying it in the report. I have tried using the exp
>> =IIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day(Fields!Date.Value)&"/"&Year(Fields!Date.Value)).
>> But this doesnt work. I see #error in my report where ever date value is
>> NULL or blank. I have also tried using Len and IsDate functions but they
>> too dont seem to work. Can anyone help me ?
>> Thanks,
>> RK
>>
>>
>|||On Feb 5, 11:49=A0pm, "ramakrishna" <ramakris...@.translogicsys.com>
wrote:
> Hi,
> Thanks for the reply. I want to display the date only when its not null. S=o
> I have used the IIF expression. But the checking using the function
> IsNothing or IsDate doesnt seem to work.
> --
> RK"Jean-Pierre Riehl" <jean-pierre.ri...@.bewise.fr> wrote in message
> news:19928F8F-CDF8-4F4F-8C6A-C8B50D13C5C7@.microsoft.com...
>
> > Hi,
> > Tue and False part are evaluated in a IIF function. That's why you have =an
> > #error.
> > You can use Visibility property.
> > --
> > Jean-Pierre Riehl
> >http://blog.djeepy1.net
> >http://www.bewise.fr
> > "ramakrishna" <ramakris...@.translogicsys.com> wrote in message
> >news:O2Cm3S%23ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
> >> Hi,
> >> I have a date field in my report dataset. I want to check for NULL date=
> >> before displaying it in the report. I have tried using the exp
> >> =3DIIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day=(Field=ADs!Date.Value)&"/"&Year(Fields!Date.Value)).
> >> But this doesnt work. I see #error in my report where ever date value i=s
> >> NULL or blank. I have also tried using Len and IsDate functions but the=y
> >> too dont seem to work. Can anyone help me ?
> >> Thanks,
> >> RK- Hide quoted text -
> - Show quoted text -
Any value that returns "NULL" from your database is treated like a
"Nothing" object in .Net. Wherever possible, you will want to use the
IsNothing function. If I recall, Len(Nothing) returns Nothing, not 0
Second, the Month, Day and Year functions expect a Date object. It is
possible that you are passing them a Text fieldand it doesn't know how
to parse it. This could be why the function is returning a #error.
Third, when doing Date formatting, you should probably use the Format
function instead of breaking the date apart.
=3DIIF( IsNothing(Fields!Date.Value), "", Format( CDate(Fields!
Date.Value), "M/d/yyyy" ) )
-- Scott

checking dates with triggers or constraints

Hi,

I have a table that contains various data with a start and end date. When a user enters the information I need to check that the end date is older than the start date.

What is the easiest way to do this - using a trigger?

And is the code below correct ?

CREATE TRIGGER checkdate
ON Prodn_Problem
FOR INSERT,UPDATE
AS
DECLARE @.start_date datetime
SELECT @.start_date = (SELECT startdate FROM inserted)
DECLARE @.end_date datetime
SELECT @.end_date = (SELECT enddate FROM inserted)
BEGIN
IF(@.end_date < @.start_date)
ROLLBACK
END

Thanks.By using a trigger, you check the data when it's already inserted or updated in the table. When you use a check constraint you can verify before the information is entered. Another method is to create a stored procedure which does the checking an let you users enter information through this stored procedure.

Check the continuity of dates

I am trying to find coverage dates for a given list of dates that may
have breakage.
For example person 123 has the below records:
PersonID Date
123 10/15/03
123 11/15/03
123 12/15/03
123 3/15/05
123 4/15/05
123 5/15/05
For PersonID 123 I would expect 2 records with coverage dates of
10/1/03 - 12/31/03 and another one of 3/1/05 - 5/31/05. What is the
best approach for this? I am unconcerned about getting the First/End
of the month dates because I have functions to get this info. I need
to know the best way to check the continuity of dates.It won't produce results in the format that you wan't, but this will show yo
u
if there are any gaps in coverage:
select t1.PersonID, DATEADD(mm, 1, t1.[Date]) FROM [YourTable] t1
WHERE NOT EXISTS
(SELECT t2.[Date] FROM [YourTable] t2 WHERE t1.PersonID=t2.PersonID
AND t2.[Date] = DATEADD(mm, 1, t1.[Date])
This will show the months for which a lapse in coverage begins.
Assuming that there was only 1 gap in coverage for all customers, you could
do something like this:
select t1.PersonID, DATEADD(mm, 1, t1.[Date]) AS "LapseBegins",
t5.LapseEnds
FROM [YourTable] t1
WHERE NOT EXISTS
(SELECT t2.[Date] FROM [YourTable] t2 WHERE t1.PersonID=t2.PersonID
AND t2.[Date] = DATEADD(mm, 1, t1.[Date])
INNER JOIN
(
select t3.PersonID, t3.[Date] AS "LapseEnds" FROM [YourTable] t3
WHERE NOT EXISTS
(SELECT t4.[Date] FROM [YourTable] t4 WHERE t3.PersonID=t4.PersonID
AND t3.[Date] = DATEADD(mm, -1, t4.[Date])
) t5
ON t5.PersonID=t5.PersonID
This would return results like
PersonID LapseBegins LapseEnds
-- -- --
123 1/15//04 3/15/05
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.
"cxg" wrote:

> I am trying to find coverage dates for a given list of dates that may
> have breakage.
> For example person 123 has the below records:
> PersonID Date
> 123 10/15/03
> 123 11/15/03
> 123 12/15/03
> 123 3/15/05
> 123 4/15/05
> 123 5/15/05
> For PersonID 123 I would expect 2 records with coverage dates of
> 10/1/03 - 12/31/03 and another one of 3/1/05 - 5/31/05. What is the
> best approach for this? I am unconcerned about getting the First/End
> of the month dates because I have functions to get this info. I need
> to know the best way to check the continuity of dates.
>|||Considering the following DDL and sample data:
CREATE TABLE TheTable (
PersonID int,
SomeDate smalldatetime,
PRIMARY KEY (PersonID, SomeDate)
)
INSERT INTO TheTable VALUES (123, '20031010')
INSERT INTO TheTable VALUES (123, '20031011')
INSERT INTO TheTable VALUES (123, '20031012')
INSERT INTO TheTable VALUES (123, '20050503')
INSERT INTO TheTable VALUES (123, '20050504')
INSERT INTO TheTable VALUES (123, '20050505')
INSERT INTO TheTable VALUES (124, '20050503')
INSERT INTO TheTable VALUES (124, '20050504')
INSERT INTO TheTable VALUES (124, '20050505')
INSERT INTO TheTable VALUES (124, '20050606')
INSERT INTO TheTable VALUES (125, '20050707')
Let's suppose that we expect the following result:
PersonID StartDate EndDate
-- -- --
123 2003-10-10 2003-10-12
123 2005-05-03 2005-05-05
124 2005-05-03 2005-05-05
124 2005-06-06 2005-06-06
125 2005-07-07 2005-07-07
The following query returns the above result:
SELECT PersonID, SomeDate AS StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=A.PersonID
AND C.SomeDate>=A.SomeDate
AND C.SomeDate<ISNULL((
SELECT MIN(D.SomeDate) FROM TheTable D
WHERE D.PersonID=C.PersonID
AND D.SomeDate>A.SomeDate
AND NOT EXISTS (
SELECT * FROM TheTable E
WHERE D.SomeDate=E.SomeDate+1
)
),C.SomeDate+1)
) AS EndDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
The query can be simplified using a CTE in SQL Server 2005 (or a view):
WITH MyCTE AS (
SELECT PersonID, SomeDate AS StartDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
)
SELECT PersonID, StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=X.PersonID
AND C.SomeDate>=X.StartDate
AND C.SomeDate<ISNULL((
SELECT MIN(Y.StartDate) FROM MyCTE Y
WHERE Y.PersonID=C.PersonID
AND Y.StartDate>X.StartDate
),C.SomeDate+1)
) as EndDate
FROM MyCTE X
The first query was inspired by reading (a few years ago) the following
article:
[url]http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp[
/url]
Razvan|||Just a little addition:
I understand that your requirements are a little different: you don't want
to check for consecutive days, but for dates in consecutive months. I will
leave the modifications for you, as an exercise :)
Razvan|||Google the use of an auxilary Calendar table. DATE is both a reserved
word and too vague to be data element name. And the only format allowed
in Standard SQL is ISO-8601.
We need a range of dates to consider for the report, so make them
parameters. We want to find calendar dates in the range that are not
matched to foo_dates in the same range.
SELECT F1.person_id, C1.cal_date,
@.report_start_date, @.report_end_date
FROM Foobar AS F1, Calendar AS C1
WHERE NOT EXISTS
(SELECT *
FROM Foobar AS F2
WHERE C1.cal_date BETWEEN @.report_start_date AND
@.report_end_date
AND F1.cal_date BETWEEN @.report_start_date AND
@.report_end_date
AND C1.cal_date = F1.foo_date);
If you want to express this result as ranges, you can Google some other
postings about gaps shown as (start, end) pairs.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||Consider the following DDL and sample data:
CREATE TABLE TheTable (
PersonID int,
SomeDate smalldatetime,
PRIMARY KEY (PersonID, SomeDate)
)
INSERT INTO TheTable VALUES (123, '20031010')
INSERT INTO TheTable VALUES (123, '20031011')
INSERT INTO TheTable VALUES (123, '20031012')
INSERT INTO TheTable VALUES (123, '20050503')
INSERT INTO TheTable VALUES (123, '20050504')
INSERT INTO TheTable VALUES (123, '20050505')
INSERT INTO TheTable VALUES (124, '20050503')
INSERT INTO TheTable VALUES (124, '20050504')
INSERT INTO TheTable VALUES (124, '20050505')
INSERT INTO TheTable VALUES (124, '20050606')
INSERT INTO TheTable VALUES (125, '20050707')
Let's suppose the expected result is:
PersonID StartDate EndDate
-- -- --
123 2003-10-10 2003-10-12
123 2005-05-03 2005-05-05
124 2005-05-03 2005-05-05
124 2005-06-06 2005-06-06
125 2005-07-07 2005-07-07
The following query returns the above results:
SELECT PersonID, SomeDate AS StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=A.PersonID
AND C.SomeDate>=A.SomeDate
AND C.SomeDate<ISNULL((
SELECT MIN(D.SomeDate) FROM TheTable D
WHERE D.PersonID=C.PersonID
AND D.SomeDate>A.SomeDate
AND NOT EXISTS (
SELECT * FROM TheTable E
WHERE D.SomeDate=E.SomeDate+1
)
),C.SomeDate+1)
) AS EndDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
We can simplify it a bit using a CTE in SQL Server 2005 (or a view):
WITH MyCTE AS (
SELECT PersonID, SomeDate AS StartDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
)
SELECT PersonID, StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=X.PersonID
AND C.SomeDate>=X.StartDate
AND C.SomeDate<ISNULL((
SELECT MIN(Y.StartDate) FROM MyCTE Y
WHERE Y.PersonID=C.PersonID
AND Y.StartDate>X.StartDate
),C.SomeDate+1)
) as EndDate
FROM MyCTE X
I understand that your requirements are different: you do not need to
have consecutive days, but dates in consecutive months. I will leave
the modifications of the query to you, as an exercise.
The above query was inspired by reading (a few years ago) the following
article:
[url]http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp[
/url]
Razvan|||A small update:
I understand that your requirements are a little different: you don't
want to check for consecutive days, but for dates in consecutive
months. I will leave the modifications for you, as an exercise :)
Razvan|||Consider the following DDL and sample data:
CREATE TABLE TheTable (
PersonID int,
SomeDate smalldatetime,
PRIMARY KEY (PersonID, SomeDate)
)
INSERT INTO TheTable VALUES (123, '20031010')
INSERT INTO TheTable VALUES (123, '20031011')
INSERT INTO TheTable VALUES (123, '20031012')
INSERT INTO TheTable VALUES (123, '20050503')
INSERT INTO TheTable VALUES (123, '20050504')
INSERT INTO TheTable VALUES (123, '20050505')
INSERT INTO TheTable VALUES (124, '20050503')
INSERT INTO TheTable VALUES (124, '20050504')
INSERT INTO TheTable VALUES (124, '20050505')
INSERT INTO TheTable VALUES (124, '20050606')
INSERT INTO TheTable VALUES (125, '20050707')
Let's suppose the expected result is:
PersonID StartDate EndDate
-- -- --
123 2003-10-10 2003-10-12
123 2005-05-03 2005-05-05
124 2005-05-03 2005-05-05
124 2005-06-06 2005-06-06
125 2005-07-07 2005-07-07
The following query returns this result:
SELECT PersonID, SomeDate AS StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=A.PersonID
AND C.SomeDate>=A.SomeDate
AND C.SomeDate<ISNULL((
SELECT MIN(D.SomeDate) FROM TheTable D
WHERE D.PersonID=C.PersonID
AND D.SomeDate>A.SomeDate
AND NOT EXISTS (
SELECT * FROM TheTable E
WHERE D.SomeDate=E.SomeDate+1
)
),C.SomeDate+1)
) AS EndDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
The query can be simplified by using a CTE in SQL Server 2005 (or a
view):
WITH MyCTE AS (
SELECT PersonID, SomeDate AS StartDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
)
SELECT PersonID, StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=X.PersonID
AND C.SomeDate>=X.StartDate
AND C.SomeDate<ISNULL((
SELECT MIN(Y.StartDate) FROM MyCTE Y
WHERE Y.PersonID=C.PersonID
AND Y.StartDate>X.StartDate
),C.SomeDate+1)
) as EndDate
FROM MyCTE X
I understand that your requirements are a little different: you don't
want to check for consecutive days, but for dates in consecutive
months. I will leave the modifications to you, as an exercise. :)
The above query was inspired by reading (a few years ago) the following
article:
[url]http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp[
/url]
Razvan|||Thanks so much for you assistance. This was of immense help to me.|||Thanks Joe!
Your books are great!