Compare two values in the same table

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)

Compare two values in the same table

Author

Topic

freshfitz
Starting Member

5 Posts

Posted - 2008-11-03 : 17:24:51

I have a sql table with cust_no, stdprice, and season. I want to see if stdprice is higher in season 2009 then 2008 by more then 30%. how would I do this in SQL since they are coming from the same table. in the table the cust_no is listed more then once, 1 listing for each season

Example
select cust_no, stdprice, season from program where status ='8' and season > '2007' and progdefid ='67'
order by cust_no

Cust_no|Stdprice|season
31 |44.00 |2008
31 |70.00 |2009
72 |54.00 |2009
72 |40.80 |2008

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-11-03 : 18:22:02

[code]
create table #program ( Cust_no int, Stdprice float, season int)

insert into #program
select 31 ,44.00 ,2008 union all
select 31 ,70.00 ,2009 union all
select 72 ,54.00 ,2009 union all
select 72 ,40.80 ,2008

select * from #program

select p2.* from
(select * from #program where season = 2008) p1
left join
(select * from #program where season = 2009) p2
on p1.cust_no=p2.cust_no
WHERE p2.Stdprice > p1.Stdprice*1.3

drop table #program

[/code]

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

freshfitz
Starting Member

5 Posts

Posted - 2008-11-03 : 19:57:12

Can i do it without creating the table cause there is 8000 records

freshfitz
Starting Member

5 Posts

Posted - 2008-11-03 : 20:02:31

Cool I think this worked THanks

select p2.* from
(select * from program where season = 2008 and status = 8 and progdefid = 67) p1
left join
(select * from program where season = 2009 and status = 8 and progdefid = 67) p2
on p1.cust_no=p2.cust_no
WHERE p2.Stdprice > p1.Stdprice*1.3
order by cust_no

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-04 : 01:18:20

Your WHERE clause is negating your LEFT JOIN.

SELECT		p2.*
FROM		Program AS p1
WHERE		p1.Season = 2008
		AND p1.Status = 8
		AND p1.ProgDefID = 67
LEFT JOIN	Program AS p2 ON p2.cust_no = p1.cust_no
			AND p2.Season = 2009
			AND p2.Status = 8
			AND p2.ProgDefID = 67
			AND p2.StdPrice > p1.StdPrice * 1.3
ORDER BY	p2.cust_no

SELECT		p2.*
FROM		Program AS p1
WHERE		p1.Season = 2008
		AND p1.Status = 8
		AND p1.ProgDefID = 67
INNER JOIN	Program AS p2 ON p2.cust_no = p1.cust_no
			AND p2.Season = 2009
			AND p2.Status = 8
			AND p2.ProgDefID = 67
WHERE		p2.StdPrice > p1.StdPrice * 1.3
ORDER BY	p2.cust_no

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources