Date Range Question..

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
> >
> >
>

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?
>

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

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

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.

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