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 PARAMETER-- Simple Question

Hi,
I am using the following simple stored procedure to dispaly data between 2
date ranges.
CREATE PROCEDURE [dbo].[sp_Triotek_MasterPOS]
(
@.manucode varchar(50),
@.brand varchar(50),
@.StartDate datetime,
@.EndDate datetime
)
AS
SELECT ITEMHIST.PERIOD, ITEMHIST.PER_Q_SI, ITEMS.ITEMNO, ITEMS.DESCRIPT,
ITEMS.BRAND, ITEMS.MANUCODE, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RESER,
ITEMHIST.FISCAL_YR, ITEMS.Q_ON_ORDER, ITEMS.QTY_STK
FROM ITEMHIST INNER JOIN
ITEMS ON ITEMHIST.ITEMNO = ITEMS.ITEMNO
INNER JOIN MANUFACT ON ITEMS.MANUCODE=MANUFACT.CODE
WHERE (ITEMS.MANUCODE = @.manucode or @.manucode is null )
AND( ITEMS.BRAND=@.brand or @.brand is null)
AND (ITEMS.ACTIVE='T')
AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate) )
AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate ) )
GO
Now the prblem is that when I enterd start date as 1 Sept 2004 and end date
as 1Aug 2005, then there is no data displayed. I know the problem is with th
e
last 2 "AND" clauses of my stored procedure. Please help. I want to display
data between the 2 date ranges.
Thanks
--
pmudAND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate) )
AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate ) )
Did you mean EndDate here, and not StartDate, on the last line? Anyway, I'm
not sure that breaking down a datetime into month and year to calculate a
range is very wise. If you're interested in blocking by months only, have
you considered adding a column to ITEMHIST that represents the month and
year combined, e.g. 20050101, 20050201, etc. This makes querying by date
ranges a true date range query, instead of separating the components of the
date and assuming that the range will always have a starting month and year
and then the ending date is starting month - 1 and ending year + 1.
A
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:F524E910-7FF5-43B4-957D-2814DAE7D6BD@.microsoft.com...
> Hi,
> I am using the following simple stored procedure to dispaly data between 2
> date ranges.
> CREATE PROCEDURE [dbo].[sp_Triotek_MasterPOS]
> (
> @.manucode varchar(50),
> @.brand varchar(50),
> @.StartDate datetime,
> @.EndDate datetime
> )
> AS
> SELECT ITEMHIST.PERIOD, ITEMHIST.PER_Q_SI, ITEMS.ITEMNO,
> ITEMS.DESCRIPT,
> ITEMS.BRAND, ITEMS.MANUCODE, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RESER,
> ITEMHIST.FISCAL_YR, ITEMS.Q_ON_ORDER, ITEMS.QTY_STK
> FROM ITEMHIST INNER JOIN
> ITEMS ON ITEMHIST.ITEMNO = ITEMS.ITEMNO
> INNER JOIN MANUFACT ON ITEMS.MANUCODE=MANUFACT.CODE
> WHERE (ITEMS.MANUCODE = @.manucode or @.manucode is null )
> AND( ITEMS.BRAND=@.brand or @.brand is null)
> AND (ITEMS.ACTIVE='T')
> AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate) )
> AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate ) )
> GO
> Now the prblem is that when I enterd start date as 1 Sept 2004 and end
> date
> as 1Aug 2005, then there is no data displayed. I know the problem is with
> the
> last 2 "AND" clauses of my stored procedure. Please help. I want to
> display
> data between the 2 date ranges.
> Thanks
> --
> pmud|||Try,
...
WHERE
(ITEMS.MANUCODE = @.manucode or @.manucode is null )
AND (ITEMS.BRAND=@.brand or @.brand is null)
AND (ITEMS.ACTIVE='T')
AND (ITEMHIST.FISCAL_YR * 100) + ITEMHIST.PERIOD
between (Year(@.StartDate ) * 100) + Month(@.StartDate)
AND (Year(@.EndDate) * 100) + Month(@.EndDate)
If there are indexes in table [ITEMHIST] by [FISCAL_YR] and / or [PERIOD],
do not expect sql server to perform an index s in these indexes. Using
those columns in an expression, limit then to be considered search arguments
.
AMB
"pmud" wrote:

> Hi,
> I am using the following simple stored procedure to dispaly data between 2
> date ranges.
> CREATE PROCEDURE [dbo].[sp_Triotek_MasterPOS]
> (
> @.manucode varchar(50),
> @.brand varchar(50),
> @.StartDate datetime,
> @.EndDate datetime
> )
> AS
> SELECT ITEMHIST.PERIOD, ITEMHIST.PER_Q_SI, ITEMS.ITEMNO, ITEMS.DESCRIP
T,
> ITEMS.BRAND, ITEMS.MANUCODE, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RESER,
> ITEMHIST.FISCAL_YR, ITEMS.Q_ON_ORDER, ITEMS.QTY_STK
> FROM ITEMHIST INNER JOIN
> ITEMS ON ITEMHIST.ITEMNO = ITEMS.ITEMNO
> INNER JOIN MANUFACT ON ITEMS.MANUCODE=MANUFACT.CODE
> WHERE (ITEMS.MANUCODE = @.manucode or @.manucode is null )
> AND( ITEMS.BRAND=@.brand or @.brand is null)
> AND (ITEMS.ACTIVE='T')
> AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate) )
> AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate ) )
> GO
> Now the prblem is that when I enterd start date as 1 Sept 2004 and end dat
e
> as 1Aug 2005, then there is no data displayed. I know the problem is with
the
> last 2 "AND" clauses of my stored procedure. Please help. I want to displa
y
> data between the 2 date ranges.
> Thanks
> --
> pmud|||Adding a computed column as Aaron mentioned would seem to be a much better
long term solution that what you are doing now. That said, if you are
unable to alter the schema, give this a try. Replace the last two AND
conditions with the following:
cast(cast(ITEMHIST.PERIOD as varchar) + '-1-' + cast(ITEMHIST.FISCAL_YR as
varchar) as datetime) between @.StartDate and @.EndDate
--Brian
(Please reply to the newsgroups only.)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23yOQwrpoFHA.2180@.TK2MSFTNGP15.phx.gbl...
> AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate) )
> AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate ) )
> Did you mean EndDate here, and not StartDate, on the last line? Anyway,
> I'm not sure that breaking down a datetime into month and year to
> calculate a range is very wise. If you're interested in blocking by
> months only, have you considered adding a column to ITEMHIST that
> represents the month and year combined, e.g. 20050101, 20050201, etc.
> This makes querying by date ranges a true date range query, instead of
> separating the components of the date and assuming that the range will
> always have a starting month and year and then the ending date is starting
> month - 1 and ending year + 1.
> A
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:F524E910-7FF5-43B4-957D-2814DAE7D6BD@.microsoft.com...
>|||Hi ,
Yes, I meant @.EndDate in the last line. Is there any other way of doing
this. It is not necesaary to do month and year separately. i did it that way
bcoz the ITEMHIST table does not have a Date field but separate PERIOD(
month) and FISCAL_YEAR fields.
Is there any way I can do it together rather than breaking it down...If not,
then can you please explain your menthod in detail..
Thansk for all your help..
--
pmud
"Alejandro Mesa" wrote:
> Try,
>
> ...
> WHERE
> (ITEMS.MANUCODE = @.manucode or @.manucode is null )
> AND (ITEMS.BRAND=@.brand or @.brand is null)
> AND (ITEMS.ACTIVE='T')
> AND (ITEMHIST.FISCAL_YR * 100) + ITEMHIST.PERIOD
> between (Year(@.StartDate ) * 100) + Month(@.StartDate)
> AND (Year(@.EndDate) * 100) + Month(@.EndDate)
> If there are indexes in table [ITEMHIST] by [FISCAL_YR] and / or [PERIOD],
> do not expect sql server to perform an index s in these indexes. Using
> those columns in an expression, limit then to be considered search argumen
ts.
>
> AMB
> "pmud" wrote:
>|||Hi,
Actually I just now checked, Aaron was right, I by chance wrote @.StartDate
instaed of @.EndDate in the sp and after changing ti , it works. I apologize.
Though I would really like to underastand the enw methods you all told me.I
didnt quite understand them...Can you please explain your solutions...
I really appreciate all your help
--
pmud
"pmud" wrote:
> Hi ,
> Yes, I meant @.EndDate in the last line. Is there any other way of doing
> this. It is not necesaary to do month and year separately. i did it that w
ay
> bcoz the ITEMHIST table does not have a Date field but separate PERIOD(
> month) and FISCAL_YEAR fields.
> Is there any way I can do it together rather than breaking it down...If no
t,
> then can you please explain your menthod in detail..
> Thansk for all your help..
> --
> pmud
>
> "Alejandro Mesa" wrote:
>|||It is hard for me to give you a good explanation because my englis is far
from good, but I am posting an example so you can get the idea.
I am creating a number based on FISCAL_YEAR and PERIOD (yyyymm):
(FISCAL_YEAR * 100) + PERIOD
and the same with the start and end dates. Then I am selecting just where
this number is between the ones from start and end date.
create table t1 (
c1 int not null identity primary key,
c2 int not null check (c2 between 1900 and 3000),
c3 int not null check (c3 between 1 and 12)
)
go
insert into t1(c2, c3) values(2000, 1)
insert into t1(c2, c3) values(2000, 5)
insert into t1(c2, c3) values(2001, 6)
insert into t1(c2, c3) values(2002, 8)
insert into t1(c2, c3) values(2005, 7)
insert into t1(c2, c3) values(2005, 8)
go
declare @.sd datetime
declare @.ed datetime
set @.sd = '20000201'
set @.ed = '20050701'
select
c1,
c2,
c3,
(c2 * 100) + c3 as c4,
(year(@.sd) * 100) + month(@.sd) as c5,
(year(@.ed) * 100) + month(@.ed) as c6
from
t1
where
(c2 * 100) + c3 between (year(@.sd) * 100) + month(@.sd) and (year(@.ed) *
100) + month(@.ed)
order by
c2, c3
go
drop table t1
go
AMB
"pmud" wrote:
> Hi ,
> Yes, I meant @.EndDate in the last line. Is there any other way of doing
> this. It is not necesaary to do month and year separately. i did it that w
ay
> bcoz the ITEMHIST table does not have a Date field but separate PERIOD(
> month) and FISCAL_YEAR fields.
> Is there any way I can do it together rather than breaking it down...If no
t,
> then can you please explain your menthod in detail..
> Thansk for all your help..
> --
> pmud
>
> "Alejandro Mesa" wrote:
>|||Hi Aljendro,
Thanks for talking the time and explaining it. I appreciate it. Your
explanation was very helpful and whatever little remaining doubt I have I
think when I will implement it myself, that will make it still clearer.
Thanks for ur help.
--
pmud
"Alejandro Mesa" wrote:
> It is hard for me to give you a good explanation because my englis is far
> from good, but I am posting an example so you can get the idea.
> I am creating a number based on FISCAL_YEAR and PERIOD (yyyymm):
> (FISCAL_YEAR * 100) + PERIOD
> and the same with the start and end dates. Then I am selecting just where
> this number is between the ones from start and end date.
> create table t1 (
> c1 int not null identity primary key,
> c2 int not null check (c2 between 1900 and 3000),
> c3 int not null check (c3 between 1 and 12)
> )
> go
> insert into t1(c2, c3) values(2000, 1)
> insert into t1(c2, c3) values(2000, 5)
> insert into t1(c2, c3) values(2001, 6)
> insert into t1(c2, c3) values(2002, 8)
> insert into t1(c2, c3) values(2005, 7)
> insert into t1(c2, c3) values(2005, 8)
> go
> declare @.sd datetime
> declare @.ed datetime
> set @.sd = '20000201'
> set @.ed = '20050701'
> select
> c1,
> c2,
> c3,
> (c2 * 100) + c3 as c4,
> (year(@.sd) * 100) + month(@.sd) as c5,
> (year(@.ed) * 100) + month(@.ed) as c6
> from
> t1
> where
> (c2 * 100) + c3 between (year(@.sd) * 100) + month(@.sd) and (year(@.ed) *
> 100) + month(@.ed)
> order by
> c2, c3
> go
> drop table t1
> go
>
> AMB
>
> "pmud" wrote:
>|||Hi Brian,
I used the cast statement as it is.. and it works...I am trying to
undersatnd how it exactly works though...
pmud
"Brian Lawton" wrote:

> Adding a computed column as Aaron mentioned would seem to be a much better
> long term solution that what you are doing now. That said, if you are
> unable to alter the schema, give this a try. Replace the last two AND
> conditions with the following:
> cast(cast(ITEMHIST.PERIOD as varchar) + '-1-' + cast(ITEMHIST.FISCAL_YR as
> varchar) as datetime) between @.StartDate and @.EndDate
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:%23yOQwrpoFHA.2180@.TK2MSFTNGP15.phx.gbl...
>
>|||Basically it just converts your Period and FiscalYear combination into a
date with the format mm/01/yyyy. As a datetime datatype, it then does the
comparison to the @.StartDate and @.EndDate via the BETWEEN.
--Brian
(Please reply to the newsgroups only.)
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:0AD179F0-3096-48B9-88D3-AD7C9F9BE88E@.microsoft.com...
> Hi Brian,
> I used the cast statement as it is.. and it works...I am trying to
> undersatnd how it exactly works though...
> --
> pmud
>
> "Brian Lawton" wrote:
>

Date issue with Derived Column / Expression Language

Can someone confirm this for me? The expression language in SSIS has the same limitations on date ranges as Sql Server? That limitation is that valid date ranges are from Jan 1, 1753 to Dec 31, 9999.

When ever I try to do a date function (DATEPART, for example) in a Derived Column Transformation on a date less than 1/1/1753, I get an error. I initially discovered this when bringing data over from Oracle to Sql Server. Just as a test, I created a text file filled with various dates and tried to import it. Whenever a date is less than 1/1/1753, it blows up.

For example, this expression code - DATEPART("YEAR",Date) will yield this error - [Derived Column [24]] Error: The "component "Derived Column" (24)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "YEAR" (80)" specifies failure on error. An error occurred on the specified object of the specified component.

As a workaround, I've been using a Script Component to do date checking, but this is obviously not ideal.

Jeff,

I don't think that's the case. I have just created a package containing a DT_DBTIMESTAMP, DT_DBDATE & DT_DATE and managed to put the value "1500-12-31" into each of those columns.

-Jamie

|||Hey Jamie, thanks for taking the time to answer....but, did you attempt a date function on any of the dates. Try doing a DATEPART("YEAR",date_col) and see what happens.|||

Hey. That function works on any date after 1753-01-01, nothing before that.

Looks like you were right!!

-Jamie

|||Great. I wanted some independent verification. I just submitted this as a bug.

Date Format

Hi everyone, I need help with the date formatting in ssrs 2000. I'm writting reports with date ranges, specific dates, and all are working fine. Now, I was asked to create a report that shows current month, a different one for the QTR, and and also for the current year. I've tried different formats, keep gettin stuck where it doesnt return data. Please help.

Abner

Are you using stored procedure? where do you need a help? in SQL or Report designer, please give me some more details.

|||

I need help in report designer. I was able to run my report and get current dates, GETDATE(), but when it comes to current month, I've tried different ways but it dont work. So, I'm seeking as much help as possible. I'm still new at reporting service, but eager to learn as much as possible.

Thanks advance

Abner

|||

Hi, Abner:

IDoes this meet your needs?

Month(ToDay())

Year(ToDay())

|||

Hi REX,

Thanks for the reply, but I have tried those formats and i just get a blank page. Well, when I type MONTH(TODAY()) it dont reconize today, so I wrote MONTH(GETDATE()), and it just returns a blank page...ive tried it with =DatePart(m, GETDATE()), also =DATEADD(m, 0, GETDATE()), and DatePart("m", field!Promise_Date.Value). and it still just give me a blank page. Is it because I also have it divided by week? even thought I test it without breaking it into weeks. Pleasssssssssseeeeeeeeee help. I dont know how else to do it.

Abner

|||

Hi Abner,

Date related functions in report viewer are not same as we have in dot net, they have some weired behavior, so we cant not use abbreviated Dateparts like "mm", "m" or "d". Check this post for more info on this -

http://blogs.msdn.com/bimusings/archive/2005/09/13/464836.aspx