how to use the subquery with in the case statement in mssql 2008

Question

I need to change the following procedure if else statement into a single query using the select case statement with the subquery...

 if((select COUNT(*) from pseb.dbo.meterattributedetails where meterid=@meterid)=0)
  select @emf=EMF from pseb.dbo.METERMASTER where MeterID=@meterid
  else  if((select COUNT(*) from pseb.dbo.meterattributedetails where meterid=@meterid and dateadd(day,1,@fromdate)<DateTime)>0) 
    select top 1 @emf=oldvalue from pseb.dbo.meterattributedetails  where MeterID=@meterid and dateadd(day,1,@fromdate)<datetime order by DateTime 
     else 
  select top 1 @emf=newvalue from pseb.dbo.meterattributedetails  where meterid=@meterid and DateTime<@fromdate order by DateTime desc

meterattribute table structure as like this:

AttributeID AttributeName   Oldvalue    newvalue    DateTime    meterid
1           EMF         2.00000         4.00000        2012-07-05   4756
1           EMF         4.00000         6.00000        2012-07-10   4756
1           EMF         6.00000         8.00000        2012-07-15   4756
1           EMF         8.00000        10.00000        2012-07-25   4756

in that table datetime refers the emf changed date, if my input is '2012-07-17' means i have to fetch he newvalue for that particular date,,, in between the july 15-25 the emf value is 8.00000.

---

I formatted your code using http://www.dpriver.com/pp/sqlformat.htm -- Michael

IF( (SELECT Count(*) 
     FROM   pseb.dbo.meterattributedetails 
     WHERE  meterid = @meterid) = 0 ) 
  SELECT @emf = emf 
  FROM   pseb.dbo.metermaster 
  WHERE  meterid = @meterid 
ELSE IF( (SELECT Count(*) 
     FROM   pseb.dbo.meterattributedetails 
     WHERE  meterid = @meterid 
            AND Dateadd(day, 1, @fromdate) < datetime) > 0 ) 
  SELECT TOP 1 @emf = oldvalue 
  FROM   pseb.dbo.meterattributedetails 
  WHERE  meterid = @meterid 
         AND Dateadd(day, 1, @fromdate) < datetime 
  ORDER  BY datetime 
ELSE 
  SELECT TOP 1 @emf = newvalue 
  FROM   pseb.dbo.meterattributedetails 
  WHERE  meterid = @meterid 
         AND datetime < @fromdate 
  ORDER  BY datetime DESC 

Answer 1

With no attempt at syntax checking, I think you're looking for something like this:

select @emf = case
  when not exists ( select 42 from pseb.dbo.meterattributedetails where meterid = @meterid )
    then ( select emf from pseb.dbo.metermaster where meterid = @meterid )
  when exists ( select 42 from pseb.dbo.meterattributedetails where meterid = @meterid and
    Dateadd(day, 1, @fromdate) < datetime )
    then ( select top 1 oldvalue from pseb.dbo.meterattributedetails where meterid = @meterid and
      Dateadd(day, 1, @fromdate) < datetime order by datetime )
  else ( select 1 newvalue from pseb.dbo.meterattributedetails where meterid = @meterid and
    datetime < @fromdate order by datetime desc )
  end

EDIT: In general, if you are obtaining a count to check against zero you can replace it with a suitable EXISTS with better performance.

Answer 2

@Michael - thanks for the link to http://www.dpriver.com/pp/sqlformat.htm.

Try this (untested, of course)

SELECT @emf = CASE 
            WHEN ( (SELECT Count(*) 
                    FROM   pseb.dbo.meterattributedetails 
                    WHERE  meterid = @meterid) = 0 ) THEN 
            (SELECT emf 
             FROM   pseb.dbo.metermaster 
             WHERE  meterid = @meterid) 
            WHEN ( (SELECT Count(*) 
                    FROM   pseb.dbo.meterattributedetails 
                    WHERE  meterid = @meterid 
                           AND Dateadd(day, 1, @fromdate) < datetime) > 0 ) 
          THEN ( 
            SELECT 
            TOP 1 oldvalue 
            FROM 
          pseb.dbo.meterattributedetails 
            WHERE 
          meterid = @meterid 
          AND Dateadd(day, 1, @fromdate) < datetime 
            ORDER 
          BY datetime) 
            ELSE (SELECT TOP 1 newvalue 
                  FROM   pseb.dbo.meterattributedetails 
                  WHERE  meterid = @meterid 
                         AND datetime < @fromdate 
                  ORDER  BY datetime DESC) 
          END 

@Saranya: if this doesn't work, how about setting up a test case on http://sqlfiddle.com/

Good luck!