how to use the subquery with in the case statement in mssql 2008
50,187
@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!
Comments
-
Saranya Jothiprakasam almost 2 years
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
-
Narendra almost 12 yearsedited :). hope you can read it now.
-
whytheq almost 12 years+1 @AaronBertrand ... I shouldn't encourage you
-