Select Query for flat table - 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)

Select Query for flat table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-15 : 10:14:33

Paul writes "My data tables have been reorganized and now I have a new problem. My Where clause has to be restructured in a way that is unknown to me.

I now have columns named: Period, Symbol, Revenue

This is a flat table at this point. The Periods repeat themselves numerous times (as do the Symbols), like:

Period,Symbol,Revenue
Q1_2003, A, 12
Q2_2003, A, 13
Q3_2003, A, 11
Q4_2003, A, 12
Q1_2003, B, 11
Q2_2003, B, 13
Q3_2003, B, 14
Q4_2003, B, 15

The query criteria is to be:

Revenue for Q3_2003 > Revenue for Q2_2003 > Revenue for Q1_2003

I don't want sums. I want to return a list of Symbols that match the Revenue criteria above, ie Q3>Q2>Q1. In my example above, only Symbol B would be returned.

This was easy when the column headers were the Periods and the rows were the Symbols and the record data was Revenue.

If this is possible to do, I sure would appreciate some help.

Thanks,

Paul"

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-15 : 11:16:54

select Symbol
from (select Symbol, Revenue = sum(Revenue) from tbl where Period = 'Q1_2003' group by Symbol) Q1_2003
join (select Symbol, Revenue = sum(Revenue) from tbl where Period = 'Q2_2003' group by Symbol) Q2_2003
on Q1_2003.Symbol = Q2_2003.Symbol
join (select Symbol, Revenue = sum(Revenue) from tbl where Period = 'Q3_2003' group by Symbol) Q3_2003
on Q1_2003.Symbol = Q3_2003.Symbol
where Q3.2003.Revenue > Q3.2003.Revenue
and Q2.2003.Revenue > Q1.2003.Revenue

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-15 : 11:20:27

Actually, it's not a flat table, it's a normalized table -- which is a good thing ! sometimes it can be a little more work to write some queries, as you mention, but trust me : it's worth it !

Unfortunately, you would be a little better off if the quarter and the year were seperate columns (it would make this query much easier to make more generic), but you're still OK I think.

one way to do it is:


select 
  a.symbol
from 
  yourtable a
inner join 
  (select symbol from Yourtable where period = 'Q2_2003') Q2
on
  a.symbol = Q2.symbol and a.Revenue > Q2.Revenue
inner join
  (select symbol from Yourtable where period = 'Q1_2003') Q1
on
  a.symbol = Q1.symbol and Q2.Revenue > Q1.Revenue
where
  a.Period = 'Q3_2003'

Not very generic at this point ... also, what do you want to return when there are NO rows at all for Q1 or Q2 ? the above won't return anything....

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-15 : 13:05:17

oh -- one more way to do it: you mentioned that is was easier the old way. So why not make it look like the old way in a VIEW, and then use that?

And, this will make it more generic as well so try it out:


CREATE View OldWay
AS
SELECT 
  Right(Period,4) as Year, 
  Symbol, 
  SUM(CASE WHEN Left(Period,2) = 'Q1' THEN Revenue ELSE 0 END) as Q1,
  SUM(CASE WHEN Left(Period,2) = 'Q2' THEN Revenue ELSE 0 END) as Q2,
  SUM(CASE WHEN Left(Period,2) = 'Q3' THEN Revenue ELSE 0 END) as Q3,
  SUM(CASE WHEN Left(Period,2) = 'Q4' THEN Revenue ELSE 0 END) as Q4
FROM
  YourTable
GROUP BY Right(Period,4), Symbol

Check it out, see what it returns.

and then you can say:

SELECT * from OldWay
WHERE Year = 2003
AND (Q3 > Q2) and (Q2 > Q1)

Just because the data is stored different doesn't always mean you have to work with it differently.

- Jeff

kloepper
Yak Posting Veteran

77 Posts

Posted - 2003-12-15 : 13:09:39

Not very generic at this point ... also, what do you want to return when there are NO rows at all for Q1 or Q2 ? the above won't return anything....

- Jeff
[/quote]

I just lost a long post to you, so here's the short version...

I'm acquiring data in Excel so the empty rows have all been taken care of. Ultimately, I will acquire data via xml/xbrl so that'll be an entirely new ball of wax.

I haven't tried your code yet, but I will.

Currently, I'm testing this code:

CREATE VIEW dbo.Improving_Revenue
AS
select Name
from Data_01
group by Name
having
max(case when Period = 'Q3_2003' then Revenue else -2000000000 end)
> max(case when Period = 'Q2_2003' then Revenue else -2000000000 end)
and
max(case when Period = 'Q2_2003' then Revenue else -2000000000 end)
> max(case when Period = 'Q1_2003' then Revenue else -2000000000 end)

Thanks much for your help

,

Paul

kloepper
Yak Posting Veteran

77 Posts

Posted - 2003-12-15 : 13:13:27

It appears that I have 4 different ways of doing this and being new to sql, my head is spinning now

...I'll test your code as soon as I can.

Currently, I'm testing this code:

CREATE VIEW dbo.Improving_Revenue
AS
select Name
from Data_01
group by Name
having
max(case when Period = 'Q3_2003' then Revenue else -2000000000 end)
> max(case when Period = 'Q2_2003' then Revenue else -2000000000 end)
and
max(case when Period = 'Q2_2003' then Revenue else -2000000000 end)
> max(case when Period = 'Q1_2003' then Revenue else -2000000000 end)

Thanks much for your help ,

Paul

kloepper
Yak Posting Veteran

77 Posts

Posted - 2003-12-15 : 13:17:46

Jeff:

Thanks again. I'm a one armed paper hanger today

, so I'll get to this eventually.

This will help me understand things better when I can see multiple solutions to the same problem.

Sincerely,

Paul

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-15 : 13:29:22

Paul --

Try to avoid hard-coding in dates with years and all that into Views. Check out the view I created, and see how it returns 1 row per year per symbol, and then 1 column per quarter but named w/o the year.

If you leave the VIEWs very generic like that, you can just filter them later on to return the data that you need.

So, a modified version of the view I gave you that would be really handy might be:


CREATE View SymbolRevenueByYear
AS
SELECT 
  Right(Period,4) as Year, 
  Symbol, 
  SUM(CASE WHEN Left(Period,2) = 'Q1' THEN Revenue ELSE 0 END) as Q1,
  SUM(CASE WHEN Left(Period,2) = 'Q2' THEN Revenue ELSE 0 END) as Q2,
  SUM(CASE WHEN Left(Period,2) = 'Q3' THEN Revenue ELSE 0 END) as Q3,
  SUM(CASE WHEN Left(Period,2) = 'Q4' THEN Revenue ELSE 0 END) as Q4,
  SUM(Revenue) as Total
FROM
  YourTable
GROUP BY Right(Period,4), Symbol

Good luck !

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources