CTE - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

CTE

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-03-28 : 18:56:53

I am trying to use CTE to get both YTDrevenue and MTD revenue. my question is how I can join YTDrevenue and MTDrevenue to get YTDrevenue.payments and MTDrevenue.payments?

with YTDrevenue
AS
( select sum(payments)as payments from revenue_aggregate a
join Revenue_aggregate_assertions b on a.[month] = b.[month] and a.[year] = b.[year] and a.[Unit_abbr] = b.[Unit_abbr] and a.financial_class_code = b.financial_class_code
WHERE ((a.[month] <= 12) AND (a.[year] = 2005) AND (a.[unit_abbr] = 'AA'))
)

,
MTDrevenue
AS
(
select sum(payments)as payments from revenue_aggregate a
join Revenue_aggregate_assertions b on a.[month] = b.[month] and a.[year] = b.[year] and a.[Unit_abbr] = b.[Unit_abbr] and a.financial_class_code = b.financial_class_code
WHERE ((a.[month] = 12) AND (a.[year] = 2005) AND (a.[unit_abbr] = 'AA'))
)

select YTDrevenue.payments,MTDrevenue.payments
from MTDrevenue join YTD revenue

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources