Datetime convert fails!

Hi guys
I exported some data from a text file to sql server. Here is the sample data
.
This table has about 2 million rows.There is a date field in the table which
comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
get an error as operation timed out..
Here is the data from the text file...
Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag
Patient category Units dispensed Total days supply
1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
I have tried the bulk insert as well.
Here is the script for the create table ..
USE [Library]
GO
/****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:4
5:59
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NormalOutlier1](
[Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Formulation ID] [float] NULL,
[Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_Genera
l_CI_AS
NULL,
[NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL
,
[Units dispensed] [float] NULL,
[Total days supply] [float] NULL
) ON [PRIMARY]
Hope this helpsAssume that the data is imported into their respective table columns
correctly, you can change a.m. to am and the convertion to datetime should
work.
Linchi
"mita" wrote:

> Hi guys
> I exported some data from a text file to sql server. Here is the sample da
ta..
>
> This table has about 2 million rows.There is a date field in the table whi
ch
> comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
> get an error as operation timed out..
>
> Here is the data from the text file...
> Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS fl
ag
> Patient category Units dispensed Total days supply
> 1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
>
> I have tried the bulk insert as well.
> Here is the script for the create table ..
>
> USE [Library]
> GO
> /****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14
:45:59
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[NormalOutlier1](
> [Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Formulation ID] [float] NULL,
> [Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_Gene
ral_CI_AS
> NULL,
> [NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NU
LL,
> [Units dispensed] [float] NULL,
> [Total days supply] [float] NULL
> ) ON [PRIMARY]
>
>
>
> Hope this helps
>

Datetime convert fails!

Hi guys
I exported some data from a text file to sql server. Here is the sample data..
This table has about 2 million rows.There is a date field in the table which
comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
get an error as operation timed out..
Here is the data from the text file...
Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag
Patient category Units dispensed Total days supply
1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
I have tried the bulk insert as well.
Here is the script for the create table ..
USE [Library]
GO
/****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:45:59
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NormalOutlier1](
[Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Formulation ID] [float] NULL,
[Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_General_CI_AS
NULL,
[NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Units dispensed] [float] NULL,
[Total days supply] [float] NULL
) ON [PRIMARY]
Hope this helps
Assume that the data is imported into their respective table columns
correctly, you can change a.m. to am and the convertion to datetime should
work.
Linchi
"mita" wrote:

> Hi guys
> I exported some data from a text file to sql server. Here is the sample data..
>
> This table has about 2 million rows.There is a date field in the table which
> comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
> get an error as operation timed out..
>
> Here is the data from the text file...
> Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag
> Patient category Units dispensed Total days supply
> 1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
>
> I have tried the bulk insert as well.
> Here is the script for the create table ..
>
> USE [Library]
> GO
> /****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:45:59
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[NormalOutlier1](
> [Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Formulation ID] [float] NULL,
> [Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_General_CI_AS
> NULL,
> [NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Units dispensed] [float] NULL,
> [Total days supply] [float] NULL
> ) ON [PRIMARY]
>
>
>
> Hope this helps
>

Datetime convert fails!

Hi guys
I exported some data from a text file to sql server. Here is the sample data..
This table has about 2 million rows.There is a date field in the table which
comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
get an error as operation timed out..
Here is the data from the text file...
Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag
Patient category Units dispensed Total days supply
1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
I have tried the bulk insert as well.
Here is the script for the create table ..
USE [Library]
GO
/****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:45:59
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NormalOutlier1](
[Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Formulation ID] [float] NULL,
[Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_General_CI_AS
NULL,
[NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Units dispensed] [float] NULL,
[Total days supply] [float] NULL
) ON [PRIMARY]
Hope this helpsAssume that the data is imported into their respective table columns
correctly, you can change a.m. to am and the convertion to datetime should
work.
Linchi
"mita" wrote:
> Hi guys
> I exported some data from a text file to sql server. Here is the sample data..
>
> This table has about 2 million rows.There is a date field in the table which
> comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
> get an error as operation timed out..
>
> Here is the data from the text file...
> Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag
> Patient category Units dispensed Total days supply
> 1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
>
> I have tried the bulk insert as well.
> Here is the script for the create table ..
>
> USE [Library]
> GO
> /****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:45:59
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[NormalOutlier1](
> [Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Formulation ID] [float] NULL,
> [Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_General_CI_AS
> NULL,
> [NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Units dispensed] [float] NULL,
> [Total days supply] [float] NULL
> ) ON [PRIMARY]
>
>
>
> Hope this helps
>

Dateproblem

Hello
I'm trying to get data from a view and it's not going well, im trying to
"group" the rows abit so I dont end up with a million rows to display a date
range....
Hope this makes sense....
CREATE TABLE #Test (
Startdate datetime,
Enddate datetime,
Avalible char(1)
)
INSERT INTO #Test
(Startdate,Enddate,Avalible)
SELECT
'2006-01-31 13:00','2006-01-31 13:30','Y'
UNION SELECT
'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
'2006-01-31 14:00','2006-01-31 14:30','N'
UNION SELECT
'2006-01-31 14:30','2006-01-31 15:00','N'
UNION SELECT
'2006-01-31 15:00','2006-01-31 15:30','Y'
UNION SELECT
'2006-01-31 15:30','2006-01-31 16:00','Y'
UNION SELECT
'2006-01-31 16:00','2006-01-31 16:30','N'
UNION SELECT
'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
'2006-02-01 15:30','2006-02-01 16:00','N'
SELECT * FROM #Test
/*
Desired result:
StartDate EndDate
'2006-01-31 13:00' '2006-01-31 14:00'
'2006-01-31 15:00' '2006-01-31 16:00'
'2006-02-01 15:00' '2006-02-01 15:30'
*/
DROP TABLE #Testerr... use this DLL instead..
CREATE TABLE #Test (
ObjectID int,
Startdate datetime,
Enddate datetime,
Avalible char(1)
)
INSERT INTO #Test
(ObjectID,Startdate,Enddate,Avalible)
SELECT
1,'2006-01-31 13:00','2006-01-31 13:30','Y'
UNION SELECT
1,'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
1,'2006-01-31 14:00','2006-01-31 14:30','N'
UNION SELECT
1,'2006-01-31 14:30','2006-01-31 15:00','N'
UNION SELECT
1,'2006-01-31 15:00','2006-01-31 15:30','Y'
UNION SELECT
1,'2006-01-31 15:30','2006-01-31 16:00','Y'
UNION SELECT
1,'2006-01-31 16:00','2006-01-31 16:30','N'
UNION SELECT
1,'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
1,'2006-02-01 15:30','2006-02-01 16:00','N'
SELECT * FROM #Test
/*
Desired result:
StartDate EndDate
'2006-01-31 13:00' '2006-01-31 14:00'
'2006-01-31 15:00' '2006-01-31 16:00'
'2006-02-01 15:00' '2006-02-01 15:30'
*/
DROP TABLE #Test
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23ikIzF$KGHA.3100@.tk2msftngp13.phx.gbl...
> Hello
> I'm trying to get data from a view and it's not going well, im trying to
> "group" the rows abit so I dont end up with a million rows to display a
date
> range....
> Hope this makes sense....
>
> CREATE TABLE #Test (
> Startdate datetime,
> Enddate datetime,
> Avalible char(1)
> )
> INSERT INTO #Test
> (Startdate,Enddate,Avalible)
> SELECT
> '2006-01-31 13:00','2006-01-31 13:30','Y'
> UNION SELECT
> '2006-01-31 13:30','2006-01-31 14:00','Y'
> UNION SELECT
> '2006-01-31 14:00','2006-01-31 14:30','N'
> UNION SELECT
> '2006-01-31 14:30','2006-01-31 15:00','N'
> UNION SELECT
> '2006-01-31 15:00','2006-01-31 15:30','Y'
> UNION SELECT
> '2006-01-31 15:30','2006-01-31 16:00','Y'
> UNION SELECT
> '2006-01-31 16:00','2006-01-31 16:30','N'
> UNION SELECT
> '2006-02-01 15:00','2006-02-01 15:30','Y'
> UNION SELECT
> '2006-02-01 15:30','2006-02-01 16:00','N'
>
> SELECT * FROM #Test
> /*
> Desired result:
> StartDate EndDate
> '2006-01-31 13:00' '2006-01-31 14:00'
> '2006-01-31 15:00' '2006-01-31 16:00'
> '2006-02-01 15:00' '2006-02-01 15:30'
> */
> DROP TABLE #Test
>|||Lasse Edsvik wrote:

> I'm trying to get data from a view and it's not going well, im trying
> to "group" the rows abit so I dont end up with a million rows to
> display a date range....
> Hope this makes sense....
I once had the same problem and question asked: http://tinyurl.com/7gnst
HTH,
Stijn Verrept.|||Or this if it makes it more simple.... :S
CREATE TABLE #Test (
ObjectID int,
Wkday tinyint,
StartTime char(5),
EndTime char(5),
Avalible char(1)
)
INSERT INTO #Test
(ObjectID,Wkday,StartTime,EndTime,Avalib
le)
SELECT
1,1,'13:00','13:30','Y'
UNION SELECT
1,1,'13:30','14:00','Y'
UNION SELECT
1,1,'14:00','14:30','N'
UNION SELECT
1,1,'14:30','15:00','N'
UNION SELECT
1,1,'15:00','15:30','Y'
UNION SELECT
1,1,'15:30','16:00','Y'
UNION SELECT
1,1,'16:00','16:30','N'
UNION SELECT
1,2,'15:00','15:30','Y'
UNION SELECT
1,2,'15:30','16:00','N'
SELECT * FROM #Test
/*
Desired result:
ObjectID Wkday StartTime EndTime
1 1 '13:00' '14:00'
1 1 '15:00' '16:00'
1 2 '15:00' '15:30'
*/
DROP TABLE #Test
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23ikIzF$KGHA.3100@.tk2msftngp13.phx.gbl...
> Hello
> I'm trying to get data from a view and it's not going well, im trying to
> "group" the rows abit so I dont end up with a million rows to display a
date
> range....
> Hope this makes sense....
>
> CREATE TABLE #Test (
> Startdate datetime,
> Enddate datetime,
> Avalible char(1)
> )
> INSERT INTO #Test
> (Startdate,Enddate,Avalible)
> SELECT
> '2006-01-31 13:00','2006-01-31 13:30','Y'
> UNION SELECT
> '2006-01-31 13:30','2006-01-31 14:00','Y'
> UNION SELECT
> '2006-01-31 14:00','2006-01-31 14:30','N'
> UNION SELECT
> '2006-01-31 14:30','2006-01-31 15:00','N'
> UNION SELECT
> '2006-01-31 15:00','2006-01-31 15:30','Y'
> UNION SELECT
> '2006-01-31 15:30','2006-01-31 16:00','Y'
> UNION SELECT
> '2006-01-31 16:00','2006-01-31 16:30','N'
> UNION SELECT
> '2006-02-01 15:00','2006-02-01 15:30','Y'
> UNION SELECT
> '2006-02-01 15:30','2006-02-01 16:00','N'
>
> SELECT * FROM #Test
> /*
> Desired result:
> StartDate EndDate
> '2006-01-31 13:00' '2006-01-31 14:00'
> '2006-01-31 15:00' '2006-01-31 16:00'
> '2006-02-01 15:00' '2006-02-01 15:30'
> */
> DROP TABLE #Test
>|||Lasse,
select ObjectID,Startdate, coalesce(last_available, enddate) enddate
from
(
SELECT t.*,
(select max(startdate) FROM #Test t1
where t1.startdate<t.startdate and t1.available='Y') prev_available,
(select max(startdate) FROM #Test t1
where t1.startdate<t.startdate and t1.available='N')
prev_not_available,
(select max(enddate) FROM #Test t1
where t1.startdate>t.startdate and t1.available='Y'
and not exists(select 1 from #test t2
where t2.available='N' and t2.startdate between t.startdate and
t1.startdate)
) last_available
FROM #Test t
where available='Y'
) t
where prev_available<prev_not_available
or prev_available is null
ObjectID Startdate
enddate
-- ---
---
1 2006-01-31 13:00:00.000
2006-01-31 14:00:00.000
1 2006-01-31 15:00:00.000
2006-01-31 16:00:00.000
1 2006-02-01 15:00:00.000
2006-02-01 15:30:00.000
(3 row(s) affected)
Nice puzzle, thanks!|||hmm,
Server: Msg 207, Level 16, State 3, Line 30
Invalid column name 'available'.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1139324776.706400.210190@.f14g2000cwb.googlegroups.com...
> Lasse,
> select ObjectID,Startdate, coalesce(last_available, enddate) enddate
> from
> (
> SELECT t.*,
> (select max(startdate) FROM #Test t1
> where t1.startdate<t.startdate and t1.available='Y') prev_available,
> (select max(startdate) FROM #Test t1
> where t1.startdate<t.startdate and t1.available='N')
> prev_not_available,
> (select max(enddate) FROM #Test t1
> where t1.startdate>t.startdate and t1.available='Y'
> and not exists(select 1 from #test t2
> where t2.available='N' and t2.startdate between t.startdate and
> t1.startdate)
> ) last_available
> FROM #Test t
> where available='Y'
> ) t
> where prev_available<prev_not_available
> or prev_available is null
> ObjectID Startdate
> enddate
> -- ---
> ---
> 1 2006-01-31 13:00:00.000
> 2006-01-31 14:00:00.000
> 1 2006-01-31 15:00:00.000
> 2006-01-31 16:00:00.000
> 1 2006-02-01 15:00:00.000
> 2006-02-01 15:30:00.000
> (3 row(s) affected)
> Nice puzzle, thanks!
>|||yes the spell checker replaced Avalible with Available. I did not argue
with it ;)|||Alexander,
oh k :) You have any idea why ObjectID=2 doesnt show up?
CREATE TABLE #Test (
ObjectID int,
Startdate datetime,
Enddate datetime,
available char(1)
)
INSERT INTO #Test
(ObjectID,Startdate,Enddate,available)
SELECT
1,'2006-01-31 13:00','2006-01-31 13:30','Y'
UNION SELECT
1,'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
1,'2006-01-31 14:00','2006-01-31 14:30','N'
UNION SELECT
2,'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
2,'2006-01-31 14:00','2006-01-31 16:00','Y'
UNION SELECT
1,'2006-01-31 14:30','2006-01-31 15:00','N'
UNION SELECT
1,'2006-01-31 15:00','2006-01-31 15:30','Y'
UNION SELECT
1,'2006-01-31 15:30','2006-01-31 16:00','Y'
UNION SELECT
1,'2006-01-31 16:00','2006-01-31 16:30','N'
UNION SELECT
1,'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
1,'2006-02-01 15:30','2006-02-01 16:00','N'
UNION SELECT
1,'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
1,'2006-02-02 15:00','2006-02-02 15:30','Y'
UNION SELECT
1,'2006-02-02 16:00','2006-02-02 16:30','Y'
SELECT ObjectID,Startdate, COALESCE(last_available, Enddate) Enddate
FROM
(
SELECT t.*,
(SELECT MAX(startdate) FROM #Test t1
WHERE t1.startdate<t.startdate AND t1.available='Y') prev_available,
(SELECT MAX(startdate) FROM #Test t1
WHERE t1.startdate<t.startdate AND t1.available='N')
prev_not_available,
(SELECT MAX(enddate) FROM #Test t1
WHERE t1.startdate>t.startdate and t1.available='Y'
AND NOT EXISTS(SELECT 1 FROM #test t2
WHERE t2.Available='N' and t2.Startdate between t.Startdate and
t1.Startdate)
) last_available
FROM #Test t
WHERE Available='Y'
) t
WHERE prev_available<prev_not_available
OR prev_available IS NULL
ORDER BY Startdate
/*
Desired result:
StartDate EndDate
'2006-01-31 13:00' '2006-01-31 14:00'
'2006-01-31 15:00' '2006-01-31 16:00'
'2006-02-01 15:00' '2006-02-01 15:30'
*/
DROP TABLE #Test
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1139325716.672360.263040@.g47g2000cwa.googlegroups.com...
> yes the spell checker replaced Avalible with Available. I did not argue
> with it ;)
>|||because I did not think of ObjectId at all. It's easy to take care of
however:
SELECT ObjectID,Startdate, COALESCE(last_available, Enddate) Enddate
FROM
(
SELECT t.*,
(SELECT MAX(startdate) FROM #Test t1
WHERE t.ObjectID=t1.ObjectID and t1.startdate<t.startdate AND
t1.available='Y') prev_available,
(SELECT MAX(startdate) FROM #Test t1
WHERE t.ObjectID=t1.ObjectID and t1.startdate<t.startdate AND
t1.available='N')
prev_not_available,
(SELECT MAX(enddate) FROM #Test t1
WHERE t.ObjectID=t1.ObjectID and t1.startdate>t.startdate and
t1.available='Y'
AND NOT EXISTS(SELECT 1 FROM #test t2
WHERE t2.ObjectID=t1.ObjectID and t2.Available='N' and
t2.Startdate between t.Startdate and
t1.Startdate)
) last_available
FROM #Test t
WHERE Available='Y'
) t
WHERE prev_available<prev_not_available
OR prev_available IS NULL
ORDER BY Startdate
ObjectID Startdate
Enddate
-- ---
---
1 2006-01-31 13:00:00.000
2006-01-31 14:00:00.000
2 2006-01-31 13:30:00.000
2006-01-31 16:00:00.000
1 2006-01-31 15:00:00.000
2006-01-31 16:00:00.000
1 2006-02-01 15:00:00.000
2006-02-01 15:30:00.000
1 2006-02-02 15:00:00.000
2006-02-02 16:30:00.000
(5 row(s) affected)

DATEADD returning odd count of records

Hi,
We have a table here containing over 18 million rows and is updatred to
the tune of about 50,000 rows per day. Once a month I would like to run
a script that will delete any rows older than 18 months. All easy, I
here you say. Well when I do the following:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m, -18, getdate())
this returns a count of approx. 610,000, however when I workout all the
rows for the month of January 2004 (18 months ago the month I need to
remove) I return a count of approx 812,000
select count(*) from MyTable
where [Insertion Date] >= '20040101' and [Insertion Date] <= '20040131'
Apologies for being a tad vague here (obviously you don't know the ins
and outs of the data) but I was wondering if anyone else out there had
the same issue. The table schema for the first few columns is below:
CREATE TABLE [MyTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Consignment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date] [datetime] NULL ,
[Batch ID] [int] NOT NULL ,
[Insertion Date] [datetime] NULL ,
Thanks
qhDATEADD(m, -18, getdate()) is giving you 26th Jan 2004. That means
everything older than or equal to 26th Jan 2004. But your second query is
looking for data between 1st and 31st of Jan, which obviously is going to
have 5 days worth of additional data.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1122371409.746888.44830@.g49g2000cwa.googlegroups.com...
Hi,
We have a table here containing over 18 million rows and is updatred to
the tune of about 50,000 rows per day. Once a month I would like to run
a script that will delete any rows older than 18 months. All easy, I
here you say. Well when I do the following:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m, -18, getdate())
this returns a count of approx. 610,000, however when I workout all the
rows for the month of January 2004 (18 months ago the month I need to
remove) I return a count of approx 812,000
select count(*) from MyTable
where [Insertion Date] >= '20040101' and [Insertion Date] <= '20040131'
Apologies for being a tad vague here (obviously you don't know the ins
and outs of the data) but I was wondering if anyone else out there had
the same issue. The table schema for the first few columns is below:
CREATE TABLE [MyTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Consignment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date] [datetime] NULL ,
[Batch ID] [int] NOT NULL ,
[Insertion Date] [datetime] NULL ,
Thanks
qh|||Hello, Scott
You should notice the fact that "DATEADD(m, -18, getdate())"
returns January 26, 2004 and in your second query you are
comparing with January 31, 2004.
If you want to obtain the last day of the current month,
you can use the following:
SELECT DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0) - 1
So your query might be:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m,DATEDIFF(m,0,getdate())-17,0)-1
Razvan|||Hi Razvan,
Cheers for the reply, your solution sorted it. I didn't expect the
count to be over 200k for the days 26-31st Jan. I think it's myself
who doesn't know his own data!
;o)
Thanks
Scott
Razvan Socol wrote:
> Hello, Scott
> You should notice the fact that "DATEADD(m, -18, getdate())"
> returns January 26, 2004 and in your second query you are
> comparing with January 31, 2004.
> If you want to obtain the last day of the current month,
> you can use the following:
> SELECT DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0) - 1
> So your query might be:
> select count(*) from MyTable
> where [Insertion Date] <= DATEADD(m,DATEDIFF(m,0,getdate())-17,0)-1
> Razvan