WHERE > CASE > IN

Question

Post reply

WHERE > CASE > IN

lanky_doodle

SSCrazy

Points: 2378
More actions
April 8, 2013 at 6:57 am

#273156

Hey,
I'm using MONTH and YEAR functions to match on current month and current year. If the Month is 12 (December) I want to include Jan of the next year
This is the current code:
WHERE MONTH(abr.ABR_Shift_Date) IN (MONTH(GETDATE()), MONTH(DATEADD(m, 1, GETDATE())))
AND YEAR(abr.ABR_Shift_Date) IN (YEAR(GETDATE()), YEAR(DATEADD(yy, 1, GETDATE())))
Can I do anything this (not working):
AND CASE WHEN YEAR(abr.ABR_Shift_Date) < 12 THEN YEAR(abr.ABR_Shift_Date) IN (YEAR(GETDATE()), YEAR(DATEADD(yy, 1, GETDATE()))) ELSE YEAR(abr.ABR_Shift_Date) IN (YEAR(GETDATE())) END
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

-- try using SARGable ranges instead of non-SARGable functions on your columns:

SELECT *

FROM (SELECT ABR_Shift_Date = GETDATE()) abr

CROSS APPLY (

SELECT

ThisyearLower = DATEADD(month,DATEDIFF(MONTH,0,GETDATE()),0), -- 1st April this year

ThisyearUpper = DATEADD(month,2+DATEDIFF(MONTH,0,GETDATE()),0), -- 1st June this year

NextyearLower = DATEADD(year,1,DATEADD(month,DATEDIFF(MONTH,0,GETDATE()),0)), -- 1st April next year

NextyearUpper = DATEADD(year,1,DATEADD(month,2+DATEDIFF(MONTH,0,GETDATE()),0)) -- 1st June next year

) Ranges

WHERE (abr.ABR_Shift_Date >= Ranges.ThisyearLower AND abr.ABR_Shift_Date < Ranges.ThisyearUpper)

OR (abr.ABR_Shift_Date >= Ranges.NextyearLower AND abr.ABR_Shift_Date < Ranges.NextyearUpper)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Wrapping functions around columns isn't a good idea if it can be avoided as indexes can't be used.

Give this a go and see if it's what you're after:

WHERE abr.ABR_Shift_Date >= DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) --start of this month

AND abr.ABR_Shift_Date < DATEADD(mm, DATEDIFF(mm,0,getdate())+1, 0) --first day of next month

E.g. you're just manipulating the static parameters, rather than manipulating the column.

lanky_doodle SSCrazy Points: 2378 More actions · Answer 3

The thing is I only want it to include the next year if the current month is 12.

So if the current month is Jan-Nov only take dates from this year. If it's Dec take Dec of this year and Jan of next year.

I'm showing a scheduled absence list comprising the current month and the next month only.

HowardW One Orange Chip Points: 29109 More actions · Answer 4

lanky_doodle (4/8/2013)
The thing is I only want it to include the next year if the current month is 12.
So if the current month is Jan-Nov only take dates from this year. If it's Dec take Dec of this year and Jan of next year.
I'm showing a scheduled absence list comprising the current month and the next month only.

So, just this then?

WHERE abr.ABR_Shift_Date >= DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) --start of this month

AND abr.ABR_Shift_Date < DATEADD(mm, DATEDIFF(mm,0,getdate())+2, 0) --first day of two months time

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

lanky_doodle (4/8/2013)
The thing is I only want it to include the next year if the current month is 12.
So if the current month is Jan-Nov only take dates from this year. If it's Dec take Dec of this year and Jan of next year.
I'm showing a scheduled absence list comprising the current month and the next month only.

Can you summarise as "this month and next month"? If so, then it's in the code I posted above:

DECLARE @Today DATE

SET @Today = GETDATE()+263 -- 27TH December 2013

SELECT

ThisyearLower = DATEADD(month,DATEDIFF(MONTH,0,@Today),0),

ThisyearUpper = DATEADD(month,2+DATEDIFF(MONTH,0,@Today),0)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

lanky_doodle SSCrazy Points: 2378 More actions · Answer 6

Thanks both. Although I didn't exactly copy/paste, with the guidance above this is what works for me;

WHEREUser_ID = xxxx

AND MONTH(Date) = xxxx

AND (MONTH(GETDATE()) < 12 AND YEAR(Date) = YEAR(GETDATE())

OR MONTH(GETDATE()) = 12 AND YEAR(Date) IN (YEAR(GETDATE()), YEAR(DATEADD(yyyy, 1, GETDATE()))))

ORDER BYDate

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

lanky_doodle (4/8/2013)
Thanks both. Although I didn't exactly copy/paste, with the guidance above this is what works for me;
WHEREUser_ID = xxxx
AND MONTH(Date) = xxxx
AND (MONTH(GETDATE()) < 12 AND YEAR(Date) = YEAR(GETDATE())
OR MONTH(GETDATE()) = 12 AND YEAR(Date) IN (YEAR(GETDATE()), YEAR(DATEADD(yyyy, 1, GETDATE()))))
ORDER BYDate

Getting the correct results is Step 1 of the three basic steps in writing a SQL Server statement. The other two are "Make it fast" and "Make it pretty" (documentation). This code will be slow because, even if you have an index on [Date] to support date-filtered queries against the table, it won't be used. If you're not familiar with the date arithmetic above, it's well worth spending some time on it now so you understand it in the future. And of course, you'll need to "Make it pretty" so other folks looking at your tight, efficient code will know how it works 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Here is what would really help, the DDL (CREATE TABLE statement) for the table(s) involved, sample data (as a series of INSERT INTO statements) for the table(s), and expected resutls bassed on the sample data.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

If what you are looking for is Current month (say 2013-04) and next month (2013-05), the following will work (it will also work if currrent month is 2013-12 and next month is 2014-01):

select

*

from

dbo.YourTable

where

YourDateColumn >= dateadd(month, datediff(month,0,getdate()),0) and

YourDateColumn < dateadd(month, datediff(month,0,getdate()) + 2,0);

SQLWannabe SSCrazy Points: 2842 More actions · Answer 10

Wow, is it really that easy? Evidently I was trying to make it harder than it actually is/was.

Lowell, I've read several posts where people keep saying stuff like, "If you had DDL and sample data, that would help". So I figured that if I had invested some time with a little DDL and sample data, I might get a better response.

Thanks again, both of you.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

SQLWannabe (4/8/2013)
Wow, is it really that easy? Evidently I was trying to make it harder than it actually is/was.
Lowell, I've read several posts where people keep saying stuff like, "If you had DDL and sample data, that would help". So I figured that if I had invested some time with a little DDL and sample data, I might get a better response.
Thanks again, both of you.

Posting DDL, sample data, and expected results based on the sample data can greatly increase the accuracy and validity of responses. Sometimes shots in the dark are correct, but which would you rather have when trying to get help solving a problem?