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!

Chr(13) not working as intended

When I use Chr(13) in my textbox expression, it does not produce a carriage return in the text. For example if I use "Test 1" & Chr(13) & "Test2", Test1 and Test2 come in the same line but if I use just Chr(10) in the place of Chr(13), it works as intended. In Crystal reports, Chr(13) does produce a carriage return and the text come in 2 different lines.

Thanks,

Shyam

It is a common issue here and you are right the difference with crystal is apparent.

You have to use a line feed to get an actual line return (10) as the carraige return (13) just feeds the symbol for systems to translate as they feel and the HTML report viewer will not action a (13) whereas the crystal viewer does.

Choosing explicit cell in Excel to SQL Database

Hi, is there a way to choose a specific Excel cell and put the value into a specifc column in a SQL table using the wizard? For example, when the package is run, to have cell B3's value placed into a newly created row in column 0 and have F6's value placed into the same row in column 1?

Thanks for your time,

James

EDIT: What happens currently is I'll map a particular Excel column and it will create almost 200 rows in my table with mostly null values, as that is the format of the Excel sheet. I would like to create 1 row per Excel sheet, with selected information input. Thanks!

It is unclear.

With the explanation you gave, I would recommend to look into ways to reformat your Excel input so you can use either PIVOT or UNPIVOT to load this into your database.

You will still have to deal with the potential lack of heterogeneity of Excel.

What if the user give you smashed potatoes?

Regards,

Philippe

|||

Hi, thanks for the response. The Excel sheet is template based - every Excel sheet put into the database has the exact same format.

What I'm looking to do is this:

I would like to take the value from one particular cell from the Excel sheet (say, B5 for example) and insert it into a particular spot into a new row of a SQL database table. So, in all, I want to create a new row in the table, put B5 into the first column of this row, E9 into the second column of this row, and F13 into the third column of this row. Does this make sense?

Thanks,

James

|||

It makes sense. A way would be to use Automation to trigger a macro in the workbook from the ssis package, this macro would put B5, F9 and F13 in one row on another spreadsheet and the package would load this spreadsheet rather than the original.

You could also create another spreadsheet with indirect references to the master sheet. That would work only if the cells to "normalize" have a constant position.

The question I am asking myself is "How do you know that B5, F9 and F13 have to be in Col1 Col2 Col3 Is it set in stone? "

May be you could Name these ranges, then load several times by refering these names, the first pass would create the row and insert Name1, the second and third pass would only update the row with Name2 and Name3.

I guess I prefer the last approach.

Philippe

|||

James, you can use DataDefractor SSIS source data flow component to extract data from Excel workbooks. It is template driven and it supports data extraction from specific cells, series of cells, columns, rows and any combination of the above. You can download a free beta of the component at http://www.datadefractor.com.

Choosing explicit cell in Excel to SQL Database

Hi, is there a way to choose a specific Excel cell and put the value into a specifc column in a SQL table using the wizard? For example, when the package is run, to have cell B3's value placed into a newly created row in column 0 and have F6's value placed into the same row in column 1?

Thanks for your time,

James

EDIT: What happens currently is I'll map a particular Excel column and it will create almost 200 rows in my table with mostly null values, as that is the format of the Excel sheet. I would like to create 1 row per Excel sheet, with selected information input. Thanks!

It is unclear.

With the explanation you gave, I would recommend to look into ways to reformat your Excel input so you can use either PIVOT or UNPIVOT to load this into your database.

You will still have to deal with the potential lack of heterogeneity of Excel.

What if the user give you smashed potatoes?

Regards,

Philippe

|||

Hi, thanks for the response. The Excel sheet is template based - every Excel sheet put into the database has the exact same format.

What I'm looking to do is this:

I would like to take the value from one particular cell from the Excel sheet (say, B5 for example) and insert it into a particular spot into a new row of a SQL database table. So, in all, I want to create a new row in the table, put B5 into the first column of this row, E9 into the second column of this row, and F13 into the third column of this row. Does this make sense?

Thanks,

James

|||

It makes sense. A way would be to use Automation to trigger a macro in the workbook from the ssis package, this macro would put B5, F9 and F13 in one row on another spreadsheet and the package would load this spreadsheet rather than the original.

You could also create another spreadsheet with indirect references to the master sheet. That would work only if the cells to "normalize" have a constant position.

The question I am asking myself is "How do you know that B5, F9 and F13 have to be in Col1 Col2 Col3 Is it set in stone? "

May be you could Name these ranges, then load several times by refering these names, the first pass would create the row and insert Name1, the second and third pass would only update the row with Name2 and Name3.

I guess I prefer the last approach.

Philippe

|||

James, you can use DataDefractor SSIS source data flow component to extract data from Excel workbooks. It is template driven and it supports data extraction from specific cells, series of cells, columns, rows and any combination of the above. You can download a free beta of the component at http://www.datadefractor.com.

choose max value of multiple calculations

Hi,

I need to be able to get the maximum value of different calculations.

Example :

i have 5 calculations like this :

