PRINT 1/(1+26)=0?

Why does this T-SQL round incorrectly?

PRINT 1/(1+26)

when it should be 0.037037037?

Thanks,
MichaelI should have been more specific:

DECLARE @.A int
DECLARE @.B int
DECLARE @.C float

SET @.A = 1
SET @.B = 26
SET @.C = @.A / (@.A + @.B)
PRINT @.C

mpa...@.htxml.com wrote:

Quote:

Originally Posted by

Why does this T-SQL round incorrectly?
>
PRINT 1/(1+26)
>
when it should be 0.037037037?
>
Thanks,
Michael

|||Ok, I just figured it out:

DECLARE @.A int
DECLARE @.B int
DECLARE @.C float

SET @.A = 1
SET @.B = 26
SET @.C = CAST(@.A AS float) / (CAST(@.A AS float) + CAST(@.B AS float))
PRINT @.C

This is unlike any other programming language that I have used before
-- so strange.

mpaine@.htxml.com wrote:

Quote:

Originally Posted by

Why does this T-SQL round incorrectly?
>
PRINT 1/(1+26)
>
when it should be 0.037037037?
>
Thanks,
Michael

|||If any one of the operators had a decimal point then the results would
have had a decimal point. Performaing pure integer arithmatic, as in
the original example, returns an integer result.

Roy Harvey
Beacon Falls, CT

On 20 Nov 2006 12:57:04 -0800, mpaine@.htxml.com wrote:

Quote:

Originally Posted by

>Ok, I just figured it out:
>
>
>
>DECLARE @.A int
>DECLARE @.B int
>DECLARE @.C float
>
>SET @.A = 1
>SET @.B = 26
>SET @.C = CAST(@.A AS float) / (CAST(@.A AS float) + CAST(@.B AS float))
>PRINT @.C
>
>This is unlike any other programming language that I have used before
>-- so strange.
>
>mpaine@.htxml.com wrote:

Quote:

Originally Posted by

>Why does this T-SQL round incorrectly?
>>
>PRINT 1/(1+26)
>>
>when it should be 0.037037037?
>>
>Thanks,
>Michael

|||So is PRINT CAST(1 AS float)/(CAST(1 AS float)+CAST(26 AS float)) the
best way to get the correct answer for PRINT 1/(1+26)?

- Michael

Roy Harvey wrote:

Quote:

Originally Posted by

If any one of the operators had a decimal point then the results would
have had a decimal point. Performaing pure integer arithmatic, as in
the original example, returns an integer result.
>
Roy Harvey
Beacon Falls, CT
>
On 20 Nov 2006 12:57:04 -0800, mpaine@.htxml.com wrote:
>

Quote:

Originally Posted by

Ok, I just figured it out:

DECLARE @.A int
DECLARE @.B int
DECLARE @.C float

SET @.A = 1
SET @.B = 26
SET @.C = CAST(@.A AS float) / (CAST(@.A AS float) + CAST(@.B AS float))
PRINT @.C

This is unlike any other programming language that I have used before
-- so strange.

mpaine@.htxml.com wrote:

Quote:

Originally Posted by

Why does this T-SQL round incorrectly?
>
PRINT 1/(1+26)
>
when it should be 0.037037037?
>
Thanks,
Michael

|||On 20 Nov 2006 14:53:34 -0800, mpaine@.htxml.com wrote:

Quote:

Originally Posted by

>So is PRINT CAST(1 AS float)/(CAST(1 AS float)+CAST(26 AS float)) the
>best way to get the correct answer for PRINT 1/(1+26)?
>
>- Michael

If a value is a constant, simply adding a decimal point to it is
sufficient, and only one of the operators needs the decimal point:

PRINT 1.0/(1+26)

Alternately, if none of the values are constants, converting any one
of them is sufficient. However, I would not choose to convert to
FLOAT, but to an appropriately sized DECIMAL.

PRINT CONVERT(DECIMAL(15,6),1)/(1+26)

Roy Harvey
Beacon Falls, CT

print / export SSRS report from t-sql

Greetings,

I have a report that I would like to execute (export to PDF) from t-sql. I can view it interactively from the SSRS website; I can view it from my asp.net application; I can also create the PDF programmatically from the asp.net front-end, but I need to run a batch type query from a stored proc with a report(PDF) as the output...

For example, I want to create and send out a PDF based on a trigger....

How do I do this from t-sql?

