January 4, 2014 at 11:01 am
Jeff Moden (1/4/2014)
sql crazy kid (1/4/2014)
Hello,First i would thank to Michael ,i never known in date function by default 0 treat as beginning of the date[1900-01-01] which drives here to get the corresponding start and end month.
I think below coding also server your purpose. This just another option..
You are in current month, then travel to 15 days back ,again subtract exact day to reach month end.
For the next month end just to add 1 month to previous code , you will stand in between your data :hehe:
1-15 - drive to previous month
16-31 -stay on current month
declare @date datetime
set @date='2014-01-14'--getdate()
select DATEADD(dd,-15,@date)-DAY(DATEADD(dd,-15,@date)) 'PrevMonthEnd'
select DATEADD(mm,1,DATEADD(dd,-15,@date)-DAY(DATEADD(dd,-15,@date))) 'CurrMonthEnd'
That'll work but be careful. That will only work for one value at a time and it requires 2 separate SELECTs. If you were to translate that into working with a table, you would end up having the date column (whatever it is) inside a formula. When you do that, it makes it impossible to get a high performance INDEX SEEK on the date column. Look at Michael's code. The date column is always by itself so that an INDEX SEEK can be realized in the presence of a correct index.
Hello Jeff,
Correct me if any performance hit if i rephrased code as like below .
select
*
from
@MyData
where
EndDate > (DATEADD(dd,-15,@CurrDate)-DAY(DATEADD(dd,-15,@CurrDate))) and
EndDate <= (DATEADD(mm,1,DATEADD(dd,-15,@CurrDate)-DAY(DATEADD(dd,-15,@CurrDate))))
January 4, 2014 at 11:38 am
sql crazy kid (1/4/2014)
Hello Jeff,Correct me if any performance hit if i rephrased code as like below .
select
*
from
@MyData
where
EndDate > (DATEADD(dd,-15,@CurrDate)-DAY(DATEADD(dd,-15,@CurrDate))) and
EndDate <= (DATEADD(mm,1,DATEADD(dd,-15,@CurrDate)-DAY(DATEADD(dd,-15,@CurrDate))))
That would be better performance wise but it doesn't work correctly. Given the original data, your code should return both rows from Dec of 2013 and it only returns one.
declare @MyData table (Id int not null, StartDate datetime not null, EndDate datetime not null )
insert into @MyData
select 101 as Id,'2014-01-14 13:30:00.000' as StartDate,'2014-02-14 13:30:00.000' as EndDate union all
select 102 as Id,'2013-12-19 13:30:00.000' as StartDate,'2014-01-12 13:30:00.000' as EndDate union all
select 103 as Id,'2013-12-14 13:30:00.000' as StartDate,'2014-01-20 13:30:00.000' as EndDate union all
select 104 as Id,'2013-12-01 13:30:00.000' as StartDate,'2013-12-30 13:30:00.000' as EndDate union all
select 105 as Id,'2013-12-14 13:30:00.000' as StartDate,'2013-12-20 13:30:00.000' as EndDate
DECLARE @CurrDate DATETIME;
SET @CurrDate = '2014-01-01'
select
*
from
@MyData
where
EndDate > (DATEADD(dd,-15,@CurrDate)-DAY(DATEADD(dd,-15,@CurrDate))) and
EndDate <= (DATEADD(mm,1,DATEADD(dd,-15,@CurrDate)-DAY(DATEADD(dd,-15,@CurrDate))))
Results:
Id StartDate EndDate
----------- ----------------------- -----------------------
105 2013-12-14 13:30:00.000 2013-12-20 13:30:00.000
--Jeff Moden
January 4, 2014 at 10:43 pm
Jeff Moden (1/4/2014)
sql crazy kid (1/4/2014)
Hello Jeff,Correct me if any performance hit if i rephrased code as like below .
select
*
from
@MyData
where
EndDate > (DATEADD(dd,-15,@CurrDate)-DAY(DATEADD(dd,-15,@CurrDate))) and
EndDate <= (DATEADD(mm,1,DATEADD(dd,-15,@CurrDate)-DAY(DATEADD(dd,-15,@CurrDate))))
That would be better performance wise but it doesn't work correctly. Given the original data, your code should return both rows from Dec of 2013 and it only returns one.
declare @MyData table (Id int not null, StartDate datetime not null, EndDate datetime not null )insert into @MyData
select 101 as Id,'2014-01-14 13:30:00.000' as StartDate,'2014-02-14 13:30:00.000' as EndDate union all
select 102 as Id,'2013-12-19 13:30:00.000' as StartDate,'2014-01-12 13:30:00.000' as EndDate union all
select 103 as Id,'2013-12-14 13:30:00.000' as StartDate,'2014-01-20 13:30:00.000' as EndDate union all
select 104 as Id,'2013-12-01 13:30:00.000' as StartDate,'2013-12-30 13:30:00.000' as EndDate union all
select 105 as Id,'2013-12-14 13:30:00.000' as StartDate,'2013-12-20 13:30:00.000' as EndDate
DECLARE @CurrDate DATETIME;
SET @CurrDate = '2014-01-01'
select
*
from
@MyData
where
EndDate > (DATEADD(dd,-15,@CurrDate)-DAY(DATEADD(dd,-15,@CurrDate))) and
EndDate <= (DATEADD(mm,1,DATEADD(dd,-15,@CurrDate)-DAY(DATEADD(dd,-15,@CurrDate))))
Results:
Id StartDate EndDate----------- ----------------------- -----------------------
105 2013-12-14 13:30:00.000 2013-12-20 13:30:00.000
Thanks , this is a bug in my code.. have corrected ..
select
*
from
@MyData
where
EndDate >= (DATEADD(dd,-15,@CurrDate)-DAY(DATEADD(dd,-15,@CurrDate))+1) and
EndDate < dateadd(mm,1,(DATEADD(dd,-15,@CurrDate)-DAY(DATEADD(dd,-15,@CurrDate))+1))
January 5, 2014 at 9:27 am
@sql crazy kid,
Your code now appears to work the same as Micheal's...
declare @MyData table (Id int not null, StartDate datetime not null, EndDate datetime not null )
insert into @MyData
select 101 as Id,'2014-01-14 13:30:00.000' as StartDate,'2014-02-14 13:30:00.000' as EndDate union all
select 102 as Id,'2013-12-19 13:30:00.000' as StartDate,'2014-01-12 13:30:00.000' as EndDate union all
select 103 as Id,'2013-12-14 13:30:00.000' as StartDate,'2014-01-20 13:30:00.000' as EndDate union all
select 104 as Id,'2013-12-01 13:30:00.000' as StartDate,'2013-12-30 13:30:00.000' as EndDate union all
select 105 as Id,'2013-12-14 13:30:00.000' as StartDate,'2013-12-20 13:30:00.000' as EndDate
DECLARE @CurrDate DATETIME;
SET @CurrDate = '2014-01-16'
PRINT '===== sql crazy kid''s code =============================================';
select
*
from
@MyData
where
EndDate >= (DATEADD(dd,-15,@CurrDate)-DAY(DATEADD(dd,-15,@CurrDate))+1) and
EndDate < DATEADD(mm,1,(DATEADD(dd,-15,@CurrDate)-DAY(DATEADD(dd,-15,@CurrDate))+1))
PRINT '===== Michael''s code ==================================================';
select
*
from
@MyData
where
EndDate >= dateadd(mm,datediff(mm,0,dateadd(dd,-15,@CurrDate)),0) and
EndDate < dateadd(mm,datediff(mm,0,dateadd(dd,-15,@CurrDate))+1,0)
Results from above...
(5 row(s) affected)
===== sql crazy kid's code =============================================
Id StartDate EndDate
----------- ----------------------- -----------------------
102 2013-12-19 13:30:00.000 2014-01-12 13:30:00.000
103 2013-12-14 13:30:00.000 2014-01-20 13:30:00.000
(2 row(s) affected)
===== Michael's code ==================================================
Id StartDate EndDate
----------- ----------------------- -----------------------
102 2013-12-19 13:30:00.000 2014-01-12 13:30:00.000
103 2013-12-14 13:30:00.000 2014-01-20 13:30:00.000
(2 row(s) affected)
Now that I look at it and compare those results to the original request (emphasis is mine)...
born2achieve (1/3/2014)
I want to write a query for the below requirement,if the current date falls between 1 to 15 then i have to get the previous month data. if the current date falls between 16th to 31 then have to get current month data.
with sample as (
select 101 as Id,'2014-01-14 13:30:00.000' as StartDate,'2014-02-14 13:30:00.000' as EndDate union all
select 102 as Id,'2013-12-19 13:30:00.000' as StartDate,'2014-01-12 13:30:00.000' as EndDate union all
select 103 as Id,'2013-12-14 13:30:00.000' as StartDate,'2014-01-20 13:30:00.000' as EndDate union all
select 104 as Id,'2014-01-01 13:30:00.000' as StartDate,'2014-01-01 13:30:00.000' as EndDate union all
select 105 as Id,'2013-06-14 13:30:00.000' as StartDate,'2013-06-14 13:30:00.000' as EndDate )
we have to check the previous month dates [font="Arial Black"]falls betweee startdate and enddate column for previous month data and currents month dates falls between startdate and enddate for current month data.[/font]
Can anyone please help me in this.
... I'm thinking that both sets of code could be missing the requirements emphasized above. Row 101 clearly has a StartDate within the month of data that is supposed according to the given @CurrDate to be returned but it is not a part of either result set. I believe both code snippets need to be modified to check the StartDate, as well as the EndDate. In fact, I believe that the actual requirement is to "find anything active" according to the requirements of @CurrDate. That means that, given an @CurrDate of 2014-01-16, the code should return rows that have (for example) a StartDate of 2013-07-01 and 2015-07-01. Dunno for sure, though. Let's ask the OP...
[font="Arial Black"]@born2achieve,[/font]
After reading the above, would you provide some clarification, please?
--Jeff Moden
January 5, 2014 at 12:13 pm
Hi Jeff,
Thanks for your precious time on this post and here is my exact business requirement,
From the sample i have provided, assume current date is January 5th which falls b/w 1st and 15th on the month, i need to get the records where the previous month dates falls in startdate and Enddate should be displayed. So below two start date falls in startdate and enddate column.
select 102 as Id,'2013-12-19 13:30:00.000' as StartDate,'2014-01-12 13:30:00.000' as EndDate
select 103 as Id,'2013-12-14 13:30:00.000' as StartDate,'2014-01-20 13:30:00.000' as EndDate
Assume today's date is 16th. In that case i have to fetch the current month dates falls in between start and Enddate. In that case below are two rows data i will get it,
select 101 as Id,'2014-01-14 13:30:00.000' as StartDate,'2014-02-14 13:30:00.000' as EndDate
select 104 as Id,'2014-01-01 13:30:00.000' as StartDate,'2014-01-01 13:30:00.000' as EndDate
Hope now the requirement is clear. Please Let me know if you have any queries,
January 5, 2014 at 12:59 pm
born2achieve (1/5/2014)
Hi Jeff,Thanks for your precious time on this post and here is my exact business requirement,
From the sample i have provided, assume current date is January 5th which falls b/w 1st and 15th on the month, i need to get the records where the previous month dates falls in startdate and Enddate should be displayed. So below two start date falls in startdate and enddate column.
select 102 as Id,'2013-12-19 13:30:00.000' as StartDate,'2014-01-12 13:30:00.000' as EndDate
select 103 as Id,'2013-12-14 13:30:00.000' as StartDate,'2014-01-20 13:30:00.000' as EndDate
Assume today's date is 16th. In that case i have to fetch the current month dates falls in between start and Enddate. In that case below are two rows data i will get it,
select 101 as Id,'2014-01-14 13:30:00.000' as StartDate,'2014-02-14 13:30:00.000' as EndDate
select 104 as Id,'2014-01-01 13:30:00.000' as StartDate,'2014-01-01 13:30:00.000' as EndDate
Hope now the requirement is clear. Please Let me know if you have any queries,
That's pretty much the way I thought. In that case, the code posted so far isn't quite going to do it for you. I'll be back with some code (unless someone beats me to it 🙂 ).
--Jeff Moden
January 5, 2014 at 1:02 pm
Almost forgot. What do you want to do about the Jul 2013 to Jul 2014 scenario that I previously stated?
--Jeff Moden
January 5, 2014 at 2:21 pm
you meant to say startdate is any date in july 2013 and enddate is any date in jusly 2014. Obviously the data has to fetched because all the 30 dates of previous month(december 01 to december 30th) falls in between july 2013 and juley 2014.
Can you please post your code
January 5, 2014 at 2:47 pm
born2achieve (1/5/2014)
you meant to say startdate is any date in july 2013 and enddate is any date in jusly 2014. Obviously the data has to fetched because all the 30 dates of previous month(december 01 to december 30th) falls in between july 2013 and juley 2014.Can you please post your code
Heh... You just confirmed the requirements less than 30 minutes ago. I just came in from shoveling snow. Of course I'll post my code... just a soon as I get around to writing it. 😉 It's a weekend day, to boot. Be patient. I only want to have to do this once. :laugh:
--Jeff Moden
January 5, 2014 at 3:17 pm
Thank you gentle Man!! I will wait.
January 5, 2014 at 4:39 pm
Ok, here we go. It always takes me a little longer than others because I like to explain things a bit.
First, I like Michael's code the best out what has been posted so far because it's "in-your-face" obvious that you're working with month boundaries based on the current date thanks to the use of the "mm" date-part in his code. So, I used his code for this.
Shifting gears, this requirement is the classic requirement of simply finding overlapping ranges no matter how briefly they may overlap. Lot's of people write code for the 6 different conditions that could exist but, using a little formula reduction, it actually breaks down to two conditions, which Michael's code sets us up for quite nicely.
So here are the actual "Requirements" as I see them and, again, it's the classic "overlapping date ranges" problem...
Given a table of date ranges, each having a known start and end date, determine if a desired range of dates (also having a start and end date) have any kind of overlap no matter how small or large. This "overlap" is simply defined as the row being "active" sometime/anytime during the desired range for simplicity sake.
You've also added in the caveat that the "desired" range of dates should be based on the current date. If that date is the 1st through the 15th of the month, then the desired range of dates are the boundaries of the previous month. Otherwise, the desired range of dates are the boundaries of the same month as the current date.
Michael's code very nicely calculates what the boundaries of the desired range should be so there were no changes required there. We just need to change the way they're being used.
Most people understand that there are 6 different possibilities in this type of problem.
1. Both dates to the left and outside of the desired range. [font="Arial Black"]Don't include.[/font]
2. Both dates to the right and outside of the desired range. [font="Arial Black"]Don't include.[/font]
3. Start date within the desired range. [font="Arial Black"]Include.[/font]
4. End date within the desired range. [font="Arial Black"]Include.[/font]
5. Both dates within the desired range. [font="Arial Black"]Include.[/font]
6. Start date to the left of and outside the desired range and end date to the right of and outside the desired range. In other words, the row dates span the entirety of the desire range. [font="Arial Black"]Include.[/font]
A lot of people write code to solve for all 6 eventualities. It's not necessary, though. There are two incredibly simple conditions that will solve for all of the above and a simple diagram will make it all obvious. Note that conditions 1 and 2 are the ONLY conditions that are NOT to be included as they are the only conditions that have no overlap with the desired range.
| Desired |
Left "Outside"| Range |Right "OutSide"
<-------------|---------|-------------->
<--1--> | <--5--> | <--2-->
S E | S E | S E
| |
<--4--> <--3-->
S | E S | E
| |
<----------|----6----|---------->
S | | E
DS DE
LEGEND:
S = StartDate of a row.
E = EndDate of a row.
DS = StartDate of desired range. Note that this is Temporally "Closed" or "Inclusive" for this post.
DE = EndDate of desired range. Note that this is Temporally "Open" or "Exclusive" for this post.
If you look carefully, conditions 3, 4, 5, and 6 are not only to be included, there are two conditions that they have in common that conditions 1 and 2 do not.
Conditions for Inclusion:
E >= DS
AND
S < DE
That means only minor changes to Michael's original code and we're done. I've "embolded" the only changes required.
SELECT ID,StartDate,EndDate
FROM @MyData
WHERE [font="Arial Black"]EndDate >= [/font]DATEADD(mm,DATEDIFF(mm,0,DATEADD(dd,-15,@CurrDate)) ,0)
AND [font="Arial Black"]StartDate < [/font]DATEADD(mm,DATEDIFF(mm,0,DATEADD(dd,-15,@CurrDate))+1,0)
;
Any questions on this?
{EDIT} I've made a couple of grammatical changes to the post above but the code has not been changed.
--Jeff Moden
January 5, 2014 at 4:55 pm
Hi Geff,
First of all i don't have enough vocabulary to thank you because the way you answering to the forum questions are much professional and elaborated which will help every one to understand the concept.
I love the way you answer. Also thanks a lot for you to consider this post as i would have in trouble if i don't notice what you captured. Also i understand the logic what you have explained.
I love this forum because of such a Genius like you and other good friends who are sharing their knowledge to help others.
Happy coding and Have good year. I have one more question, not sure should i open new thread or not. even i can google it but getting answer from you will be more happy than googling.
In clause vs with joins
What is the drawback of in clause which will resolve by joins
Kind Regards.
January 5, 2014 at 8:22 pm
born2achieve (1/5/2014)
Hi Geff,First of all i don't have enough vocabulary to thank you because the way you answering to the forum questions are much professional and elaborated which will help every one to understand the concept.
I love the way you answer. Also thanks a lot for you to consider this post as i would have in trouble if i don't notice what you captured. Also i understand the logic what you have explained.
I love this forum because of such a Genius like you and other good friends who are sharing their knowledge to help others.
Happy coding and Have good year. I have one more question, not sure should i open new thread or not. even i can google it but getting answer from you will be more happy than googling.
In clause vs with joins
What is the drawback of in clause which will resolve by joins
Kind Regards.
Thank you for the feedback. :blush: Heh... and no, I'm no "genius". I just know what I went through when I was learning all of this stuff and wish I had someone to explain it to me way back when.
Speaking of that, I have a favor to ask of you. "Pass it forward". If you see someone having a similar problem, give them a helping hand with understanding the problem and then... tell them to "Pass it forward". 🙂
Shifting gears to your request above, there are a couple of differences between an INNER JOIN and WHERE IN.
Rather than me rewrite what has already been correctly stated, please see the following articles created by a trusted friend and Microsoft Certified Master, Gail Shaw.
http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/
--Jeff Moden
January 6, 2014 at 5:19 am
Hi Jeff,
Thanks for the response and sure i will pass it forward.Please keep continue your helping hand because "your known is ocean for us" and we will keep learning.
I will readout the article you suggested.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply