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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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  
 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 1.11 seconds. Powered By: Snitz Forums 2000