Please help me out in this case statements

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 2005 Forums

Transact-SQL (2005)

Please help me out in this case statements

Author

Topic

sunny4
Starting Member

2 Posts

Posted - 2008-02-13 : 12:47:03

SELECT vw_LM_AM1.LM_MBR_KEY, dbo.tbl_LM_MBR_DEMO.GRP_NBR, YEAR(vw_LM_AM1.maxeff_dt) AS Eff_Year,
YEAR(vw_LM_AM1.maxref_dt) AS Ref_Year, DATEPART(quarter, vw_LM_AM1.maxeff_dt) AS Eff_Qtr, DATEPART(quarter,
vw_LM_AM1.maxref_dt) AS Ref_Qtr, dbo.tbl_LM_MBR_DEMO.Line_of_business, vw_LM_AM1.maxeff_dt (= case When
(u30597.vw_LM_AM1.maxeff_dt < 1/1/2007 and u30597.vw_LM_AM1.maxeff_dt > 1/31/2007) THEN '2007' else 'eff_year' end,....This line is test line.)
vw_LM_AM1.maxref_dt
FROM vw_LM_AM1 INNER JOIN
dbo.tbl_LM_MBR_DEMO ON vw_LM_AM1.LM_MBR_KEY = dbo.tbl_LM_MBR_DEMO.LM_MBR_KEY

This is a sample syntax for my Report.

Here i need 3 fields from eff_year i.e...2007,2008 and Cumulative.

For 2007 i need data between 1-1-2007 and 12-31-2007,
for YTD2008 ineed data from 1-1-2008 to date,
for Cumulative field i have to show data from 1-1-2007 to current data.
This is the view i am querying..please guide me where i am going wrong !!!!!!!

thanks.

talleyrand
Starting Member

35 Posts

Posted - 2008-02-13 : 18:20:55

Your syntax is wrong for the case statement.

, vw_LM_AM1.maxeff_dt (= case When
(u30597.vw_LM_AM1.maxeff_dt < 1/1/2007 and u30597.vw_LM_AM1.maxeff_dt > 1/31/2007) THEN '2007' else 'eff_year' end,....This line is test line.)
vw_LM_AM1.maxref_dt

You are probably looking for something like
, CASE
WHEN
(u30597.vw_LM_AM1.maxeff_dt < 1/1/2007 AND u30597.vw_LM_AM1.maxeff_dt > 1/31/2007)
THEN '2007'
ELSE 'eff_year'
END AS CaseStatement

or this depending on where you like your identifier (CaseStatement in this example)

, CaseStatement = CASE
WHEN
(u30597.vw_LM_AM1.maxeff_dt < 1/1/2007 AND u30597.vw_LM_AM1.maxeff_dt > 1/31/2007)
THEN '2007'
ELSE 'eff_year'
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources