HAVING and Case problem - 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)

HAVING and Case problem

Author

Topic

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-01-05 : 17:09:39

I'm reorganizing my tables/joins and need to re-write this.

I'm trying to return revenue that is greater in the period Q3 than in Q2 and Q2 > Q1.

I think the FROM is OK, but the HAVING portion is not correct because of the change in the join.

Any ideas on what is correct?

Thanks,

Paul

CREATE VIEW dbo.Improving_Revenue
AS
SELECT ak_Period_Year_Quarter.Period, ak_Name_Symbol.Name, ak_Name_Symbol.Symbol, ak_Hyperlinks.MSN_10Qs, ak_Data_01.Revenue
FROM ak_Data_01 INNER JOIN
ak_Name_Symbol ON ak_Data_01.[Lookup to ak_Name_Symbol] = ak_Name_Symbol.Symbol INNER JOIN
ak_Period_Year_Quarter ON ak_Data_01.[Lookup to ak_Period_Year_Quarter] = ak_Period_Year_Quarter.Period INNER JOIN
ak_Hyperlinks ON ak_Data_01.[Lookup to ak_Hyperlinks] = ak_Hyperlinks.Yahoo_Main
GROUP BY ak_Period_Year_Quarter.Period, ak_Name_Symbol.Name, ak_Name_Symbol.Symbol, ak_Hyperlinks.MSN_10Qs, ak_Data_01.Revenue
HAVING
max(case when ak_Period_Year_Quarter.Period = '2003_Q3' then ak_Data_01.Revenue else -2000000000 end)
> max(case when ak_Period_Year_Quarter.Period = '2003_Q2' then ak_Data_01.Revenue else -2000000000 end)
and
max(case when ak_Period_Year_Quarter.Period = '2003_Q2' then ak_Data_01.Revenue else -2000000000 end)
> max(case when ak_Period_Year_Quarter.Period = '2003_Q1' then ak_Data_01.Revenue else -2000000000 end)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-05 : 18:15:07

Simplifying things, your basic (without HAVING) select produces this:


-------------------------
QQ  ...   ...   Revenue
-------------------------
Q3              5
Q3              8
Q3              4
Q2              2
Q2              6
Q1              2
Q1              3
-------------------------

What rows from this query would you like to get?

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-01-05 : 18:54:44

-------------------------
QQ Symbol ... Revenue
-------------------------
Q3 A 5
Q3 B 8
Q3 C 4
Q2 A 2
Q2 B 6
Q1 A 2
Q1 B 3
-------------------------
Symbol B qualifies

quote:
Originally posted by Stoad

Simplifying things, your basic (without HAVING) select produces this:

-------------------------
QQ ... ... Revenue
-------------------------
Q3 5
Q3 8
Q3 4
Q2 2
Q2 6
Q1 2
Q1 3
-------------------------

What rows from this query would you like to get?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-05 : 20:03:06

Think it is. Here Query is your view from your 1st post but
without HAVING clause (btw, GROUP BY is also useless there).

select Period, Revenue from Query where Period='Q1' and
Revenue>(select max(Revenue) from Query where Period='Q2' and
Revenue>(select max(Revenue) from Query where Period='Q1'))
UNION ALL
select Period, Revenue from Query where Period='Q2' and
Revenue>(select max(Revenue) from Query where Period='Q1')
UNION ALL
select max(Period), max(Revenue) from Query where Period='Q1'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources