Thursday, March 29, 2012
date range query not returning anticipated results
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
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 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 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 daily job
I have a SQL job that runs a query. That job has to be run daily (or
couple of times per day) The query has to collect information for some
created apps
WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
[Right Now]. How can dynamically program the First Day of Current
Month at 12:00am. Anybody know of any function that can give me this
value no matter at what time I run that job?
Thank you,
T.T.
declare @.date datetime
-- Figure out the first day of the month
select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
-- Trim off the time.
select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
SELECT @.date
RLF
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1173964963.027568.47310@.n59g2000hsh.googlegroups.com...
> Hi!
> I have a SQL job that runs a query. That job has to be run daily (or
> couple of times per day) The query has to collect information for some
> created apps
> WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
> [Right Now]. How can dynamically program the First Day of Current
> Month at 12:00am. Anybody know of any function that can give me this
> value no matter at what time I run that job?
> Thank you,
> T.
>|||On Mar 15, 9:41 am, "Russell Fields" <russellfie...@.nomail.com> wrote:[vbcol=seagreen]
> T.
> declare @.date datetime
> -- Figure out the first day of the month
> select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
> -- Trim off the time.
> select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
> SELECT @.date
> RLF
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1173964963.027568.47310@.n59g2000hsh.googlegroups.com...
>
>
>
>
Thanks. It works.sql
Date range for daily job
I have a SQL job that runs a query. That job has to be run daily (or
couple of times per day) The query has to collect information for some
created apps
WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
[Right Now]. How can dynamically program the First Day of Current
Month at 12:00am. Anybody know of any function that can give me this
value no matter at what time I run that job?
Thank you,
T.
T.
declare @.date datetime
-- Figure out the first day of the month
select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
-- Trim off the time.
select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
SELECT @.date
RLF
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1173964963.027568.47310@.n59g2000hsh.googlegro ups.com...
> Hi!
> I have a SQL job that runs a query. That job has to be run daily (or
> couple of times per day) The query has to collect information for some
> created apps
> WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
> [Right Now]. How can dynamically program the First Day of Current
> Month at 12:00am. Anybody know of any function that can give me this
> value no matter at what time I run that job?
> Thank you,
> T.
>
|||On Mar 15, 9:41 am, "Russell Fields" <russellfie...@.nomail.com> wrote:[vbcol=seagreen]
> T.
> declare @.date datetime
> -- Figure out the first day of the month
> select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
> -- Trim off the time.
> select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
> SELECT @.date
> RLF
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1173964963.027568.47310@.n59g2000hsh.googlegro ups.com...
>
>
Thanks. It works.
Date range for daily job
I have a SQL job that runs a query. That job has to be run daily (or
couple of times per day) The query has to collect information for some
created apps
WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
[Right Now]. How can dynamically program the First Day of Current
Month at 12:00am. Anybody know of any function that can give me this
value no matter at what time I run that job?
Thank you,
T.T.
declare @.date datetime
-- Figure out the first day of the month
select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
-- Trim off the time.
select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
SELECT @.date
RLF
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1173964963.027568.47310@.n59g2000hsh.googlegroups.com...
> Hi!
> I have a SQL job that runs a query. That job has to be run daily (or
> couple of times per day) The query has to collect information for some
> created apps
> WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
> [Right Now]. How can dynamically program the First Day of Current
> Month at 12:00am. Anybody know of any function that can give me this
> value no matter at what time I run that job?
> Thank you,
> T.
>|||On Mar 15, 9:41 am, "Russell Fields" <russellfie...@.nomail.com> wrote:
> T.
> declare @.date datetime
> -- Figure out the first day of the month
> select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
> -- Trim off the time.
> select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
> SELECT @.date
> RLF
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1173964963.027568.47310@.n59g2000hsh.googlegroups.com...
> > Hi!
> > I have a SQL job that runs a query. That job has to be run daily (or
> > couple of times per day) The query has to collect information for some
> > created apps
> > WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
> > [Right Now]. How can dynamically program the First Day of Current
> > Month at 12:00am. Anybody know of any function that can give me this
> > value no matter at what time I run that job?
> > Thank you,
> > T.
Thanks. It works.
Date Range filter
Hi guys,
I need to filter result in MDX query by passed Start Date and EndDate
I come up with following solution
WHERE (
FILTER([Dimension1].[ Dimension1].ALLMEMBERS,
CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) >= CDate('2/1/2007')--'2/6/2007'
AND CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) <= CDate('2/10/2007')--'2/7/2007'
)
But when I execute script it returns me null for all values in fact table.
I am sure there is data in specified interval
Any suggestions are very appreciated.
What is best approach to filter by date range in MDX query?
What service pack are you using? There's been some fixes in SP2 that may apply to this case.|||Actually it worked on SQL 2005 RTM. After I applied SP2 I start getting the empty cells.
|||This is confirmed: It worked fine with SSAS v9.00.247.00 but with SSAS v 9.00.3042.00 return null for all rows and columns
Does anyone has idea how to make it works?
|||Without specifics on what you're doing, it's hard to suggest a specific course of action. (What is the full query, what tool, etc.). Some suggestions on how to proceed:
Validate that your filter "set" is in fact correct. I'd look at the the specifics behind the selection, date formatting, etc to ensure it's still being properly parse.
Date Range filter
Hi guys,
I need to filter result in MDX query by passed Start Date and EndDate
I come up with following solution
WHERE (
FILTER([Dimension1].[ Dimension1].ALLMEMBERS,
CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) >= CDate('2/1/2007')--'2/6/2007'
AND CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) <= CDate('2/10/2007')--'2/7/2007'
)
But when I execute script it returns me null for all values in fact table.
I am sure there is data in specified interval
Any suggestions are very appreciated.
What is best approach to filter by date range in MDX query?
What service pack are you using? There's been some fixes in SP2 that may apply to this case.|||Actually it worked on SQL 2005 RTM. After I applied SP2 I start getting the empty cells.
|||This is confirmed: It worked fine with SSAS v9.00.247.00 but with SSAS v 9.00.3042.00 return null for all rows and columns
Does anyone has idea how to make it works?
|||Without specifics on what you're doing, it's hard to suggest a specific course of action. (What is the full query, what tool, etc.). Some suggestions on how to proceed:
Validate that your filter "set" is in fact correct. I'd look at the the specifics behind the selection, date formatting, etc to ensure it's still being properly parse.Date Range filter
Hi guys,
I need to filter result in MDX query by passed Start Date and EndDate
I come up with following solution
WHERE (
FILTER([Dimension1].[ Dimension1].ALLMEMBERS,
CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) >= CDate('2/1/2007')--'2/6/2007'
AND CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) <= CDate('2/10/2007')--'2/7/2007'
)
But when I execute script it returns me null for all values in fact table.
I am sure there is data in specified interval
Any suggestions are very appreciated.
What is best approach to filter by date range in MDX query?
What service pack are you using? There's been some fixes in SP2 that may apply to this case.|||Actually it worked on SQL 2005 RTM. After I applied SP2 I start getting the empty cells.
|||This is confirmed: It worked fine with SSAS v9.00.247.00 but with SSAS v 9.00.3042.00 return null for all rows and columns
Does anyone has idea how to make it works?
|||Without specifics on what you're doing, it's hard to suggest a specific course of action. (What is the full query, what tool, etc.). Some suggestions on how to proceed:
Validate that your filter "set" is in fact correct. I'd look at the the specifics behind the selection, date formatting, etc to ensure it's still being properly parse.sqlDate Range
information needed. The date is always 30 days for the current date. Is
there a way to set up a query to select current date minus 30 days that I
will not have to change the date each time?
Thanks in advance for the help.
On Tue, 28 Feb 2006 12:41:27 -0800, Daniell wrote:
>I have a query that I have to place a beginning and ending date to select the
>information needed. The date is always 30 days for the current date. Is
>there a way to set up a query to select current date minus 30 days that I
>will not have to change the date each time?
>Thanks in advance for the help.
Hi Daniell,
SELECT DATEADD(day, -30, CURRENT_TIMESTAMP)
Hugo Kornelis, SQL Server MVP
|||Thanks Hugo, I will give that a try.
"Hugo Kornelis" wrote:
> On Tue, 28 Feb 2006 12:41:27 -0800, Daniell wrote:
>
> Hi Daniell,
> SELECT DATEADD(day, -30, CURRENT_TIMESTAMP)
> --
> Hugo Kornelis, SQL Server MVP
>
sql
Date questions...
I have a field that's currently YYYYMMDD, and the Data Type is int. So what I need to do for a report is turn the date around to MMDDYYYY and pull just the last week worth of info. With some manupulation (probably not conventional though) I'm able to get the MMDDYYYY in a varchar format, but I'm stumped as to how to pull just the last week worth of info? I tried various forms of - 7 and of course it's not working. I can't say I've had to deal much with dates that weren't already in date/datetime format? Anyone else have ideas on how to convert it or pull the data?
Thanks again to all!
TiffanieHave you looked up the topics cast, convert, and dateadd in BOL. If not, please do so. Once you've done that this may help:
declare @.cdat char(08), @.edat datetime, @.bdat datetime
select @.cdat = '20050401' -- April 1, 2005
select @.edat = convert(datetime, @.cdat)
select @.bdat = dateadd(d,-7,@.edat)
select @.bdat, @.edat
Results:
-------- --------
2005-03-25 00:00:00.000 2005-04-01 00:00:00.000|||First, whoever designed your database to store dates like that is a freaking idiot. Please go find them and tell them so for me...
...are you back now? Good. Thanks.
Now, convert that to a true datetime value like this:
convert(datetime, cast(@.dateint as char(8)))
...then you can check for entries in the last seven days like this:
where datediff(d, convert(datetime, cast(@.dateint as char(8))), getdate()) < 7|||I would concur blindman! However, this db has been in use for almost 16 years with modifications throughout time and of course lots of turnover in personel - so I'm not able to track down and pummel the individual myself! Only been here for about a month now and this db was handed to me on Monday.
I've been reading up on the CAST, CONVERT just don't have much practice yet. I've used date functions in Oracle, just never came across an int date before?
Thanks!
Tiffanie
Date question
on say the 15th of every month (that part, I know, is easily scheduled) that
will pull the following month in it's entirety? For instance, I want an
automated script to run every month on the 15th that will give me every one
that purchased a specific product on nay day of the following month. Thanks
for you help.
WillieWillie
what do you want to do with the data once you have it? (ie insert into
a talbe)
Also let me make sure i understand what you are looking for;
you want to know for a product that was order a month in the past on
the day you run this (ie jan/15/2006 would get data from Dec/15/2005).
Is that correct?|||Asuming that you want to get a report of items purchased in the PREVIOUS
month executed on the 15th, here is a suggestion:
-- BEGIN SCRIPT
set nocount on
declare @.date datetime
-- Creating a Item Purchased Table
declare @.Items table(Item varchar(50), PurchDate datetime)
-- Populating the tables
insert into @.items
values ('Item1', '12/15/2005')
insert into @.items
values ('Item2', '12/15/2005')
insert into @.items
values ('Item1', '12/18/2005')
insert into @.items
values ('Item3', '12/17/2005')
insert into @.items
values ('Item1', '12/29/2005')
insert into @.items
values ('Item4', '12/15/2005')
insert into @.items
values ('Item1', '01/14/2006')
set @.date = '01/15/2006' -- getdate() on the real script
-- Only executes on the 15th date of the month
if (select DATEPART(dd, @.date)) = 15
begin
select Item
, PurchDate
from @.Items
where PurchDate between DATEADD(day, -30, @.date) and (@.date)
end
set nocount off
except for the table and the insert statements (I just created them as an
example) you can add this script in to run every day, however it will only
execute on the 15th of the month and it will go back 30 days to get a report
of the items purchased and the correspondent dates.
Let me know if it helps.
"Willie Bodger" wrote:
> Is it possible with SQL date functions to have a recurring query that runs
> on say the 15th of every month (that part, I know, is easily scheduled) th
at
> will pull the following month in it's entirety? For instance, I want an
> automated script to run every month on the 15th that will give me every on
e
> that purchased a specific product on nay day of the following month. Thank
s
> for you help.
> Willie
>
>sql
Tuesday, March 27, 2012
date query when date field is char and mmyy format
I have a field which stored date as char datatype in mmyy format.
Acutally, I have inherited this and there is no way I can change the
design of the database.
Now, I needed to run a qeury between two months (from December 2005 to
January 2006).
My query was:
select * from table1 where claim_date between '1205' and 0106'
This did not give me any records whereas, I know there are records for
the month of December (claim_date : 1205)
I understand that 0106 is smaller than 1205 and that is the main reason
that I am not getting any records. Because if I change it to
select * from table1 where claim_date between '0106' and '1205', then
it gives me the results I want. I feel this is not right.
My question is how can I handle this problem in the query, so that I
can really use the date range as claim_date between '1205' and '0106'.
CREATE TABLE [dbo].[table1] (
[Claim_Number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Claim_Date] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Prime_ABC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
Insert table1
select 'D124', '1205', 'A'
union all
select 'D243', '0106', 'A'
Thanks a million in advance.
Best regards,
MamunWithout changing the table structure and storing the right data in the right
format, don't expect this to have sizzling performance.
SELECT * FROM
(
SELECT
*,
claim_date_calc = '20'+RIGHT(claim_date,2)+LEFT(claim_date
,2)+'01'
FROM
table1
WHERE
ISDATE('20'+RIGHT(claim_date,2)+LEFT(cla
im_date,2)+'01') = 1
)
x
WHERE CONVERT(SMALLDATETIME, claim_date_calc) BETWEEN '20051201' AND
'20060101';
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1139340637.499729.6330@.f14g2000cwb.googlegroups.com...
> Hi All,
> I have a field which stored date as char datatype in mmyy format.
> Acutally, I have inherited this and there is no way I can change the
> design of the database.
> Now, I needed to run a qeury between two months (from December 2005 to
> January 2006).
> My query was:
> select * from table1 where claim_date between '1205' and 0106'
> This did not give me any records whereas, I know there are records for
> the month of December (claim_date : 1205)
> I understand that 0106 is smaller than 1205 and that is the main reason
> that I am not getting any records. Because if I change it to
> select * from table1 where claim_date between '0106' and '1205', then
> it gives me the results I want. I feel this is not right.
> My question is how can I handle this problem in the query, so that I
> can really use the date range as claim_date between '1205' and '0106'.
> CREATE TABLE [dbo].[table1] (
> [Claim_Number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Claim_Date] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Prime_ABC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> Insert table1
> select 'D124', '1205', 'A'
> union all
> select 'D243', '0106', 'A'
> Thanks a million in advance.
> Best regards,
> Mamun
>|||where substring(Claim_Date,3,2)+substring(Clai
m_Date,1,2)
between '0512' and '0601'|||Both solutions worked great. I was not looking at the performance but
to get the results.
Thanks a ton to both of you.
best regards,
mamun|||Another approach would be to convert the values for claim_date into honest
datetimes, and compare to other datetime values
SELECT Claim_Number, Claim_Date, Prime_ABC
FROM table1
WHERE
CONVERT(datetime, RIGHT(Claim_Date,2) + LEFT(Claim_Date,2) + '01', 12)
BETWEEN '20051201' AND '20060101'
With all respect to Alexander, using BETWEEN with character datatypes gives
me the heebee jeebees.
"microsoft.public.dotnet.languages.vb" wrote:
> Hi All,
> I have a field which stored date as char datatype in mmyy format.
> Acutally, I have inherited this and there is no way I can change the
> design of the database.
> Now, I needed to run a qeury between two months (from December 2005 to
> January 2006).
> My query was:
> select * from table1 where claim_date between '1205' and 0106'
> This did not give me any records whereas, I know there are records for
> the month of December (claim_date : 1205)
> I understand that 0106 is smaller than 1205 and that is the main reason
> that I am not getting any records. Because if I change it to
> select * from table1 where claim_date between '0106' and '1205', then
> it gives me the results I want. I feel this is not right.
> My question is how can I handle this problem in the query, so that I
> can really use the date range as claim_date between '1205' and '0106'.
> CREATE TABLE [dbo].[table1] (
> [Claim_Number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Claim_Date] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Prime_ABC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> Insert table1
> select 'D124', '1205', 'A'
> union all
> select 'D243', '0106', 'A'
> Thanks a million in advance.
> Best regards,
> Mamun
>
Date Query Using Month Name
I have made a query that does a cross-tab of data. The date data is normal like 02/04/2004, 03/04/2004 etc.
I am using Month Name to group the dates by Month name.
My problem is the query result sorts the month names in alphabetical order, not month order.
I am using the query to generate a graph and the months are not in order.
Is there a way to sort the months by month order not alphabetical order.
Thanks
GoongI usually will have to use the month name for display and the month number for sorting. I'd have to see the query to advise how you might accomplish that.
Terri|||Hi Terri
SELECT { fn MONTHNAME(EnteredDate) } AS Month,
COUNT(CASE WHEN pesttype = 'rats' THEN id ELSE NULL END) AS rats,
COUNT(CASE WHEN pesttype = 'mice' THEN id ELSE NULL END) AS Mice,
COUNT(CASE WHEN pesttype = 'pigeons' THEN id ELSE NULL END) AS Pigeons,
COUNT(CASE WHEN pesttype = 'birds' THEN id ELSE NULL END) AS birds
FROM pestsighting
GROUP BY { fn MONTHNAME(EnteredDate) }
This query is used to present data to build a Stacked Bar Chart.
Thanks
Goong|||I would use this:
SELECT DATENAME(month,EnteredDate) AS Month,
MONTH(EnteredDate) AS MonthNumber,
COUNT(CASE WHEN pesttype = 'rats' THEN id ELSE NULL END) AS rats,
COUNT(CASE WHEN pesttype = 'mice' THEN id ELSE NULL END) AS Mice,
COUNT(CASE WHEN pesttype = 'pigeons' THEN id ELSE NULL END) AS Pigeons,
COUNT(CASE WHEN pesttype = 'birds' THEN id ELSE NULL END) AS birds
FROM pestsighting
GROUP BY DATENAME(month,EnteredDate), MONTH(EnteredDate)
ORDER BY 2
Terri
PS, sounds like a fun project ;-)|||Thanks Terri,
Works Great
Goong
Date query question!
I've a problem with making a query. I need to get out a result where a date
for example 25-02-05 is in a table.
The problem is that the dates in the table are old like 25-02-03. So is
there a sollution where I only need the day and month to get the result?
Thanks,
Fred"news.wanadoo.nl" <fabfreddy@.zonnet.nl> wrote in message
news:421f7fda$0$32252$afc38c87@.news.wanadoo.nl...
> Hi,
> I've a problem with making a query. I need to get out a result where a
> date for example 25-02-05 is in a table.
> The problem is that the dates in the table are old like 25-02-03. So is
> there a sollution where I only need the day and month to get the result?
> Thanks,
> Fred
select *
from dbo.MyTable
where right(convert(char(8), datecol, 112), 4) = '0225'
select *
from dbo.MyTable
where day(datecol) = 25 and month(datecol) = 2
These will return multiple rows, of course, if you have 20030225, 20020225
etc in your table - I don't know if that's what you want or not. Also check
out DATEPART() in Books Online.
Simonsql