How to write the Query - 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

General SQL Server Forums

New to SQL Server Programming

How to write the Query

Author

Topic

AAAV
Posting Yak Master

152 Posts

Posted - 2010-06-25 : 10:01:06

i have a query like

Select Avg(cost1) from table1 where year = year1

on checking certain condition
i have to either do (year=year1) when true
(year = year1 or year=year1-1) when false

is there a way for me to get it done in one sql instead of writing 2 sql?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-25 : 10:13:22

Select Avg(cost1) from table1
where (year = year1 and @param='true') or ((year = year1 or year=year-1) and @param='false')

Madhivanan

Failing to plan is Planning to fail

AAAV
Posting Yak Master

152 Posts

Posted - 2010-06-25 : 10:23:18

Great Thanks...
I kind of did a similar approach... set the year2 val to 0 when the condition fails...(there will be no record with year 2=0 in the table)
but your answer is perfect.

Sachin.Nand

2937 Posts

Posted - 2010-06-25 : 10:31:37

I wonder how it can be done using case.


select * from table where year=
case  'your  condition' when true then year1
when false then year1 --surely this is illegal and year1-1 
end

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources