Solved

SQL Select Dynamic date range

Posted on 2013-05-28
21
723 Views
Last Modified: 2013-05-29
Hi Experts,

I need to select records by date where that date falls in to a period (1 Nov - 31 Oct).

I need to be able to do this dynamically so that at any point in time I see only the records for the current 'year'.

I have been trying to follow this example http://www.twoninemedia.com/blog/index.cfm/2008/2/1/SQL-Dynamic-Date-Range

but I have yet to get a working example.

My current stored proceedure uses a series of datepart restrictions to get records in the desired range but I think this is only working as the records began on 1 Nov 2012 so we are in yr one and I think come 1 Nov 2013 my current select will break:

WHERE 
((DATEPART (m, CPDitems.CPDDate) <= DATEPART(m, getdate())AND DATEPART(yy, CPDitems.CPDdate) <= DATEPART(yy, getdate()))
OR 
(DATEPART(m, CPDitems.CPDDate) = 11 AND DATEPART(yy, CPDitems.CPDdate) < DATEPART(yy, getdate()))
OR 
(DATEPART(m, CPDitems.CPDDate) = 12 AND DATEPART(yy, CPDitems.CPDdate) < DATEPART(yy, getdate())))

Open in new window



I presume I need to write an inline function and then call that in my SP?
0
Comment
Question by:forsters
  • 10
  • 10
21 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39200944
where [datetime_field] >= [a low date] and [datetime_field] < [a high date]

you most likely don't need to use datepart at all

is CPDitems.CPDDate a datetime field?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39200964
an example:
declare @from as datetime, @until as datetime
set @from = '2012-11-01'
set @until = '2013-11-01'

select
<<whatever>>
from CPDitems
join <<whatever>>
where ( CPDitems.CPDDate >= @from and CPDitems.CPDDate < @until )

Open in new window

Note in particular that you do not use 31st October, use "less than" 1st of November

fuller explanation for this logic in  "Beware of Between"
0
 

Author Comment

by:forsters
ID: 39200973
Hi PortletPaul,

It is a datetime field yes.

I should be clearer in my explanation I think - I need all the records falling between 1 Nov and 31 Oct that is to say currently I want records between 1Nov 2012 - 31Oct 2013. However from 1 Nov this year I want to forget about those and start showing records from 1 Nov 2013 to 31 Oct 2014 and so on, I want to code it in such a way that I do not need to worry about it again.

My current code is merely to illustrate how far I am from my solution!
As the linked article suggests I want to set the day and month of my year-start and year-end but pick up the year dynamically based on the getdate() year, I'm just not sure how to integrate this solution in to my SP and get the whole thing connected up and working....
0
 

Author Comment

by:forsters
ID: 39200980
We crossed paths, apologies

Thank you for the sample thats helpful - don't want to hard-code the dates though because that means I will have to remember to change each year...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39200984
ok, thanks, it's clearer now.

you want this literally to flip over on the basis of getdate()
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39200997
I need to select records by date where that date falls in to a period (1 Nov - 31 Oct).

I see your query is doing exactly as you need and it wont break when the year changes ad you presumed.

But, I see it gives you only for the current year...
so, for example now in 2013 it give you the results of 01-nov-2012 to current date.....

is it what is the desired outcome...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39201007
try this, use @mydate to do some tests
declare @mydate as datetime = '2013-10-20'

select
  dateadd(month,10,dateadd(year,year(getdate())-1900,0))
, dateadd(month,10,dateadd(year,year(getdate())-1899,0))

, dateadd(month,10,dateadd(year,year(@mydate)-1900,0))
, dateadd(month,10,dateadd(year,year(@mydate)-1899,0))

Open in new window

0
 

Author Comment

by:forsters
ID: 39201031
I'm sorry I don't understand what I am supposed to be doing with that...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39201037
oops - that was incomplete anyway... sorry ... back soon
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 39201095
very limited testing done on this, but I think the following will work
declare @fromDate as datetime 
declare @untilDate as datetime 
set @fromDate = getdate()

select @fromDate = case when month(@fromDate) < 11 then dateadd(year,-1,@fromDate) else @fromDate end

set @fromDate  = dateadd(month,10,dateadd(year,year(@fromDate)-1900,0))
set @untilDate = dateadd(month,10,dateadd(year,year(@fromDate)-1899,0))

--select @fromDate, @untilDate

select
<<whatever>>
from CPDitems
join <<whatever>>
where ( CPDitems.CPDDate >= @fromDate and CPDitems.CPDDate < @untilDate )

Open in new window

the intention (or assumption) is that you would use datetime variable within your SP along the lines outlined above. i.e. you would test getdate() then adjust the upper and lower dates to be used in your filter to suit.

to test this snippet do something along these lines manually setting the date to test:
declare @fromDate as datetime 
declare @untilDate as datetime 
set @fromDate = '2012-11-28' -- getdate() -- << i.e. normally you would use getdate()

select @fromDate = case when month(@fromDate) < 11 then dateadd(year,-1,@fromDate) else @fromDate end

set @fromDate  = dateadd(month,10,dateadd(year,year(@fromDate)-1900,0))
set @untilDate = dateadd(month,10,dateadd(year,year(@fromDate)-1899,0))

select @fromDate, @untilDate

Open in new window

hope this makes more sense now
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:forsters
ID: 39201188
Ok, think I follow most of it, I will have a play and get back to you asap. Many thanks
0
 

Author Comment

by:forsters
ID: 39201597
I just get syntax errors - if i've understood you're saying I should be able to include the new code within my SP?

Can you show me because it's having none of it which I'm hoping means I'm just being ignorant...

This is my current SP

ALTER PROCEDURE [dbo].[SP_GetCPDItemsTEST] 
	
@username varchar(30)
AS
BEGIN
	
	SET NOCOUNT ON;

   
	SELECT item.CPDDate, item.CPDHours, item.CPDDesc, item.CPDprovider, record.UserId, format.FormatName, part.ParticipationName,
	Cat.CPDCategoryDesc 
FROM CPDItems item LEFT JOIN CPDFormat format on item.CPDformat = format.FormatID
	LEFT JOIN CPDparticipation part on item.CPDparticipate = part.ParticipationID
	LEFT JOIN CPDCategory Cat on item.CPDcat = Cat.CategoryID
	RIGHT JOIN CPDRecord record on item.CPDid = record.CPDID
	WHERE ((DATEPART (m, item.CPDDate) <= DATEPART(m, getdate())AND DATEPART(yy, item.CPDdate) <= DATEPART(yy, getdate())) 
OR (DATEPART(m, item.CPDDate) = 11 AND DATEPART(yy, item.CPDdate) < DATEPART(yy, getdate())) 
OR (DATEPART(m, item.CPDDate) = 12 AND DATEPART(yy, item.CPDdate) < DATEPART(yy, getdate()))) AND UserID = (SELECT StaffId from Intranet.IntranetDB.dbo.StaffDirectory 
	WHERE username = @username)
	ORDER BY item.CPDdate desc
END

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39203288
see it working at: http://sqlfiddle.com/#!3/b3ca3/1 I didn't try to simulate all your tables so please remove comments which are there simply to allow compliation
CREATE PROCEDURE [dbo].[SP_GetCPDItemsTEST2] @username VARCHAR(30)
AS
BEGIN

declare @fromDate as datetime 
declare @untilDate as datetime 
set @fromDate = getdate()

select @fromDate = case when month(@fromDate) < 10 then dateadd(year,-1,@fromDate) else @fromDate end

set @fromDate  = dateadd(month,10,dateadd(year,year(@fromDate)-1900,0))
set @untilDate = dateadd(year,1,@fromDate) -- I changed this, bit easier to follow

