I have a parameter called @.To that utilizes the date field in my datasource,
What I need it to do is to give the data that ends at @.To and that starts at
the first day of the month specified in the parameter @.To.
So I have constructed this query
WHERE (CONVERT(char(11), Date, 3) BETWEEN '1/@.TO/@.TO' AND @.TO)
However this doesn't work :(
So I do it this way:
WHERE (CONVERT(char(11), Date) BETWEEN '@.TO 1 @.TO' AND @.TO)
This does work, however the parameter must be entered in the following
format Apr 16 2004 (which is useless to me)
Can anyone out there help?
AshYou might want to use an expression-based command text, where you write a
VB.NET expression which evaluates into a SQL query commandtext. However, you
have to be careful to not mix up SQL and VB.NET syntax. You might want to
checkout this walkthrough in BOL:
http://msdn.microsoft.com/library/en-us/RSAMPLES/htm/rss_tutorials_v1_7qr7.asp
Using an expression-based command text you could then use VB functions like
CDate(), etc. to dynamically construct the dates you need in the query.
There are many options for your case. Assuming you have have two DateTime
report parameters:
="SELECT ... BETWEEN CAST('" & CDate(Parameters!StartDate.Value) & "' as
smalldatetime) AND CAST('" & CDate(Parameters!EndDate.Value) & "' as
smalldatatime))"
You could then initialize these two report parameters with an
expression-based default value that automatically calculates the first and
the last day of the current month:
StartDate:
=DateValue(Month(Today) & " 1," & Year(Today))
EndDate:
=DateValue(Month(Today) & " 1," &
Year(Today)).AddDays(System.DateTime.DaysInMonth(Year(Today),
Month(Today))-1)
Information about DateTime / conversion functions is available on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateAdd.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatediff.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatepart.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"AshVsAOD" <.> wrote in message
news:eAxsg90VEHA.2564@.TK2MSFTNGP11.phx.gbl...
> I have a parameter called @.To that utilizes the date field in my
datasource,
> What I need it to do is to give the data that ends at @.To and that starts
at
> the first day of the month specified in the parameter @.To.
> So I have constructed this query
> WHERE (CONVERT(char(11), Date, 3) BETWEEN '1/@.TO/@.TO' AND @.TO)
> However this doesn't work :(
> So I do it this way:
> WHERE (CONVERT(char(11), Date) BETWEEN '@.TO 1 @.TO' AND @.TO)
> This does work, however the parameter must be entered in the following
> format Apr 16 2004 (which is useless to me)
> Can anyone out there help?
> Ash
>|||Thanks for the quick reply!!!
I do not want to use two parameters only one. And sadly I just don't have
the knowledge of VB to construct these expressions. I am basically there
using SQL only code and will continue the battle.
Thanks for your help.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:O6lS%23b1VEHA.3428@.TK2MSFTNGP12.phx.gbl...
> You might want to use an expression-based command text, where you write a
> VB.NET expression which evaluates into a SQL query commandtext. However,
you
> have to be careful to not mix up SQL and VB.NET syntax. You might want to
> checkout this walkthrough in BOL:
>
http://msdn.microsoft.com/library/en-us/RSAMPLES/htm/rss_tutorials_v1_7qr7.asp
> Using an expression-based command text you could then use VB functions
like
> CDate(), etc. to dynamically construct the dates you need in the query.
> There are many options for your case. Assuming you have have two DateTime
> report parameters:
> ="SELECT ... BETWEEN CAST('" & CDate(Parameters!StartDate.Value) & "' as
> smalldatetime) AND CAST('" & CDate(Parameters!EndDate.Value) & "' as
> smalldatatime))"
> You could then initialize these two report parameters with an
> expression-based default value that automatically calculates the first and
> the last day of the current month:
> StartDate:
> =DateValue(Month(Today) & " 1," & Year(Today))
> EndDate:
> =DateValue(Month(Today) & " 1," &
> Year(Today)).AddDays(System.DateTime.DaysInMonth(Year(Today),
> Month(Today))-1)
>
> Information about DateTime / conversion functions is available on MSDN:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateAdd.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatediff.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatepart.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "AshVsAOD" <.> wrote in message
> news:eAxsg90VEHA.2564@.TK2MSFTNGP11.phx.gbl...
> > I have a parameter called @.To that utilizes the date field in my
> datasource,
> > What I need it to do is to give the data that ends at @.To and that
starts
> at
> > the first day of the month specified in the parameter @.To.
> >
> > So I have constructed this query
> >
> > WHERE (CONVERT(char(11), Date, 3) BETWEEN '1/@.TO/@.TO' AND @.TO)
> >
> > However this doesn't work :(
> >
> > So I do it this way:
> > WHERE (CONVERT(char(11), Date) BETWEEN '@.TO 1 @.TO' AND @.TO)
> >
> > This does work, however the parameter must be entered in the following
> > format Apr 16 2004 (which is useless to me)
> >
> > Can anyone out there help?
> >
> > Ash
> >
> >
>
Thursday, March 29, 2012
date range query not returning anticipated results
Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
Query:
select startdate
from auditlog
where startdate between '6/18/2004' and '6/20/2004'
I get results like this, with dates outside the specified range:
6/18/2004
6/18/2004
6/2/2004
6/19/2003
6/19/2003
6/2/2004
6/2/2004
6/2/2004
Any suggestions?
Any difference if you do it this way?
select startdate
from auditlog
where startdate between '20040618' and '20040620'
Andrew J. Kelly SQL MVP
"Yayahim" <Yayahim@.discussions.microsoft.com> wrote in message
news:C9A1AD19-40FE-42CE-BAC1-C69353F5F458@.microsoft.com...
> Newbie question. When I query a field (datetime datatype) the results
appear as if it were looking at text. There is no time data stored in the
field.
> Query:
> select startdate
> from auditlog
> where startdate between '6/18/2004' and '6/20/2004'
> I get results like this, with dates outside the specified range:
> 6/18/2004
> 6/18/2004
> 6/2/2004
> 6/19/2003
> 6/19/2003
> 6/2/2004
> 6/2/2004
> 6/2/2004
> Any suggestions?
|||Any difference if you do it this way?
select startdate
from auditlog
where startdate between '20040618' and '20040620'
Andrew J. Kelly SQL MVP
"Yayahim" <Yayahim@.discussions.microsoft.com> wrote in message
news:C9A1AD19-40FE-42CE-BAC1-C69353F5F458@.microsoft.com...
> Newbie question. When I query a field (datetime datatype) the results
appear as if it were looking at text. There is no time data stored in the
field.
> Query:
> select startdate
> from auditlog
> where startdate between '6/18/2004' and '6/20/2004'
> I get results like this, with dates outside the specified range:
> 6/18/2004
> 6/18/2004
> 6/2/2004
> 6/19/2003
> 6/19/2003
> 6/2/2004
> 6/2/2004
> 6/2/2004
> Any suggestions?
|||Are you using SQL Server 7? I don't think SQL Server 2000 will do this,
but in any case, try
where startdate between cast('20040618' as datetime) and cast('20040620'
as datetime)
Steve Kass
Drew University
Yayahim wrote:
>Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
>Query:
>select startdate
>from auditlog
>where startdate between '6/18/2004' and '6/20/2004'
>I get results like this, with dates outside the specified range:
>6/18/2004
>6/18/2004
>6/2/2004
>6/19/2003
>6/19/2003
>6/2/2004
>6/2/2004
>6/2/2004
>Any suggestions?
>
|||Are you using SQL Server 7? I don't think SQL Server 2000 will do this,
but in any case, try
where startdate between cast('20040618' as datetime) and cast('20040620'
as datetime)
Steve Kass
Drew University
Yayahim wrote:
>Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
>Query:
>select startdate
>from auditlog
>where startdate between '6/18/2004' and '6/20/2004'
>I get results like this, with dates outside the specified range:
>6/18/2004
>6/18/2004
>6/2/2004
>6/19/2003
>6/19/2003
>6/2/2004
>6/2/2004
>6/2/2004
>Any suggestions?
>
Query:
select startdate
from auditlog
where startdate between '6/18/2004' and '6/20/2004'
I get results like this, with dates outside the specified range:
6/18/2004
6/18/2004
6/2/2004
6/19/2003
6/19/2003
6/2/2004
6/2/2004
6/2/2004
Any suggestions?
Any difference if you do it this way?
select startdate
from auditlog
where startdate between '20040618' and '20040620'
Andrew J. Kelly SQL MVP
"Yayahim" <Yayahim@.discussions.microsoft.com> wrote in message
news:C9A1AD19-40FE-42CE-BAC1-C69353F5F458@.microsoft.com...
> Newbie question. When I query a field (datetime datatype) the results
appear as if it were looking at text. There is no time data stored in the
field.
> Query:
> select startdate
> from auditlog
> where startdate between '6/18/2004' and '6/20/2004'
> I get results like this, with dates outside the specified range:
> 6/18/2004
> 6/18/2004
> 6/2/2004
> 6/19/2003
> 6/19/2003
> 6/2/2004
> 6/2/2004
> 6/2/2004
> Any suggestions?
|||Any difference if you do it this way?
select startdate
from auditlog
where startdate between '20040618' and '20040620'
Andrew J. Kelly SQL MVP
"Yayahim" <Yayahim@.discussions.microsoft.com> wrote in message
news:C9A1AD19-40FE-42CE-BAC1-C69353F5F458@.microsoft.com...
> Newbie question. When I query a field (datetime datatype) the results
appear as if it were looking at text. There is no time data stored in the
field.
> Query:
> select startdate
> from auditlog
> where startdate between '6/18/2004' and '6/20/2004'
> I get results like this, with dates outside the specified range:
> 6/18/2004
> 6/18/2004
> 6/2/2004
> 6/19/2003
> 6/19/2003
> 6/2/2004
> 6/2/2004
> 6/2/2004
> Any suggestions?
|||Are you using SQL Server 7? I don't think SQL Server 2000 will do this,
but in any case, try
where startdate between cast('20040618' as datetime) and cast('20040620'
as datetime)
Steve Kass
Drew University
Yayahim wrote:
>Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
>Query:
>select startdate
>from auditlog
>where startdate between '6/18/2004' and '6/20/2004'
>I get results like this, with dates outside the specified range:
>6/18/2004
>6/18/2004
>6/2/2004
>6/19/2003
>6/19/2003
>6/2/2004
>6/2/2004
>6/2/2004
>Any suggestions?
>
|||Are you using SQL Server 7? I don't think SQL Server 2000 will do this,
but in any case, try
where startdate between cast('20040618' as datetime) and cast('20040620'
as datetime)
Steve Kass
Drew University
Yayahim wrote:
>Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
>Query:
>select startdate
>from auditlog
>where startdate between '6/18/2004' and '6/20/2004'
>I get results like this, with dates outside the specified range:
>6/18/2004
>6/18/2004
>6/2/2004
>6/19/2003
>6/19/2003
>6/2/2004
>6/2/2004
>6/2/2004
>Any suggestions?
>
Date range problem in crystal report 8.5
Hi all
I have a problem in using date range
If i select a date range,need to get values for proveious years june 1st to may 31st of current year
ex: 02/01/2006 - 02/28/2006(Current)
need to get values for 06/01/2005 - 05/31/2006 date range
could any one help me in writing formula for this
Thanks in advancedoesn't look like real question ,, , pls be specific|||Thanks for your reply
i would like to give you more specific
In my requiremnet if i selected a date range ex:01/06/2006 - 31/06/2006
need to get the values of preveious years of June 1st - current years May 31st
01/06/2005 - 31/05/2006 values should display
actually we have two fields in that in first field need to display the first date range values and in second one need to do like that
We have two fields named
current and year to date
current means date range the user selected
Year to date means data from June 1 of previous year to May 31 st of Current year|||Hi
use this formula
datetime('01-jan-2006')-365
include if condition to handle leap year
Hope it helps|||It's very simple just use this formula in formula field
formula =DateAdd ("yyyy",-1 , {YOUR DATE })
and use this formula wherever you want to use same date of previous year
Best Luck...|||Thanks for your reply
its useful for me
could you pls tell how to take fixed date 1st june to end date 31st may
I have a problem in using date range
If i select a date range,need to get values for proveious years june 1st to may 31st of current year
ex: 02/01/2006 - 02/28/2006(Current)
need to get values for 06/01/2005 - 05/31/2006 date range
could any one help me in writing formula for this
Thanks in advancedoesn't look like real question ,, , pls be specific|||Thanks for your reply
i would like to give you more specific
In my requiremnet if i selected a date range ex:01/06/2006 - 31/06/2006
need to get the values of preveious years of June 1st - current years May 31st
01/06/2005 - 31/05/2006 values should display
actually we have two fields in that in first field need to display the first date range values and in second one need to do like that
We have two fields named
current and year to date
current means date range the user selected
Year to date means data from June 1 of previous year to May 31 st of Current year|||Hi
use this formula
datetime('01-jan-2006')-365
include if condition to handle leap year
Hope it helps|||It's very simple just use this formula in formula field
formula =DateAdd ("yyyy",-1 , {YOUR DATE })
and use this formula wherever you want to use same date of previous year
Best Luck...|||Thanks for your reply
its useful for me
could you pls tell how to take fixed date 1st june to end date 31st may
Date range problem
Hello all,
I am trying to write a query that compares a member's enrollment period
with the products that their group has had during that period (all
members belong to a group and the products that the member has are
based on that group). I need to get the date range for all products
that the member had during their enrollment.
Here are a few rules:
- In the source table there are some group products that have two
ranges that are really contiguous. This is because another column that
we don't care about may have changed between those two periods. If the
end_date = DATEADD(dy, 1, start_date) then the two periods are actually
contiguous. These should only appear as one row in the output.
- If the gap is greater than one day then two rows should result
- If the product changes, of course it should be two rows in the output
- If a group has a product from before the start of the member's
enrollment then the start_date for the row should be the member's
start_date. If the product extends past the member leaving the group
then the end_date should be that of the member.
- In my sample data below I only have as many as two rows back to back
for the same product that are contiguous. In reality there could be
even more than that.
I have SQL that will join the two tables based on either the start or
the end date of the group product falling in the member's enrollment
period, but I'm not sure of the best way to merge the contiguous date
ranges into single rows. Any suggestions?
Erland, despite it being late on a Friday afternoon, the SQL and sample
output are below. ;-)
Thanks,
-Tom.
CREATE TABLE Members (
group_id INT NOT NULL,
member_id INT NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL )
GO
ALTER TABLE Membership ADD CONSTRAINT PRIMARY KEY PK_Members PRIMARY
KEY (group_id, member_id)
GO
CREATE TABLE Group_Products (
group_id INT NOT NULL,
product_id INT NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL )
GO
ALTER TABLE Group_Products ADD CONSTRAINT PRIMARY KEY PK_Group_Products
PRIMARY KEY (group_id, product_id, start_date)
GO
INSERT INTO Members VALUES (1, 1, '2002-01-01', '9999-12-31')
INSERT INTO Members VALUES (1, 2, '2004-11-01', '9999-12-31')
INSERT INTO Members VALUES (1, 3, '2000-10-01', '2004-12-31')
INSERT INTO Members VALUES (2, 4, '2002-01-01', '2005-01-15')
INSERT INTO Members VALUES (2, 5, '2004-10-01', '9999-12-31')
GO
INSERT INTO Group_Products VALUES (1, 1, '2001-01-01', '2003-12-31')
INSERT INTO Group_Products VALUES (1, 1, '2004-01-01', '2004-11-15')
INSERT INTO Group_Products VALUES (1, 2, '2004-11-16', '9999-12-31')
INSERT INTO Group_Products VALUES (2, 1, '2002-01-01', '2004-11-01')
INSERT INTO Group_Products VALUES (2, 1, '2004-11-15', '9999-12-31')
GO
Expected Output:
group_id member_id product_id start_date end_date
--- --- ---- ---- ----
1 1 1 2002-01-01 2004-11-15
1 1 2 2004-11-16 9999-12-31
1 2 1 2004-11-01 2004-11-15
1 2 2 2004-11-16 9999-12-31
1 3 1 2001-01-01 2004-11-15
1 3 2 2004-11-16 2004-12-31
2 4 1 2002-01-01 2004-11-01
2 4 1 2004-11-15 9999-12-31
2 5 1 2004-10-01 2004-11-01
2 5 1 2004-11-15 9999-12-31Thomas R. Hummel (tom_hummel@.hotmail.com) writes:
> I have SQL that will join the two tables based on either the start or
> the end date of the group product falling in the member's enrollment
> period, but I'm not sure of the best way to merge the contiguous date
> ranges into single rows. Any suggestions?
> Erland, despite it being late on a Friday afternoon, the SQL and sample
> output are below. ;-)
Hey, here on my side it's not late Friday afternoon, it's just about
midnight!
Anyway, this looks like it will work, but please test further:
SELECT m.group_id, m.member_id, gp.product_id, gp.start_date, gp.end_date
FROM Members m
JOIN (SELECT a.group_id, a.product_id, a.start_date,
end_date = MIN(b.end_date)
FROM Group_Products a
JOIN Group_Products b ON a.group_id = b.group_id
AND a.product_id = b.product_id
WHERE a.start_date <= b.start_date
AND NOT EXISTS
(SELECT *
FROM Group_Products c
WHERE a.group_id = c.group_id
AND a.product_id = c.product_id
AND dateadd(DAY, -1, a.start_date) = c.end_date)
AND NOT EXISTS
(SELECT *
FROM Group_Products d
WHERE b.group_id = d.group_id
AND b.product_id = d.product_id
AND b.end_date = dateadd(DAY, -1, d.start_date))
GROUP BY a.group_id, a.product_id, a.start_date) gp
ON m.group_id = gp.group_id
ORDER BY m.group_id, m.member_id, gp.product_id, gp.start_date
All the fun goes on the derived table. First I make partial self-join
between two instances of Group_Products, so I get all combinations
of start_date, end_date for a group/product combo. The first WHERE
condition filters away those where the left table has start_date later
then than to the right. Then the two NOT EXISTS does away with the
rows where end_date to the left has an adjacent start_date and
vice versa.
Here is some augmented test data:
INSERT INTO Group_Products VALUES (1, 1, '2001-01-01', '2003-12-31')
INSERT INTO Group_Products VALUES (1, 1, '2004-01-01', '2004-01-05')
INSERT INTO Group_Products VALUES (1, 1, '2004-01-06', '2004-11-15')
INSERT INTO Group_Products VALUES (1, 1, '2005-01-06', '2005-11-15')
INSERT INTO Group_Products VALUES (1, 2, '2004-11-16', '9999-12-31')
INSERT INTO Group_Products VALUES (2, 1, '2002-01-01', '2004-11-01')
INSERT INTO Group_Products VALUES (2, 1, '2004-11-15', '9999-12-31')
There's three adjacent periods for 1/1, and then there is a non-adjcent
rerun as well.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 18 Mar 2005 13:01:32 -0800, Thomas R. Hummel wrote:
>Erland, despite it being late on a Friday afternoon, the SQL and sample
>output are below. ;-)
Hi Tom,
I hope this doesn't mean that only Erland may reply?
Let's break this down in easy steps.
First, create a query that uses lots of self-joins and not exists
queries to join contiguous group_products. The logic is: only take rows
that are the start of a contiguous set (no row has end_date equal to
start_date - 1); join these to all rows with same group/product that
don't start earlier and that are not themselves the start of a
contiguous set. In SQL:
SELECT a.group_id, a.product_id, a.start_date, MAX(b.end_date)
FROM Group_Products AS a
INNER JOIN Group_Products AS b
ON b.group_id = a.group_id
AND b.product_id = a.product_id
AND b.start_date >= a.start_date
AND NOT EXISTS (SELECT *
FROM Group_Products AS c
WHERE c.group_id = a.group_id
AND c.product_id = a.product_id
AND c.start_date > a.start_date
AND c.start_date < b.end_date
AND NOT EXISTS (SELECT *
FROM Group_Products AS d
WHERE d.group_id = c.group_id
AND d.product_id =
c.product_id
AND d.end_date =
DATEADD(day, -1, c.start_date)))
WHERE NOT EXISTS (SELECT *
FROM Group_Products AS e
WHERE e.group_id = a.group_id
AND e.product_id = a.product_id
AND e.end_date = DATEADD(day, -1,
a.start_date))
GROUP BY a.group_id, a.product_id, a.start_date
Converting NOT EXISTS to outer joins can be interesting. It sometimes
speeds up the query. And if you do it with nested NOT EXISTS, you end up
with nested outer joins - always fun, at friday midnight! <g
SELECT a.group_id, a.product_id, a.start_date, MAX(b.end_date)
FROM Group_Products AS a
INNER JOIN Group_Products AS b
ON b.group_id = a.group_id
AND b.product_id = a.product_id
AND b.start_date >= a.start_date
LEFT JOIN Group_Products AS c
LEFT JOIN Group_Products AS d
ON d.group_id = c.group_id
AND d.product_id = c.product_id
AND d.end_date = DATEADD(day, -1, c.start_date)
ON c.group_id = a.group_id
AND c.product_id = a.product_id
AND c.start_date > a.start_date
AND c.start_date < b.end_date
AND d.group_id IS NULL
LEFT JOIN Group_Products AS e
ON e.group_id = a.group_id
AND e.product_id = a.product_id
AND e.end_date = DATEADD(day, -1, a.start_date)
WHERE e.group_id IS NULL
AND c.group_id IS NULL
GROUP BY a.group_id, a.product_id, a.start_date
In this case, I saw no performance difference. Test it on your own data
to see how it works for you. I'll stick with this version for the rest
of the post (if only to prevent linewrapping). you use whichever suits
you best.
Once we have this result, the rest is easy. Just use the query above as
derived table (or you could create a view) and join that to the members
table. Use CASE to find out which start_date and end_date to display:
SELECT m.group_id, m.member_id, g.product_id,
CASE WHEN m.start_date < g.start_date
THEN g.start_date
ELSE m.start_date END AS start_date,
CASE WHEN m.end_date > g.end_date
THEN g.end_date
ELSE m.end_date END AS end_date
FROM Members AS m
INNER JOIN (SELECT a.group_id, a.product_id, a.start_date,
MAX(b.end_date) AS end_date
FROM Group_Products AS a
INNER JOIN Group_Products AS b
ON b.group_id = a.group_id
AND b.product_id = a.product_id
AND b.start_date >= a.start_date
LEFT JOIN Group_Products AS c
LEFT JOIN Group_Products AS d
ON d.group_id = c.group_id
AND d.product_id = c.product_id
AND d.end_date = DATEADD(day, -1, c.start_date)
ON c.group_id = a.group_id
AND c.product_id = a.product_id
AND c.start_date > a.start_date
AND c.start_date < b.end_date
AND d.group_id IS NULL
LEFT JOIN Group_Products AS e
ON e.group_id = a.group_id
AND e.product_id = a.product_id
AND e.end_date = DATEADD(day, -1, a.start_date)
WHERE e.group_id IS NULL
AND c.group_id IS NULL
GROUP BY a.group_id, a.product_id, a.start_date) AS g
ON g.group_id = m.group_id
AND g.start_date <= m.end_date
AND g.end_date >= m.start_date
ORDER BY m.group_id, m.member_id, g.product_id, start_date
This query produces the requested output when I run it on your test
data. I didn't test it on other input data.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 18 Mar 2005 23:03:10 +0000 (UTC), Erland Sommarskog wrote:
>Anyway, this looks like it will work, but please test further:
Drat! You did manage to beat me to it...
Have a nice weekend!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote in message
news:1111179692.412743.218250@.f14g2000cwb.googlegr oups.com...
> Hello all,
> I am trying to write a query that compares a member's enrollment period
> with the products that their group has had during that period (all
> members belong to a group and the products that the member has are
> based on that group). I need to get the date range for all products
> that the member had during their enrollment.
> Here are a few rules:
> - In the source table there are some group products that have two
> ranges that are really contiguous. This is because another column that
> we don't care about may have changed between those two periods. If the
> end_date = DATEADD(dy, 1, start_date) then the two periods are actually
> contiguous. These should only appear as one row in the output.
> - If the gap is greater than one day then two rows should result
> - If the product changes, of course it should be two rows in the output
> - If a group has a product from before the start of the member's
> enrollment then the start_date for the row should be the member's
> start_date. If the product extends past the member leaving the group
> then the end_date should be that of the member.
> - In my sample data below I only have as many as two rows back to back
> for the same product that are contiguous. In reality there could be
> even more than that.
> I have SQL that will join the two tables based on either the start or
> the end date of the group product falling in the member's enrollment
> period, but I'm not sure of the best way to merge the contiguous date
> ranges into single rows. Any suggestions?
> Erland, despite it being late on a Friday afternoon, the SQL and sample
> output are below. ;-)
> Thanks,
> -Tom.
> CREATE TABLE Members (
> group_id INT NOT NULL,
> member_id INT NOT NULL,
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL )
> GO
> ALTER TABLE Membership ADD CONSTRAINT PRIMARY KEY PK_Members PRIMARY
> KEY (group_id, member_id)
> GO
> CREATE TABLE Group_Products (
> group_id INT NOT NULL,
> product_id INT NOT NULL,
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL )
> GO
> ALTER TABLE Group_Products ADD CONSTRAINT PRIMARY KEY PK_Group_Products
> PRIMARY KEY (group_id, product_id, start_date)
> GO
> INSERT INTO Members VALUES (1, 1, '2002-01-01', '9999-12-31')
> INSERT INTO Members VALUES (1, 2, '2004-11-01', '9999-12-31')
> INSERT INTO Members VALUES (1, 3, '2000-10-01', '2004-12-31')
> INSERT INTO Members VALUES (2, 4, '2002-01-01', '2005-01-15')
> INSERT INTO Members VALUES (2, 5, '2004-10-01', '9999-12-31')
> GO
> INSERT INTO Group_Products VALUES (1, 1, '2001-01-01', '2003-12-31')
> INSERT INTO Group_Products VALUES (1, 1, '2004-01-01', '2004-11-15')
> INSERT INTO Group_Products VALUES (1, 2, '2004-11-16', '9999-12-31')
> INSERT INTO Group_Products VALUES (2, 1, '2002-01-01', '2004-11-01')
> INSERT INTO Group_Products VALUES (2, 1, '2004-11-15', '9999-12-31')
> GO
> Expected Output:
> group_id member_id product_id start_date end_date
> --- --- ---- ---- ----
> 1 1 1 2002-01-01 2004-11-15
> 1 1 2 2004-11-16 9999-12-31
> 1 2 1 2004-11-01 2004-11-15
> 1 2 2 2004-11-16 9999-12-31
> 1 3 1 2001-01-01 2004-11-15
> 1 3 2 2004-11-16 2004-12-31
> 2 4 1 2002-01-01 2004-11-01
> 2 4 1 2004-11-15 9999-12-31
> 2 5 1 2004-10-01 2004-11-01
> 2 5 1 2004-11-15 9999-12-31
The following view will coalesce ranges. It's been separated out from
the main query for readability and other applicability.
CREATE VIEW GroupProductsCoalesced
(group_id, product_id, start_date, end_date)
AS
SELECT P.group_id, P.product_id,
MIN(P.start_date) AS start_date, P.end_date
FROM (SELECT P2.group_id, P2.product_id,
P2.start_date, MIN(P1.end_date) AS end_date
FROM (SELECT group_id, product_id, start_date, end_date
FROM Group_Products AS P1
WHERE NOT EXISTS
(SELECT *
FROM Group_Products AS P2
WHERE P1.group_id = P2.group_id AND
P1.product_id = P2.product_id AND
P1.end_date = P2.start_date - 1))
AS P1
INNER JOIN
Group_Products AS P2
ON P1.group_id = P2.group_id AND
P1.product_id = P2.product_id AND
P1.start_date >= P2.start_date
GROUP BY P2.group_id, P2.product_id, P2.start_date) AS P
GROUP BY P.group_id, P.product_id, P.end_date
SELECT M.group_id, M.member_id, P.product_id,
CASE WHEN P.start_date >= M.start_date
THEN P.start_date
ELSE M.start_date
END AS start_date,
CASE WHEN P.end_date <= M.end_date
THEN P.end_date
ELSE M.end_date
END AS end_date
FROM Members AS M
INNER JOIN
GroupProductsCoalesced AS P
ON M.group_id = P.group_id AND
P.start_date < M.end_date AND
P.end_date > M.start_date
ORDER BY M.group_id, M.member_id, P.product_id, start_date
group_id member_id product_id start_date end_date
1 1 1 2002-01-01 00:00:00.000 2004-11-15 00:00:00.000
1 1 2 2004-11-16 00:00:00.000 9999-12-31 00:00:00.000
1 2 1 2004-11-01 00:00:00.000 2004-11-15 00:00:00.000
1 2 2 2004-11-16 00:00:00.000 9999-12-31 00:00:00.000
1 3 1 2001-01-01 00:00:00.000 2004-11-15 00:00:00.000
1 3 2 2004-11-16 00:00:00.000 2004-12-31 00:00:00.000
2 4 1 2002-01-01 00:00:00.000 2004-11-01 00:00:00.000
2 4 1 2004-11-15 00:00:00.000 2005-01-15 00:00:00.000
2 5 1 2004-10-01 00:00:00.000 2004-11-01 00:00:00.000
2 5 1 2004-11-15 00:00:00.000 9999-12-31 00:00:00.000
--
JAG|||I ended up using a method similar to this, but encapsulating the main
logic for determining the ranges within a view as John had suggested. I
checked about a dozen different cases and it looks like it is working
correctly. Performance isn't too bad either.
Thanks to everyone for the suggestions.
-Tom.|||Well, for some reason my original reply to this never seemed to get
posted, so...
>On 18 Mar 2005 13:01:32 -0800, Thomas R. Hummel wrote:
>>Erland, despite it being late on a Friday afternoon, the SQL and
sample
>>output are below. ;-)
> I hope this doesn't mean that only Erland may reply?
Not at all. Last week I posted a question to the newsgroup and I
neglected to include all of the CREATE TABLE, INSERT, and expected
outcome information. Erland gave me a (minor) hard time about it and my
excuse was that it was late on a Friday. I was just making a reference
to that.
Thanks for your solution!
-Tom.|||On 22 Mar 2005 13:27:41 -0800, Thomas R. Hummel wrote:
>> I hope this doesn't mean that only Erland may reply?
>Not at all.
(snip)
Hi Tom,
Woops, I appear to have caused confusion.
I meant to write a smiley on that line, but I apparently forgot it. Here
is is: :-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Date Range Problem
I have a report with one table that has a start-date field that I would
like to use to filter the results on the report. I do not want the
user to enter a date instead I would like to create a parameter in a
drop down that they can choose Period1, Period 2 and so on.
There is not a Period column to reference to and I am not sure how to
use a start and end date to reference different parameters. Is there a
way to use the start_date and statically assign a date range value to a
parameter and then have the results filtered back based on the
parameter?
Basically here is what I am trying to do...
Parameter Value
Period 1: 01/02/06 through 02/05/06 (these values come
from the start-date field
Period 2: 02/06/06 through 03/05/06
Period 3: 03/06/06 through 04/05/06
and so on for twelve periods.
I know how to create a non-queried parameter but I don't know how to
set the value to reference a date range.
Any help is greatly appreciated!Is your date range is fixed.
ie Period 2: 02/06/06 through 03/05/06 is this date is fixed
meaning for period 2 always you will get 02/06/06 through 03/05/06 then it
can be done.
try this code in your data tab.
if @.period = 1
select * from ABC where [start_date] between '2005/1/01' and '2005/1/31'
else
select * from ABC where [start_date] between '2005/2/01' and '2005/2/31'
and so on....
when you select the period dependiong on the period selected it executes the
query
Amarnath
"swtjen01" wrote:
> I have a report with one table that has a start-date field that I would
> like to use to filter the results on the report. I do not want the
> user to enter a date instead I would like to create a parameter in a
> drop down that they can choose Period1, Period 2 and so on.
> There is not a Period column to reference to and I am not sure how to
> use a start and end date to reference different parameters. Is there a
> way to use the start_date and statically assign a date range value to a
> parameter and then have the results filtered back based on the
> parameter?
> Basically here is what I am trying to do...
> Parameter Value
> Period 1: 01/02/06 through 02/05/06 (these values come
> from the start-date field
> Period 2: 02/06/06 through 03/05/06
> Period 3: 03/06/06 through 04/05/06
> and so on for twelve periods.
> I know how to create a non-queried parameter but I don't know how to
> set the value to reference a date range.
> Any help is greatly appreciated!
>sql
like to use to filter the results on the report. I do not want the
user to enter a date instead I would like to create a parameter in a
drop down that they can choose Period1, Period 2 and so on.
There is not a Period column to reference to and I am not sure how to
use a start and end date to reference different parameters. Is there a
way to use the start_date and statically assign a date range value to a
parameter and then have the results filtered back based on the
parameter?
Basically here is what I am trying to do...
Parameter Value
Period 1: 01/02/06 through 02/05/06 (these values come
from the start-date field
Period 2: 02/06/06 through 03/05/06
Period 3: 03/06/06 through 04/05/06
and so on for twelve periods.
I know how to create a non-queried parameter but I don't know how to
set the value to reference a date range.
Any help is greatly appreciated!Is your date range is fixed.
ie Period 2: 02/06/06 through 03/05/06 is this date is fixed
meaning for period 2 always you will get 02/06/06 through 03/05/06 then it
can be done.
try this code in your data tab.
if @.period = 1
select * from ABC where [start_date] between '2005/1/01' and '2005/1/31'
else
select * from ABC where [start_date] between '2005/2/01' and '2005/2/31'
and so on....
when you select the period dependiong on the period selected it executes the
query
Amarnath
"swtjen01" wrote:
> I have a report with one table that has a start-date field that I would
> like to use to filter the results on the report. I do not want the
> user to enter a date instead I would like to create a parameter in a
> drop down that they can choose Period1, Period 2 and so on.
> There is not a Period column to reference to and I am not sure how to
> use a start and end date to reference different parameters. Is there a
> way to use the start_date and statically assign a date range value to a
> parameter and then have the results filtered back based on the
> parameter?
> Basically here is what I am trying to do...
> Parameter Value
> Period 1: 01/02/06 through 02/05/06 (these values come
> from the start-date field
> Period 2: 02/06/06 through 03/05/06
> Period 3: 03/06/06 through 04/05/06
> and so on for twelve periods.
> I know how to create a non-queried parameter but I don't know how to
> set the value to reference a date range.
> Any help is greatly appreciated!
>sql
Date range parameters don't work in reportviewer?
Hi, I've got this ssrs 2005 report that works great passing a few
security related parameters from asp.net codebehind. However, there
are two date related parameters that won't be coming from my web form,
but rather from the report form itself. When I test the report's date
parameters from visual studio it work fine, but when I attempt the
same report from a reportviewer no matter what input I place on the
report's date fields or even if I select the date picker, the report
simply resets to default and reloads. And actually the date picker
from the reportviewer does not not even pop up.
Here's my aspx code:
<%@. Page Language="VB" AutoEventWireup="false"
Inherits="_ReportViewer" MasterPageFile="~/Main/MasterPage.master"
CodeFile="~/Reports/CashSales.aspx.vb" Title="Cash Sales" %>
<%@. Register Assembly="Microsoft.ReportViewer.WebForms,
Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<asp:Content ID="Content1" ContentPlaceHolderID="Content1"
runat="Server">
<rsweb:ReportViewer BackColor="Transparent" ZoomMode="PageWidth"
Width="100%" ProcessingMode="Remote" ID="ReportViewer1"
runat="server">
<ServerReport ReportPath="/Retailer/CashSales"
ReportServerUrl="http://myserver/reportserver" />
</rsweb:ReportViewer>
</asp:Content>
the code behind:
Imports system.web.security.membership
Imports system.web.security.Roles
Imports Microsoft.Reporting.Webforms
Note: I'm only passing the parameters that are not coming from the
reportviewer form.
Partial Class _ReportViewer
Inherits System.Web.UI.Page
Private Users As New Retailer.Core()
Protected Overrides Sub OnLoad(ByVal e As EventArgs)
Dim Roles() As String = GetRolesForUser(Page.User.Identity.Name.ToString)
Dim cred As New Retailer.ReportServerCredentials("myuser",
"mypassword", "mydomain")
ReportViewer1.ServerReport.ReportServerCredentials = cred
Dim param As New ReportParameter("r_user",
Page.User.Identity.Name.ToString)
Dim param2 As New ReportParameter("r_role", Roles(0))
Dim p() As ReportParameter = {param, param2}
ReportViewer1.ServerReport.SetParameters(p)
ReportViewer1.ServerReport.Refresh()
End Sub
End Class
on my reportviewer form, at the top I have parameters for startdate
and end date, they are not set to internal or hidden.
thanks for any help or information.update on this. I see I am getting a javascript error, but it then
loading the report. How can I troubleshoot this? I've got disable i.e
script debugging turned off on i.e 7.0, but where would i place this
javascript to trap the error.
debugger; // execution will break here
to see the errors in VS.NET.
my report viewer in on a contentpage.
I see others have had a similar problem.
thanks for any help or information!|||More on this..
I noticed I get a javascript error when click on the calendar select:
Line: 606
Object Required
When I attempt to debug I get a Just-In-time failed : Unspecified
error. Check the documentation index for 'Just-in-time debugging,
errors' for more information.
and if I enter anything in the date fields, it disregards them and
loads with the defaults.
Could it be that I'm not sending security information when these built
in javascript functions are called?
thanks again.
security related parameters from asp.net codebehind. However, there
are two date related parameters that won't be coming from my web form,
but rather from the report form itself. When I test the report's date
parameters from visual studio it work fine, but when I attempt the
same report from a reportviewer no matter what input I place on the
report's date fields or even if I select the date picker, the report
simply resets to default and reloads. And actually the date picker
from the reportviewer does not not even pop up.
Here's my aspx code:
<%@. Page Language="VB" AutoEventWireup="false"
Inherits="_ReportViewer" MasterPageFile="~/Main/MasterPage.master"
CodeFile="~/Reports/CashSales.aspx.vb" Title="Cash Sales" %>
<%@. Register Assembly="Microsoft.ReportViewer.WebForms,
Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<asp:Content ID="Content1" ContentPlaceHolderID="Content1"
runat="Server">
<rsweb:ReportViewer BackColor="Transparent" ZoomMode="PageWidth"
Width="100%" ProcessingMode="Remote" ID="ReportViewer1"
runat="server">
<ServerReport ReportPath="/Retailer/CashSales"
ReportServerUrl="http://myserver/reportserver" />
</rsweb:ReportViewer>
</asp:Content>
the code behind:
Imports system.web.security.membership
Imports system.web.security.Roles
Imports Microsoft.Reporting.Webforms
Note: I'm only passing the parameters that are not coming from the
reportviewer form.
Partial Class _ReportViewer
Inherits System.Web.UI.Page
Private Users As New Retailer.Core()
Protected Overrides Sub OnLoad(ByVal e As EventArgs)
Dim Roles() As String = GetRolesForUser(Page.User.Identity.Name.ToString)
Dim cred As New Retailer.ReportServerCredentials("myuser",
"mypassword", "mydomain")
ReportViewer1.ServerReport.ReportServerCredentials = cred
Dim param As New ReportParameter("r_user",
Page.User.Identity.Name.ToString)
Dim param2 As New ReportParameter("r_role", Roles(0))
Dim p() As ReportParameter = {param, param2}
ReportViewer1.ServerReport.SetParameters(p)
ReportViewer1.ServerReport.Refresh()
End Sub
End Class
on my reportviewer form, at the top I have parameters for startdate
and end date, they are not set to internal or hidden.
thanks for any help or information.update on this. I see I am getting a javascript error, but it then
loading the report. How can I troubleshoot this? I've got disable i.e
script debugging turned off on i.e 7.0, but where would i place this
javascript to trap the error.
debugger; // execution will break here
to see the errors in VS.NET.
my report viewer in on a contentpage.
I see others have had a similar problem.
thanks for any help or information!|||More on this..
I noticed I get a javascript error when click on the calendar select:
Line: 606
Object Required
When I attempt to debug I get a Just-In-time failed : Unspecified
error. Check the documentation index for 'Just-in-time debugging,
errors' for more information.
and if I enter anything in the date fields, it disregards them and
loads with the defaults.
Could it be that I'm not sending security information when these built
in javascript functions are called?
thanks again.
Date Range Parameter Help
Crystal Reports XI-
I have entered a date range parameter into my report to pick a range for Appointment Dates. When I choose a start date of 01/01/2007 and an end date of 01/31/2007, my report has all of January's appointments, but also has the last appointment of the previous month (in this case December). The same happens if I pick a date range from Feb 1st to the 28th. I get all of Feb appointments, but also the last day in January.
Am I missing something? Or has this happened to anyone else?Are you wanting the dates between your 2 date paramenters, and if so did you reference your date ranges in the record selection?
{table.appointmentdate} In {?StartDate} To {EndDate}
This will give you everything between the 2 dates.
GJ
I have entered a date range parameter into my report to pick a range for Appointment Dates. When I choose a start date of 01/01/2007 and an end date of 01/31/2007, my report has all of January's appointments, but also has the last appointment of the previous month (in this case December). The same happens if I pick a date range from Feb 1st to the 28th. I get all of Feb appointments, but also the last day in January.
Am I missing something? Or has this happened to anyone else?Are you wanting the dates between your 2 date paramenters, and if so did you reference your date ranges in the record selection?
{table.appointmentdate} In {?StartDate} To {EndDate}
This will give you everything between the 2 dates.
GJ
date range parameter
i have a need by date in my report. i would like to create a date parameter
where the user can select today, 1 week, 1 month, 60, 90, etc. days between
current date and need by date. i've created parameters before, but this
one's giving me trouble. any ideas?Hi,
I think I would create 2 parameters like to work with that kind of
requirements
- Datevalue: would hold the value (Number)
- DateType: would hold the parameter type (DropDown) filled with value
like (Days,Weeks,Months,...)
After that i would do the calculation at the stored procedure level
using the T-SQL DATEADD function..
HTH,
Eric|||Create parameters using non-queried and put in the following (as example)
Label Value
Today 0
1 Week 7
30 Days 30
60 Days 60
90 Days 90
Then in your query do this:
select * from whatever where mydatefield < dateadd(dd,?, getdate())
Anyway, gives you an idea of how to do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
> i have a need by date in my report. i would like to create a date
parameter
> where the user can select today, 1 week, 1 month, 60, 90, etc. days
between
> current date and need by date. i've created parameters before, but this
> one's giving me trouble. any ideas?|||In your where clause, you could have a statement such as:
WHERE
MyDate >= DATEADD(DAY, -@.DaysBack, GETDATE())
Then, in your parameter dropdown, you'll specify the number of days that
correlate to each value in the dropdown, e.g. 1 week - 7, etc.
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
>i have a need by date in my report. i would like to create a date
>parameter
> where the user can select today, 1 week, 1 month, 60, 90, etc. days
> between
> current date and need by date. i've created parameters before, but this
> one's giving me trouble. any ideas?|||Thanks for your help, but when I tried to do this I got an error message,
"argument data type datetime is invalid for argument 2 of dateadd function"
My where statement reads:
WHERE (mydate <= DATEDIFF(dd, @.date , GETDATE()))
@.date being the name of my parameter.
"Bruce L-C [MVP]" wrote:
> Create parameters using non-queried and put in the following (as example)
> Label Value
> Today 0
> 1 Week 7
> 30 Days 30
> 60 Days 60
> 90 Days 90
> Then in your query do this:
> select * from whatever where mydatefield < dateadd(dd,?, getdate())
> Anyway, gives you an idea of how to do this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "jmann" <jmann@.discussions.microsoft.com> wrote in message
> news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
> > i have a need by date in my report. i would like to create a date
> parameter
> > where the user can select today, 1 week, 1 month, 60, 90, etc. days
> between
> > current date and need by date. i've created parameters before, but this
> > one's giving me trouble. any ideas?
>
>|||Hi,
After giving it a little more tought, Here is what i would do:
Create 2 parameters
- DateValue (Integer) holding the value
- DateType (Dropdown) holding the parameter type
Label Value
Days 0
Weeks 1
Months 2
...
After that i would do the calculation at the query of stored procedure
level with something like this:
SELECT *
FROM Table1
WHERE Table1.DateField >= CASE
WHEN @.DateType = 0 THEN
DATEADD(dd,CAST(@.DateNumber AS
INTEGER),GETDATE())
WHEN @.DateType = 1 THEN
DATEADD(wk,CAST(@.DateNumber AS
INTEGER),GETDATE())
WHEN @.DateType = 2 THEN
DATEADD(mm,CAST(@.DateNumber AS
INTEGER),GETDATE())
END
HTH,
Eric|||WHERE (mydate <= DATEDIFF(dd, CAST(@.date AS INTEGER) , GETDATE()))|||This will work, or, just set the parameter type as integer instead of
string.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aiwa" <eric__brochu@.hotmail.com> wrote in message
news:1105996187.872921.265900@.f14g2000cwb.googlegroups.com...
> WHERE (mydate <= DATEDIFF(dd, CAST(@.date AS INTEGER) , GETDATE()))
>
where the user can select today, 1 week, 1 month, 60, 90, etc. days between
current date and need by date. i've created parameters before, but this
one's giving me trouble. any ideas?Hi,
I think I would create 2 parameters like to work with that kind of
requirements
- Datevalue: would hold the value (Number)
- DateType: would hold the parameter type (DropDown) filled with value
like (Days,Weeks,Months,...)
After that i would do the calculation at the stored procedure level
using the T-SQL DATEADD function..
HTH,
Eric|||Create parameters using non-queried and put in the following (as example)
Label Value
Today 0
1 Week 7
30 Days 30
60 Days 60
90 Days 90
Then in your query do this:
select * from whatever where mydatefield < dateadd(dd,?, getdate())
Anyway, gives you an idea of how to do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
> i have a need by date in my report. i would like to create a date
parameter
> where the user can select today, 1 week, 1 month, 60, 90, etc. days
between
> current date and need by date. i've created parameters before, but this
> one's giving me trouble. any ideas?|||In your where clause, you could have a statement such as:
WHERE
MyDate >= DATEADD(DAY, -@.DaysBack, GETDATE())
Then, in your parameter dropdown, you'll specify the number of days that
correlate to each value in the dropdown, e.g. 1 week - 7, etc.
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
>i have a need by date in my report. i would like to create a date
>parameter
> where the user can select today, 1 week, 1 month, 60, 90, etc. days
> between
> current date and need by date. i've created parameters before, but this
> one's giving me trouble. any ideas?|||Thanks for your help, but when I tried to do this I got an error message,
"argument data type datetime is invalid for argument 2 of dateadd function"
My where statement reads:
WHERE (mydate <= DATEDIFF(dd, @.date , GETDATE()))
@.date being the name of my parameter.
"Bruce L-C [MVP]" wrote:
> Create parameters using non-queried and put in the following (as example)
> Label Value
> Today 0
> 1 Week 7
> 30 Days 30
> 60 Days 60
> 90 Days 90
> Then in your query do this:
> select * from whatever where mydatefield < dateadd(dd,?, getdate())
> Anyway, gives you an idea of how to do this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "jmann" <jmann@.discussions.microsoft.com> wrote in message
> news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
> > i have a need by date in my report. i would like to create a date
> parameter
> > where the user can select today, 1 week, 1 month, 60, 90, etc. days
> between
> > current date and need by date. i've created parameters before, but this
> > one's giving me trouble. any ideas?
>
>|||Hi,
After giving it a little more tought, Here is what i would do:
Create 2 parameters
- DateValue (Integer) holding the value
- DateType (Dropdown) holding the parameter type
Label Value
Days 0
Weeks 1
Months 2
...
After that i would do the calculation at the query of stored procedure
level with something like this:
SELECT *
FROM Table1
WHERE Table1.DateField >= CASE
WHEN @.DateType = 0 THEN
DATEADD(dd,CAST(@.DateNumber AS
INTEGER),GETDATE())
WHEN @.DateType = 1 THEN
DATEADD(wk,CAST(@.DateNumber AS
INTEGER),GETDATE())
WHEN @.DateType = 2 THEN
DATEADD(mm,CAST(@.DateNumber AS
INTEGER),GETDATE())
END
HTH,
Eric|||WHERE (mydate <= DATEDIFF(dd, CAST(@.date AS INTEGER) , GETDATE()))|||This will work, or, just set the parameter type as integer instead of
string.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aiwa" <eric__brochu@.hotmail.com> wrote in message
news:1105996187.872921.265900@.f14g2000cwb.googlegroups.com...
> WHERE (mydate <= DATEDIFF(dd, CAST(@.date AS INTEGER) , GETDATE()))
>
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!
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
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
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
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
>
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
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
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
>
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
>
date range in DRILLTHROUGH Statement
Hi,
I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?
*********************************************************************************************************************************
DRILLTHROUGH
SELECT ([Measures].[Total Test Count]) ON columns
FROM [OLAP Test Cube]
WHERE
([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]
,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]
)
RETURN
KEY([$Dim Station].[Dim Tests],0)
,KEY([$Dim Station].[Dim Tests],1),
[$Dim Test Cycle].[Test Cycle],
[$Dim OverallResult].[Overall Result]
*********************************************************************************************************************************
Error:
Executing the query ...
Drillthrough failed because the coordinate identified by the SELECT clause is out of range.
Execution complete
*********************************************************************************************************************************
FYI If I remove data range part (RED color area) then it is working fine.
Regards,
Dinesh Patel
Try to use a subselect for the datarange
like..
SELECT ([Measures].[Total Test Count]) ON columns
FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])
WHERE
([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])
HANNES
|||When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.
DRILLTHROUGH
SELECT ([Measures].[Total Test Count]) ON columns
FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}
on columns from [OLAP Test Cube])
WHERE
([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]
)
RETURN
KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),
[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],
[$Dim Test Date].[Test Date]
seee out put below:
[$Dim Station].[Dim Tests] [$Dim Station].[Dim Tests] [$Dim Test Cycle].[Test Cycle] [$Dim OverallResult].[Overall Result] [$Dim Test Date].[Test Date]
2006-03-10T16:14:13 AA998280 ASM Fail 03-10-2006
2006-03-11T10:37:04 AA998280 OBD Only Fail 03-11-2006
2006-03-11T12:41:38 AA998280 OBD Only Fail 03-11-2006
2006-03-14T11:31:33 AA998280 ASM Fail 03-14-2006
2006-03-14T12:08:11 AA998280 ASM Fail 03-14-2006
2006-02-01T10:10:51 AA998280 TSI Fail 02-01-2006
2006-02-01T11:40:20 AA998280 OBD Only Fail 02-01-2006
2006-02-01T14:57:37 AA998280 TSI Fail 02-01-2006
2006-02-02T14:07:10 AA998280 ASM Fail 02-02-2006
2006-02-03T11:07:16 AA998280 ASM Fail 02-03-2006
2006-02-03T15:16:29 AA998280 ASM Fail 02-03-2006
2006-02-04T14:49:22 AA998280 OBD Only Fail 02-04-2006
2006-02-06T15:53:24 AA998280 ASM Fail 02-06-2006
2006-02-07T14:10:47 AA998280 OBD Only Fail 02-07-2006
2006-02-07T16:09:47 AA998280 OBD Only Fail 02-07-2006
2006-02-09T15:11:58 AA998280 ASM Fail 02-09-2006
2006-02-09T17:10:22 AA998280 OBD Only Fail 02-09-2006
2006-02-10T16:47:52 AA998280 OBD Only Fail 02-10-2006
2006-02-10T17:00:50 AA998280 TSI Fail 02-10-2006
2006-02-15T11:12:51 AA998280 OBD Only Fail 02-15-2006
2006-02-17T12:07:59 AA998280 ASM Fail 02-17-2006
2006-02-21T14:35:25 AA998280 OBD Only Fail 02-21-2006
2006-02-23T14:48:36 AA998280 OBD Only Fail 02-23-2006
2006-02-24T11:53:01 AA998280 ASM Fail 02-24-2006
2006-02-25T14:38:41 AA998280 TSI Fail 02-25-2006
2006-02-27T16:55:43 AA998280 OBD Only Fail 02-27-2006
2006-02-28T10:30:33 AA998280 OBD Only Fail 02-28-2006
2006-02-28T13:50:07 AA998280 ASM Fail 02-28-2006
2006-02-28T14:43:15 AA998280 ASM Fail 02-28-2006
2006-03-04T14:17:36 AA998280 OBD Only Fail 03-04-2006
2006-03-07T14:22:09 AA998280 OBD Only Fail 03-07-2006
2006-03-09T13:05:30 AA998280 ASM Fail 03-09-2006
2006-03-10T10:33:33 AA998280 OBD Only Fail 03-10-2006
HANNES
any solution?
|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.date range in DRILLTHROUGH Statement
Hi,
I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?
*********************************************************************************************************************************
DRILLTHROUGH
SELECT ([Measures].[Total Test Count]) ON columns
FROM [OLAP Test Cube]
WHERE
([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]
,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]
)
RETURN
KEY([$Dim Station].[Dim Tests],0)
,KEY([$Dim Station].[Dim Tests],1),
[$Dim Test Cycle].[Test Cycle],
[$Dim OverallResult].[Overall Result]
*********************************************************************************************************************************
Error:
Executing the query ...
Drillthrough failed because the coordinate identified by the SELECT clause is out of range.
Execution complete
*********************************************************************************************************************************
FYI If I remove data range part (RED color area) then it is working fine.
Regards,
Dinesh Patel
Try to use a subselect for the datarange
like..
SELECT ([Measures].[Total Test Count]) ON columns
FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])
WHERE
([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])
HANNES
|||When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.
DRILLTHROUGH
SELECT ([Measures].[Total Test Count]) ON columns
FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}
on columns from [OLAP Test Cube])
WHERE
([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]
)
RETURN
KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),
[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],
[$Dim Test Date].[Test Date]
seee out put below:
[$Dim Station].[Dim Tests] [$Dim Station].[Dim Tests] [$Dim Test Cycle].[Test Cycle] [$Dim OverallResult].[Overall Result] [$Dim Test Date].[Test Date]
2006-03-10T16:14:13 AA998280 ASM Fail 03-10-2006
2006-03-11T10:37:04 AA998280 OBD Only Fail 03-11-2006
2006-03-11T12:41:38 AA998280 OBD Only Fail 03-11-2006
2006-03-14T11:31:33 AA998280 ASM Fail 03-14-2006
2006-03-14T12:08:11 AA998280 ASM Fail 03-14-2006
2006-02-01T10:10:51 AA998280 TSI Fail 02-01-2006
2006-02-01T11:40:20 AA998280 OBD Only Fail 02-01-2006
2006-02-01T14:57:37 AA998280 TSI Fail 02-01-2006
2006-02-02T14:07:10 AA998280 ASM Fail 02-02-2006
2006-02-03T11:07:16 AA998280 ASM Fail 02-03-2006
2006-02-03T15:16:29 AA998280 ASM Fail 02-03-2006
2006-02-04T14:49:22 AA998280 OBD Only Fail 02-04-2006
2006-02-06T15:53:24 AA998280 ASM Fail 02-06-2006
2006-02-07T14:10:47 AA998280 OBD Only Fail 02-07-2006
2006-02-07T16:09:47 AA998280 OBD Only Fail 02-07-2006
2006-02-09T15:11:58 AA998280 ASM Fail 02-09-2006
2006-02-09T17:10:22 AA998280 OBD Only Fail 02-09-2006
2006-02-10T16:47:52 AA998280 OBD Only Fail 02-10-2006
2006-02-10T17:00:50 AA998280 TSI Fail 02-10-2006
2006-02-15T11:12:51 AA998280 OBD Only Fail 02-15-2006
2006-02-17T12:07:59 AA998280 ASM Fail 02-17-2006
2006-02-21T14:35:25 AA998280 OBD Only Fail 02-21-2006
2006-02-23T14:48:36 AA998280 OBD Only Fail 02-23-2006
2006-02-24T11:53:01 AA998280 ASM Fail 02-24-2006
2006-02-25T14:38:41 AA998280 TSI Fail 02-25-2006
2006-02-27T16:55:43 AA998280 OBD Only Fail 02-27-2006
2006-02-28T10:30:33 AA998280 OBD Only Fail 02-28-2006
2006-02-28T13:50:07 AA998280 ASM Fail 02-28-2006
2006-02-28T14:43:15 AA998280 ASM Fail 02-28-2006
2006-03-04T14:17:36 AA998280 OBD Only Fail 03-04-2006
2006-03-07T14:22:09 AA998280 OBD Only Fail 03-07-2006
2006-03-09T13:05:30 AA998280 ASM Fail 03-09-2006
2006-03-10T10:33:33 AA998280 OBD Only Fail 03-10-2006
HANNES
any solution?
|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.date range in DRILLTHROUGH Statement
Hi,
I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?
*********************************************************************************************************************************
DRILLTHROUGH
SELECT ([Measures].[Total Test Count]) ON columns
FROM [OLAP Test Cube]
WHERE
([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]
,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]
)
RETURN
KEY([$Dim Station].[Dim Tests],0)
,KEY([$Dim Station].[Dim Tests],1),
[$Dim Test Cycle].[Test Cycle],
[$Dim OverallResult].[Overall Result]
*********************************************************************************************************************************
Error:
Executing the query ...
Drillthrough failed because the coordinate identified by the SELECT clause is out of range.
Execution complete
*********************************************************************************************************************************
FYI If I remove data range part (RED color area) then it is working fine.
Regards,
Dinesh Patel
Try to use a subselect for the datarange
like..
SELECT ([Measures].[Total Test Count]) ON columns
FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])
WHERE
([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])
HANNES
|||When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.
DRILLTHROUGH
SELECT ([Measures].[Total Test Count]) ON columns
FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}
on columns from [OLAP Test Cube])
WHERE
([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]
)
RETURN
KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),
[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],
[$Dim Test Date].[Test Date]
seee out put below:
[$Dim Station].[Dim Tests] [$Dim Station].[Dim Tests] [$Dim Test Cycle].[Test Cycle] [$Dim OverallResult].[Overall Result] [$Dim Test Date].[Test Date]
2006-03-10T16:14:13 AA998280 ASM Fail 03-10-2006
2006-03-11T10:37:04 AA998280 OBD Only Fail 03-11-2006
2006-03-11T12:41:38 AA998280 OBD Only Fail 03-11-2006
2006-03-14T11:31:33 AA998280 ASM Fail 03-14-2006
2006-03-14T12:08:11 AA998280 ASM Fail 03-14-2006
2006-02-01T10:10:51 AA998280 TSI Fail 02-01-2006
2006-02-01T11:40:20 AA998280 OBD Only Fail 02-01-2006
2006-02-01T14:57:37 AA998280 TSI Fail 02-01-2006
2006-02-02T14:07:10 AA998280 ASM Fail 02-02-2006
2006-02-03T11:07:16 AA998280 ASM Fail 02-03-2006
2006-02-03T15:16:29 AA998280 ASM Fail 02-03-2006
2006-02-04T14:49:22 AA998280 OBD Only Fail 02-04-2006
2006-02-06T15:53:24 AA998280 ASM Fail 02-06-2006
2006-02-07T14:10:47 AA998280 OBD Only Fail 02-07-2006
2006-02-07T16:09:47 AA998280 OBD Only Fail 02-07-2006
2006-02-09T15:11:58 AA998280 ASM Fail 02-09-2006
2006-02-09T17:10:22 AA998280 OBD Only Fail 02-09-2006
2006-02-10T16:47:52 AA998280 OBD Only Fail 02-10-2006
2006-02-10T17:00:50 AA998280 TSI Fail 02-10-2006
2006-02-15T11:12:51 AA998280 OBD Only Fail 02-15-2006
2006-02-17T12:07:59 AA998280 ASM Fail 02-17-2006
2006-02-21T14:35:25 AA998280 OBD Only Fail 02-21-2006
2006-02-23T14:48:36 AA998280 OBD Only Fail 02-23-2006
2006-02-24T11:53:01 AA998280 ASM Fail 02-24-2006
2006-02-25T14:38:41 AA998280 TSI Fail 02-25-2006
2006-02-27T16:55:43 AA998280 OBD Only Fail 02-27-2006
2006-02-28T10:30:33 AA998280 OBD Only Fail 02-28-2006
2006-02-28T13:50:07 AA998280 ASM Fail 02-28-2006
2006-02-28T14:43:15 AA998280 ASM Fail 02-28-2006
2006-03-04T14:17:36 AA998280 OBD Only Fail 03-04-2006
2006-03-07T14:22:09 AA998280 OBD Only Fail 03-07-2006
2006-03-09T13:05:30 AA998280 ASM Fail 03-09-2006
2006-03-10T10:33:33 AA998280 OBD Only Fail 03-10-2006
HANNES
any solution?
|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.date range in DRILLTHROUGH Statement
Hi,
I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?
*********************************************************************************************************************************
DRILLTHROUGH
SELECT ([Measures].[Total Test Count]) ON columns
FROM [OLAP Test Cube]
WHERE
([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]
,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]
)
RETURN
KEY([$Dim Station].[Dim Tests],0)
,KEY([$Dim Station].[Dim Tests],1),
[$Dim Test Cycle].[Test Cycle],
[$Dim OverallResult].[Overall Result]
*********************************************************************************************************************************
Error:
Executing the query ...
Drillthrough failed because the coordinate identified by the SELECT clause is out of range.
Execution complete
*********************************************************************************************************************************
FYI If I remove data range part (RED color area) then it is working fine.
Regards,
Dinesh Patel
Try to use a subselect for the datarange
like..
SELECT ([Measures].[Total Test Count]) ON columns
FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])
WHERE
([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])
HANNES
|||When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.
DRILLTHROUGH
SELECT ([Measures].[Total Test Count]) ONcolumns
FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}
oncolumnsfrom [OLAP Test Cube])
WHERE
([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]
)
RETURN
KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),
[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],
[$Dim Test Date].[Test Date]
seee out put below:
[$Dim Station].[Dim Tests] [$Dim Station].[Dim Tests] [$Dim Test Cycle].[Test Cycle] [$Dim OverallResult].[Overall Result] [$Dim Test Date].[Test Date]
2006-03-10T16:14:13 AA998280 ASM Fail 03-10-2006
2006-03-11T10:37:04 AA998280 OBD Only Fail 03-11-2006
2006-03-11T12:41:38 AA998280 OBD Only Fail 03-11-2006
2006-03-14T11:31:33 AA998280 ASM Fail 03-14-2006
2006-03-14T12:08:11 AA998280 ASM Fail 03-14-2006
2006-02-01T10:10:51 AA998280 TSI Fail 02-01-2006
2006-02-01T11:40:20 AA998280 OBD Only Fail 02-01-2006
2006-02-01T14:57:37 AA998280 TSI Fail 02-01-2006
2006-02-02T14:07:10 AA998280 ASM Fail 02-02-2006
2006-02-03T11:07:16 AA998280 ASM Fail 02-03-2006
2006-02-03T15:16:29 AA998280 ASM Fail 02-03-2006
2006-02-04T14:49:22 AA998280 OBD Only Fail 02-04-2006
2006-02-06T15:53:24 AA998280 ASM Fail 02-06-2006
2006-02-07T14:10:47 AA998280 OBD Only Fail 02-07-2006
2006-02-07T16:09:47 AA998280 OBD Only Fail 02-07-2006
2006-02-09T15:11:58 AA998280 ASM Fail 02-09-2006
2006-02-09T17:10:22 AA998280 OBD Only Fail 02-09-2006
2006-02-10T16:47:52 AA998280 OBD Only Fail 02-10-2006
2006-02-10T17:00:50 AA998280 TSI Fail 02-10-2006
2006-02-15T11:12:51 AA998280 OBD Only Fail 02-15-2006
2006-02-17T12:07:59 AA998280 ASM Fail 02-17-2006
2006-02-21T14:35:25 AA998280 OBD Only Fail 02-21-2006
2006-02-23T14:48:36 AA998280 OBD Only Fail 02-23-2006
2006-02-24T11:53:01 AA998280 ASM Fail 02-24-2006
2006-02-25T14:38:41 AA998280 TSI Fail 02-25-2006
2006-02-27T16:55:43 AA998280 OBD Only Fail 02-27-2006
2006-02-28T10:30:33 AA998280 OBD Only Fail 02-28-2006
2006-02-28T13:50:07 AA998280 ASM Fail 02-28-2006
2006-02-28T14:43:15 AA998280 ASM Fail 02-28-2006
2006-03-04T14:17:36 AA998280 OBD Only Fail 03-04-2006
2006-03-07T14:22:09 AA998280 OBD Only Fail 03-07-2006
2006-03-09T13:05:30 AA998280 ASM Fail 03-09-2006
2006-03-10T10:33:33 AA998280 OBD Only Fail 03-10-2006
HANNES
any solution?
|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.sqlDate Range for SQL 2005
I was reading the article and was unable to get some of queries to
execute. Can anyone help get these to work:
SELECT dateadd(DAY, -1, convert(char(6), CURRENT_TIMESTAMP, 112) + '01')
Below alternative to the technique Erland posted. Although it's not as
clear as building a date string, it performs better in situations where
dates are calculated for each row. The overhead of converting to/from
date/string can add up for large tables.
Hi Aaron,
execute. Can anyone help get these to work:
How to get the last day of the previous month? And the last day of the
current month?
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'19991231')
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'20000131')
Thanks,
AaronAaron (odysseus183@.hotmail.com) writes:
Quote:
Originally Posted by
I was reading the article and was unable to get some of queries to
execute. Can anyone help get these to work:
>
How to get the last day of the previous month? And the last day of the
current month?
>
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'19991231')
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'20000131')
SELECT dateadd(DAY, -1, convert(char(6), CURRENT_TIMESTAMP, 112) + '01')
SELECT dateadd(DAY, -1,
dateadd(MONTH, 1, convert(char(6), CURRENT_TIMESTAMP, 112) + '01'))
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||How to get the last day of the previous month? And the last day of the
Quote:
Originally Posted by
current month?
Below alternative to the technique Erland posted. Although it's not as
clear as building a date string, it performs better in situations where
dates are calculated for each row. The overhead of converting to/from
date/string can add up for large tables.
SELECT
DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
SELECT
DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1,
0))
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Aaron" <odysseus183@.hotmail.comwrote in message
news:1184361210.176551.223620@.n2g2000hse.googlegro ups.com...
Quote:
Originally Posted by
>I was reading the article and was unable to get some of queries to
execute. Can anyone help get these to work:
>
How to get the last day of the previous month? And the last day of the
current month?
>
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'19991231')
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'20000131')
>
Thanks,
Aaron
>
Quote:
Originally Posted by
>I was reading the article and was unable to get some of queries to
>execute. Can anyone help get these to work:
>
>How to get the last day of the previous month? And the last day of the
>current month?
>
>SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
>'19991231')
>SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
>'20000131')
Hi Aaron,
When I copy and paste these queries into SSMS, I get the desired
results: June 30 and July 31. Your request for help to get them to work
implies that you got different results. Could you expand on that?
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Date Range for Report built against cube using MDX query
Hi,
I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.
My Issue:
I have the filtering working ok but if i select a date which is outside the range of the data within my cube for example if i select the starting date for the range as 1/Jan/1965 but by data starts from 15/Jan/1965 then no data is returned.
Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.
My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.
Thanks
MDX sets do not really work like this, they are made up of sets of discrete members and while they are ordered and you can get ranges of members, the idea of a between predicate does not really map well. I usually try to constrain my report parameter so that users cannot pick values outside of the valid member range.
If you can't do this you could probably use the filter function, provided that you had the actual date value stored somewhere (the MemberValue property is ideal for this). Which would make your range statement something like this:
filter( <date dim>.<hierarchy>.<date level>.Members
, <date dim>.<hierarchy>.MemberValue >= <fromDate>
and <date dim>.<hierarchy>.MemberValue <= <toDate>))
But this is going to be a lot slower than directly specifying a start and end member directly.|||
Hi,
Thanks for your reply. I managed to solve this issue by creating a time dimension with all the possible date combinations for the next five years and previous year which could occur in my data. Performance was an issue that is why this solution implemented.
|||Yeah, that's what I do aswell. And 5 years is only 1500 members which is not big as dimensions go.
Date Range for Report built against a cube using MDX query
Hi,
I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.
My Issue:
I have the filtering working ok but if i select a date which is outside the range of the data within my cube for example if i select the starting date for the range as 1/Jan/1965 but by data starts from 15/Jan/1965 then no data is returned.
Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.
My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.
Many Thanks
Absolutely it is possible. Have you tried removing the CONSTRAINED flag? Also do a search of this forum, i have posted several items regarding using dates and MDX within reports.
|||Hi,
Thanks for your reply. I managed to solve this issue by creating a time dimension with all the possible date combinations for the next five years and previous year which could occur in my data. Performance was an issue that is why this solution implemented.
Date Range for Previous Week
How do i get only the records for everything with only the dates from last week (monday-friday)? I want to have this run every week for the previous week. Here's my stored procudure. Right now i have this to run for the day before, but now i need it for the week before. So its a range. Please Help. Thanks!
Code Snippet
CREATE PROCEDURE [dbo].[Testing_Out_Of_Stock_SKUS_WEEKLY]
(@.Classification varchar(50))
AS
BEGIN
SELECT RC_STAT.dbo.Brand_Dimension.Report_Level,
RC_STAT.dbo.Brand_Dimension.[Cat vs Dog],
RC_STAT.dbo.Brand_Dimension.Item_Merged,
Qry_Sales_Group.Region_Key,
Qry_Sales_Group.Region,
Qry_Out_Of_Stock.product_structure_level,
Qry_Out_Of_Stock.product_entity_code,
Qry_Out_Of_Stock.cycle_day,
Qry_Out_Of_Stock.customer_code,
Qry_Out_Of_Stock.description,
Qry_Sales_Group.Code,
Qry_Sales_Group.SR_Name,
Qry_Sales_Group.Name AS Territory_Name,
Qry_Out_Of_Stock.Store_Name,
Qry_Out_Of_Stock.time_log,
Qry_Out_Of_Stock.out_of_stock,
Period_Code
FROM RC_STAT.dbo.Brand_Dimension INNER JOIN
dbo.Qry_Out_Of_Stock ON
RC_STAT.dbo.Brand_Dimension.Item_Key = dbo.Qry_Out_Of_Stock.product_entity_code COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN
dbo.Qry_Sales_Group ON
dbo.Qry_Out_Of_Stock.sales_person_code = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS
Where Classification=@.Classification DateDiff(dd,0,dbo.Qry_Out_Of_Stock.time_log) =
case
when Datepart(Weekday, Getdate()) = 2 then datediff(dd,0,GetDate()) - 3
else datediff(dd,0,GetDate()) - 1
end
END
SET NOCOUNT OFF
Here it is,
Code Snippet
CREATE PROCEDURE [dbo].[Testing_Out_Of_Stock_SKUS_WEEKLY]
(@.Classification varchar(50))
AS
BEGIN
SELECT
RC_STAT.dbo.Brand_Dimension.Report_Level,
RC_STAT.dbo.Brand_Dimension.[Cat vs Dog],
RC_STAT.dbo.Brand_Dimension.Item_Merged,
Qry_Sales_Group.Region_Key,
Qry_Sales_Group.Region,
Qry_Out_Of_Stock.product_structure_level,
Qry_Out_Of_Stock.product_entity_code,
Qry_Out_Of_Stock.cycle_day,
Qry_Out_Of_Stock.customer_code,
Qry_Out_Of_Stock.description,
Qry_Sales_Group.Code,
Qry_Sales_Group.SR_Name,
Qry_Sales_Group.Name AS Territory_Name,
Qry_Out_Of_Stock.Store_Name,
Qry_Out_Of_Stock.time_log,
Qry_Out_Of_Stock.out_of_stock,
Period_Code
FROM
RC_STAT.dbo.Brand_Dimension
INNER JOIN dbo.Qry_Out_Of_Stock
ON RC_STAT.dbo.Brand_Dimension.Item_Key = dbo.Qry_Out_Of_Stock.product_entity_code COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN dbo.Qry_Sales_Group
ON dbo.Qry_Out_Of_Stock.sales_person_code = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS
Where
Classification=@.Classification
And dbo.Qry_Out_Of_Stock.time_log >= dateadd(dd,-6,dateadd(dd,1-datepart(w,getdate()),convert(varchar,getdate(),101))), --Monday
And dbo.Qry_Out_Of_Stock.time_log < dateadd(dd,-1,dateadd(dd,1-datepart(w,getdate()),convert(varchar,getdate(),101))) -- Less than Saturday
END
SET NOCOUNT OFF
|||Thanks!! now how would i do this for periods. I do have a period field, but its a Char so i cant really do the (-1) . What would be a good aproach for this, is there a way i can have the timelog=period, each period has a date.|||What do you meant "Period", can you provide some sample data (both input & expected output)|||
I have a "Period_code" Field in the stored procedure. The field is a char. And looks like this 200708 (for period 8). Should i convert this number to an INT and then do a -1? for the where clause. And do a trim, to just grab the last 2 digits.
I want to get all the records for the previous period. So if today is period 200709 , i want period 200708 etc etc. I hope this helps.
Also how would i get it to realized that today=period 200709 or whatever period it is, on the date the report is run. Im trying to find a way to link these.
Subscribe to:
Posts (Atom)