Using IF statement in SQL - 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

Using IF statement in SQL

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-12 : 08:35:56

I've these following table

tblPRK
Date_Taken | Time | MainID | Hit
---------------------------------------
5/1/2008 | 100 | 213 | 233
5/1/2008 | 300 | 213 | 230
5/1/2008 | 500 | 213 | 225
5/1/2008 | 100 | 214 | 125
5/1/2008 | 300 | 214 | 325
5/1/2008 | 500 | 214 | 225
5/2/2008 | 100 | 213 | 433
5/2/2008 | 300 | 213 | 234
5/2/2008 | 500 | 213 | 109
5/2/2008 | 100 | 214 | 546
5/2/2008 | 300 | 214 | 267
5/2/2008 | 500 | 214 | 207
...
...

MainID | StationID | State
-----------------------------------
213 | 00001 | PRK
214 | 00002 | PRK
215 | 00003 | SEL
216 | 00004 | PER
...
...
*StationID is foreign key to tblTempValue

tblTempValue
StationID | 3MthCumRf | 6MthCumRf | 9MthCumRf
----------------------------------------------------
00001 | 525.8 | 934.23 | 466.33
00002 | 435.5 | 765.34 | 388.98
00003 | 644.5 | 899.23 | 766.33
...
...
...

Requirement
-----------
1. From tblPRK, each Date_Taken and each MainID will display the MAX hit
2. If Date_Taken within 3month, query will display 3MthCumRf column from tblTempValue, If Date_Taken within 6month, query will display 6MthCumRf, if Date_Taken within 9month, query will display 9MthCumRf

So far, i've only below query. I'm lack of using IF Statement in SQL.

SELECT t1.MainID,t1.Date_Taken,t1.Time,t1.Hit
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY MainID, Date_Taken ORDER BY Hit DESC) AS RowNo,*
FROM dbo.tblPRK)t1
WHERE t1.RowNo=1 AND
t1.Date_Taken>=CONVERT(VARCHAR(10),DATEADD(m,-3,GETDATE()),101) AND
t1.Date_Taken<=CONVERT(VARCHAR(10), GETDATE(), 101)
order by t1.MainID,t1.Date_Taken

Expected Output
---------------
Date_Taken | Time | MainID | MaxHit | 3MthCumRf
----------------------------------------------------
5/1/2008 | 100 | 213 | 233 | 525.8
5/1/2008 | 300 | 214 | 325 | 435.5
5/2/2008 | 100 | 213 | 433 | 525.8
...
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 09:41:01

[code]SELECT t.Date_Taken,t.Time,t.MainID,t.Hit AS MaxHit,
CASE WHEN DATDIFF(mm,t.Date_Taken,GETDATE()) <=3 THEN tv.3MthCumRf
WHEN DATDIFF(mm,t.Date_Taken,GETDATE()) <=6 THEN tv.6MthCumRf
WHEN DATDIFF(mm,t.Date_Taken,GETDATE()) <=9 THEN tv.9MthCumRf
END
AS CumRf
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Date_Taken,MainID ORDER BY Hit DESC) AS RowNo,
*
FROM tblPRK)t
INNER JOIN LinkTable l
ON l.MainID=t.MainID
AND t.RowNo=1
INNER JOIN tblTempValue tv
ON tv.StationID =l.StationID[/code]

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-12 : 12:21:08

why i put
SELECT t.Date_Taken,t.Time,t.MainID,t.Hit AS MaxHit,
CASE WHEN DATEDIFF(mm,t.Date_Taken,GETDATE()) <=3 THEN tv.3MthCumRf
WHEN DATEDIFF(mm,t.Date_Taken,GETDATE()) <=6 THEN tv.6MthCumRf
WHEN DATEDIFF(mm,t.Date_Taken,GETDATE()) <=9 THEN tv.9MthCumRf
END
AS CumRf,t1.Hit-CumRf as Diff

there's a error Invalid column name 'CumRf'.

I want do calculation on the fly t.Hit-CumRf

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 12:43:36

quote:
Originally posted by wkm1925

why i put
SELECT t.Date_Taken,t.Time,t.MainID,t.Hit AS MaxHit,
CASE WHEN DATEDIFF(mm,t.Date_Taken,GETDATE()) <=3 THEN tv.3MthCumRf
WHEN DATEDIFF(mm,t.Date_Taken,GETDATE()) <=6 THEN tv.6MthCumRf
WHEN DATEDIFF(mm,t.Date_Taken,GETDATE()) <=9 THEN tv.9MthCumRf
END
AS CumRf,t1.Hit-CumRf as Diff

there's a error Invalid column name 'CumRf'.

I want do calculation on the fly t.Hit-CumRf

You cant use the CumRf alias directly. EIther you have to repeat the CASE WHEN or do like this:-

SELECT tmp.Date_Taken,tmp.Time,tmp.MainID,tmp.MaxHit,
tmp.MaxHit-tmp.CumRf
FROM
(
SELECT t.Date_Taken,t.Time,t.MainID,t.Hit AS MaxHit,
CASE WHEN DATDIFF(mm,t.Date_Taken,GETDATE()) <=3 THEN tv.3MthCumRf
     WHEN DATDIFF(mm,t.Date_Taken,GETDATE()) <=6 THEN tv.6MthCumRf
     WHEN DATDIFF(mm,t.Date_Taken,GETDATE()) <=9 THEN tv.9MthCumRf
END
AS CumRf
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Date_Taken,MainID ORDER BY Hit DESC) AS RowNo,
*
FROM tblPRK)t
INNER JOIN LinkTable l
ON l.MainID=t.MainID
AND t.RowNo=1
INNER JOIN tblTempValue tv
ON tv.StationID =l.StationID
)tmp

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-12 : 21:47:54

Kindest Mr. Visakh16,

Tq very much. Your wisdom my inspiration.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources