April 8, 2013 at 6:57 am
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
April 8, 2013 at 7:29 am
-- 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)
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
April 8, 2013 at 7:33 am
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.
April 8, 2013 at 7:52 am
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.
April 8, 2013 at 7:56 am
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
April 8, 2013 at 8:03 am
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)
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
April 8, 2013 at 8:43 am
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
April 8, 2013 at 9:01 am
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 = xxxxAND 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 😉
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
April 8, 2013 at 9:50 am
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.
April 8, 2013 at 9:58 am
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);
April 8, 2013 at 3:09 pm
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.
April 8, 2013 at 3:20 pm
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?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply