int to decimal but coming out int

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

int to decimal but coming out int

Author

Topic

Menorel
Starting Member

15 Posts

Posted - 2013-07-14 : 03:04:50

I have a table that has the time a person has worked stored at an int in seconds. I am using the below T-SQL in a stored procedure but the output is being displayed as an integer still. I use an almost identical query in an asp page that give me the time in a decimal format but this is just acting strange, not sure what I am missing.


CREATE TABLE #tbl_WORKEDPayRoll(
EmployeeID int,
StartDate datetime,
ExternalID int,
TempDept int,
ActivityType nchar(6),
Worked decimal,
Is_Paid bit,
Is_Holiday bit,
PAYROLLDATAID int)

INSERT INTO #tbl_WORKEDPayRoll
                      (EmployeeID, Startdate, ExternalID, TempDept, ActivityType, Worked, Is_Paid, Is_Holiday, PAYROLLDATAID)
SELECT     EmployeeID, StartDate, ExternalID, TempDept, ActivityType, CASE WHEN (CASE WHEN IsHoliday IS NULL THEN 0 ELSE IsHoliday END) = 1 AND 
                      ActivityType = N'WORKED' THEN SUM((CONVERT(decimal(10,2), vw_PayRollDataWithHoliday.WORKED) / 3600) * 1.5) ELSE SUM((CONVERT(decimal(10,2), 
                      vw_PayRollDataWithHoliday.WORKED) / 3600)) END AS Worked, Is_Paid, CASE WHEN IsHoliday IS NULL THEN 0 ELSE IsHoliday END AS Is_Holiday, 
                      PAYROLLDATAID
FROM         vw_PayRollDataWithHoliday
GROUP BY EmployeeID, StartDate, ExternalID, TempDept, ActivityType, Is_Paid, PAYROLLDATAID, CASE WHEN IsHoliday IS NULL THEN 0 ELSE IsHoliday END
HAVING      (StartDate BETWEEN @Startdate AND @Enddate) AND (ExternalID = @ExternalID) AND (ActivityType = N'WORKED')
ORDER BY StartDate

SELECT * FROM #tbl_WORKEDPayRoll

Sample of data out put....
EmpID StartDate ExternalID TempDept ActivityType Worked Is Paid IsHoliday TimeDataID
520691 2013-07-01 00:00:00.000 3662 249983 WORKED 6 1 0 9324
520691 2013-07-02 00:00:00.000 3662 249983 WORKED 2 1 0 9072
520691 2013-07-02 00:00:00.000 3662 249983 WORKED 6 1 0 9178
520691 2013-07-03 00:00:00.000 3662 249983 WORKED 2 1 0 8915
520691 2013-07-03 00:00:00.000 3662 249983 WORKED 6 1 0 9012
520691 2013-07-04 00:00:00.000 3662 249983 WORKED 2 1 1 10408
520691 2013-07-09 00:00:00.000 3662 249983 WORKED 6 1 0 8235
520691 2013-07-10 00:00:00.000 3662 249983 WORKED 8 1 0 8065
520691 2013-07-11 00:00:00.000 3662 249983 WORKED 8 1 0 7936

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-14 : 07:24:11

the reason is you've not specified precision and scale for the decimal field hence it will default to server set values

see

http://visakhm.blogspot.in/2010/02/importance-of-specifying-length-in.html

so code should be


CREATE TABLE #tbl_WORKEDPayRoll(
EmployeeID int,
StartDate datetime,
ExternalID int,
TempDept int,
ActivityType nchar(6),
Worked decimal(10,2),
Is_Paid bit,
Is_Holiday bit,
PAYROLLDATAID int)

INSERT INTO #tbl_WORKEDPayRoll
                      (EmployeeID, Startdate, ExternalID, TempDept, ActivityType, Worked, Is_Paid, Is_Holiday, PAYROLLDATAID)
SELECT     EmployeeID, StartDate, ExternalID, TempDept, ActivityType, CASE WHEN (CASE WHEN IsHoliday IS NULL THEN 0 ELSE IsHoliday END) = 1 AND 
                      ActivityType = N'WORKED' THEN SUM((CONVERT(decimal(10,2), vw_PayRollDataWithHoliday.WORKED) / 3600) * 1.5) ELSE SUM((CONVERT(decimal(10,2), 
                      vw_PayRollDataWithHoliday.WORKED) / 3600)) END AS Worked, Is_Paid, CASE WHEN IsHoliday IS NULL THEN 0 ELSE IsHoliday END AS Is_Holiday, 
                      PAYROLLDATAID
FROM         vw_PayRollDataWithHoliday
GROUP BY EmployeeID, StartDate, ExternalID, TempDept, ActivityType, Is_Paid, PAYROLLDATAID, CASE WHEN IsHoliday IS NULL THEN 0 ELSE IsHoliday END
HAVING      (StartDate BETWEEN @Startdate AND @Enddate) AND (ExternalID = @ExternalID) AND (ActivityType = N'WORKED')
ORDER BY StartDate

SELECT * FROM #tbl_WORKEDPayRoll

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Menorel
Starting Member

15 Posts

Posted - 2013-07-15 : 09:01:02

That was it, knew it was something simple I was missing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-16 : 00:58:59

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources