embedded code to check parameter

Hi..
i have 2 parameters, start_date and end_date. is it possible to use
embedded code to prompt a message box if start_date is later than end
date ?
so far from what i've read, embedded code are called using the globally
defined 'Code' member. but in my case where i need to check the
parameters, how do i call the embedded code when user click the View
Report button ?
thanksss...The first thing that came to my mind is this:
1) Are you creating this report to run off a .NET webform? If you are,
you can make the check there and bring up the appropriate UI
2) The store procedure/or database call can also check the dates and
then either rerturn results, or one result saying "Error: Begin date is
after the end date" or if thta happens use default paramteres.
3) Also, you can edit report code to check the dates and either
hide/show (using the visible properties) , and display proper error
handling there.|||thanks Sorcerdon..
1) yes, the reports will run on .net webform. but where do i check?
2) the stored procedure will return a dataset.. so if i were to put a
one-line error message, the columns for table referencing the dataset
will be missing and hence caused an error when i preview report. or
have i done it incorrectly?
3) but the stored procedure will also be run if use the visibility
properties, no?
thanks again|||i manage to get the msg box out but without the caption.. i tried the
following code:
System.Windows.Forms.MessageBox.Show("Date Range From must be earlier
or the same as Date Range To","Error","OK","","")
but i received this error:
Overload resolution failed because no accessible 'Show' can be called
without a narrowing conversion:
i checked in msdn website and don't see what's wrong with my syntax...
anyhow, is there a way not to run the query for dataset since the dates
will not return result.
thanks..|||ops... i have another problem... the messagebox won't show when i view
it after deployment.

Email passwords

I am desigining an intranet site using vb.net/asp.net. The sql - users table has a username, password, e-mail address and date field (date is for when the user last updated there password). My login page etc is working fine, but what I want to do is when someone types in the wrong password, to send an email to the address from my users table giving them the correct password. I am not sure how to go about doing this and would appreciate any advice on this.I am desigining an intranet site using vb.net/asp.net. The sql - users table has a username, password, e-mail address and date field (date is for when the user last updated there password). My login page etc is working fine, but what I want to do is when someone types in the wrong password, to send an email to the address from my users table giving them the correct password. I am not sure how to go about doing this and would appreciate any advice on this.
Won't that be a bit dangerous and a security risk?
To E-mail the password when the user typed it wrong?
What if His/Her significant other or a friend or a neighbor takes a peek at the e-mail?
:mad:sql

EM query strange behavior

In the Enterprise Manager query tool, a query (the date entered in the
criteria field as 31.03.05) displays as an SQL statement:
SELECT *
FROM [TABLE]
WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
and yields the expected results.
However, changing query type to
DELETE FROM [SAME TABLE AS ABOVE]
WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
results in the opposite of what is to be expected - all records where
TRANS_DATE < [the entered time] is deleted.
The TRANS_DATE field is of type DATETIME, and I cannot see that there are
any properties either in the field or the table that separates it from all
my other DATETIME fields in any other tables, where this bizarre delete
action does not occur.
I assume this has to do with some configuration of the automatic
timeconversion that happens in the tool itself, but how to change this
conversion setting so as to produce more desired delete results?
And how is it that it only happens with data from one single table'
Thanks for answering.
Message posted via http://www.droptable.comHi
What happens when you run this:
DELETE FROM [SAME TABLE AS ABOVE]
WHERE (TRANS_DATE > '2005-03-31 00:00:00'
Regards
Mike
"Knut Bohn via droptable.com" wrote:

> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
> SELECT *
> FROM [TABLE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> and yields the expected results.
> However, changing query type to
> DELETE FROM [SAME TABLE AS ABOVE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> results in the opposite of what is to be expected - all records where
> TRANS_DATE < [the entered time] is deleted.
> The TRANS_DATE field is of type DATETIME, and I cannot see that there are
> any properties either in the field or the table that separates it from all
> my other DATETIME fields in any other tables, where this bizarre delete
> action does not occur.
> I assume this has to do with some configuration of the automatic
> timeconversion that happens in the tool itself, but how to change this
> conversion setting so as to produce more desired delete results?
> And how is it that it only happens with data from one single table'
> Thanks for answering.
> --
> Message posted via http://www.droptable.com
>|||Knut
I dont uderstand you.
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
You said that the table's column is defined as datetime datatype and data
was entered as 31.03.05 which is thrown the error
--insert into table1 values ('31.03.05')
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated."
"Knut Bohn via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:b10f4c5d93df49c6b3c31bee2a957db0@.SQ
droptable.com...
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
> SELECT *
> FROM [TABLE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> and yields the expected results.
> However, changing query type to
> DELETE FROM [SAME TABLE AS ABOVE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> results in the opposite of what is to be expected - all records where
> TRANS_DATE < [the entered time] is deleted.
> The TRANS_DATE field is of type DATETIME, and I cannot see that there are
> any properties either in the field or the table that separates it from all
> my other DATETIME fields in any other tables, where this bizarre delete
> action does not occur.
> I assume this has to do with some configuration of the automatic
> timeconversion that happens in the tool itself, but how to change this
> conversion setting so as to produce more desired delete results?
> And how is it that it only happens with data from one single table'
> Thanks for answering.
> --
> Message posted via http://www.droptable.com|||Mike;
Same thing - deletes records with TRANS_DATE < entered value.
What's really bizzarre is that there's another datetime column in the
table, and running a delete with criteria filtered against those date
values yields the expected result.
Again, I really can't spot any differences in the respective data
properties of these two columns.
Uri;
I don't think I follow what you're getting at? The SELECT runs fine with
the date criteria entered as 31.03.05, but somehow things go off
differently with no other change thatn replacing SELECT with DELETE.
Message posted via http://www.droptable.com|||Hi
I did some test
CREATE TABLE #Test
(
col DATETIME
)
INSERT INTO #Test VALUES ('20050331')--Proper format to insert datetime
INSERT INTO #Test VALUES ('20050401')
INSERT INTO #Test VALUES ('20050328')
DELETE FROM #Test WHERE col>'20050331'--Deletes 1 row
SELECT* FROM #Test--Everything is ok
DROP TABLE #Test
--Another repro
CREATE TABLE #Test
(
col VARCHAR(30)
)
INSERT INTO #Test VALUES ('31.03.05')--with datetime this insert will be
failed
INSERT INTO #Test VALUES ('28.03.05')--with datetime this insert will be
failed
INSERT INTO #Test VALUES ('01.04.05')--with datetime this insert will be
failed
DELETE FROM #Test WHERE col>CONVERT(DATETIME,'2005-03-31',102)--Error is
thrown
SELECT CONVERT(DATETIME,REPLACE(col,'.',''),102) FROM #Test
--2031-03-05 00:00:00.000
--2028-03-05 00:00:00.000
--2001-04-05 00:00:00.000
--That's why it deletes wrong data
DELETE FROM #Test WHERE CONVERT(DATETIME,REPLACE(col,'.',''),112)
>CONVERT(DATETIME,'2005-03-31',102)
SELECT* FROM #Test
DROP TABLE #Test
"Knut Bohn via droptable.com" <forum@.droptable.com> wrote in message
news:c446a5e8529b4bc49709d20ca6d2518d@.SQ
droptable.com...
> Mike;
> Same thing - deletes records with TRANS_DATE < entered value.
> What's really bizzarre is that there's another datetime column in the
> table, and running a delete with criteria filtered against those date
> values yields the expected result.
> Again, I really can't spot any differences in the respective data
> properties of these two columns.
> Uri;
> I don't think I follow what you're getting at? The SELECT runs fine with
> the date criteria entered as 31.03.05, but somehow things go off
> differently with no other change thatn replacing SELECT with DELETE.
> --
> Message posted via http://www.droptable.com|||Uri,
Thaanks for taking the time to test this. However, I don't think it
explains the criteria oddity that occurs when in the query changing from
SELECT to DELETE?
best,
knut
Message posted via http://www.droptable.com

EM query strange behavior

In the Enterprise Manager query tool, a query (the date entered in the
criteria field as 31.03.05) displays as an SQL statement:
SELECT *
FROM [TABLE]
WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
and yields the expected results.
However, changing query type to
DELETE FROM [SAME TABLE AS ABOVE]
WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
results in the opposite of what is to be expected - all records where
TRANS_DATE < [the entered time] is deleted.
The TRANS_DATE field is of type DATETIME, and I cannot see that there are
any properties either in the field or the table that separates it from all
my other DATETIME fields in any other tables, where this bizarre delete
action does not occur.
I assume this has to do with some configuration of the automatic
timeconversion that happens in the tool itself, but how to change this
conversion setting so as to produce more desired delete results?
And how is it that it only happens with data from one single table?
Thanks for answering.
Message posted via http://www.droptable.com
Hi
What happens when you run this:
DELETE FROM [SAME TABLE AS ABOVE]
WHERE (TRANS_DATE > '2005-03-31 00:00:00'
Regards
Mike
"Knut Bohn via droptable.com" wrote:

> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
> SELECT *
> FROM [TABLE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> and yields the expected results.
> However, changing query type to
> DELETE FROM [SAME TABLE AS ABOVE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> results in the opposite of what is to be expected - all records where
> TRANS_DATE < [the entered time] is deleted.
> The TRANS_DATE field is of type DATETIME, and I cannot see that there are
> any properties either in the field or the table that separates it from all
> my other DATETIME fields in any other tables, where this bizarre delete
> action does not occur.
> I assume this has to do with some configuration of the automatic
> timeconversion that happens in the tool itself, but how to change this
> conversion setting so as to produce more desired delete results?
> And how is it that it only happens with data from one single table?
> Thanks for answering.
> --
> Message posted via http://www.droptable.com
>
|||Knut
I dont uderstand you.
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
You said that the table's column is defined as datetime datatype and data
was entered as 31.03.05 which is thrown the error
--insert into table1 values ('31.03.05')
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated."
"Knut Bohn via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:b10f4c5d93df49c6b3c31bee2a957db0@.droptable.co m...
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
> SELECT *
> FROM [TABLE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> and yields the expected results.
> However, changing query type to
> DELETE FROM [SAME TABLE AS ABOVE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> results in the opposite of what is to be expected - all records where
> TRANS_DATE < [the entered time] is deleted.
> The TRANS_DATE field is of type DATETIME, and I cannot see that there are
> any properties either in the field or the table that separates it from all
> my other DATETIME fields in any other tables, where this bizarre delete
> action does not occur.
> I assume this has to do with some configuration of the automatic
> timeconversion that happens in the tool itself, but how to change this
> conversion setting so as to produce more desired delete results?
> And how is it that it only happens with data from one single table?
> Thanks for answering.
> --
> Message posted via http://www.droptable.com
|||Mike;
Same thing - deletes records with TRANS_DATE < entered value.
What's really bizzarre is that there's another datetime column in the
table, and running a delete with criteria filtered against those date
values yields the expected result.
Again, I really can't spot any differences in the respective data
properties of these two columns.
Uri;
I don't think I follow what you're getting at? The SELECT runs fine with
the date criteria entered as 31.03.05, but somehow things go off
differently with no other change thatn replacing SELECT with DELETE.
Message posted via http://www.droptable.com
|||Hi
I did some test
CREATE TABLE #Test
(
col DATETIME
)
INSERT INTO #Test VALUES ('20050331')--Proper format to insert datetime
INSERT INTO #Test VALUES ('20050401')
INSERT INTO #Test VALUES ('20050328')
DELETE FROM #Test WHERE col>'20050331'--Deletes 1 row
SELECT* FROM #Test--Everything is ok
DROP TABLE #Test
--Another repro
CREATE TABLE #Test
(
col VARCHAR(30)
)
INSERT INTO #Test VALUES ('31.03.05')--with datetime this insert will be
failed
INSERT INTO #Test VALUES ('28.03.05')--with datetime this insert will be
failed
INSERT INTO #Test VALUES ('01.04.05')--with datetime this insert will be
failed
DELETE FROM #Test WHERE col>CONVERT(DATETIME,'2005-03-31',102)--Error is
thrown
SELECT CONVERT(DATETIME,REPLACE(col,'.',''),102) FROM #Test
--2031-03-05 00:00:00.000
--2028-03-05 00:00:00.000
--2001-04-05 00:00:00.000
--That's why it deletes wrong data
DELETE FROM #Test WHERE CONVERT(DATETIME,REPLACE(col,'.',''),112)
>CONVERT(DATETIME,'2005-03-31',102)
SELECT* FROM #Test
DROP TABLE #Test
"Knut Bohn via droptable.com" <forum@.droptable.com> wrote in message
news:c446a5e8529b4bc49709d20ca6d2518d@.droptable.co m...
> Mike;
> Same thing - deletes records with TRANS_DATE < entered value.
> What's really bizzarre is that there's another datetime column in the
> table, and running a delete with criteria filtered against those date
> values yields the expected result.
> Again, I really can't spot any differences in the respective data
> properties of these two columns.
> Uri;
> I don't think I follow what you're getting at? The SELECT runs fine with
> the date criteria entered as 31.03.05, but somehow things go off
> differently with no other change thatn replacing SELECT with DELETE.
> --
> Message posted via http://www.droptable.com
|||Uri,
Thaanks for taking the time to test this. However, I don't think it
explains the criteria oddity that occurs when in the query changing from
SELECT to DELETE?
best,
knut
Message posted via http://www.droptable.com

EM query strange behavior

In the Enterprise Manager query tool, a query (the date entered in the
criteria field as 31.03.05) displays as an SQL statement:
SELECT *
FROM [TABLE]
WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
and yields the expected results.
However, changing query type to
DELETE FROM [SAME TABLE AS ABOVE]
WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
results in the opposite of what is to be expected - all records where
TRANS_DATE < [the entered time] is deleted.
The TRANS_DATE field is of type DATETIME, and I cannot see that there are
any properties either in the field or the table that separates it from all
my other DATETIME fields in any other tables, where this bizarre delete
action does not occur.
I assume this has to do with some configuration of the automatic
timeconversion that happens in the tool itself, but how to change this
conversion setting so as to produce more desired delete results?
And how is it that it only happens with data from one single table'
Thanks for answering.
--
Message posted via http://www.sqlmonster.comHi
What happens when you run this:
DELETE FROM [SAME TABLE AS ABOVE]
WHERE (TRANS_DATE > '2005-03-31 00:00:00'
Regards
Mike
"Knut Bohn via SQLMonster.com" wrote:
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
> SELECT *
> FROM [TABLE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> and yields the expected results.
> However, changing query type to
> DELETE FROM [SAME TABLE AS ABOVE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> results in the opposite of what is to be expected - all records where
> TRANS_DATE < [the entered time] is deleted.
> The TRANS_DATE field is of type DATETIME, and I cannot see that there are
> any properties either in the field or the table that separates it from all
> my other DATETIME fields in any other tables, where this bizarre delete
> action does not occur.
> I assume this has to do with some configuration of the automatic
> timeconversion that happens in the tool itself, but how to change this
> conversion setting so as to produce more desired delete results?
> And how is it that it only happens with data from one single table'
> Thanks for answering.
> --
> Message posted via http://www.sqlmonster.com
>|||Knut
I dont uderstand you.
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
You said that the table's column is defined as datetime datatype and data
was entered as 31.03.05 which is thrown the error
--insert into table1 values ('31.03.05')
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated."
"Knut Bohn via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:b10f4c5d93df49c6b3c31bee2a957db0@.SQLMonster.com...
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
> SELECT *
> FROM [TABLE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> and yields the expected results.
> However, changing query type to
> DELETE FROM [SAME TABLE AS ABOVE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> results in the opposite of what is to be expected - all records where
> TRANS_DATE < [the entered time] is deleted.
> The TRANS_DATE field is of type DATETIME, and I cannot see that there are
> any properties either in the field or the table that separates it from all
> my other DATETIME fields in any other tables, where this bizarre delete
> action does not occur.
> I assume this has to do with some configuration of the automatic
> timeconversion that happens in the tool itself, but how to change this
> conversion setting so as to produce more desired delete results?
> And how is it that it only happens with data from one single table'
> Thanks for answering.
> --
> Message posted via http://www.sqlmonster.com|||Mike;
Same thing - deletes records with TRANS_DATE < entered value.
What's really bizzarre is that there's another datetime column in the
table, and running a delete with criteria filtered against those date
values yields the expected result.
Again, I really can't spot any differences in the respective data
properties of these two columns.
Uri;
I don't think I follow what you're getting at? The SELECT runs fine with
the date criteria entered as 31.03.05, but somehow things go off
differently with no other change thatn replacing SELECT with DELETE.
--
Message posted via http://www.sqlmonster.com|||Hi
I did some test
CREATE TABLE #Test
(
col DATETIME
)
INSERT INTO #Test VALUES ('20050331')--Proper format to insert datetime
INSERT INTO #Test VALUES ('20050401')
INSERT INTO #Test VALUES ('20050328')
DELETE FROM #Test WHERE col>'20050331'--Deletes 1 row
SELECT* FROM #Test--Everything is ok
DROP TABLE #Test
--Another repro
CREATE TABLE #Test
(
col VARCHAR(30)
)
INSERT INTO #Test VALUES ('31.03.05')--with datetime this insert will be
failed
INSERT INTO #Test VALUES ('28.03.05')--with datetime this insert will be
failed
INSERT INTO #Test VALUES ('01.04.05')--with datetime this insert will be
failed
DELETE FROM #Test WHERE col>CONVERT(DATETIME,'2005-03-31',102)--Error is
thrown
SELECT CONVERT(DATETIME,REPLACE(col,'.',''),102) FROM #Test
--2031-03-05 00:00:00.000
--2028-03-05 00:00:00.000
--2001-04-05 00:00:00.000
--That's why it deletes wrong data
DELETE FROM #Test WHERE CONVERT(DATETIME,REPLACE(col,'.',''),112)
>CONVERT(DATETIME,'2005-03-31',102)
SELECT* FROM #Test
DROP TABLE #Test
"Knut Bohn via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c446a5e8529b4bc49709d20ca6d2518d@.SQLMonster.com...
> Mike;
> Same thing - deletes records with TRANS_DATE < entered value.
> What's really bizzarre is that there's another datetime column in the
> table, and running a delete with criteria filtered against those date
> values yields the expected result.
> Again, I really can't spot any differences in the respective data
> properties of these two columns.
> Uri;
> I don't think I follow what you're getting at? The SELECT runs fine with
> the date criteria entered as 31.03.05, but somehow things go off
> differently with no other change thatn replacing SELECT with DELETE.
> --
> Message posted via http://www.sqlmonster.com|||Uri,
Thaanks for taking the time to test this. However, I don't think it
explains the criteria oddity that occurs when in the query changing from
SELECT to DELETE?
best,
knut
--
Message posted via http://www.sqlmonster.com

EM and QA displays different format for a date field

Hi,
I have a datetime field with a value '1/1/02'. EM display it as 1/1/02 But
Query Analyzer displays it as 2002-01-01 00:00:00.000 (I used SELECT * FROM
Table1)
So in Query Analyzer, I can't tell the fomat of it. I just wonder if your
PC does the same.
Thanks.You have a problem under standing of what a data value is and how it is
stored. It is actually just stored as numeric value, denoting offsets from
some time in the reasonably distant past. Format is based on the user. QA
uses a standard value, mostly because when you are using QA the goal is
usually to see ALL of the data.
Enterprise manager is probably using your system settings. When you
actually use the data, you can format it as you wish.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:OqYzX0yUFHA.2136@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a datetime field with a value '1/1/02'. EM display it as 1/1/02
> But Query Analyzer displays it as 2002-01-01 00:00:00.000 (I used SELECT *
> FROM Table1)
> So in Query Analyzer, I can't tell the fomat of it. I just wonder if your
> PC does the same.
> Thanks.
>|||Chrissi,
Different clients display things differently, with the possible
excepting of strings made up of printable ASCII with codes
from 32 to 127.
If you want the datetime displayed in a particular format, I'd
suggest you select CONVERT(varchar(40),yourDatetimeColumn,X
XXX),
where XXXX is the appropriate SQL Server format code (see the
Books Online article CAST and CONVERT).
If you do this, you won't be able to "edit" the datetime values
in Enterprise Manager, but that's not a good idea anyway. Better
to make changes programmatically with UPDATE or INSERT
statements.
Steve Kass
Drew University
Chrissi wrote:

>Hi,
>I have a datetime field with a value '1/1/02'. EM display it as 1/1/02 Bu
t
>Query Analyzer displays it as 2002-01-01 00:00:00.000 (I used SELECT * FROM
>Table1)
>So in Query Analyzer, I can't tell the fomat of it. I just wonder if your
>PC does the same.
>Thanks.
>
>

Efficient Date Query?

Have data stored in two separate fields (Start_Date and End_Date). i.e.:
ID Start_Date End_Date
1 5/5/2006 7/8/2006
2 7/7/2006 7/9/2006
3 6/8/2006 8/5/2006
....
Basically I have to query this based on a MONTH and a YEAR and return all
records where any day in that month will fall between the start/end date.
So if I pass 7/2006 to the query, it will return all of the records. If I
choose 8/2006 it will only return the last one. If I choose 6/2006 it will
return the first and last. Theoretically, I could do a UNION of between
statements for every day a given month, but that is ridiculously ineffecient
and borderline stupid. There has to be a good way of doing this, I just
can't think of it off the top of my head and the guy who's paid to do so
isn't here. Can someon refresh my memory on an easy way to do this?
Thank you!This is a multi-part message in MIME format.
--=_NextPart_000_081B_01C6A751.D81740F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The guy that's paid to do this isn't here either...
This may work for you. (I've selected using the ISO format for date.)
DECLARE @.MyDate varchar(6)
DECLARE @.Durations table
( ID int
, Start_Date datetime
, End_Date datetime
)
INSERT INTO @.Durations Values (1, '5/5/2006','7/8/2006')
INSERT INTO @.Durations Values (2, '7/7/2006','7/9/2006')
INSERT INTO @.Durations Values (3, '6/8/2006','8/5/2006')
SET @.MyDate =3D '200608'
SELECT ID
, Start_Date
, End_Date
FROM @.Durations WHERE @.MyDate BETWEEN ( convert( varchar(6), Start_Date, 112 )) AND ( =convert( varchar(6), End_Date, 112 ))
-- Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"James" <minorkeys@.gmail.com> wrote in message =news:uP8Lwh4pGHA.516@.TK2MSFTNGP05.phx.gbl...
> Have data stored in two separate fields (Start_Date and End_Date). =i.e.:
> > ID Start_Date End_Date
> 1 5/5/2006 7/8/2006
> 2 7/7/2006 7/9/2006
> 3 6/8/2006 8/5/2006
> ....
> > Basically I have to query this based on a MONTH and a YEAR and return =all > records where any day in that month will fall between the start/end =date. > So if I pass 7/2006 to the query, it will return all of the records. =If I > choose 8/2006 it will only return the last one. If I choose 6/2006 it =will > return the first and last. Theoretically, I could do a UNION of =between > statements for every day a given month, but that is ridiculously =ineffecient > and borderline stupid. There has to be a good way of doing this, I =just > can't think of it off the top of my head and the guy who's paid to do =so > isn't here. Can someon refresh my memory on an easy way to do this?
> > Thank you! > >
--=_NextPart_000_081B_01C6A751.D81740F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

The guy that's paid to do this isn't =here either...
This may work for you. (I've selected =using the ISO format for date.)
DECLARE @.MyDate =varchar(6)
DECLARE @.Durations =table ( ID int , Start_Date datetime , End_Date datetime )
INSERT INTO @.Durations Values =(1, '5/5/2006','7/8/2006')INSERT INTO @.Durations Values (2, '7/7/2006','7/9/2006')INSERT INTO @.Durations Values (3, '6/8/2006','8/5/2006')
SET @.MyDate =3D ='200608'
SELECT = ID , Start_Date , End_DateFROM =@.Durations WHERE @.MyDate BETWEEN ( convert( varchar(6), Start_Date, 112 )) AND =( convert( varchar(6), End_Date, 112 ))
-- Arnie Rowland"To be =successful, your heart must accompany your knowledge."
"James" =wrote in message news:uP8Lwh4pGHA.516@.TK2MSFTNGP05.phx.gbl...> =Have data stored in two separate fields (Start_Date and End_Date). i.e.:> => ID Start_Date End_Date> 1 5/5/2006 7/8/2006> 2 7/7/2006 7/9/2006> 3 6/8/2006 8/5/2006> ....> > Basically I have to query this based on a MONTH =and a YEAR and return all > records where any day in that month will =fall between the start/end date. > So if I pass 7/2006 to the query, =it will return all of the records. If I > choose 8/2006 it will =only return the last one. If I choose 6/2006 it will > return the first =and last. Theoretically, I could do a UNION of between > =statements for every day a given month, but that is ridiculously ineffecient > =and borderline stupid. There has to be a good way of doing this, I =just > can't think of it off the top of my head and the guy who's paid =to do so > isn't here. Can someon refresh my memory on an easy =way to do this?> > Thank you! > >

---

--=_NextPart_000_081B_01C6A751.D81740F0--

Efficient Date Query?

Have data stored in two separate fields (Start_Date and End_Date). i.e.:
ID Start_Date End_Date
1 5/5/2006 7/8/2006
2 7/7/2006 7/9/2006
3 6/8/2006 8/5/2006
....
Basically I have to query this based on a MONTH and a YEAR and return all
records where any day in that month will fall between the start/end date.
So if I pass 7/2006 to the query, it will return all of the records. If I
choose 8/2006 it will only return the last one. If I choose 6/2006 it will
return the first and last. Theoretically, I could do a UNION of between
statements for every day a given month, but that is ridiculously ineffecient
and borderline stupid. There has to be a good way of doing this, I just
can't think of it off the top of my head and the guy who's paid to do so
isn't here. Can someon refresh my memory on an easy way to do this?
Thank you!The guy that's paid to do this isn't here either...
This may work for you. (I've selected using the ISO format for date.)
DECLARE @.MyDate varchar(6)
DECLARE @.Durations table
( ID int
, Start_Date datetime
, End_Date datetime
)
INSERT INTO @.Durations Values (1, '5/5/2006','7/8/2006')
INSERT INTO @.Durations Values (2, '7/7/2006','7/9/2006')
INSERT INTO @.Durations Values (3, '6/8/2006','8/5/2006')
SET @.MyDate = '200608'
SELECT
ID
, Start_Date
, End_Date
FROM @.Durations
WHERE @.MyDate BETWEEN ( convert( varchar(6), Start_Date, 112 )) AND ( conver
t( varchar(6), End_Date, 112 ))
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"James" <minorkeys@.gmail.com> wrote in message news:uP8Lwh4pGHA.516@.TK2MSFTNGP05.phx.gbl...[
vbcol=seagreen]
> Have data stored in two separate fields (Start_Date and End_Date). i.e.:
>
> ID Start_Date End_Date
> 1 5/5/2006 7/8/2006
> 2 7/7/2006 7/9/2006
> 3 6/8/2006 8/5/2006
> ....
>
> Basically I have to query this based on a MONTH and a YEAR and return all
> records where any day in that month will fall between the start/end date.
> So if I pass 7/2006 to the query, it will return all of the records. If I
> choose 8/2006 it will only return the last one. If I choose 6/2006 it wil
l
> return the first and last. Theoretically, I could do a UNION of between
> statements for every day a given month, but that is ridiculously ineffecie
nt
> and borderline stupid. There has to be a good way of doing this, I just
> can't think of it off the top of my head and the guy who's paid to do so
> isn't here. Can someon refresh my memory on an easy way to do this?
>
> Thank you!
>
>[/vbcol]

Effective Date selection in SQL

Problem: Selecting the most appropriate row based upon the row's effective date (a user defined field of the row) verses the current date and time.

Platform: Microsoft SQL Server 2000

Details:
In a table of products, each record is a product, or a version of a product. These products or versions of products are "Effective Dated", meaning, a row is activated by the date it becomes effective by (do not be confused by an active row, and an active product - active row is the currently effective row, an active product is a flag set by the user as to define whether the product is active when it's effective date is reached). Example:

Product 1 (version 1)
Effective Date: 6/1/2003 12:00:00 AM
Price: $30
Status: Active

Product 1 (version 2)
Effective Date: 7/11/2003 12:00:00 AM
Price: $20
Status: Active

Product 1 (version 3)
Effective Date: 12/25/2003 12:00:00 AM
Price: $15
Status: Active

Product 1 (version 4)
Effective Date: 1/1/2004 12:00:00 AM
Status: Inactive

In the above case, this SINGLE product has four records in the product table representing different versions. Product 1 version 1 has expired since Product 1 version 2 is effective today (7/11). Product 1 version 3 and version 4, however, have not come about yet due to their effective dating in the future. Product versions 1 through 3 are all active, and the product is no longer available after 1/1/2004, when version 4 becomes effective and the status changes to "Inactive".

The problem I'm experiencing with my SQL is that I cannot differentiate between the version effective currently and the future versions.

My current SQL looks as such:

SELECT sProdName
FROM tblProducts
WHERE NOT EXISTS
(
SELECT P.cProdCode, P.dtEffDate
FROM tblProducts AS P
WHERE
tblProducts.cProdCode = P.cProdCode AND
tblProducts.dtEffDate < P.dtEffDate
)
AND bActive = 1
AND cProdCode = 'somecode'
ORDER BY sProdName

In the above SQL, bActive is a flag that declares this product as an active product (verses inactive) and cProdCode is a code that all versions of the Product share. Ex:

iID: 1
sProdName: Product 1
cProdcode: PROD1
bActive: 1
dtEffDate: 6/1/2003 12:00:00 AM

iID: 2
sProdName: Product 1
cProdcode: PROD1
bActive: 1
dtEffDate: 7/11/2003 12:00:00 AM

iID: 3
sProdName: Product 1
cProdCode: PROD1
bActive: 0
dtEffDate: 1/1/2004 12:00:00 AM

The above SQL is effective in ignoring the expired versions of the product, but returns the current and future versions. I've attempted to massage the SQL to ignore the expired and future versions (leaving me with the single, currently active, version).

Ideally, I'd like to do this without having to create flags for the records that deam them as past, present, future, or having to export versions out into an archive table. This should be able to be determined by strictly SQL, but haven't been able to get the data to return as I'd like it..

In the end, the final result would be a single record of the version of the product which is currently effective, and alertnately, the ability to retrieve a set of records listing all of the currently effective active products.

Any ideas?Now, I should mention that I have a version of this SQL that works, but I suppose my ultimate question is "Is this the most efficient and proper way to do this or is there a better way?"

Here is the SQL that does work, but it nags at me with the use of TOP and ORDER BY. It doesn't seem as though this is the proper elegant solution:

SELECT TOP 1 *
FROM tblProducts AS P
WHERE
P.iProdID IN
(
SELECT iProdID
FROM tblProducts P2
WHERE
P2.dtEffDate < GETDATE()
)
AND P.bActive = 1
AND P.cProdCode = 'somecode'
ORDER BY P.dtEffDAte DESC

If I do not use TOP, it returns every version of a product except the future versions. If I do not use ORDER BY it returns them in an order inconsistant with their effective date. So combined together, the records are sorted most current->least current, then chopped at the top for the most current effective dated product.

Now, this comes into play heavily when we start talking about retrieving the most recent version of all of the products. Sure the above works when getting the most current version of a single product, but (due to the "TOP") does not work properly when attempting to retrieve the most current version of all active products. The above SQL's "TOP" directive is in there to drop off the expired version of the product, and in this case, TOP drops off more then just expired versions of products.. it drops everything except the lucky record at the top of the set.|||i really don't understand your various definitions of active, inactive, flags, effective dates, etc.

your design sounds fairly complicated

however, i did pick up on one thing

you said your sql works, but only for one product at a time, and "does not work properly when attempting to retrieve the most current version of all active products"

okay, i think i can translate your sql so it will work for all products:
select *
from tblProducts AS P
where dtEffDate =
( select max(dtEffDate)
from tblProducts
where cProdCode = P.cProdCode
and dtEffDate < GETDATE()
and bActive = 1
)
and bActive = 1 this is a correlated subquery so it picks the highest date in each group, where the group is defined as all product rows with the same cProdCode (the P inside the subquery is the correlation variable)

let me know if it works

rudy
http://r937.com/|||Hi Rudy,

I apologize for the complexity of the project, but I think you nailed the solution. I've done some cursory testing, and just looking over the SQL, it looks right on. Tonight I'll take some time to do some more testing, but it looks good. I hadn't even thought of using the MAX function. Thanks for your assistance, I appreciate you taking the time to go through all my details (even if they were a bit long and confusing) and coming up with a solid solution.|||It looks good.. I've done some more thorough testing and it seems to work well. Thanks again!|||It looks good.. I've done some more thorough testing and it seems to work well. Thanks again!

Editing the subject line for email subscriptions

Is there a way to get the subject line to report the values of parameters?
For example, "Report Date Range is {start date param} to {end date param}"Hi !
This can easily be achived by adding a reference in the sql string for the
subscription. Example for the parameter Product_Name:
Product_Name AS Subject
In the sample below the YearLastMonth, ForrigeMnd and FileName would be
selectable to the subject field as well..
Sample SQL Select:
SELECT Butikk_id_Parent, Produkt_id, Produkt_navn,
YEAR(DATEADD(m, - 1, GETDATE())) AS YearLastMonth,
ForrigeMnd as Mnd,
'Butikk' + CAST(Butikk_id_Parent AS VARCHAR) + '_' + CAST(Produkt_id AS
VARCHAR) AS FileName
FROM dbo.MAN_RPT_ButikkInfo_V
WHERE (EMail IS NULL)
Regards
Frank
"toolman_2000" wrote:
> Is there a way to get the subject line to report the values of parameters?
> For example, "Report Date Range is {start date param} to {end date param}"|||OK, but how do you get those values into the subject line on the subscription
set up page in Report manager? I want to use a parameter value the same way
the global values are used.
"frankiebody" wrote:
> Hi !
> This can easily be achived by adding a reference in the sql string for the
> subscription. Example for the parameter Product_Name:
> Product_Name AS Subject
> In the sample below the YearLastMonth, ForrigeMnd and FileName would be
> selectable to the subject field as well..
> Sample SQL Select:
> SELECT Butikk_id_Parent, Produkt_id, Produkt_navn,
> YEAR(DATEADD(m, - 1, GETDATE())) AS YearLastMonth,
> ForrigeMnd as Mnd,
> 'Butikk' + CAST(Butikk_id_Parent AS VARCHAR) + '_' + CAST(Produkt_id AS
> VARCHAR) AS FileName
> FROM dbo.MAN_RPT_ButikkInfo_V
> WHERE (EMail IS NULL)
> Regards
> Frank
> "toolman_2000" wrote:
> > Is there a way to get the subject line to report the values of parameters?
> > For example, "Report Date Range is {start date param} to {end date param}"|||What Frank refers to can only be done when using Data Driven Subscriptions.
You don't have access to the actual Parameter values, you would need to have
those values in a database table that you read during the execution of the
Data Driven subscription.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"toolman_2000" <toolman2000@.discussions.microsoft.com> wrote in message
news:D504C83D-1F98-481E-88B8-F9B9CE9FC8F4@.microsoft.com...
> OK, but how do you get those values into the subject line on the
> subscription
> set up page in Report manager? I want to use a parameter value the same
> way
> the global values are used.
> "frankiebody" wrote:
>> Hi !
>> This can easily be achived by adding a reference in the sql string for
>> the
>> subscription. Example for the parameter Product_Name:
>> Product_Name AS Subject
>> In the sample below the YearLastMonth, ForrigeMnd and FileName would be
>> selectable to the subject field as well..
>> Sample SQL Select:
>> SELECT Butikk_id_Parent, Produkt_id, Produkt_navn,
>> YEAR(DATEADD(m, - 1, GETDATE())) AS YearLastMonth,
>> ForrigeMnd as Mnd,
>> 'Butikk' + CAST(Butikk_id_Parent AS VARCHAR) + '_' + CAST(Produkt_id AS
>> VARCHAR) AS FileName
>> FROM dbo.MAN_RPT_ButikkInfo_V
>> WHERE (EMail IS NULL)
>> Regards
>> Frank
>> "toolman_2000" wrote:
>> > Is there a way to get the subject line to report the values of
>> > parameters?
>> > For example, "Report Date Range is {start date param} to {end date
>> > param}"|||it is still not clear how to do so.
let's say i have set a data driven subs. that returns all the email
addresses i want to send to something like:
select email,name,accountName from subscribers
where subscribe=1
how do i insert the field accountName into the subject of this mail?
how do i refer to the recordset field in the subject field?
pls show a simple example.
"Daniel Reib [MSFT]" wrote:
> What Frank refers to can only be done when using Data Driven Subscriptions.
> You don't have access to the actual Parameter values, you would need to have
> those values in a database table that you read during the execution of the
> Data Driven subscription.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "toolman_2000" <toolman2000@.discussions.microsoft.com> wrote in message
> news:D504C83D-1F98-481E-88B8-F9B9CE9FC8F4@.microsoft.com...
> > OK, but how do you get those values into the subject line on the
> > subscription
> > set up page in Report manager? I want to use a parameter value the same
> > way
> > the global values are used.
> >
> > "frankiebody" wrote:
> >
> >> Hi !
> >> This can easily be achived by adding a reference in the sql string for
> >> the
> >> subscription. Example for the parameter Product_Name:
> >> Product_Name AS Subject
> >>
> >> In the sample below the YearLastMonth, ForrigeMnd and FileName would be
> >> selectable to the subject field as well..
> >>
> >> Sample SQL Select:
> >> SELECT Butikk_id_Parent, Produkt_id, Produkt_navn,
> >> YEAR(DATEADD(m, - 1, GETDATE())) AS YearLastMonth,
> >> ForrigeMnd as Mnd,
> >> 'Butikk' + CAST(Butikk_id_Parent AS VARCHAR) + '_' + CAST(Produkt_id AS
> >> VARCHAR) AS FileName
> >> FROM dbo.MAN_RPT_ButikkInfo_V
> >> WHERE (EMail IS NULL)
> >>
> >> Regards
> >> Frank
> >>
> >> "toolman_2000" wrote:
> >>
> >> > Is there a way to get the subject line to report the values of
> >> > parameters?
> >> > For example, "Report Date Range is {start date param} to {end date
> >> > param}"
>
>|||The data driven subscription is the way to do it. You will have to include
the field you are selecting from the table in your subscription query if you
want to display it in the subject line. When you edit the page for "special
delivery extension settings" in the data driven subscription you will want to
change the 'subject' to select "Get the value from the dataabase" click on
the dropdown and select the field you want to display.
Hope this hleps!
golf4fun
"Granov" wrote:
> it is still not clear how to do so.
> let's say i have set a data driven subs. that returns all the email
> addresses i want to send to something like:
> select email,name,accountName from subscribers
> where subscribe=1
> how do i insert the field accountName into the subject of this mail?
> how do i refer to the recordset field in the subject field?
> pls show a simple example.
> "Daniel Reib [MSFT]" wrote:
> > What Frank refers to can only be done when using Data Driven Subscriptions.
> > You don't have access to the actual Parameter values, you would need to have
> > those values in a database table that you read during the execution of the
> > Data Driven subscription.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "toolman_2000" <toolman2000@.discussions.microsoft.com> wrote in message
> > news:D504C83D-1F98-481E-88B8-F9B9CE9FC8F4@.microsoft.com...
> > > OK, but how do you get those values into the subject line on the
> > > subscription
> > > set up page in Report manager? I want to use a parameter value the same
> > > way
> > > the global values are used.
> > >
> > > "frankiebody" wrote:
> > >
> > >> Hi !
> > >> This can easily be achived by adding a reference in the sql string for
> > >> the
> > >> subscription. Example for the parameter Product_Name:
> > >> Product_Name AS Subject
> > >>
> > >> In the sample below the YearLastMonth, ForrigeMnd and FileName would be
> > >> selectable to the subject field as well..
> > >>
> > >> Sample SQL Select:
> > >> SELECT Butikk_id_Parent, Produkt_id, Produkt_navn,
> > >> YEAR(DATEADD(m, - 1, GETDATE())) AS YearLastMonth,
> > >> ForrigeMnd as Mnd,
> > >> 'Butikk' + CAST(Butikk_id_Parent AS VARCHAR) + '_' + CAST(Produkt_id AS
> > >> VARCHAR) AS FileName
> > >> FROM dbo.MAN_RPT_ButikkInfo_V
> > >> WHERE (EMail IS NULL)
> > >>
> > >> Regards
> > >> Frank
> > >>
> > >> "toolman_2000" wrote:
> > >>
> > >> > Is there a way to get the subject line to report the values of
> > >> > parameters?
> > >> > For example, "Report Date Range is {start date param} to {end date
> > >> > param}"
> >
> >
> >