(sum(Fields!CountTest1.Value)/sum(Fields!TestCount.Value))*100

(sum(Fields!CountTest2.Value)/sum(Fields!TestCount.Value))*100

(sum(Fields!CountTest3.Value)/sum(Fields!TestCount.Value))*100

(sum(Fields!CountTest4.Value)/sum(Fields!TestCount.Value))*100

(sum(Fields!CountTest5.Value)/sum(Fields!TestCount.Value))*100

these calculations give me a percentage from a test value against the total test values.

what function or expression can i use to get the max value of all the calculations ?

I was looking at the "choose" function but i'm not quite sure how...

anybody ? help ....

Vinnie

Hi again ...

I found a solution to my problem.

the way to do this is by using the following expression:

=math.max(value1, math.max(value2, math.max(value3, math.max(value4, value5))))

in fact it is the max function that is standard in the math expressions from rs.

greetings.

Vinnie

Child package ConnectionManager visibility

Hopefully a simple question about parent-child package relationship. For this example, let's say I have a simple setup - one parent package: parent.dtsx, and one child package: child.dtsx. The parent package calls the child package via the ExecutePackage Task.

If I add an OleDB ConnectionManager to the parent package called MySqlConnectionManager, should I be able to reference this connection via a script task (or custom component) from my child package? I realize that I will have a problem doing this at design time, but I thought I could get around it with the script task or custom component. That said, when I look in the Connections collection at run-time from within my child package, I do not see the parent package's MySqlConnectionManager. Am I missing something, or is this the way it was intended to work?

Thanks,

David

David,

I suspect you cannot do this. Connection managers can only be used in the package in which they reside - even if you're using a script task.

-Jamie

|||

Jamie,

Thanks for the response. I must say it is somewhat disappointing, though I think I have a work around for my situation. That said, I would still be interested in hearing a rationale for why this is the case. It seems to me like it breaks the container hierarchy paradigm.

David

|||

Well I can see why you think this but remember that connection managers don't follow container scope like variables do so the same rules don't apply.

Having said that, there were plans to scope conenction managers to the container hierarchy but it couldn't be done in time (or something). Reading between the lines its something they (well...kirk Haselden) wanted to do but it was down the priority list.

-Jamie

|||

Thanks again, Jamie. Hopefully this will be implemented at some point in the future.

Related, I found the opposite to be true when dealing with log providers. Interestingly, the connections collection of the parent package DOES appear to be available to child package log providers (I have built a custom log provider in which this appears to be true). It strikes me as bizzare that the functionality I want is there for log providers, but not for the package tasks. That may be due, however, to gaps in my understanding of parent-child package relationships.

checksum

It seems that the checksum function does not distinguish between positive
and negative decimals or floats. Is this by design? For example
select checksum(1.0)
select checksum(-1.0)
returns:
-1374215283
-1374215283Elmer Miller wrote:
> It seems that the checksum function does not distinguish between positive
> and negative decimals or floats. Is this by design? For example
> select checksum(1.0)
> select checksum(-1.0)
> returns:
> -1374215283
> -1374215283
That's right. CHECKSUM doesn't necessarily return distinct results for
different inputs.
SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
ABC ASH
-- --
1132495864 1132495864
(1 row(s) affected)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||It is odd. I am trying to use checksum to build an index. The documentation
states that checksum is intended for the building of hash indexes.
However...
I have a table of approx 1.3 million rows and 25 columns (mixed types). I
ran the following statements:
select count(*) as vol, checksum(*) as Hash from <tablename> group by
checksum(*) order by vol desc
This returned 227 records that had the same check sums as another row in the
same table. No row was matched more than twice. This means that 0.01% of the
rows have the same check sums. I then took a look at the rows and they are
very different. They do have the same data types across the columns but there
is a 0.01% chance that my table returns the same checksum despite the data
within them being very different.
This means that I can't really use it as an index. Could there be another
way of creating an index from columns? perhaps an MD5 Hash?
thanks for any help on this.
"David Portas" wrote:
> Elmer Miller wrote:
> > It seems that the checksum function does not distinguish between positive
> > and negative decimals or floats. Is this by design? For example
> > select checksum(1.0)
> >
> > select checksum(-1.0)
> >
> > returns:
> >
> > -1374215283
> >
> > -1374215283
> That's right. CHECKSUM doesn't necessarily return distinct results for
> different inputs.
> SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
> ABC ASH
> -- --
> 1132495864 1132495864
> (1 row(s) affected)
>
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Some other checksums will give you better results but basically no checksum
will guarantee you won't have collisions. Checksums are useful as indexes
just as hash functions are useful in building hash tables even though
uniqueness is not guaranteed. If I understood your statistics correctly,
you would return a maximum of two rows which is pretty good for 1.3 million
candidates. Presumably once you have narrowed the search to two or three
rows you can use some other means to get the exact row you want.
A checksum ensures that no two identical rows will return different
checksums but it doesn't ensure that the same checksum can't be returned
from different rows.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sharat Koya" <SharatKoya@.discussions.microsoft.com> wrote in message
news:4E79996B-27A5-46B5-8E9B-E3FFC68024D5@.microsoft.com...
> It is odd. I am trying to use checksum to build an index. The
> documentation
> states that checksum is intended for the building of hash indexes.
> However...
> I have a table of approx 1.3 million rows and 25 columns (mixed types). I
> ran the following statements:
> select count(*) as vol, checksum(*) as Hash from <tablename> group by
> checksum(*) order by vol desc
> This returned 227 records that had the same check sums as another row in
> the
> same table. No row was matched more than twice. This means that 0.01% of
> the
> rows have the same check sums. I then took a look at the rows and they are
> very different. They do have the same data types across the columns but
> there
> is a 0.01% chance that my table returns the same checksum despite the data
> within them being very different.
> This means that I can't really use it as an index. Could there be another
> way of creating an index from columns? perhaps an MD5 Hash?
> thanks for any help on this.
>
>
> "David Portas" wrote:
>> Elmer Miller wrote:
>> > It seems that the checksum function does not distinguish between
>> > positive
>> > and negative decimals or floats. Is this by design? For example
>> > select checksum(1.0)
>> >
>> > select checksum(-1.0)
>> >
>> > returns:
>> >
>> > -1374215283
>> >
>> > -1374215283
>> That's right. CHECKSUM doesn't necessarily return distinct results for
>> different inputs.
>> SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
>> ABC ASH
>> -- --
>> 1132495864 1132495864
>> (1 row(s) affected)
>>
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>

Checking Format of a string in Report Parameter

Is there a way to find out if the user entered the date in correct format for the report parameter? For example, I am using string data type and requring the user to enter date in yyyy/mm/dd format. Can I capture and prompt the user accordingly if the date string is not provided in the above format?

Why dont you create the parameter in a Datetime format and let the user enter it in a date time format. After that you use the functions to convert itto a string in your desired format.

|||

You might be able to take advantage of the isDate() function; however, at the moment I don't see how to apply it.

Checking for NULL within an Expression

Hi All,

I would like an example of checking for NULL within an expression. I don't know the correct syntax. Checking for IsNull is incorrect so is IS Null. Can anyone help me?

Another C#er!

Try if(IsNothing(Myvalue)) or if(Myvvalue = Nothing)

hth

Helen

|||

Read this post, it should help

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1248764&SiteID=1

checkboxlist and SQL search using AND/OR on selected checkboxlist items.

I have a checkbox list like the one above. For example, Training OR Production – should include everyone with an Training OR everyone with a Production checked OR everyone with both Training and Production checked. If service AND technical support – just those two options will show – the customer can only have those 2 options selected in their account and nothing else. Is there an easy way to build the SQL query for this scenario? Any suggestions or tips?Thank you for any help

You can create a procedure that accepts a parameter ('AND' or 'OR), which perform a query based on the parameter:

create proc sp_testQuery @.logicOP varchar(3)='AND',@.id int,@.name varchar(20)
as
if @.logicOP='AND'
select * from t1 whereid=@.id and name=isnull(@.name,name)
else if @.logicOP='OR'
select * from t1 whereid=@.id orname=@.name
else raiserror('You must choose a logic operator ''AND'' or ''OR''',16,1)
go
sp_testQuery 'AND',1,null

Then what your code need to do is just call the stored procedure with providing all required parameters that come from your website.

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!

Check stored procedure response times

Is there any way we can be alerted for any sprocs that are being executed on
SQL Server that are running for say > x secs.
So for example if we know that on a good day, all sprocs run for less than a
sec but want to capture any sproc that may run for 5 secs or greater so we
can take a look into it and be proactive before it gets worse and cause
other issues as a result. I can only think of running profiler and then
running some queries against the data captured.
I was hoping if there was any DMVs we could query that may capture this info
or any other way to do so.
Please let me know..
The reason I ask for this capability is that while SQL Server seems to be up
and running, at times we may have a change in query plan and now these
sprocs run a bit slower but we dont get to know about it quickly unless the
customer talks about slowness... so i was hoping to trap it as soon as any
query hits the threshold.Hi Hassan
You may want to try profiling for a period and then analysing the results.
This can be automated if you use a server side trace.
John
"Hassan" wrote:
> Is there any way we can be alerted for any sprocs that are being executed on
> SQL Server that are running for say > x secs.
> So for example if we know that on a good day, all sprocs run for less than a
> sec but want to capture any sproc that may run for 5 secs or greater so we
> can take a look into it and be proactive before it gets worse and cause
> other issues as a result. I can only think of running profiler and then
> running some queries against the data captured.
> I was hoping if there was any DMVs we could query that may capture this info
> or any other way to do so.
> Please let me know..
> The reason I ask for this capability is that while SQL Server seems to be up
> and running, at times we may have a change in query plan and now these
> sprocs run a bit slower but we dont get to know about it quickly unless the
> customer talks about slowness... so i was hoping to trap it as soon as any
> query hits the threshold.
>
>