It's truncation of intermediate results.

Using Decimal data type will fix it:

-------------------------------------------------------------------------

DECLARE @A Int, @b-2 Int, @C Int;

SELECT @A = 9,

@b-2 = 3,

@C = 2;

SELECT Cast(SUM(@A*@B) / SUM(@C) AS DECIMAL(30,5));

SELECT Cast(SUM(@A*@B)...

Money & Smallmoney both have 4 decimal places, so that's why they lose the detail. If you're looking for accuracy it's better to use decimal.

It's all a bit of...

Have a look at sp_MSForEachDB.

It's an undocumented sp which runs code against each db. There are plenty of examples if you search this site.

Why do you want to do that?

I think the intermediate result from @v1 / @v2 is being truncated because you're using smallmoney data type.

Can you use a different datatype, or cast one value to decimal to...

SELECT a.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,

CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays

INTO NewTable

FROM master..sysdatabases a

LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name

GROUP BY a.name, b.type

ORDER BY a.name, b.type

How about adding 2 rows to the ORDER BY statement? See 'Example'.

--================ TEST DATA ==========================

if object_id('dbo.tblGISStreetIndex') is not null

drop table dbo.tblGISStreetIndex;

create table dbo.tblGISStreetIndex

(

FirstName Varchar(40),

LastName Varchar(40),

StreetName Varchar(40),

AddFrom Varchar(40),

AddTo Varchar(40),

Maintainer Varchar(40),

LCity Varchar(40),

RCity...

Can you post a more complete statement so it's clearer what you're doing?

You could use a table-valued parameter perhaps?

http://msdn.microsoft.com/en-us/library/bb510489.aspx

No problem:-)

That's a bit more complicated:

I've used CTEs because I want to refer to CTE1 twice.

The ints do not need quotes in their definition, so I've removed them.

DECLARE @tab TABLE

(

flag1...

You're welcome

AAAGH.. Well it'll still work 😀

Try this:

if object_id('dbo.MasterDefects') is not null

drop table dbo.MasterDefects;

if object_id('dbo.Results') is not null

drop table dbo.Results;

create table dbo.MasterDefects

(

Department Varchar(20),

Process Varchar(20),

Type Varchar(20),

Defect Varchar(20)

);

insert dbo.MasterDefects values ( 'Timber', 'CrossCut', 'Wood Fault',...

DELETE table FROM table... is used when you want to use a something like a select statement with joins (usually) to control which records you delete. You leave off 'SELECT...