SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 int to decimal but coming out int
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Menorel
Starting Member

USA
15 Posts

Posted - 07/14/2013 :  03:04:50  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 07/14/2013 :  07:24:11  Show Profile  Reply with Quote
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
Go to Top of Page

Menorel
Starting Member

USA
15 Posts

Posted - 07/15/2013 :  09:01:02  Show Profile  Reply with Quote
That was it, knew it was something simple I was missing.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/16/2013 :  00:58:59  Show Profile  Reply with Quote
Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000