how to use (with) statment in sql 2000 ??

Question

how to use (with) statment in sql 2000 ??

i have this code in sql 2008 but it dos't work in sql 2000

how to make it work without i losse my time to make new view

code in 2008

{

CREATE VIEW [dbo].[QRateDefMonumPrice]
AS
WITH ETC AS (SELECT RateDefID, SUM(Admission) AS SumAdmission
FROM dbo.QRateDefMonumDaysPrice
GROUP BY RateDefID)
SELECT RateDefID, SumAdmission, CASE WHEN [SumAdmission] != (FLOOR([SumAdmission])) THEN (FLOOR([SumAdmission]) + 1) ELSE (FLOOR([SumAdmission]))
END AS FixSumAdmission
FROM ETC AS ETC_1

GO

}

with dos't work in sql 2000

Answer 1

CREATE VIEW [dbo].[QRateDefMonumPrice]
AS
	SELECT	RateDefID, 
			SumAdmission, 
			CASE WHEN [SumAdmission] != (FLOOR([SumAdmission])) 
				 THEN (FLOOR([SumAdmission]) + 1) 
				 ELSE (FLOOR([SumAdmission]))
			END AS FixSumAdmission
	FROM	(
				SELECT	RateDefID, 
						SUM(Admission) AS SumAdmission
				FROM	dbo.QRateDefMonumDaysPrice
				GROUP BY RateDefID
			) AS ETC_1;

There is no WITH keyword in SQL 2000. Use a sub-query instead of it or upgrade (the extended support expires next year).

btw, one post is normally enough.