SET NOCOUNT ON;

    SELECT
          item.CPDDate
        , item.CPDHours
        , item.CPDDesc
        , item.CPDprovider
        , @username as uname
        , @fromDate as fromDate
        , @untilDate as untilDate
        , item.id
        --, record.UserId
        --, format.FormatName
        --, part.ParticipationName
        --, Cat.CPDCategoryDesc
    FROM CPDItems item
    --LEFT JOIN CPDFormat format ON item.CPDformat = format.FormatID
    --LEFT JOIN CPDparticipation part ON item.CPDparticipate = part.ParticipationID
    --LEFT JOIN CPDCategory Cat ON item.CPDcat = Cat.CategoryID
    --RIGHT JOIN CPDRecord record ON item.CPDid = record.CPDID
    WHERE ( 
               item.CPDDate >= @fromDate 
           and item.CPDDate <  @untilDate 
          )
        --AND UserID = (
        --    SELECT StaffId
        --    FROM Intranet.IntranetDB.dbo.StaffDirectory
        --    WHERE username = @username
        --    )
    ORDER BY item.CPDdate DESC
END;

Open in new window

0
 

Author Comment

by:forsters
ID: 39203981
Ah I see, not even going to tell you what I was doing! That now runs without error, thank you very much, didn't realise you could do this sort of thing in an SP...but am I right in thinking that we've not fully tackled the from and until date?

I'm reading that and not entirely following:

So first we declare two variables as datetime values...

declare @fromDate as datetime 
declare @untilDate as datetime 

Open in new window


Then we set the from date as today...

set @fromDate = getdate()

Open in new window


Then we say get fromdate(todays date) and where the month is 1-9 reduce the year by one, otherwise (i.e. if month 10,11 or 12) leave the year alone - have I understood that right??

select @fromDate = case when month(@fromDate) < 10 then dateadd(year,-1,@fromDate) else @fromDate end

Open in new window


Then I get a bit lost - what are we doing here? I don't understand this section  :\

set @fromDate  = dateadd(month,10,dateadd(year,year(@fromDate)-1900,0))
set @untilDate = dateadd(year,1,@fromDate) -- I changed this, bit easier to follow

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 39204084
the objective is to locate the correct fiscal year for any 'current date/time'
the case expression is testing for those few months in a year where (start of) fiscal year = current year (for most months the start of a fiscal year is 'last year')

once the year is settled,
then take that year (jan 1 of that year actually) and add 10 months = 1st November
this establishes the @fromDate
then add another 1 year to that date = the @untilDate

you should then get only pairs of:
year-11-01
(year+1)-11-01

e.g.
2012-11-01
2013-11-01

Then in the where clause
       datefield >= @fromDate -- 2012-11-01
and datefield <   @untilDate -- 2013-11-01

Please satisfy for yourself that the calculations are correct -- I have suggested ways to to that.
0
 

Author Comment

by:forsters
ID: 39204198
Thanks that makes more sense - I was reading it with a slightly different logic, so one last question ...what does the 1900 represent/do in the line below:

set @fromDate  = dateadd(month,10,dateadd(year,year(@fromDate)-1900,0))

I will need to change the until date to add 1 year -1day to get to 31 Oct so I'm keen to understand this completely; the dateadd(month, 10 makes sense - we add 10 months to Jan 01 which gets us Nov 01, then we say put that date with the year where the year is this year or last - as set previously, then we have -1900,0. what is that last part doing?
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 39204254
SQL Server uses 1900-01-01 as its baseline date

year(getdate()) = 2013

if you dateadd 2013 to 0 you go 1900 years past the year 2013
so you need to deduct 1900

try
select dateadd(year,2013,0)
then try
select dateadd(year,2013-1900,0)
0
 

Author Comment

by:forsters
ID: 39204280
Ahhhhhhhhhhhhhh I see...

Thank you, now I understand.

Ok I'm going to insert some test data to make sure I have this working properly as we currently only have records between Nov 01 2012 and now, I will report back if I may, but thank you for your help, I would never have understood this without it!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39204294
no problem
0
 

Author Comment

by:forsters
ID: 39204317
Tested - all works perfectly (which I'm sure you knew already) - I now get the significance of:

WHERE item.CPDDate >= @fromDate
and item.CPDDate <  @untilDate

saving the need to lose a day by saying less than @UntilDate, finally it all makes sense.

Thank you so much
0
 

Author Closing Comment

by:forsters
ID: 39204321
Really helpful, can't thank you enough
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now