January 3, 2014 at 10:12 am
Hi i am using sqlserver 2008 R2.
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 falls betweee startdate and enddate column for previous month data and currents month dates falls between startdate and enddate for current month data.
Can anyone please help me in this.
January 3, 2014 at 10:36 am
Which rows would you return from your sample data for today?I suspect your issue isn't the coding, but the business logic.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 3, 2014 at 10:48 am
I just corrected my sample data as follows,
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,'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 )
for my requirement i will get last two rows. because today's date is falls b/w 1 and 15. so last month dated records will be fetched. Can you help me now please
January 3, 2014 at 11:24 am
This query seems to do what you want with the first result with the date before the 16th of the month and the next result for on or after the 16th.
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-15 23:59:59.997'
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)
set @CurrDate = '2014-01-16 00:00:00.000'
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)
Id StartDate EndDate
----------- ----------------------- -----------------------
104 2013-12-01 13:30:00.000 2013-12-30 13:30:00.000
105 2013-12-14 13:30:00.000 2013-12-20 13:30:00.000
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
January 3, 2014 at 1:01 pm
Hi Michael,
Thanks for your response.
On your sample you are hardcoding the Currdate
set @CurrDate = '2014-01-15 23:59:59.997'
But i wanted to check the getdate() falls b/w 1st to 15th date range or 16th to 31st date range dynamically. i don't want to hardcode. Any suggestions please
January 3, 2014 at 1:39 pm
born2achieve (1/3/2014)
Hi Michael,Thanks for your response.
On your sample you are hardcoding the Currdate
set @CurrDate = '2014-01-15 23:59:59.997'But i wanted to check the getdate() falls b/w 1st to 15th date range or 16th to 31st date range dynamically. i don't want to hardcode. Any suggestions please
???
set @CurrDate = getdate()
January 3, 2014 at 1:49 pm
Hi Michael,
Thanks what i did already
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
select
*
from
@MyData
where
EndDate >= dateadd(mm,datediff(mm,0,dateadd(dd,-15,getdate())),0)and
EndDate < dateadd(mm,datediff(mm,0,dateadd(dd,-15,getdate()))+1,0)
select
*
from
@MyData
where
EndDate >= dateadd(mm,datediff(mm,0,dateadd(dd,-15,getdate())),0)and
EndDate < dateadd(mm,datediff(mm,0,dateadd(dd,-15,getdate()))+1,0)
there are two select statement on your sample. i need to write an if condition to check the getdate() falls b/w 1st and 15th of the month and if yes execute your 1st select statement o get previous month data
If the getdate() falls b/w 16th and 31st of the month and if yes execute your 2nd select statement to get current month data
Hope now my requirement is clear. Any suggestions please
January 3, 2014 at 1:58 pm
born2achieve (1/3/2014)
Hi Michael,Thanks what i did already
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
select
*
from
@MyData
where
EndDate >= dateadd(mm,datediff(mm,0,dateadd(dd,-15,getdate())),0)and
EndDate < dateadd(mm,datediff(mm,0,dateadd(dd,-15,getdate()))+1,0)
select
*
from
@MyData
where
EndDate >= dateadd(mm,datediff(mm,0,dateadd(dd,-15,getdate())),0)and
EndDate < dateadd(mm,datediff(mm,0,dateadd(dd,-15,getdate()))+1,0)
there are two select statement on your sample. i need to write an if condition to check the getdate() falls b/w 1st and 15th of the month and if yes execute your 1st select statement o get previous month data
If the getdate() falls b/w 16th and 31st of the month and if yes execute your 2nd select statement to get current month data
Hope now my requirement is clear. Any suggestions please
You just need to execute the 1st select statement, since both statements are absolutely identical.
I had two selects to show what happened with different dates.
January 3, 2014 at 2:26 pm
Hi Michael,
i understand, i don't need to worry about am in the first half or second half of the month. the select query itself will take care of it. Ia my assumption correct?
January 3, 2014 at 3:13 pm
Also i verified with my production data and it works perfectly.
Thanks a lot Michael. Appreciate your time on this post.
January 3, 2014 at 8:47 pm
born2achieve (1/3/2014)
Also i verified with my production data and it works perfectly.Thanks a lot Michael. Appreciate your time on this post.
Now, for the really tough question. Do you understand how Michael's code works and why? I ask because you're going to be the one that needs to maintain it in the future or will need to explain it to someone else.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2014 at 7:05 am
Hi Jeff,
Thanks for asking this and i understand the concept. going forward i can help someone what i learned.
January 4, 2014 at 10:12 am
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'
January 4, 2014 at 10:36 am
born2achieve (1/4/2014)
Hi Jeff,Thanks for asking this and i understand the concept. going forward i can help someone what i learned.
Excellent. I just wanted to be sure. Thank you for the response.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2014 at 10:40 am
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply