Case Where Logic In View, Is this possible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Case Where Logic In View, Is this possible?

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-02-13 : 09:40:37

Within a view, how can I accomodate if/else within a where statement? I have to use a view for this requirment. From what I can tell, you can't use if/else in a view. Does anyone know how I can accomplish somthing similar to below using a case statement inside a a VIEW?

Partial Contents of view:

...
...
LEFT OUTER JOIN
(
SELECT cola, SUM(colb) AS b_volume FROM dbo.MyTable
IF month(getDate()) = 1
BEGIN
WHERE YEAR(Begin_Date) = YEAR(GETDATE()) - 1 AND colc LIKE 'test%'
END
ELSE
BEGIN
WHERE YEAR(Begin_Date) = YEAR(GETDATE()) - 1 AND colc LIKE 'test%'
END
GROUP BY cola
) AS YTD_Vol
ON YTD_Vol.cola = ........

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-13 : 09:47:00

What is the difference between IF and ELSE in the example above??

Your previous thread was about the same problem I think...

WHERE (YEAR(Begin_Date) = YEAR(dateadd(month,-1,GETDATE())) AND (a_column LIKE 'test')

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 10:03:28

you cant use IF ELSE like this within SELECT
it should be CASE...WHEN instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-02-13 : 10:10:32

My bad, the else should read:

BEGIN
WHERE YEAR(Begin_Date) = YEAR(GETDATE()) AND colc LIKE 'test%'
END

I did not want to add this to my original post because I am now talking about views...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-13 : 10:15:50

Try this please.
WHERE (YEAR(Begin_Date) = YEAR(dateadd(month,-1,GETDATE())) AND (a_column LIKE 'test')

dateadd month -1 will subtract 1 month and if the date is in january then subtracting 1 month will also decrease the year.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 10:17:58

whats the purpose of this?
which period of data you're trying to retrive?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 10:21:07

do you mean this?


SELECT cola, SUM(colb) AS b_volume FROM dbo.MyTable
WHERE Begin_Date>= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-11,0)
AND Begin_Date<DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
AND colc LIKE 'test%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-02-13 : 12:56:18

I am trying to retreive all of the previous years records if current month is Jan. Otherwise, retreive all records of current year (YTD).....

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-13 : 13:29:38

WHERE DATEDIFF(dd,CONVERT(datetime,CONVERT(varchar(7),GetDate(),120)+'-01'),'09/11/2001') < 0

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-13 : 14:05:47

Here is one way:

SELECT 
    cola, 
    SUM(colb) AS b_volume 
FROM 
    dbo.MyTable
WHERE 
    Begin_Date >= 
        CASE 
            WHEN MONTH(CURRENT_TIMESTAMP) = 1 
                THEN DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP) - 1, 0)
                ELSE DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0)
        END
    AND Begin_Date <       
        CASE 
            WHEN MONTH(CURRENT_TIMESTAMP) = 1 
                THEN DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0)
                ELSE DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP) + 1, 0)
        END
    AND colc LIKE 'test%'

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-02-14 : 09:59:32

Thanks to all for the assistance, I could not wrap my head around this one.

Lamprey's solution was exactly what I was trying to do.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources