Problem with Insert, decimal field 1

[jms493]

Have a query that when I run, the last 2 fields return results from a calulation( 1.00, .80, .20 or nulls).

When I create a table the last 2 fields are decimal fields allowing nulls.

When I insert the data into the table the last 2 fields only show values (1, 0, or null)

Any ideas??

 

[SQLDenis]

do these 2 decimal fields columns have precision?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jms493]

Yes they both have a value of 18

 

[SQLDenis]

Please post DDL and DML (Table create script) and INSERT query

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jms493]

Table Script

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fte]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[fte]
GO

CREATE TABLE [dbo].[fte] (
	[tkinit] [varchar] (8) COLLATE Latin1_General_CI_AS NOT NULL ,
	[name] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
	[tklast] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[tkfirst] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[tktitle] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[tisort] [int] NULL ,
	[tkemdate] [datetime] NULL ,
	[tktmdate] [datetime] NULL ,
	[tkloc] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
	[ldesc] [varchar] (48) COLLATE Latin1_General_CI_AS NULL ,
	[tkdept] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
	[head1] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
	[tkemail] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[tsection] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
	[tsectdes] [varchar] (48) COLLATE Latin1_General_CI_AS NULL ,
	[non_person] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
	[period] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
	[mtd_avg_fte] [decimal](18, 0) NULL ,
	[ytd_avg_fte] [decimal](18, 0) NULL 
) ON [temp_data]
GO

Insert Script

Insert into FTE (tkinit, Name, tklast, tkfirst,tktitle, tisort, tkemdate, tktmdate, 
tkloc, ldesc,tkdept, head1, tkemail, tsection, tsectdes, Non_Person, Period, MTD_AVG_FTE, YTD_AVG_FTE)

SELECT timekeep.tkinit, timekeep.tkfirst+' '+timekeep.tklast Name, timekeep.tklast,
 timekeep.tkfirst, timekeep.tktitle, title.tisort, timekeep.tkemdate, timekeep.tktmdate, 
timekeep.tkloc, location.ldesc, timekeep.tkdept, deptlab.head1, timekeep.tkemail, 
tsection.tsection, tsection.tsectdes, 
case when udf.udvalue='Y' then 'Y' else 'N' end Non_Person, 
max(YTD_FTE.Period) Period, 
sum(case when YTD_FTE.YearMonth=convert(int,convert(varchar,year(dateadd("d",-day(getdate()),getdate())))+right('0'+convert(varchar,month(dateadd("d",-day(getdate()),getdate()))),2)) 
then (YTD_FTE.afte) else 0 end) MTD_AVG_FTE, 
case when ( sum(month(YTD_FTE.enddate)-month(YTD_FTE.startdate)+1) )=0 then 0 
else ( sum(YTD_FTE.afte*(month(YTD_FTE.enddate)-month(YTD_FTE.startdate)+1)) )/( sum(month(YTD_FTE.enddate)-month(YTD_FTE.startdate)+1) ) end YTD_AVG_FTE
FROM timekeep, title, location, deptlab, tsection, udf, 
 ( select atkinit,afte, adate1,adate2, 
   case when year(adate1)=year(getdate())then adate1 
   else convert(smalldatetime,'01/01/'+convert(varchar,year(getdate()))) end startdate,
   case when year(adate2)=year(getdate())then adate2 else getdate() end enddate,
   convert(Int,case when year(adate2)=year(getdate())then
                  case when month(adate2)=month(getdate()) 
                  then convert(varchar,year(adate2))+right('0'+convert(varchar,month(adate2)-1),2)
                  else convert(varchar,year(adate2))+right('0'+convert(varchar,month(adate2)),2) end
               else case when adate1 > dateadd(d,-day(getdate()),getdate()) 
                    then convert(varchar,year(getdate()))+right('0'+convert(varchar,month(getdate())),2) 
                    else convert(varchar,year(getdate()))+right('0'+convert(varchar,month(getdate())-1),2) end
              end) YearMonth,
   convert(varchar,year(getdate()))+right('0'+convert(varchar,month(getdate())-1),2) Period
   from accrate
   group by atkinit,afte,adate1,adate2
   having adate2 = (select max(tr2.adate2) from accrate tr2 
                   where tr2.atkinit = accrate.atkinit and 
                   tr2.adate1<=dateadd(dd,-1,convert(smalldatetime,dateadd(ms,-3,dateadd(mm,datediff(mm,0,getdate()),0))))
                   and year(tr2.adate2)=2025)
      or adate2 = (select max(tr2.adate2) 
                   from accrate tr2 
                   where tr2.atkinit = accrate.atkinit and year(tr2.adate2)=year(getdate()) 
                   and tr2.adate2<=dateadd(dd,-1,convert(smalldatetime,dateadd(ms,-3,dateadd(mm,datediff(mm,0,getdate()),0))))) ) YTD_FTE 
WHERE ( timekeep.tkloc=location.locode ) AND 
( timekeep.tkdept=deptlab.delcode ) AND 
( timekeep.tktitle=title.tititle ) AND 
( timekeep.tkinit=udf.udjoin ) AND 
( timekeep.tksect=tsection.tsection ) AND
 ( timekeep.tkinit*=YTD_FTE.atkinit ) AND 
( udf.udfindex=5 )
GROUP BY timekeep.tkinit, timekeep.tkfirst+' '+timekeep.tklast,
 timekeep.tklast, timekeep.tkfirst, timekeep.tktitle, title.tisort,
 timekeep.tkemdate, timekeep.tktmdate, timekeep.tkloc, location.ldesc, 
timekeep.tkdept, deptlab.head1, timekeep.tkemail, tsection.tsection, 
tsection.tsectdes, case when udf.udvalue='Y' then 'Y' else 'N' end

excuse the code I did not write and it is a little messy.

 

[SQLDenis]

Of course I meant to say scale not precision

take a look at this

declare @d  [decimal](18, 0) 

select @d = 1.2424354345

select @d

output
--------
1

declare @d  [decimal](18, 10) 

select @d = 1.2424354345

select @d

output
--------
1.2424354345

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

you also might be running into integer math

run this

select 5/2

select 5/(2*1.0)
select 5/convert(decimal(18,10),2)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jms493]

GREAT....scale fixed it.
Deleted all, reinserted and the correct values are now being displayed.

Why cant they call scale (decimal places)

Thank yOU!

 

[jms493]

While I have your attention....This is going to be a stored procedure that I need to be executed once a month.

What could do I need to insert so I dont insert duplicate values based on the period field??

 

[ESquared]

jms493,

Do you know about Books Online, the built in help system for SQL Server?

It has all the information you could possibly want on the decimal data type, including the meaning of precision and scale.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]