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.
| Author |
Topic |
|
doran_doran
Posting Yak Master
179 Posts |
 Posted - 2008-07-14 : 11:54:25
|
| currentdbo.slip.total * dbo.project.custom_10 / 100 * - 1 as ratewant to castcast((dbo.slip.total * dbo.project.custom_10 / 100 * - 1), decimal(10,2)) as rateWhy my cast is not working? |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-07-14 : 11:59:53
|
| You don't have the right syntax. Replace the comma with the word 'as'.Ryan Randall Solutions are easy. Understanding the problem, now, that's the hard part. |
 |
|
|
doran_doran
Posting Yak Master
179 Posts |
Posted - 2008-07-14 : 12:03:34
|
| cast((dbo.slip.total * dbo.project.custom_10 / 100 * - 1) as decimal(10,2)) as rateI am using group by and this expression is should sum. I guess thats why I am getting an error. |
|
|
|
doran_doran
Posting Yak Master
179 Posts |
Posted - 2008-07-14 : 12:07:45
|
| I guess this should work. I will be able to verify in 20 minutes cause I need to refresh my db.SELECT dbo.slip.customer_id, dbo.project_task.project_id, dbo.project_task.id AS project_task_id, dbo.project_task.default_category AS category_id, '146' AS user_id, @pTDate AS date, 'T' AS Type, CAST(SUM(dbo.slip.total * dbo.project.custom_10 / 100 * - 1) AS decimal(10, 2)) AS rate, '1' AS decimal, CAST(SUM(dbo.slip.total * dbo.project.custom_10 / 100 * - 1) AS decimal(10, 2)) AS RetainageFee, '1' AS slip_stage_id, 'Retainage Fee @ 5%' AS description, 'Retainage withheld for the invoice dated: ' + CAST(@pTDate AS varchar(80)) AS note, '1' AS retainage_charge, dbo.project.name AS Project, dbo.project_task.name AS [Project Task], dbo.customer.company AS CompanyFROM dbo.slip INNER JOIN dbo.project_task ON dbo.slip.project_task_id = dbo.project_task.id INNER JOIN dbo.project ON dbo.project_task.project_id = dbo.project.id INNER JOIN dbo.customer ON dbo.project.customer_id = dbo.customer.idWHERE (dbo.project.custom_10 > 0) AND (dbo.project_task.custom_13 = N'1') AND (dbo.slip.custom_16 IS NULL) AND (dbo.slip.slip_stage_id = 0) AND (dbo.slip.invoice_id = 0) AND (dbo.slip.custom_17 IS NULL) AND (dbo.slip.deleted IS NULL)GROUP BY dbo.slip.customer_id, dbo.project_task.project_id, dbo.project_task.id, dbo.project_task.default_category, dbo.project_task.name, dbo.project.name, dbo.customer.company |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-07-14 : 12:31:47
|
You might find that using table aliases is easier to work with. You query would become something like...SELECT s.customer_id, t.project_id, t.id AS project_task_id, t.default_category AS category_id, '146' AS user_id, @pTDate AS date, 'T' AS Type, CAST(SUM(s.total * p.custom_10 / 100 * - 1) AS decimal(10, 2)) AS rate, '1' AS decimal, CAST(SUM(s.total * p.custom_10 / 100 * - 1) AS decimal(10, 2)) AS RetainageFee, '1' AS slip_stage_id, 'Retainage Fee @ 5%' AS description, 'Retainage withheld for the invoice dated: ' + CAST(@pTDate AS varchar(80)) AS note, '1' AS retainage_charge, p.name AS Project, t.name AS [Project Task], c.company AS CompanyFROM dbo.slip s INNER JOIN dbo.project_task t ON s.project_task_id = t.id INNER JOIN dbo.project p ON t.project_id = p.id INNER JOIN dbo.customer c ON p.customer_id = c.idWHERE (p.custom_10 > 0) AND (t.custom_13 = N'1') AND (s.custom_16 IS NULL) AND (s.slip_stage_id = 0) AND (s.invoice_id = 0) AND (s.custom_17 IS NULL) AND (s.deleted IS NULL)GROUP BY s.customer_id, t.project_id, t.id, t.default_category, t.name, p.name, c.company Ryan Randall Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
doran_doran
Posting Yak Master
179 Posts |
Posted - 2008-07-14 : 13:12:23
|
| Thank you Ryan. Worked like charm. Thanks again for bonus suggestion. LOL |
|
|
|
|
|
|
|
|
cast(formula,decimal(10,2))