Date Range logic

Good morning all,

I've got a little headscratcher for you involving date ranges.

We have a table for recording absences:
Absence(unique_identifier, parent_identifier, date_from, date_to ... )
And an employee table
Employees(unique_identifier, Surname, Firstname, birth_date ...)

Where the relationship between the two is:
Employees.unique_identifier = Absence.parent_identifier

The problem lies when wanting to know whether an employee was off within a specified date range.

Absence:

u_id p_id date_from date_to
1 1 2007-02-01 2007-02-06
2 2 2007-01-29 2007-02-06
3 2 2007-03-25 2007-03-25
4 3 2007-06-06 2007-06-08
5 4 2007-02-05 2007-02-06

Given the above sample results, how can I identify which employees were off during the first week of February (2007-02-01 to 2007-02-07)?

Expected Results:

u_id p_id date_from date_to
1 1 2007-02-01 2007-02-06
2 2 2007-01-29 2007-02-06
5 4 2007-02-05 2007-02-06

Any advice you can give to help me get the answer I need is much appreciated :)Why's the middle line in red? I don't know the SQL for that...|||Errr... There was a reason for that, which I now realise is meaningless because I didn't post the SQL I tried already...
EDIT: I say "I", I actually mean "an employee using the WYSIWYG query editor built into a system.

SELECT p_id FROM Absence WHERE date_from BETWEEN '20070201' AND '20070207'

Clearly won't return that line, which needs including.

Did I mention that I want the results in bold, aligned right and every other line needs to be red? I am using Query Analyzer.|||I don't get the difficulty. Am I missing something?


SELECT p_id
FROM Absence
WHERE date_from BETWEEN '20070201' AND '20070207'
OR date_to BETWEEN '20070201' AND '20070207'|||Unless he wants employees that are on leave for the FULL period of
Feb 1 to Feb 6 ?|||Poots, would the following row be returned?

u_id p_id date_from date_to
6 7 2007-01-25 2007-03-01

The above was off during the period in question|||SELECT p_id
FROM Absence
WHERE date_from <= '20070207'
AND date_to >= '20070201'|||Poots, would the following row be returned?No - and it illustrates the error in concentrating on inadequate sample data instead of the logic of the problem :p|||Thanks for the solution Peso and thanks for your help Poots!
I can't believe how long I've been staring at this and not been able to get my head round a logical answer!

DATE RANGE ISSUE

Hi All,
I have below table structure:
ItemID Price FromDate
ToDate
----
--
1 10.00 01/01/2005
12/31/2005
1 9.00 01/01/2004
12/31/2004
2 2.00 01/01/1900
01/01/9999
----
--
Now I need to write a query to get price for all the items for the date
range 01/01/2005 to 12/31/2005
If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
ie, daterange specified lies between 1900 and 9999. But the query will not
give that result.
Please let me know how to get this record also.
Thanks in advance"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:628A35F2-9F1E-4E5A-B095-E5A45470CC84@.microsoft.com...
> Hi All,
> I have below table structure:
> ItemID Price FromDate
> ToDate
> ----
--
> 1 10.00 01/01/2005
> 12/31/2005
> 1 9.00 01/01/2004
> 12/31/2004
> 2 2.00 01/01/1900
> 01/01/9999
> ----
--
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
>
> If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
> only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
>
The only records your query should return are ones with a date of
01/01/2005. I'm not sure how it's returning ItemID 1
ItemID=2 does not lay within that same daterange.
It's fromdate 01/01/1900 is less than 01/01/2005 and it's Todate, is greater
than 01/01/2005.
With the AND, you're asking for stuff BETWEEN 01/01/2005 and 01/01/2005.
ItemID=2 exceeds this range on both ends.
I suspect you want OR?

> Please let me know how to get this record also.
> Thanks in advance
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 14, 9:49 am, Ram <R...@.discussions.microsoft.com> wrote:
> Hi All,
> I have below table structure:
> ItemID Price FromDate =
=20
> ToDate
> ----=
--=AD--
> 1 10.00 01/01/2005 =
=20
> 12/31/2005
> 1 9.00 01/01/2004 =
=20
> 12/31/2004
> 2 2.00 01/01/1900 =
=20
> 01/01/9999
> ----=
--=AD--
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
> If I use FromDate >=3D 01/01/2005 and ToDate <=3D 01/01/2005 then I will =
get
> only one record (ItemID=3D1). But ItemID =3D2 also lies in the same dater=
ange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
> Please let me know how to get this record also.
> Thanks in advance
I think you are storing dummy values in fromdate and todate column
when it is unknown (NULL)
If that is the case
(FromDate >=3D 01/01/2005 and ToDate < '01/01/2006' )
OR
FromDate >=3D 01/01/1900 and ToDate <=3D '01/01/9999' )
Note: < 01/01/2006 instead of <=3D 12/31/2005' to take care of time
portion
M A Srinivassql

Date Range Issue

I've been trying to find a good way to do this and it seems every idea I come up with or find only accomplishes the desired result in one scenario. Here is my issue.

I'm designing reports with SQL Reporting Services 2000 and many of these reports need to show values for a dynamic date range such as the last full month, last full week, etc. similar to the built-in functions in Crystal Reports. To accomplish this, my first effort was to filter my results by looking only at results where a specific date parameter matched the current week or month -1 or -2 or whatever was needed. The following example would look at records for the previous month only:

SELECT * FROM tbExample
WHERE MONTH([Entry Date]) = MONTH(GETDATE())-1 AND YEAR([Entry Date]) = YEAR(GETDATE())

I understand this may not be the most efficient way of performing this operation, but this seemed to work at the time. If I were looking at the previous week, I would simply replace MONTH([EntryDate]) with DATEPART(week, [Entry Date]) and get the same result. The issue that I've run into is that at the very beginning of the year (first week and month particularly), this code fails. Since the functions I've used above result in an integer, then statically subtract from it, at the beginning of the year, I potentially end up with zero or negative numbers which causes unpredictable results or errors.

To address my issue, I thought I would write an IF statement which would look at the result and if it were zero or a negative number, compensate accordingly. Following is an example for the previous month:

IF (MONTH(GETDATE())-1 <= 0)
BEGIN
SELECT * FROM tbExample
WHERE MONTH([Entry Date]) = MONTH(GETDATE())-1+12 AND YEAR([Entry Date]) = YEAR(GETDATE())-1
END
ELSE
BEGIN
SELECT * FROM tbExample
WHERE MONTH([Entry Date]) = MONTH(GETDATE())-1 AND YEAR([Entry Date]) = YEAR(GETDATE())
END
RETURN

The above example simply compensates by adding 12 months back to the result and subtracting 1 year instead. This is obviously limited, but seemed alright for my application. This example does not however work for the weekly ranges. The problem with using this on a weekly basis is that there are technically 53 weeks in a year, though the last week only has a few days. I am unsure if there is a way and if so, how to calculate this to add back 52 or 53 weeks respectively depending on where the current date falls.

In researching another issue, I received a tip of a different way to pull date ranges. Although this addresses the issue of rolling back into the previous year, it would only work for the monthly reports, not the weekly. Here is the example:

SELECT * FROM tbExample
WHERE [Entry Date] >= DATEADD(month, -1, DATEADD(day, DATEDIFF(day, 0, DATEADD(day, 1-day(GETDATE()), GETDATE())), 0)) AND [Entry Date] < DATEADD(day, DATEDIFF(day, 0, DATEADD(day, 1-day(GETDATE()), GETDATE())), 0)

As stated previously, this works perfectly by focusing on anything between the first day of the previous month and the first day of the current month. The problem is that the 1-day function does not allow for a weekly focus.

I believe I have explained my situation to the best of my ability. I am completely open to suggestion, whether it be along the line of things I have already tried or fresh, new ideas.

Thank You

Hi:

Could you try the following?

It seems DATAADD and DATEDIFF can do everything for datatime data.

--1.Monday for last week:

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate())-1, 0)

--2. For day 1 for last month SELECT DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)

|||

So if I understand correctly, you are determining the difference in weeks/months between week/month 0 and the current week/month, then subtracting 1 and then adding that number of weeks/months back to week/month 0 as the starting point?

In this case, I believe my query would look like this ...

SELECT * FROM tbExample
WHERE [Entry Date] >= DATEADD(week, DATEDIFF(week, 0, GETDATE())-1, 0) AND [Entry Date] < DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)

... or for the monthly report ...

SELECT * FROM tbExample
WHERE [Entry Date] >= DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0) AND [Entry Date] < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

Would that be correct? I will need to do some live testing to see if this fits all of my scenarios, but this seems promising.

|||

I just show you what the function you may need. let's see what you can get from your test. Good luck.

|||

This seems to work well enough. It is somewhat odd to me that by using your method, I show only results of the last week from Monday to Sunday, but with Crystal Reports and with the other method I initially used for this task, it was looking at the last week from Sunday to Saturday. This will not impact my results enough to make a difference, but it is worth noting.

Thank You

|||

Hi,

This one is for previous Sunday: ("For servers using US English as their default language, the first day of the week is Sunday"

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate())-1, -1)

Thanks for pointing this out.

DATE RANGE ISSUE

Hi All,
I have below table structure:
ItemID Price FromDate
ToDate
-----
1 10.00 01/01/2005
12/31/2005
1 9.00 01/01/2004
12/31/2004
2 2.00 01/01/1900
01/01/9999
-----
Now I need to write a query to get price for all the items for the date
range 01/01/2005 to 12/31/2005
If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
ie, daterange specified lies between 1900 and 9999. But the query will not
give that result.
Please let me know how to get this record also.
Thanks in advance
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:628A35F2-9F1E-4E5A-B095-E5A45470CC84@.microsoft.com...
> Hi All,
> I have below table structure:
> ItemID Price FromDate
> ToDate
> -----
> 1 10.00 01/01/2005
> 12/31/2005
> 1 9.00 01/01/2004
> 12/31/2004
> 2 2.00 01/01/1900
> 01/01/9999
> -----
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
>
> If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
> only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
>
The only records your query should return are ones with a date of
01/01/2005. I'm not sure how it's returning ItemID 1
ItemID=2 does not lay within that same daterange.
It's fromdate 01/01/1900 is less than 01/01/2005 and it's Todate, is greater
than 01/01/2005.
With the AND, you're asking for stuff BETWEEN 01/01/2005 and 01/01/2005.
ItemID=2 exceeds this range on both ends.
I suspect you want OR?

> Please let me know how to get this record also.
> Thanks in advance
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
|||On Mar 14, 9:49 am, Ram <R...@.discussions.microsoft.com> wrote:
> Hi All,
> I have below table structure:
> ItemID Price FromDate
> ToDate
> ----X--
> 1 10.00 01/01/2005
> 12/31/2005
> 1 9.00 01/01/2004
> 12/31/2004
> 2 2.00 01/01/1900
> 01/01/9999
> ----X--
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
> If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
> only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
> Please let me know how to get this record also.
> Thanks in advance
I think you are storing dummy values in fromdate and todate column
when it is unknown (NULL)
If that is the case
(FromDate >= 01/01/2005 and ToDate < '01/01/2006' )
OR
FromDate >= 01/01/1900 and ToDate <= '01/01/9999' )
Note: < 01/01/2006 instead of <= 12/31/2005' to take care of time
portion
M A Srinivas

DATE RANGE ISSUE