Any ideas?

Thanks.

Dan

Web Service calls are only hard to implement in TSQL, close to say that this is not possible. Would could be done is to write an application and execute this application on a trigger base with passing the needed parameters to it..BUT..Keep in mind that triggers behave synchronously, which means that the execution and the lock will be hold until the processing of the trigger is finished. So you have to be careful if you really want to do this on a trigger base rather than starting a job e.g. every minute.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks Jens -- I think I'll just fall back to using the front end application to initiate it. Maybe MS will help out in the future....

Thanks again.

primary key error with INSERT INTO

Using the following t-sql statement on table with a primary key [DateTime], I get a primary key violation. How can I avoid adding duplicate records?

INSERT INTO [destSchema].[destTable]

SELECT t2.*

FROM [srcSchema].[srcTable] t2

LEFT JOIN [destSchema].[destTable] t1

ON t2.[DateTime] = t1.[DateTime]

WHERE (t1.[DateTime] IS NULL) AND (t1.[DateTime] <> t2.[DateTime])

ORDER BY t1.[DateTime];

Is [destTable].[DateTime] the primary key?

Code Snippet

INSERT INTO [destSchema].[destTable]

SELECT

t2.*

FROM

[srcSchema].[srcTable] t2

LEFT OUTER JOIN

[destSchema].[destTable] t1

ON

t2.[DateTime] = t1.[DateTime]

WHERE

t1.[DateTime] IS NULL

|||

Yes

|||

The dupe data can be coming from t2. So, you will have to decide what you want to insert into t1.

This query will give you a list of dupe dates.

Code Snippet

select t2.[DateTime]

from [srcSchema].[srcTable] t2

where not exists(select 1 from [destSchema].[destTable] t1 where t2.[DateTime] = t1.[DateTime])

group by t2.[DateTime]

having count(*)>1

|||

The code to list dupe dates works great. However, the other code generates the same primary key error that I′ve been getting all along:

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_destTable'. Cannot insert duplicate key in object 'destSchema.destTable'.

The statement has been terminated.

|||

If the code lists dupes, you will have to clean your data in table t2 before you insert it into t1. The bottom line, you have to guarantee the data from t2 is unique before you commit inserting into t1 - this involves either deleting the duped data or only selecting a row for each name. Else, you will get the primary constraint violation. This is by design.

Only you know your data, you will have to make the choice of what to insert into t1. If you post DDL + sample data + expected result here, we might be able to offer a solution.

|||

The following are sample fields in the source table, actual field names vary depending on the source but they all contain DateTime (Field0):

[DateTime] [datetime] NOT NULL,

[Field1] [decimal](10, 2) NULL,

[Field2] [decimal](10, 2) NULL,

[Field3] [decimal](10, 2) NULL,

[Field4] [decimal](10, 2) NULL

Here is some sample data from the source table that demonstrates the problem (non-black lines indicates duplicated rows). Note that the DateTime value is duplicated but the other fields contain different values:

2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:22:00,502.90,502.90,502.90,502.90
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:26:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,498.40,498.40,498.40,498.40
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30
2005-11-28 18:39:00,498.10,498.10,498.00,498.00

Desired results:

2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30

Any and all help appreciated.

|||

Here you go.

Code Snippet

create table #tmp([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)
go
create table #tmp2([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)

go
insert #tmp
select '2005-11-28 18:21:00',498.70,498.70,498.70,498.70
union all select '2005-11-28 18:22:00',498.50,498.50,498.50,498.50
union all select '2005-11-28 18:22:00',502.90,502.90,502.90,502.90
union all select '2005-11-28 18:23:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:26:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:26:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:30:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:31:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:32:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:33:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:34:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:36:00',502.50,502.50,502.50,502.50
union all select '2005-11-28 18:39:00',502.40,502.40,502.30,502.30
union all select '2005-11-28 18:39:00',498.10,498.10,498.00,498.00
go
;with cte
as
(select *,
row_number() over(partition by [datetime] order by [datetime] ) r
from #tmp
)
insert #tmp2
select [Datetime],Field1,Field2,Field3,Field4
from cte
where r=1
and not exists(select 1 from #tmp2 t2 where t2.[Datetime]=cte.[Datetime])
go
select * from #tmp2
go
drop table #tmp, #tmp2

|||

With mycte

as

(SELECT myDatatime, f1, f2, f3, f4 FROM

(SELECT myDatatime, f1, f2, f3, f4, ROW_NUMBER() OVER(partition by myDatatime ORDER BY f1) as RowNum

FROM dupDateTimedata) t

WHERE RowNum=1)

SELECT * INTO dupDateTimedataRemoved

FROM mycte

|||

limno, "order by f1" will not give you the "top 1"...i.e. you will get this instead of the desired row.

2005-11-28 18:39:00.000 498.10 498.10 498.00 498.00

|||

Thanks oj for pointing this out. The problem is even with order by [datetime], we may still not get the right result.

We may need a little more clarification from rwbta to confirm your result.

My intention was by using Partion by datetime then I will keep the samllest number for f1 within the same datetime rows.

|||

Since we partition by datetime, order by datetime again will force the engine to generate the rownumber based on the logical order of the rows which we then select only the first row. Essentially, it is equivalent to "select top 1 * from tb" - this is what was asked by the OP as the desired result.

|||

This certainly turned out more complicated than I imagined. What additional information is needed?

Just as a summary, my original intention was to insert records into a new or existing table without including duplicate DateTime (primary key) values. If that's not possible, I would like to remove records in the source table which contain duplicate DateTime values.

Since the fields are not likely to contain exactly the same values in the duplicated records, DISTINCT won't work. Only the DateTime values are duplicated, inserting only the first occurrence of a duplicated DateTime would be acceptable. Or, alternatively, deleting subsequent duplications in the source table.

|||

Below is what I have done to resolve this problem. Add a primary key ID to the source table to aid in identification of duplicate DateTime's. Then delete duplicates. After that I can insert into a new or existing table.

Add PK ID:

ALTER TABLE srcSchema.srcTable

ADD

DataID int NOT NULL IDENTITY(1, 1),

CONSTRAINT PK_srcTable PRIMARY KEY(DataID)

Delete Duplicates:

DELETE FROM

t1

FROM

srcSchema.srcTable t1

INNER JOIN

(

SELECT

MIN(DataID) AS DataID,

[DateTime]

FROM

srcSchema.srcTable

GROUP BY

[DateTime]

HAVING

COUNT(*) > 1

) t2

ON(

t1.[DateTime] = t2.[DateTime]

AND

t1.DataID <> t2.DataID

)

primary key error with INSERT INTO

Using the following t-sql statement on table with a primary key [DateTime], I get a primary key violation. How can I avoid adding duplicate records?

INSERT INTO [destSchema].[destTable]

SELECT t2.*

FROM [srcSchema].[srcTable] t2

LEFT JOIN [destSchema].[destTable] t1

ON t2.[DateTime] = t1.[DateTime]

WHERE (t1.[DateTime] IS NULL) AND (t1.[DateTime] <> t2.[DateTime])

ORDER BY t1.[DateTime];

Is [destTable].[DateTime] the primary key?

Code Snippet

INSERT INTO [destSchema].[destTable]

SELECT

t2.*

FROM

[srcSchema].[srcTable] t2

LEFT OUTER JOIN

[destSchema].[destTable] t1

ON

t2.[DateTime] = t1.[DateTime]

WHERE

t1.[DateTime] IS NULL

|||

Yes

|||

The dupe data can be coming from t2. So, you will have to decide what you want to insert into t1.

This query will give you a list of dupe dates.

Code Snippet

select t2.[DateTime]

from [srcSchema].[srcTable] t2

where not exists(select 1 from [destSchema].[destTable] t1 where t2.[DateTime] = t1.[DateTime])

group by t2.[DateTime]

having count(*)>1

|||

The code to list dupe dates works great. However, the other code generates the same primary key error that I′ve been getting all along:

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_destTable'. Cannot insert duplicate key in object 'destSchema.destTable'.

The statement has been terminated.

|||

If the code lists dupes, you will have to clean your data in table t2 before you insert it into t1. The bottom line, you have to guarantee the data from t2 is unique before you commit inserting into t1 - this involves either deleting the duped data or only selecting a row for each name. Else, you will get the primary constraint violation. This is by design.

Only you know your data, you will have to make the choice of what to insert into t1. If you post DDL + sample data + expected result here, we might be able to offer a solution.

|||

The following are sample fields in the source table, actual field names vary depending on the source but they all contain DateTime (Field0):

[DateTime] [datetime] NOT NULL,

[Field1] [decimal](10, 2) NULL,

[Field2] [decimal](10, 2) NULL,

[Field3] [decimal](10, 2) NULL,

[Field4] [decimal](10, 2) NULL

Here is some sample data from the source table that demonstrates the problem (non-black lines indicates duplicated rows). Note that the DateTime value is duplicated but the other fields contain different values:

2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:22:00,502.90,502.90,502.90,502.90
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:26:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,498.40,498.40,498.40,498.40
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30
2005-11-28 18:39:00,498.10,498.10,498.00,498.00

Desired results:

2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30

Any and all help appreciated.

|||

Here you go.

Code Snippet

create table #tmp([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)
go
create table #tmp2([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)

go
insert #tmp
select '2005-11-28 18:21:00',498.70,498.70,498.70,498.70
union all select '2005-11-28 18:22:00',498.50,498.50,498.50,498.50
union all select '2005-11-28 18:22:00',502.90,502.90,502.90,502.90
union all select '2005-11-28 18:23:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:26:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:26:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:30:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:31:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:32:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:33:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:34:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:36:00',502.50,502.50,502.50,502.50
union all select '2005-11-28 18:39:00',502.40,502.40,502.30,502.30
union all select '2005-11-28 18:39:00',498.10,498.10,498.00,498.00
go
;with cte
as
(select *,
row_number() over(partition by [datetime] order by [datetime] ) r
from #tmp
)
insert #tmp2
select [Datetime],Field1,Field2,Field3,Field4
from cte
where r=1
and not exists(select 1 from #tmp2 t2 where t2.[Datetime]=cte.[Datetime])
go
select * from #tmp2
go
drop table #tmp, #tmp2

|||

With mycte

as

(SELECT myDatatime, f1, f2, f3, f4 FROM

(SELECT myDatatime, f1, f2, f3, f4, ROW_NUMBER() OVER(partition by myDatatime ORDER BY f1) as RowNum

FROM dupDateTimedata) t

WHERE RowNum=1)

SELECT * INTO dupDateTimedataRemoved

FROM mycte

|||

limno, "order by f1" will not give you the "top 1"...i.e. you will get this instead of the desired row.

2005-11-28 18:39:00.000 498.10 498.10 498.00 498.00

|||

Thanks oj for pointing this out. The problem is even with order by [datetime], we may still not get the right result.

We may need a little more clarification from rwbta to confirm your result.

My intention was by using Partion by datetime then I will keep the samllest number for f1 within the same datetime rows.

|||

Since we partition by datetime, order by datetime again will force the engine to generate the rownumber based on the logical order of the rows which we then select only the first row. Essentially, it is equivalent to "select top 1 * from tb" - this is what was asked by the OP as the desired result.

|||

This certainly turned out more complicated than I imagined. What additional information is needed?

Just as a summary, my original intention was to insert records into a new or existing table without including duplicate DateTime (primary key) values. If that's not possible, I would like to remove records in the source table which contain duplicate DateTime values.

Since the fields are not likely to contain exactly the same values in the duplicated records, DISTINCT won't work. Only the DateTime values are duplicated, inserting only the first occurrence of a duplicated DateTime would be acceptable. Or, alternatively, deleting subsequent duplications in the source table.

|||

Below is what I have done to resolve this problem. Add a primary key ID to the source table to aid in identification of duplicate DateTime's. Then delete duplicates. After that I can insert into a new or existing table.

Add PK ID:

ALTER TABLE srcSchema.srcTable

ADD

DataID int NOT NULL IDENTITY(1, 1),

CONSTRAINT PK_srcTable PRIMARY KEY(DataID)

Delete Duplicates:

DELETE FROM

t1

FROM

srcSchema.srcTable t1

INNER JOIN

(

SELECT

MIN(DataID) AS DataID,

[DateTime]

FROM

srcSchema.srcTable

GROUP BY

[DateTime]

HAVING

COUNT(*) > 1

) t2

ON(

t1.[DateTime] = t2.[DateTime]

AND

t1.DataID <> t2.DataID

)

primary key error with INSERT INTO

Using the following t-sql statement on table with a primary key [DateTime], I get a primary key violation. How can I avoid adding duplicate records?

INSERT INTO [destSchema].[destTable]

SELECT t2.*

FROM [srcSchema].[srcTable] t2

LEFT JOIN [destSchema].[destTable] t1

ON t2.[DateTime] = t1.[DateTime]

WHERE (t1.[DateTime] IS NULL) AND (t1.[DateTime] <> t2.[DateTime])

ORDER BY t1.[DateTime];

Is [destTable].[DateTime] the primary key?

Code Snippet

INSERT INTO [destSchema].[destTable]

SELECT

t2.*

FROM

[srcSchema].[srcTable] t2

LEFT OUTER JOIN

[destSchema].[destTable] t1

ON

t2.[DateTime] = t1.[DateTime]

WHERE

t1.[DateTime] IS NULL

|||

Yes

|||

The dupe data can be coming from t2. So, you will have to decide what you want to insert into t1.

This query will give you a list of dupe dates.

Code Snippet

select t2.[DateTime]

from [srcSchema].[srcTable] t2

where not exists(select 1 from [destSchema].[destTable] t1 where t2.[DateTime] = t1.[DateTime])

group by t2.[DateTime]

having count(*)>1

|||

The code to list dupe dates works great. However, the other code generates the same primary key error that I′ve been getting all along:

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_destTable'. Cannot insert duplicate key in object 'destSchema.destTable'.

The statement has been terminated.

|||

If the code lists dupes, you will have to clean your data in table t2 before you insert it into t1. The bottom line, you have to guarantee the data from t2 is unique before you commit inserting into t1 - this involves either deleting the duped data or only selecting a row for each name. Else, you will get the primary constraint violation. This is by design.

Only you know your data, you will have to make the choice of what to insert into t1. If you post DDL + sample data + expected result here, we might be able to offer a solution.

|||

The following are sample fields in the source table, actual field names vary depending on the source but they all contain DateTime (Field0):

[DateTime] [datetime] NOT NULL,

[Field1] [decimal](10, 2) NULL,

[Field2] [decimal](10, 2) NULL,

[Field3] [decimal](10, 2) NULL,

[Field4] [decimal](10, 2) NULL

Here is some sample data from the source table that demonstrates the problem (non-black lines indicates duplicated rows). Note that the DateTime value is duplicated but the other fields contain different values:

2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:22:00,502.90,502.90,502.90,502.90
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:26:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,498.40,498.40,498.40,498.40
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30
2005-11-28 18:39:00,498.10,498.10,498.00,498.00

Desired results:

2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30

Any and all help appreciated.

|||

Here you go.

Code Snippet

create table #tmp([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)
go
create table #tmp2([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)

go
insert #tmp
select '2005-11-28 18:21:00',498.70,498.70,498.70,498.70
union all select '2005-11-28 18:22:00',498.50,498.50,498.50,498.50
union all select '2005-11-28 18:22:00',502.90,502.90,502.90,502.90
union all select '2005-11-28 18:23:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:26:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:26:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:30:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:31:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:32:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:33:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:34:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:36:00',502.50,502.50,502.50,502.50
union all select '2005-11-28 18:39:00',502.40,502.40,502.30,502.30
union all select '2005-11-28 18:39:00',498.10,498.10,498.00,498.00
go
;with cte
as
(select *,
row_number() over(partition by [datetime] order by [datetime] ) r
from #tmp
)
insert #tmp2
select [Datetime],Field1,Field2,Field3,Field4
from cte
where r=1
and not exists(select 1 from #tmp2 t2 where t2.[Datetime]=cte.[Datetime])
go
select * from #tmp2
go
drop table #tmp, #tmp2

|||

With mycte

as

(SELECT myDatatime, f1, f2, f3, f4 FROM

(SELECT myDatatime, f1, f2, f3, f4, ROW_NUMBER() OVER(partition by myDatatime ORDER BY f1) as RowNum

FROM dupDateTimedata) t

WHERE RowNum=1)

SELECT * INTO dupDateTimedataRemoved

FROM mycte

|||

limno, "order by f1" will not give you the "top 1"...i.e. you will get this instead of the desired row.

2005-11-28 18:39:00.000 498.10 498.10 498.00 498.00

|||

Thanks oj for pointing this out. The problem is even with order by [datetime], we may still not get the right result.

We may need a little more clarification from rwbta to confirm your result.

My intention was by using Partion by datetime then I will keep the samllest number for f1 within the same datetime rows.

|||

Since we partition by datetime, order by datetime again will force the engine to generate the rownumber based on the logical order of the rows which we then select only the first row. Essentially, it is equivalent to "select top 1 * from tb" - this is what was asked by the OP as the desired result.

|||

This certainly turned out more complicated than I imagined. What additional information is needed?

Just as a summary, my original intention was to insert records into a new or existing table without including duplicate DateTime (primary key) values. If that's not possible, I would like to remove records in the source table which contain duplicate DateTime values.

Since the fields are not likely to contain exactly the same values in the duplicated records, DISTINCT won't work. Only the DateTime values are duplicated, inserting only the first occurrence of a duplicated DateTime would be acceptable. Or, alternatively, deleting subsequent duplications in the source table.

|||

Below is what I have done to resolve this problem. Add a primary key ID to the source table to aid in identification of duplicate DateTime's. Then delete duplicates. After that I can insert into a new or existing table.

Add PK ID:

ALTER TABLE srcSchema.srcTable

ADD

DataID int NOT NULL IDENTITY(1, 1),

CONSTRAINT PK_srcTable PRIMARY KEY(DataID)

Delete Duplicates:

DELETE FROM

t1

FROM

srcSchema.srcTable t1

INNER JOIN

(

SELECT

MIN(DataID) AS DataID,

[DateTime]

FROM

srcSchema.srcTable

GROUP BY

[DateTime]

HAVING

COUNT(*) > 1

) t2

ON(

t1.[DateTime] = t2.[DateTime]

AND

t1.DataID <> t2.DataID

)

Primary Key - Multiple Columns

Friends,
In Access I can set Primary Keys on two columns so that, together, those two
columns comprise a Primary Key.
Is there a way, through T-SQL, to define two or more columns as the Primary
Key on a table? For example, it may be that the [sku] column contains
duplicate values and the [salesman] column contains duplicate values, but if
I GROUP BY [sku] and [salesman] then those two columns taken together contain
no duplicate values and can act as a Primary Key (i.e., there are no records
that contain the same [sku] AND [salesman]. Can I make two columns the
Primary Key?
Thanks for your help ...
Bill MorganIf you look in the documentation (Books Online) under
"PRIMARY KEY", you'll find the article "Creating and Modifying
PRIMARY KEY Constraints", which sort of shows how to do this
when you create the table and also how to do this after the table
is created.
But it's not easy to dig through the entire CREATE/ALTER TABLE
syntax to see how to do it, so...
-- When you create the table:
CREATE TABLE T (
sku int not null,
salesman int not null,
other varchar(20) not null,
primary key (sku,salesman)
)
-- later (the columns must be NOT NULL)
ALTER TABLE T
ADD CONSTRAINT pk_you_pick_a_name_for_the_primary_key_c
onstraint
PRIMARY KEY (sku, salesman)
If you do not have Books Online installed, download the current
version here:
http://www.microsoft.com/sql/techin.../2000/books.asp
Steve Kass
Drew University
bill_morgan_3333 wrote:

>Friends,
>In Access I can set Primary Keys on two columns so that, together, those tw
o
>columns comprise a Primary Key.
>Is there a way, through T-SQL, to define two or more columns as the Primary
>Key on a table? For example, it may be that the [sku] column contains
>duplicate values and the [salesman] column contains duplicate values, but if
>I GROUP BY [sku] and [salesman] then those two columns taken together contain
>no duplicate values and can act as a Primary Key (i.e., there are no record
s
>that contain the same [sku] AND [salesman]. Can I make two columns the
>Primary Key?
>Thanks for your help ...
>Bill Morgan
>
>|||Steve,
Perfectly clear. Thanks for your assistance ...
"Steve Kass" wrote:

> If you look in the documentation (Books Online) under
> "PRIMARY KEY", you'll find the article "Creating and Modifying
> PRIMARY KEY Constraints", which sort of shows how to do this
> when you create the table and also how to do this after the table
> is created.
> But it's not easy to dig through the entire CREATE/ALTER TABLE
> syntax to see how to do it, so...
> -- When you create the table:
> CREATE TABLE T (
> sku int not null,
> salesman int not null,
> other varchar(20) not null,
> primary key (sku,salesman)
> )
> -- later (the columns must be NOT NULL)
> ALTER TABLE T
> ADD CONSTRAINT pk_you_pick_a_name_for_the_primary_key_c
onstraint
> PRIMARY KEY (sku, salesman)
> If you do not have Books Online installed, download the current
> version here:
> http://www.microsoft.com/sql/techin.../2000/books.asp
> Steve Kass
> Drew University
> bill_morgan_3333 wrote:
>
>

Preventing ALL text SQL Injection by removing single-quotes ?

I need to ask the user for some string (not INT) input... to
build a T-SQL "find" query in SQL Server 2000.
I do *NOT* need the user to ever search for "'"... so I'm removing the
single-quote character entirely.

> s = Replace(s, "'", "")
Can someone give a working example where SQL INJECTION would still be
possible?Hi Susan
You may want to read:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.sommarskog.se/dynamic_sql.html#Security2
John
"Susan S via droptable.com" <forum@.droptable.com> wrote in message
news:50B826701CBE0@.droptable.com...
>I need to ask the user for some string (not INT) input... to
> build a T-SQL "find" query in SQL Server 2000.
> I do *NOT* need the user to ever search for "'"... so I'm removing the
> single-quote character entirely.
>
> Can someone give a working example where SQL INJECTION would still be
> possible?|||I can't find a single line of that article that *SHOWS* how a
string, fully stripped of all single-quotes... can be used for
Injection.
I'm not saying that it can't.
I just need someone to post the actual code.
Ever wonder why no one has the code?
Message posted via http://www.droptable.com|||Hi
You are assuming a certain business rule is applied which is probably
quite rare. You may be able to provide dropdowns if your values are so
specific and remove the need to type in anything!!
The parameterised query option is generic and can cater for all
sutuations, it should also be fast.
John|||John... that wasn't the question at all.
I *DO* have a need to search for text without containing any single-quotes.
(I couldn't possible have a dropdown box with 1000 different searches in it.
)
Why not simple say "I don't know" instead of answering a totally different
question?
John Bell wrote:
>Hi
>You are assuming a certain business rule is applied which is probably
>quite rare. You may be able to provide dropdowns if your values are so
>specific and remove the need to type in anything!!
>The parameterised query option is generic and can cater for all
>sutuations, it should also be fast.
>John
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...curity/200507/1|||"Susan S via droptable.com" <forum@.droptable.com> wrote in message
news:510817BAE9A30@.droptable.com...
> John... that wasn't the question at all.
> I *DO* have a need to search for text without containing any
> single-quotes.
> (I couldn't possible have a dropdown box with 1000 different searches in
> it.)
> Why not simple say "I don't know" instead of answering a totally different
> question?
You don't provide us with an example of your code that builds dynamic SQL so
we can only guess as to what might or might not work. Have you considered
char(39) being mixed in somehow?
But the larger question is, what is your aversion to parameterizing your
queries? If you were truly concerned about SQL injection, that's the route
you would go. Will you feel safe if nobody here can come up with an
example? Will that mean that it can't be done? You'd learn more hanging
out in hacker's circles, if that is your approach..
So, sorry if it offends you, and I'll admit that "I don't know" of a
specific example, but the real answer to your rhetorical question is:
parameterize, then you won't need to worry about it, there are no reasons it
can't be done, only excuses.
Good Luck,
Mark
[vbcol=seagreen]
> John Bell wrote:|||Well I started out the other day writing down a list from the sql server
perspective, but they all seem to start with the programmer making a
blunder. There are so many ways you can facilitate your site getting hacked
that it is really quite absurd. This is where a lot of the challenge exists.
If ever in the design of the system someone has said "It will never do such
and such" so you haven't coded defensively for the day you will have say
multiple sites on the one server (as an EG) then you have introduced time
bombs.
The most important thing about security is like so many things - expend your
effort where it will have most benefit.
I suggest you check your program code and ensure that no strings can
overflow buffers, that you use parameterised queries, that you check every
string not just for single quotes but also script and other HTML tags, for
code that can be evaluated to quotes, script, or html, that wherever
possible you remove as much need for free text entry as you can, do not
reflect errors verbatum back to the user - IE avoid replay attacks - do not
store data ever without sanitising it, do not show data with it being
santised (again), make everything as type safe as possible, normalise your
database properly, use maximum error and warning reporting when compiling,
use proper error handling, test the system thoroughly, try to break it, do
not accept warnings during compiles, and so on.
Add to that, use a DMZ, secure your web server, secure your database server,
enforce strong security... strong passwords,... I think you get the idea.
Now, look at your original question and ask yourself "was it responsible"?
Or do you think you can now check off the single quote character in
isolation? It is never a done task.
I suggest you re-read John's answer and references as a starter.
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:uX1WrSUhFHA.3256@.TK2MSFTNGP12.phx.gbl...
> "Susan S via droptable.com" <forum@.droptable.com> wrote in message
> news:510817BAE9A30@.droptable.com...
> You don't provide us with an example of your code that builds dynamic SQL
> so we can only guess as to what might or might not work. Have you
> considered char(39) being mixed in somehow?
> But the larger question is, what is your aversion to parameterizing your
> queries? If you were truly concerned about SQL injection, that's the
> route you would go. Will you feel safe if nobody here can come up with an
> example? Will that mean that it can't be done? You'd learn more hanging
> out in hacker's circles, if that is your approach..
> So, sorry if it offends you, and I'll admit that "I don't know" of a
> specific example, but the real answer to your rhetorical question is:
> parameterize, then you won't need to worry about it, there are no reasons
> it can't be done, only excuses.
>
> Good Luck,
> Mark
>
>
>
>|||Oh, of course, the biggest sin of them all - as Mark points out. Never use
dynamic SQL. You never need to for a correctly designed database.
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:uX1WrSUhFHA.3256@.TK2MSFTNGP12.phx.gbl...
> "Susan S via droptable.com" <forum@.droptable.com> wrote in message
> news:510817BAE9A30@.droptable.com...
> You don't provide us with an example of your code that builds dynamic SQL
> so we can only guess as to what might or might not work. Have you
> considered char(39) being mixed in somehow?
> But the larger question is, what is your aversion to parameterizing your
> queries? If you were truly concerned about SQL injection, that's the
> route you would go. Will you feel safe if nobody here can come up with an
> example? Will that mean that it can't be done? You'd learn more hanging
> out in hacker's circles, if that is your approach..
> So, sorry if it offends you, and I'll admit that "I don't know" of a
> specific example, but the real answer to your rhetorical question is:
> parameterize, then you won't need to worry about it, there are no reasons
> it can't be done, only excuses.
>
> Good Luck,
> Mark
>
>
>
>|||Wow.. it was just *ONE* simple request:
Post some *ACTUAL* code... that can cause SQL injection... where *ALL* singl
e
quote
characters have been removed from the string input by the user.
Seems like everyone is talking about eveything *OTHER* than that 1 simple
request.:

> Why not use another method instead?
> Why not fully secure your servers instead?
> Why not post your code?
> What are you using this for?
> You might needs users to enter single quotes some day.
> Why not write/use long parameterized methods instead?
> What if it's an INT, not a VarChar field?
> Why don't you go back and change 1000s of lines of code that you already w
rote?
> Let's talk about some totally different problems, instead of this one.
Sheezesss. That wasn't the question here. Here it is again:
s = GetUsersString() ' Get the input
s = Replace(s, "'", "") ' Remove all single-quotes
sql = "SELECT * FROM MyTable WHERE MyField='" & s & "'" ' Execute this
The Question:
In *ONE* line... without *ANY* explaination... what text would the user ente
r
as a value for "s",
that would break the above code sample?
So simple. No one can answer that? Or prove it can't be done?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...curity/200507/1|||"Susan S via droptable.com" <forum@.droptable.com> wrote in message
news:5131626AFB930@.droptable.com...
> Wow.. it was just *ONE* simple request:
Considering all the good and relevant (despite your views) advice you've
received, it's obvious that you just don't get it; so go ahead and feel as
though you've written safe, secure code. Clearly, your mind is made up. I
think you've insulted the NG with your narrow-minded replies quite enough
for one thread.
What you refuse to accept is that it doesn't matter whether or not anyone in
this or any NG can provide you with an example. It simply doesn't matter.
It proves/disproves nothing. It does not mean it can't be done. You want
someone to prove for you that it *can* be done, otherwise you'll assume it
can't? That is a reckless approach at best.
So you choose to believe what you want to believe -- ok, fine. You chose to
ignore widely accepted best practices and now you want to justify that
choice -- that's all up to you. But don't expect the NG to validate those
choices, because -- and you know this as well as we do -- there is a body of
knowledge that says your chosen methodology is not the most secure, PERIOD,
bottom line, end of story.
And given today's Internet climate, why anyone would choose to do something
any way other than the most secure defies all reason. That's what we're on
about. That's why we can't get behind your over-simplification of the
issues.
And now I suppose you'll post another rude, frustrated, closed-minded reply
because I didn't stick to a one line answer... whatever... best of luck to
you too.
-Mark