SELECT
assignment.TskCod,
task.TskHHP,
IF(AssType = 'install', task.TskHHP * task.TskI_Percentage, task.TskHHP * task.TskR_Percentage) AS SubTotal
FROM
assignment
INNER JOIN task ON (assignment.TskCod = task.TskCod)
WHERE
AssDate >= '2011-01-01' AND
AssDate <= '2011-01-31'
BEGIN
/* It did not recognize this, so I comment it */
/*SET NOCOUNT ON; */
WITH a AS(
SELECT
IF(AssType = 'install', task.TskHHP * task.TskI_Percentage, task.TskHHP * task.TskR_Percentage) as SubTotal
FROM
assignment
INNER JOIN task ON (assignment.TskCod = task.TskCod)
WHERE
AssDate >= '2011-01-01' AND
AssDate <= '2011-01-31';
)
SELECT SUM(SubTotal)
FROM a;
END
CREATE PROCEDURE store_procedure_name
AS
BEGIN
SET NOCOUNT ON;
WITH A AS
(
SELECT field1, field2
from table1
where some condition
)
SELECT sum(field1) as mytotal
FROM a
group by field2
END
GO