Hi All,
I have below table structure:
ItemID Price FromDate
ToDate
-----
1 10.00 01/01/2005
12/31/2005
1 9.00 01/01/2004
12/31/2004
2 2.00 01/01/1900
01/01/9999
-----
Now I need to write a query to get price for all the items for the date
range 01/01/2005 to 12/31/2005
If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
ie, daterange specified lies between 1900 and 9999. But the query will not
give that result.
Please let me know how to get this record also.
Thanks in advance"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:628A35F2-9F1E-4E5A-B095-E5A45470CC84@.microsoft.com...
> Hi All,
> I have below table structure:
> ItemID Price FromDate
> ToDate
> -----
> 1 10.00 01/01/2005
> 12/31/2005
> 1 9.00 01/01/2004
> 12/31/2004
> 2 2.00 01/01/1900
> 01/01/9999
> -----
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
>
> If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
> only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
>
The only records your query should return are ones with a date of
01/01/2005. I'm not sure how it's returning ItemID 1
ItemID=2 does not lay within that same daterange.
It's fromdate 01/01/1900 is less than 01/01/2005 and it's Todate, is greater
than 01/01/2005.
With the AND, you're asking for stuff BETWEEN 01/01/2005 and 01/01/2005.
ItemID=2 exceeds this range on both ends.
I suspect you want OR?
> Please let me know how to get this record also.
> Thanks in advance
>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 14, 9:49 am, Ram <R...@.discussions.microsoft.com> wrote:
> Hi All,
> I have below table structure:
> ItemID Price FromDate = > ToDate
> ----=--=AD--
> 1 10.00 01/01/2005 = > 12/31/2005
> 1 9.00 01/01/2004 = > 12/31/2004
> 2 2.00 01/01/1900 = > 01/01/9999
> ----=--=AD--
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
> If I use FromDate >=3D 01/01/2005 and ToDate <=3D 01/01/2005 then I will =get
> only one record (ItemID=3D1). But ItemID =3D2 also lies in the same dater=ange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
> Please let me know how to get this record also.
> Thanks in advance
I think you are storing dummy values in fromdate and todate column
when it is unknown (NULL)
If that is the case
(FromDate >=3D 01/01/2005 and ToDate < '01/01/2006' )
OR
FromDate >=3D 01/01/1900 and ToDate <=3D '01/01/9999' )
Note: < 01/01/2006 instead of <=3D 12/31/2005' to take care of time
portion
M A Srinivas

date range in SQL 2005

Anyone knows if SQL 2005 extends the date range from SQL 2K (i.e.
1873-9999)?
Thanks,
Wenlei
No.
In the last 2 weeks there have been similar threads here and in
..programming. It is all explained there, so have a Google.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Wenlei Fang" <wenlei@.hotmail.com> wrote in message
news:eeY$PKINFHA.436@.TK2MSFTNGP09.phx.gbl...
> Anyone knows if SQL 2005 extends the date range from SQL 2K (i.e.
> 1873-9999)?
> Thanks,
> Wenlei
>
|||No.
Please post future SQL Server 2005 questions to the SQL Server 2005
newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Wenlei Fang" <wenlei@.hotmail.com> wrote in message
news:eeY$PKINFHA.436@.TK2MSFTNGP09.phx.gbl...
> Anyone knows if SQL 2005 extends the date range from SQL 2K (i.e.
> 1873-9999)?
> Thanks,
> Wenlei
>
|||Thank you, Mike Aaron.
"Wenlei Fang" <wenlei@.hotmail.com> wrote in message
news:eeY$PKINFHA.436@.TK2MSFTNGP09.phx.gbl...
> Anyone knows if SQL 2005 extends the date range from SQL 2K (i.e.
> 1873-9999)?
> Thanks,
> Wenlei
>

date range in SQL 2005

Anyone knows if SQL 2005 extends the date range from SQL 2K (i.e.
1873-9999)?
Thanks,
WenleiNo.
In the last 2 weeks there have been similar threads here and in
.programming. It is all explained there, so have a Google.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Wenlei Fang" <wenlei@.hotmail.com> wrote in message
news:eeY$PKINFHA.436@.TK2MSFTNGP09.phx.gbl...
> Anyone knows if SQL 2005 extends the date range from SQL 2K (i.e.
> 1873-9999)?
> Thanks,
> Wenlei
>|||No.
Please post future SQL Server 2005 questions to the SQL Server 2005
newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Wenlei Fang" <wenlei@.hotmail.com> wrote in message
news:eeY$PKINFHA.436@.TK2MSFTNGP09.phx.gbl...
> Anyone knows if SQL 2005 extends the date range from SQL 2K (i.e.
> 1873-9999)?
> Thanks,
> Wenlei
>|||Thank you, Mike Aaron.
"Wenlei Fang" <wenlei@.hotmail.com> wrote in message
news:eeY$PKINFHA.436@.TK2MSFTNGP09.phx.gbl...
> Anyone knows if SQL 2005 extends the date range from SQL 2K (i.e.
> 1873-9999)?
> Thanks,
> Wenlei
>sql