Select Case With Date - 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

Select Case With Date

Author

Topic

hockeyman9474
Starting Member

11 Posts

Posted - 2013-08-02 : 09:39:08

OK so I want to use datepart(month,getdate() with a Case statement with this logic:

SELECT CASE
datepart(month,getdate()='8' then
SELECT *
FROM [Master]
WHERE [Month]='Jul'
END

OK what's wrong with the code?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-02 : 09:49:26

Not clear to me what you are trying to do. Are you trying to get the prior month - like when month = 8 (which is August), you want to get the string for the prior month - July? Or is that a typo?

Try one of these:

SELECT
	CASE WHEN DATEPART(MONTH,GETDATE()) = 8 THEN 'Jul' END

SELECT LEFT(DATENAME(Month,GETDATE()),3)

SELECT LEFT(DATENAME(Month,DATEADD(mm,-1,GETDATE())),3)

hockeyman9474
Starting Member

11 Posts

Posted - 2013-08-02 : 09:53:38

You're partially correct. What I actually need to do is add quantities for the prior three months(prior to current month)together.So if we are in August, I need to access my [Master] table and add the quantities Where [Month]=May,June,July

I need to do this for every month, therefore a select case statement would come in handy.

hockeyman9474
Starting Member

11 Posts

Posted - 2013-08-02 : 10:02:26

So here's my code, only I am getting an error on SELECT sun(QTY) and it's looking for something after END

SELECT CASE
WHEN datepart(month,getdate())=8 THEN
SELECT sum(QTY)
FROM[MASTER]
WHERE [MONTH]='May' and [MONTH]='Jun' and[MONTH]= 'Jul'
END

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-02 : 10:30:29

Are the two columns in your table QTY and MONTH? Is MONTH stored as strings such as May, Jun, Jul? Then your query would be something like this:

SELECT
	SUM(Qty)
FROM
	Tbl
WHERE
	MONTH IN 
	(
		LEFT(DATENAME(MONTH,DATEADD(month,-1,GETDATE())),3),
		LEFT(DATENAME(MONTH,DATEADD(month,-2,GETDATE())),3),
		LEFT(DATENAME(MONTH,DATEADD(month,-3,GETDATE())),3)
	);

Storing dates/months in this manner as a string is not a good thing for a database. That makes querying and maintaining it very hard. Ideally you should use a date column with a date or datetime data type.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-02 : 10:30:40

If you have to do this every month, instead of hardcoding a specific value,
you can do this:

[CODE]

SELECT SUM(QTY)
FROM[MASTER]
WHERE [MONTH] in (LEFT(DATENAME(Month, DATEADD(mm, -1, GETDATE())),3),
LEFT(DATENAME(Month, DATEADD(mm, -2, GETDATE())),3),
LEFT(DATENAME(Month, DATEADD(mm, -1, GETDATE())),3));
[/CODE]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-02 : 10:42:57

Don't feel bad Mumu88, I sniped you by only 11 seconds. A close range shot!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources