Hi all,
I like to access a production database server from my development database,
I need to count how fast one table in the production table grows, is there
any way to do it without actually getting on the production server? linked
server?
Thanks.Yes you can create a linked server
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database
,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.|||Thanks. Because the company security reseans, I can't use the linked server,
is there a way to work around?
"SQL" wrote:
[vbcol=seagreen]
> Yes you can create a linked server
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
>|||look up OPENROWSET and OPENDATASOURCE in Books on line
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
[vbcol=seagreen]
> Thanks. Because the company security reseans, I can't use the linked serve
r,
> is there a way to work around?
>
> "SQL" wrote:
>|||Thanks for reply.
Even I have sa permission to all sql servers, I still can't have have access
from one server to a different server.
"SQL" wrote:
[vbcol=seagreen]
> look up OPENROWSET and OPENDATASOURCE in Books on line
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
>|||Matthew,
as SQL said:
1. Linked Server
2. OPENROWSET or OPENDATASOURCE - neither requires a linked server to be
created
3. Use EM, QA, OSQL or some other application to connect directly to your
other server.
4. Job or DTS to get the value required to your local db
HTH
Jerry
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:46E45877-8585-4709-AEA8-BDD7886E7FC5@.microsoft.com...[vbcol=seagreen]
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have
> access
> from one server to a different server.
> "SQL" wrote:
>|||Another option is to create an xp_sendmail proc job on the production DB
that will mail the result to yourself whenever you execute this job
"Matthew Z" wrote:
[vbcol=seagreen]
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have acce
ss
> from one server to a different server.
> "SQL" wrote:
>|||Thanks guys, based on your replies,I got a idea, I may try DTS job & send
mail functions. This is not a one time process, it runs every week for
reporting Purpose.
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database
,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.
Saturday, February 25, 2012
Between clause...
Hi all, I need write a stored proc with a clause like "between like xxx AND
like xxx" so, I can't. Thats is, a stored proc that returns values between
two parameters that use wildcards (for instance "LIKE @.date").
Any Ideas. I read all the books and, I encounter information about BETWEEN
usage and LIKE usage, but not how to use BOTH in the same SP.
Thanks an avanceNando_uy wrote:
> Hi all, I need write a stored proc with a clause like "between like xxx AN
D
> like xxx" so, I can't. Thats is, a stored proc that returns values between
> two parameters that use wildcards (for instance "LIKE @.date").
> Any Ideas. I read all the books and, I encounter information about BETWEEN
> usage and LIKE usage, but not how to use BOTH in the same SP.
> Thanks an avance
Depends on what your LIKE clause is and what the desired result is. It
seems to me that "between" two wildcards would only make sense for a
limited number of cases. For example:
CREATE tbl (col VARCHAR(10) NOT NULL, ...);
SELECT col FROM tbl
WHERE col LIKE '[a-c]%';
could be rewritten as:
..
WHERE col >= 'a' AND col < 'd';
but this is obviously different from:
..
WHERE col BETWEEN 'a' AND 'c';
Could you give a better explanation of what you want to achieve please.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Can you post the query. Seems very interesting.|||hi all, the query seems like this:
ALTER PROCEDURE dbo.Prueba_Entre
(@.vI nchar(100),
@.vF nchar(100))
AS SELECT Codigo_TSA, Id_Producto, Nombre_Emp, Ingresos, Egresos
FROM dbo.Vista_Prueba
WHERE (Codigo_TSA BETWEEN @.vI AND @.vF)
I want to substitute the parameters @.vI, @.vF for something like this:
LIKE @.vI AND LIKE @.vI, since I need that the parameters accept wildcards (%)
.
I currently use SQL server 2000 and, for simplicity use ACCESS 2003 to
graphically design the SP.
In other words, I need to retrieve values between two parameters, both MUST
accept wildcards, It's possible?
"Omnibuzz" wrote:
> Can you post the query. Seems very interesting.|||Thanks for the input.
But we need to know the functionality behind using the like operator.
Because from my experience, I would say it is logically wrong in any
situation to use like and between in the following cases
(pseudo code, of course :)
col1 between like '%abc%' and like '%xyz%'
of course if you want it to be compared between something like
'%abc' and '%xyz'
or
'abc%' and 'xyz%'
then you can use something like this in the where condition.
left(col1,3) between 'abc' and 'xyz' -- actually you don't even need the
left operator in this case
or
right(col1,3) between 'abc' and 'xyz'
Boy.. lack of info leads to lots of typing :)
Hope this helps.|||thanks for you time, the case is: I have a product table, I want to retrieve
a range of these by product code, ie: 10xxxx to 25xxx, because I want the
user have the facility to write ONLY the first characters of product code to
establish the range
I it's possible?
Thanks
"Omnibuzz" wrote:
> Thanks for the input.
> But we need to know the functionality behind using the like operator.
> Because from my experience, I would say it is logically wrong in any
> situation to use like and between in the following cases
> (pseudo code, of course :)
> col1 between like '%abc%' and like '%xyz%'
> of course if you want it to be compared between something like
> '%abc' and '%xyz'
> or
> 'abc%' and 'xyz%'
> then you can use something like this in the where condition.
> left(col1,3) between 'abc' and 'xyz' -- actually you don't even need the
> left operator in this case
> or
> right(col1,3) between 'abc' and 'xyz'
> Boy.. lack of info leads to lots of typing :)
> Hope this helps.
>
>|||Is it an integer column or a char column (or does the column hold integer
values only?).
And do they always enter the first 2 digits? or it can be 1 or 3 digits?
"Nando_uy" wrote:
> thanks for you time, the case is: I have a product table, I want to retrie
ve
> a range of these by product code, ie: 10xxxx to 25xxx, because I want the
> user have the facility to write ONLY the first characters of product code
to
> establish the range
> I it's possible?
> Thanks
> "Omnibuzz" wrote:
>|||thanks Omnibuzz, it's a character column and until now I don't decide the
numbers of characters the user input, but say, 2 characters will be fine!!!
Looks better?
"Omnibuzz" wrote:
> Is it an integer column or a char column (or does the column hold integer
> values only?).
> And do they always enter the first 2 digits? or it can be 1 or 3 digits?
>
> "Nando_uy" wrote:
>|||Anyways.. check this out..
create table #temp( a varchar(10))
insert into #temp
select '12ewew'
union all
select '13rere'
union all
select '23dds'
union all
select '26rerere'
union all
select '2454cdfd'
select * from #temp where a between '12' and '25'
Hope this helps.|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Depends on what your LIKE clause is and what the desired result is. It
> seems to me that "between" two wildcards would only make sense for a
> limited number of cases. For example:
> CREATE tbl (col VARCHAR(10) NOT NULL, ...);
> SELECT col FROM tbl
> WHERE col LIKE '[a-c]%';
> could be rewritten as:
> ...
> WHERE col >= 'a' AND col < 'd';
Maybe, but it would not necessarily generate the same result:
CREATE TABLE #tmp (a varchar(19) COLLATE Finnish_Swedish_CS_AS NOT NULL)
go
INSERT #tmp(a) VALUES ('Annichen')
INSERT #tmp(a) VALUES ('avfallskvarn')
INSERT #tmp(a) VALUES ('Beatrice')
INSERT #tmp(a) VALUES ('betongarbetare')
INSERT #tmp(a) VALUES ('Cecilia')
INSERT #tmp(a) VALUES ('citrusfrukt')
INSERT #tmp(a) VALUES ('Daneiella')
INSERT #tmp(a) VALUES ('daggfuktig')
go
SELECT a FROM #tmp WHERE a LIKE '[a-c]%' -- five rows
SELECT a FROM #tmp WHERE a >= 'a' AND a < 'd' -- six rows
go
DROP TABLE #tmp
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
like xxx" so, I can't. Thats is, a stored proc that returns values between
two parameters that use wildcards (for instance "LIKE @.date").
Any Ideas. I read all the books and, I encounter information about BETWEEN
usage and LIKE usage, but not how to use BOTH in the same SP.
Thanks an avanceNando_uy wrote:
> Hi all, I need write a stored proc with a clause like "between like xxx AN
D
> like xxx" so, I can't. Thats is, a stored proc that returns values between
> two parameters that use wildcards (for instance "LIKE @.date").
> Any Ideas. I read all the books and, I encounter information about BETWEEN
> usage and LIKE usage, but not how to use BOTH in the same SP.
> Thanks an avance
Depends on what your LIKE clause is and what the desired result is. It
seems to me that "between" two wildcards would only make sense for a
limited number of cases. For example:
CREATE tbl (col VARCHAR(10) NOT NULL, ...);
SELECT col FROM tbl
WHERE col LIKE '[a-c]%';
could be rewritten as:
..
WHERE col >= 'a' AND col < 'd';
but this is obviously different from:
..
WHERE col BETWEEN 'a' AND 'c';
Could you give a better explanation of what you want to achieve please.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Can you post the query. Seems very interesting.|||hi all, the query seems like this:
ALTER PROCEDURE dbo.Prueba_Entre
(@.vI nchar(100),
@.vF nchar(100))
AS SELECT Codigo_TSA, Id_Producto, Nombre_Emp, Ingresos, Egresos
FROM dbo.Vista_Prueba
WHERE (Codigo_TSA BETWEEN @.vI AND @.vF)
I want to substitute the parameters @.vI, @.vF for something like this:
LIKE @.vI AND LIKE @.vI, since I need that the parameters accept wildcards (%)
.
I currently use SQL server 2000 and, for simplicity use ACCESS 2003 to
graphically design the SP.
In other words, I need to retrieve values between two parameters, both MUST
accept wildcards, It's possible?
"Omnibuzz" wrote:
> Can you post the query. Seems very interesting.|||Thanks for the input.
But we need to know the functionality behind using the like operator.
Because from my experience, I would say it is logically wrong in any
situation to use like and between in the following cases
(pseudo code, of course :)
col1 between like '%abc%' and like '%xyz%'
of course if you want it to be compared between something like
'%abc' and '%xyz'
or
'abc%' and 'xyz%'
then you can use something like this in the where condition.
left(col1,3) between 'abc' and 'xyz' -- actually you don't even need the
left operator in this case
or
right(col1,3) between 'abc' and 'xyz'
Boy.. lack of info leads to lots of typing :)
Hope this helps.|||thanks for you time, the case is: I have a product table, I want to retrieve
a range of these by product code, ie: 10xxxx to 25xxx, because I want the
user have the facility to write ONLY the first characters of product code to
establish the range
I it's possible?
Thanks
"Omnibuzz" wrote:
> Thanks for the input.
> But we need to know the functionality behind using the like operator.
> Because from my experience, I would say it is logically wrong in any
> situation to use like and between in the following cases
> (pseudo code, of course :)
> col1 between like '%abc%' and like '%xyz%'
> of course if you want it to be compared between something like
> '%abc' and '%xyz'
> or
> 'abc%' and 'xyz%'
> then you can use something like this in the where condition.
> left(col1,3) between 'abc' and 'xyz' -- actually you don't even need the
> left operator in this case
> or
> right(col1,3) between 'abc' and 'xyz'
> Boy.. lack of info leads to lots of typing :)
> Hope this helps.
>
>|||Is it an integer column or a char column (or does the column hold integer
values only?).
And do they always enter the first 2 digits? or it can be 1 or 3 digits?
"Nando_uy" wrote:
> thanks for you time, the case is: I have a product table, I want to retrie
ve
> a range of these by product code, ie: 10xxxx to 25xxx, because I want the
> user have the facility to write ONLY the first characters of product code
to
> establish the range
> I it's possible?
> Thanks
> "Omnibuzz" wrote:
>|||thanks Omnibuzz, it's a character column and until now I don't decide the
numbers of characters the user input, but say, 2 characters will be fine!!!
Looks better?
"Omnibuzz" wrote:
> Is it an integer column or a char column (or does the column hold integer
> values only?).
> And do they always enter the first 2 digits? or it can be 1 or 3 digits?
>
> "Nando_uy" wrote:
>|||Anyways.. check this out..
create table #temp( a varchar(10))
insert into #temp
select '12ewew'
union all
select '13rere'
union all
select '23dds'
union all
select '26rerere'
union all
select '2454cdfd'
select * from #temp where a between '12' and '25'
Hope this helps.|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Depends on what your LIKE clause is and what the desired result is. It
> seems to me that "between" two wildcards would only make sense for a
> limited number of cases. For example:
> CREATE tbl (col VARCHAR(10) NOT NULL, ...);
> SELECT col FROM tbl
> WHERE col LIKE '[a-c]%';
> could be rewritten as:
> ...
> WHERE col >= 'a' AND col < 'd';
Maybe, but it would not necessarily generate the same result:
CREATE TABLE #tmp (a varchar(19) COLLATE Finnish_Swedish_CS_AS NOT NULL)
go
INSERT #tmp(a) VALUES ('Annichen')
INSERT #tmp(a) VALUES ('avfallskvarn')
INSERT #tmp(a) VALUES ('Beatrice')
INSERT #tmp(a) VALUES ('betongarbetare')
INSERT #tmp(a) VALUES ('Cecilia')
INSERT #tmp(a) VALUES ('citrusfrukt')
INSERT #tmp(a) VALUES ('Daneiella')
INSERT #tmp(a) VALUES ('daggfuktig')
go
SELECT a FROM #tmp WHERE a LIKE '[a-c]%' -- five rows
SELECT a FROM #tmp WHERE a >= 'a' AND a < 'd' -- six rows
go
DROP TABLE #tmp
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
BETWEEN clause & <= operators
Hi ,
In a stored procedure when retrieving records based on a DATETIME values in WHERE clause - can we use BETWEEN clause or Col <= AND Col >= ?
Please suggest which is the optimised way.
Thanks in Advance,
Hari Haran ArulmozhiThey are both the same. The optimser comverts BETWEEN to >= and <= anyway. Depends on what you prefer to type\ read. I like BETWEEN as I don't have to check if there is a >= to correspond with any <= I find.
HTH|||optimized approach for datetime ranges involving two dates is actually to use something like this --
where datetimecol >= '2006-08-09'
and datetimecol < '2006-08-11'this returns all datetimes for the 9th and the 10th
using BETWEEN you have two choices -- code the upper value as '2006-08-10 23:59:59.999' (clumsy) or code the upper end as '2006-08-11' (and risk getting a row from the 11th at midnight)
In a stored procedure when retrieving records based on a DATETIME values in WHERE clause - can we use BETWEEN clause or Col <= AND Col >= ?
Please suggest which is the optimised way.
Thanks in Advance,
Hari Haran ArulmozhiThey are both the same. The optimser comverts BETWEEN to >= and <= anyway. Depends on what you prefer to type\ read. I like BETWEEN as I don't have to check if there is a >= to correspond with any <= I find.
HTH|||optimized approach for datetime ranges involving two dates is actually to use something like this --
where datetimecol >= '2006-08-09'
and datetimecol < '2006-08-11'this returns all datetimes for the 9th and the 10th
using BETWEEN you have two choices -- code the upper value as '2006-08-10 23:59:59.999' (clumsy) or code the upper end as '2006-08-11' (and risk getting a row from the 11th at midnight)
Between Clause
Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
SouraThis works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.developersdex.com ***|||This should contain all necessary information: http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
--
William Stacey [MVP]|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:
> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
> > Hai,
> >
> > I have a table with datetime type column. I stored the date with time in
> > this column.
> > Now i want to select the records between 10/01/2005 and 10/31/2005.
> >
> > I used 'select * from tablename where columnname between '10/01/2005' and
> > '10/31/2005'' query to select records.
> >
> > But the above query returns upto 10/30/2005.
> >
> > Please advise me.
> >
> > Rgds,
> > Soura
>|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:
> > select *
> > from tablename
> > where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>|||Did you read my article?
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be disastrous for performance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each time.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
>> Soura,
>> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
>> after midnight will not be included in the results.
>> Try:
>> select *
>> from tablename
>> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
>> Rob
>> SouRa wrote:
>> > Hai,
>> >
>> > I have a table with datetime type column. I stored the date with time in
>> > this column.
>> > Now i want to select the records between 10/01/2005 and 10/31/2005.
>> >
>> > I used 'select * from tablename where columnname between '10/01/2005' and
>> > '10/31/2005'' query to select records.
>> >
>> > But the above query returns upto 10/30/2005.
>> >
>> > Please advise me.
>> >
>> > Rgds,
>> > Soura
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
SouraThis works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.developersdex.com ***|||This should contain all necessary information: http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
--
William Stacey [MVP]|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:
> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
> > Hai,
> >
> > I have a table with datetime type column. I stored the date with time in
> > this column.
> > Now i want to select the records between 10/01/2005 and 10/31/2005.
> >
> > I used 'select * from tablename where columnname between '10/01/2005' and
> > '10/31/2005'' query to select records.
> >
> > But the above query returns upto 10/30/2005.
> >
> > Please advise me.
> >
> > Rgds,
> > Soura
>|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:
> > select *
> > from tablename
> > where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>|||Did you read my article?
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be disastrous for performance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each time.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
>> Soura,
>> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
>> after midnight will not be included in the results.
>> Try:
>> select *
>> from tablename
>> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
>> Rob
>> SouRa wrote:
>> > Hai,
>> >
>> > I have a table with datetime type column. I stored the date with time in
>> > this column.
>> > Now i want to select the records between 10/01/2005 and 10/31/2005.
>> >
>> > I used 'select * from tablename where columnname between '10/01/2005' and
>> > '10/31/2005'' query to select records.
>> >
>> > But the above query returns upto 10/30/2005.
>> >
>> > Please advise me.
>> >
>> > Rgds,
>> > Soura
Between Clause
Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
Soura
This works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||This should contain all necessary information: http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura
|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura
|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
William Stacey [MVP]
|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:
> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
>
|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:
> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>
|||Did you read my article?
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be disastrous for performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...[vbcol=seagreen]
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each time.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
Soura
This works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||This should contain all necessary information: http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura
|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura
|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
William Stacey [MVP]
|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:
> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
>
|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:
> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>
|||Did you read my article?
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be disastrous for performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...[vbcol=seagreen]
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each time.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
Between Clause
Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
SouraThis works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||This should contain all necessary information: http://www.karaszi.com/SQLServer/in...fo_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
William Stacey [MVP]|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:
> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
>|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:
> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>|||Did you read my article?
> "select * from table_name where
> convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be dis
astrous for performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...[vbcol=seagreen]
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each tim
e.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
>
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
SouraThis works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||This should contain all necessary information: http://www.karaszi.com/SQLServer/in...fo_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
William Stacey [MVP]|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:
> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
>|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:
> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>|||Did you read my article?
> "select * from table_name where
> convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be dis
astrous for performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...[vbcol=seagreen]
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each tim
e.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
>
BETWEEN A AND F
Hi All.
Intervals on varchar fields.
I just want to pick all customers from my DB who has Customernames starting
with A, b, c, d, e, f. Some kind of BETWEEN A AND F. (Starting letters).
Any ideas.
Even better. I have the query ... WHERE CustomerName LIKE @.CustomerName. Is
there any combinations of %, <, > or any other characters that can give me
all customers between A and F. (E.g...WHERE CustomerName LIKE 'A-F' or
something...
Thanx all
GeirLook up wildcards in the books online; you basically want a variation
of :
WHERE CustomerName Like '[a-f]%'
Stu|||Just what I was looking for. Thanx a lot!
Still learning.....:-)
-gh
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126525507.534946.152760@.f14g2000cwb.googlegroups.com...
> Look up wildcards in the books online; you basically want a variation
> of :
> WHERE CustomerName Like '[a-f]%'
> Stu
>|||Been there :)
Books Online will be your best friend if you're trying to learn the
distinctive characteristics of SQL Server. Of course, if means you
have to know what you're looking for because it's an encyclopedia, not
a textbook.
Stu
Intervals on varchar fields.
I just want to pick all customers from my DB who has Customernames starting
with A, b, c, d, e, f. Some kind of BETWEEN A AND F. (Starting letters).
Any ideas.
Even better. I have the query ... WHERE CustomerName LIKE @.CustomerName. Is
there any combinations of %, <, > or any other characters that can give me
all customers between A and F. (E.g...WHERE CustomerName LIKE 'A-F' or
something...
Thanx all
GeirLook up wildcards in the books online; you basically want a variation
of :
WHERE CustomerName Like '[a-f]%'
Stu|||Just what I was looking for. Thanx a lot!
Still learning.....:-)
-gh
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126525507.534946.152760@.f14g2000cwb.googlegroups.com...
> Look up wildcards in the books online; you basically want a variation
> of :
> WHERE CustomerName Like '[a-f]%'
> Stu
>|||Been there :)
Books Online will be your best friend if you're trying to learn the
distinctive characteristics of SQL Server. Of course, if means you
have to know what you're looking for because it's an encyclopedia, not
a textbook.
Stu
BETWEEN 20/5/2007 AND 30/5/2007 not returning rows.
Hello,
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
SQL Server 2005
I have tried and cannot return any rows either though there are records that should be returned, using the sql below.
Code Snippet
SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN 20/5/2007 AND 30/5/2007
In the database scheduleDate is a dateTime datatype.
In the database I have copied and pasted. 23/05/2007 00:00:00
I tried the following
Code Snippet
SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN '20/5/2007' AND '30/5/2007'
And got an error message:The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Many thanks for any help with this,
Steve
First approach doesn't work because 20/5/2007 interpreted as arithmetical expression with value 0. Then with value converted to datetime as 1/1/1901. Try following code:
Code Snippet
declare @.dt datetime
set @.dt = 20/5/2007
select @.dt
You could rewrite second query with explicit convert:
Code Snippet
SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited')
AND scheduleDate BETWEEN convert('20/5/2007',104) AND convert('30/5/2007',104)
Or use
Code Snippet
SET DATEFORMAT dmy
for setting default dateformat
|||That is because the implicit conversion thinks the date is a US date.Do this:
.....BETWEEN CONVERT(datetime,'20/5/2007',103) AND CONVERT(datetime,'30/5/2007',103)
or
SET DATEFORMAT dmy
SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN '20/5/2007' AND '30/5/2007'
|||
steve_1_rm,
Try using the ISO or ISO8601 format (see function CONVERT in BOL) and SQL Server will interprete correctly the datetime string, no matter the language or setting of dateformat.
SELECT *
FROM Calls
WHERE
clientName IN ('Bankside Engineering Limited')
AND scheduleDate BETWEEN '20070527' AND '20070530'
AMB
|||Hello, Thanks for your, help. but the code above did not return the rows i wanted.I have used the following and with yyyymmdd format SELECT * FROM Calls WHERE [Date] BETWEEN '20070521' AND '20070531' This is the actual value i have in the database and this is what i am trying to get out. 23/05/2007 00:00:00 Many thanks Steve|||What do you get when you issue
Code Snippet
SELECT CONVERT(varchar(10), [Date], 103) as [Date], *
FROM Calls
WHERE [Date] BETWEEN '20070521' AND '20070531'
Between + '%'
helloo
I have this table
Id Code
-- --
1 10101001
2 10101002
3 10102001
4 10102002
5 60101001
6 60101002
7 60102001
8 60102002
9 60201001
10 60201002
11 70101001
12 70101002
I need to query this table by the following
(select id, code from table1
where code between '1' + '%' and '7' +'%')
to get all values of codes between (code starting with 1) and (code starting with 7)
or for example:
(select id, code from table1
where code between '602' + '%' and '7' +'%')
-
Im not getting correct answers or let be specific in the second query im not getting codes starting with 7, im only getting codes >= 602 but less that 7, so 7 is not included...
Any suggestions?
SELECT
ID, Code
FROM
Table1
WHERE
Code => '1%' AND Code =< '7%'
hth
Barry Andrew|||First, you don't need to concatenate the percent sign when using it as a wildcard... With that said, you can't use a wildcard in a between operation...
Try casting your Code column to an integer and using between to compare them as numbers. That is if they are all 8 characters in length, this will work nicely.
.... between 60200000 and 79999999
Phil|||thanks for this reply
but as i told codes staring with 7 are not included?|||
BilalShouman wrote:
thanks for this reply but as i told codes staring with 7 are not included?
Ah, have you tried to see what happens once you switch the 7 for an eight? 
|||the user might not know the total code so he might need
codes from 6 till 7, or from 602 till 7, or from 1 till 701
any ideas?|||
the user might not know the total code so he might need
codes from 6 till 7, or from 602 till 7, or from 1 till 701
any ideas?
EDIT, due to above post, SP is wrong, sorry, im thinking of another right now = )
Barry|||
thanks for this reply but as i told codes staring with 7 are not included?
Ah, have you tried to see what happens once you switch the 7 for an eight?
Stop it!!! :)
This is incorrect advice. Using your method is treating the percent sign as an ascii character, not a wildcard. You can only use the wildcard character (%) in a LIKE statement, which is designed to do pattern matching.
The ascii value for '%' is 37, while the number zero (0) is 48. That's why your code "works" on the left side, but not the right side, and why changing that to an 8 works as well.|||ugh I see what you mean. lol my apologies sir, ill just chop that SP I wrote for him and lets see if we can nail it. 
|||Dave:
what does char(255) stand for, and can i use this query in
dim row() as datarow=dt.select("code >= '601' and code <= '7' + Char(255)")|||
Sorry, Bilal, I deleted my post to hastily. The char (255) is the maximum possible value of a 8-bit character. Therefore, I chose to append this to your high boundary. Is this what you are looking for:
set nocount on
declare @.mock table
( [id] integer,
code varchar (10)
)
insert into @.mock values ( 1, '10101001')
insert into @.mock values ( 2, '10101002')
insert into @.mock values ( 3, '10102001')
insert into @.mock values ( 4, '10102002')
insert into @.Mock values ( 5, '60101001')
insert into @.Mock values ( 6, '60101002')
insert into @.mock values ( 7, '60102001')
insert into @.mock values ( 8, '60102002')
insert into @.mock values ( 9, '60201001')
insert into @.Mock values (10, '60201002')
insert into @.Mock values (11, '70101001')
insert into @.Mock values (12, '70101002')
insert into @.mock values (13, '70201001')
--select * from @.mock
select * from @.mock where code between '1' and '7' + char(255)
-- Output:
-- id code
-- -- -
-- 1 10101001
-- 2 10101002
-- ...
-- 11 70101001
-- 12 70101002
-- 13 70201001
select * from @.mock where code between '6' and '7' + char(255)
-- id code
-- -- -
-- 5 60101001
-- 6 60101002
-- 7 60102001
-- 8 60102002
-- 9 60201001
-- 10 60201002
-- 11 70101001
-- 12 70101002
-- 13 70201001
select * from @.mock where code between '6' and '701' + char(255)
-- id code
-- -- -
-- 5 60101001
-- 6 60101002
-- 7 60102001
-- 8 60102002
-- 9 60201001
-- 10 60201002
-- 11 70101001
-- 12 70101002
|||
Where are u guys?
any suggestions?
|||Ah you beat me to it!Oh well, using Mugambo's spliffy code here is an SP Where you could enter changing values.
CREATE PROCEDURE stp_GetBetweens
@.numLow int,
@.numHi int
AS
SELECT
Id, Code
FROM
MyTable
WHERE
Code Between @.numLow AND @.numHi + char(255)
GO
So in your app, pass it the two values you want it to use and it does the rest.
hth
Barry Andrew|||Read up dude, I think we have this one. Teamwork 
|||
Thank you, Barry. I choked on my original response!
Dave
Between [Enter the Start Date] And [Enter the End Date])
Hi all can you help me please, I need the users to be able to enter in a date for a self generating report. Usually I use the @.Enter_Date but I need them to be able to enter a start date and a end date
But when I run this query SQL gives me an error message Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Enter the Start Date'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Enter the End Date'.
What should use in place of BETWEEN [Enter the Start Date] And [Enter the End Date]??
SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes
FROM [Main Table]
WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date]))
ORDER BY [Main Table].[IR Number]Is it possible for your front end to ask for the inputs? Then you could just supply both dates as parameters to a sproc.|||My front end is an Access 2003 an MDB, I need to change it to a ADP but Jet SQL and MSSQL dont exactly speak the same language|||Sure they do, I use pass-through queries all the time.
I use code to get my parameter values from the user, and then change the
QueryDef.SQL on the fly.
Start a new query, then go to Query -> Sql Specific -> Pass-Through
Then for your SQL
Execute YourSproc "paramvalue1", "paramvalue2"|||YESSSS
SELECT [IR Number], Date, Inspector, Area, Violation, [Violation Type], Loss, [Loss Type], Employee, Action, Notes, [Action Type]
FROM dbo.[Main Table]
WHERE (Date BETWEEN @.StartDate AND @.EndDate)
ORDER BY [IR Number]
I'M SO HAPPY IT WORKS FINALLY HOOOORRAAAAYYYYY
HAPPY DANCE!!!!!!!!!!!!!!!!!!!!!!
But when I run this query SQL gives me an error message Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Enter the Start Date'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Enter the End Date'.
What should use in place of BETWEEN [Enter the Start Date] And [Enter the End Date]??
SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes
FROM [Main Table]
WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date]))
ORDER BY [Main Table].[IR Number]Is it possible for your front end to ask for the inputs? Then you could just supply both dates as parameters to a sproc.|||My front end is an Access 2003 an MDB, I need to change it to a ADP but Jet SQL and MSSQL dont exactly speak the same language|||Sure they do, I use pass-through queries all the time.
I use code to get my parameter values from the user, and then change the
QueryDef.SQL on the fly.
Start a new query, then go to Query -> Sql Specific -> Pass-Through
Then for your SQL
Execute YourSproc "paramvalue1", "paramvalue2"|||YESSSS
SELECT [IR Number], Date, Inspector, Area, Violation, [Violation Type], Loss, [Loss Type], Employee, Action, Notes, [Action Type]
FROM dbo.[Main Table]
WHERE (Date BETWEEN @.StartDate AND @.EndDate)
ORDER BY [IR Number]
I'M SO HAPPY IT WORKS FINALLY HOOOORRAAAAYYYYY
HAPPY DANCE!!!!!!!!!!!!!!!!!!!!!!
between
Hi, can I use between for nvarchar field? can some one explain more detail
please...
fd1 has: 1000, 1000a, 1000b, a1000, b1000, 2000, 2000a, 2000b, b2000.
select * from tb1 where fd1 between '1000' and 2000'Yes, you can use BETWEEN for nvarchar comparisons. "WHERE a BETWEEN x
and y" is exactly the same as saying "WHERE a >= x AND a <= y", so for
an nvarchar column that simply does an alphanumeric comparison based on
the collation the column ("a" in this example) uses.
Your example query should return
1000
1000a
1000b
2000
because those values all satisfy "fd1 >= '1000' and fd1 <= '2000'" using
an alphanumeric comparison.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
js wrote:
>Hi, can I use between for nvarchar field? can some one explain more detail
>please...
>fd1 has: 1000, 1000a, 1000b, a1000, b1000, 2000, 2000a, 2000b, b2000.
>select * from tb1 where fd1 between '1000' and 2000'
>
>|||Thanks Mike...
please...
fd1 has: 1000, 1000a, 1000b, a1000, b1000, 2000, 2000a, 2000b, b2000.
select * from tb1 where fd1 between '1000' and 2000'Yes, you can use BETWEEN for nvarchar comparisons. "WHERE a BETWEEN x
and y" is exactly the same as saying "WHERE a >= x AND a <= y", so for
an nvarchar column that simply does an alphanumeric comparison based on
the collation the column ("a" in this example) uses.
Your example query should return
1000
1000a
1000b
2000
because those values all satisfy "fd1 >= '1000' and fd1 <= '2000'" using
an alphanumeric comparison.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
js wrote:
>Hi, can I use between for nvarchar field? can some one explain more detail
>please...
>fd1 has: 1000, 1000a, 1000b, a1000, b1000, 2000, 2000a, 2000b, b2000.
>select * from tb1 where fd1 between '1000' and 2000'
>
>|||Thanks Mike...
between
hi,
what diff:
select * from mytable where intfieldname between 1 and 5
and
select * from mytable where intfieldname between 5 and 1?
Thanks.X BETWEEN Y AND Z
is equivalent to:
X>=Y AND X<=Z
so your second example will never return any rows.
David Portas
SQL Server MVP
--|||The second will return 0 rows. The smaller value need to be the first value
you list.
(SQL Server allowed both to return rows in some prior version, it was change
d in, I believe, 6.0, to
adhere to the ANSI SQL standard.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"js" <js@.someone@.hotmail.com> wrote in message news:%23itV%2388rFHA.3392@.TK2MSFTNGP11.phx.g
bl...
> hi,
> what diff:
> select * from mytable where intfieldname between 1 and 5
> and
> select * from mytable where intfieldname between 5 and 1?
> Thanks.
>|||can you see it?
select * from t1 where c1 >= 1 and c1 <= 5
select * from t1 where c1 >= 5 and c1 <= 1
AMB
"js" wrote:
> hi,
> what diff:
> select * from mytable where intfieldname between 1 and 5
> and
> select * from mytable where intfieldname between 5 and 1?
> Thanks.
>
>|||The Standard changed too. SQL99 defined a SYMMETRIC / ASYMMETRIC clause
for the BETWEEN predicate. ASYMMETRIC - the old behaviour - is still
the default, but:
X BETWEEN SYMMETRIC Y AND Z
is equivalent to:
((X BETWEEN ASYMMETRIC Y AND Z)
OR (X BETWEEN ASYMMETRIC Z AND Y))
David Portas
SQL Server MVP
--|||Order matters. The left side must be smaller.
My typical suggestion is to avoid between for this and other reasons...
http://www.aspfaq.com/2280
"js" <js@.someone@.hotmail.com> wrote in message
news:%23itV%2388rFHA.3392@.TK2MSFTNGP11.phx.gbl...
> hi,
> what diff:
> select * from mytable where intfieldname between 1 and 5
> and
> select * from mytable where intfieldname between 5 and 1?
> Thanks.
>|||> http://www.aspfaq.com/2280
Quote:
"When you are using other clauses in your query, you also have to
remember to wrap the BETWEEN clause in brackets so that its AND isn't
with other ANDs in the WHERE clause."
Just to be clear about this point. Parenthesizing the BETWEEN predicate
probably aids readability but has no effect on the order of evaluation.
That's because the AND keyword in the BETWEEN predicate has nothing to
do with the Boolean operator of the same name. "x BETWEEN y AND z" is
evaluated as a single expression. I mention this detail because I've
found people get similarly over the predicate "IS NOT NULL",
which is three keywords but a single expression.
David Portas
SQL Server MVP
--|||Yes, the point about evaluation was meant to apply only to the point about ,
not that x = 5 or x between 7 and 12 or x = 4 would be evaludated in some
weird way. The bullet itself was added merely to address readability
concerns, but it does bring up the notion of more complex queries. I'm not
sure how to word it better to turn off the alarm bells that caused your
reply, however...
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1125676773.780264.262550@.g49g2000cwa.googlegroups.com...
> Quote:
> "When you are using other clauses in your query, you also have to
> remember to wrap the BETWEEN clause in brackets so that its AND isn't
> with other ANDs in the WHERE clause."
> Just to be clear about this point. Parenthesizing the BETWEEN predicate
> probably aids readability but has no effect on the order of evaluation.
> That's because the AND keyword in the BETWEEN predicate has nothing to
> do with the Boolean operator of the same name. "x BETWEEN y AND z" is
> evaluated as a single expression. I mention this detail because I've
> found people get similarly over the predicate "IS NOT NULL",
> which is three keywords but a single expression.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks all for the help.|||That's news to me. Thanks David. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1125674797.272562.256010@.g47g2000cwa.googlegroups.com...
> The Standard changed too. SQL99 defined a SYMMETRIC / ASYMMETRIC clause
> for the BETWEEN predicate. ASYMMETRIC - the old behaviour - is still
> the default, but:
> X BETWEEN SYMMETRIC Y AND Z
> is equivalent to:
> ((X BETWEEN ASYMMETRIC Y AND Z)
> OR (X BETWEEN ASYMMETRIC Z AND Y))
> --
> David Portas
> SQL Server MVP
> --
>
what diff:
select * from mytable where intfieldname between 1 and 5
and
select * from mytable where intfieldname between 5 and 1?
Thanks.X BETWEEN Y AND Z
is equivalent to:
X>=Y AND X<=Z
so your second example will never return any rows.
David Portas
SQL Server MVP
--|||The second will return 0 rows. The smaller value need to be the first value
you list.
(SQL Server allowed both to return rows in some prior version, it was change
d in, I believe, 6.0, to
adhere to the ANSI SQL standard.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"js" <js@.someone@.hotmail.com> wrote in message news:%23itV%2388rFHA.3392@.TK2MSFTNGP11.phx.g
bl...
> hi,
> what diff:
> select * from mytable where intfieldname between 1 and 5
> and
> select * from mytable where intfieldname between 5 and 1?
> Thanks.
>|||can you see it?
select * from t1 where c1 >= 1 and c1 <= 5
select * from t1 where c1 >= 5 and c1 <= 1
AMB
"js" wrote:
> hi,
> what diff:
> select * from mytable where intfieldname between 1 and 5
> and
> select * from mytable where intfieldname between 5 and 1?
> Thanks.
>
>|||The Standard changed too. SQL99 defined a SYMMETRIC / ASYMMETRIC clause
for the BETWEEN predicate. ASYMMETRIC - the old behaviour - is still
the default, but:
X BETWEEN SYMMETRIC Y AND Z
is equivalent to:
((X BETWEEN ASYMMETRIC Y AND Z)
OR (X BETWEEN ASYMMETRIC Z AND Y))
David Portas
SQL Server MVP
--|||Order matters. The left side must be smaller.
My typical suggestion is to avoid between for this and other reasons...
http://www.aspfaq.com/2280
"js" <js@.someone@.hotmail.com> wrote in message
news:%23itV%2388rFHA.3392@.TK2MSFTNGP11.phx.gbl...
> hi,
> what diff:
> select * from mytable where intfieldname between 1 and 5
> and
> select * from mytable where intfieldname between 5 and 1?
> Thanks.
>|||> http://www.aspfaq.com/2280
Quote:
"When you are using other clauses in your query, you also have to
remember to wrap the BETWEEN clause in brackets so that its AND isn't
with other ANDs in the WHERE clause."Just to be clear about this point. Parenthesizing the BETWEEN predicate
probably aids readability but has no effect on the order of evaluation.
That's because the AND keyword in the BETWEEN predicate has nothing to
do with the Boolean operator of the same name. "x BETWEEN y AND z" is
evaluated as a single expression. I mention this detail because I've
found people get similarly
over the predicate "IS NOT NULL",which is three keywords but a single expression.
David Portas
SQL Server MVP
--|||Yes, the point about evaluation was meant to apply only to the point about ,
not that x = 5 or x between 7 and 12 or x = 4 would be evaludated in some
weird way. The bullet itself was added merely to address readability
concerns, but it does bring up the notion of more complex queries. I'm not
sure how to word it better to turn off the alarm bells that caused your
reply, however...
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1125676773.780264.262550@.g49g2000cwa.googlegroups.com...
> Quote:
> "When you are using other clauses in your query, you also have to
> remember to wrap the BETWEEN clause in brackets so that its AND isn't
>
with other ANDs in the WHERE clause."> Just to be clear about this point. Parenthesizing the BETWEEN predicate
> probably aids readability but has no effect on the order of evaluation.
> That's because the AND keyword in the BETWEEN predicate has nothing to
> do with the Boolean operator of the same name. "x BETWEEN y AND z" is
> evaluated as a single expression. I mention this detail because I've
> found people get similarly
over the predicate "IS NOT NULL",> which is three keywords but a single expression.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks all for the help.|||That's news to me. Thanks David. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1125674797.272562.256010@.g47g2000cwa.googlegroups.com...
> The Standard changed too. SQL99 defined a SYMMETRIC / ASYMMETRIC clause
> for the BETWEEN predicate. ASYMMETRIC - the old behaviour - is still
> the default, but:
> X BETWEEN SYMMETRIC Y AND Z
> is equivalent to:
> ((X BETWEEN ASYMMETRIC Y AND Z)
> OR (X BETWEEN ASYMMETRIC Z AND Y))
> --
> David Portas
> SQL Server MVP
> --
>
between
SQL Server2000 SP3
Field date is smalldatetime
I have 8 rows with data='06-Aug-2003'
SELECT * FROM TEST
where data between '06-Aug-2003' and '06-Aug-2003'
Results: 0 Rows
SELECT * FROM TEST
where data>='06-Aug-2003' and data<='06-Aug-2003'
Results: 0 Rows
What can I do? (I do not want to use '06-Aug-2003' and '07-Aug-2003')What is the time portion of the field ?
What does this give you
SELECT * FROM TEST
where data between '20030806' and '20030807'
OR
SELECT * FROM TEST
where CONVERT(char(8),data,112) = '20030806'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Dragos Hilbert" <dragos_hilbert@.yahoo.co.uk> wrote in message
news:eMh$KHAXDHA.652@.TK2MSFTNGP10.phx.gbl...
> SQL Server2000 SP3
> Field date is smalldatetime
> I have 8 rows with data='06-Aug-2003'
> SELECT * FROM TEST
> where data between '06-Aug-2003' and '06-Aug-2003'
> Results: 0 Rows
>
> SELECT * FROM TEST
> where data>='06-Aug-2003' and data<='06-Aug-2003'
> Results: 0 Rows
> What can I do? (I do not want to use '06-Aug-2003' and '07-Aug-2003')
>
>|||> SELECT * FROM TEST
> where data>='06-Aug-2003' and data<='06-Aug-2003'
SELECT * FROM test
WHERE data>='20030806' and data<'20030807'
(a) between can be ambiguous, I tend to avoid it for datetime queries.
(b) since datetime columns have time also, the above is converted to
midnight, so you won't likely have rows where "data" is between a day at
midnight and the same day at midnight.
(c) never use SELECT * in production code.
(d) consider changing the column name. "data" is not very descriptive at
all; I would never guess that it is a datetime column...
Field date is smalldatetime
I have 8 rows with data='06-Aug-2003'
SELECT * FROM TEST
where data between '06-Aug-2003' and '06-Aug-2003'
Results: 0 Rows
SELECT * FROM TEST
where data>='06-Aug-2003' and data<='06-Aug-2003'
Results: 0 Rows
What can I do? (I do not want to use '06-Aug-2003' and '07-Aug-2003')What is the time portion of the field ?
What does this give you
SELECT * FROM TEST
where data between '20030806' and '20030807'
OR
SELECT * FROM TEST
where CONVERT(char(8),data,112) = '20030806'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Dragos Hilbert" <dragos_hilbert@.yahoo.co.uk> wrote in message
news:eMh$KHAXDHA.652@.TK2MSFTNGP10.phx.gbl...
> SQL Server2000 SP3
> Field date is smalldatetime
> I have 8 rows with data='06-Aug-2003'
> SELECT * FROM TEST
> where data between '06-Aug-2003' and '06-Aug-2003'
> Results: 0 Rows
>
> SELECT * FROM TEST
> where data>='06-Aug-2003' and data<='06-Aug-2003'
> Results: 0 Rows
> What can I do? (I do not want to use '06-Aug-2003' and '07-Aug-2003')
>
>|||> SELECT * FROM TEST
> where data>='06-Aug-2003' and data<='06-Aug-2003'
SELECT * FROM test
WHERE data>='20030806' and data<'20030807'
(a) between can be ambiguous, I tend to avoid it for datetime queries.
(b) since datetime columns have time also, the above is converted to
midnight, so you won't likely have rows where "data" is between a day at
midnight and the same day at midnight.
(c) never use SELECT * in production code.
(d) consider changing the column name. "data" is not very descriptive at
all; I would never guess that it is a datetime column...
Better way to use BETWEEN Begin and End Dates
/*
Subject: How best to use BETWEEN Begin and End Dates to find out if an
employee
was/is member of any group for a certain date range?
OR (EmployeeGroups.BeginDate >= @.BeginDate AND
EmployeeGroups.BeginDate <= @.EndDate)
OR (EmployeeGroups.EndDate >= @.BeginDate AND EmployeeGroups.EndDate <=
@.EndDate)
)
OR (EmployeeGroups.BeginDate >= @.BeginDate AND
EmployeeGroups.BeginDate <= @.EndDate)
OR (EmployeeGroups.EndDate >= @.BeginDate AND EmployeeGroups.EndDate <=
@.EndDate)
)
(snip)
OR (EmployeeGroups.BeginDate >= @.BeginDate AND
>EmployeeGroups.BeginDate <= @.EndDate)
OR (EmployeeGroups.EndDate >= @.BeginDate AND EmployeeGroups.EndDate <=
>@.EndDate)
)
(snip)
Hi Serge,
Thanks Hugo. I actually drew all the possible intervals and I had
16 possibilities.
Subject: How best to use BETWEEN Begin and End Dates to find out if an
employee
was/is member of any group for a certain date range?
You can copy/paste this whole post in SQL Query Analyzer or Management
Studio and
run it once you've made sure there is no harmful code.
I am working on an existing database where there is code that is using
BETWEEN logic and three different OR conditions to search for a user that
has worked between begin and end date parameters that you search for.
For me the three WHERE conditions with the Begin and End dates are a little
confusing so I would like to know if there's a better/simpler way to write
this.
1- I have groups table with GroupID, Name
2- I have employees table with EmployeeID, LastName, FirstName
3- I have employeegroups table where the EmployeeID has the GroupID he/she
was/is a member of and from what Begin to what End dates.
The employee can never be a member of two groups in any date interval.
The employee always was/is a member of a group from a certain to a certain
date and then the next group he/she is a member of a group begins 1 date
after the previous group membership's end date. Therefore If I worked from
2006-01-01 to 2006-01-31 and then I changed group, well in this database
the
next group dates would begin at 2006-02-01 till an Open Ended default date
of
2009-12-31.
I can also be a member of a group for 1 day: 2006-05-05 to 2006-05-05
Please continue to read below at the bottom.
*/
USE tempdb
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'EmployeeGroups' AND xtype
= 'U')
BEGIN
TRUNCATE TABLE EmployeeGroups
DROP TABLE EmployeeGroups
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Groups' AND xtype = 'U')
BEGIN
TRUNCATE TABLE Groups
DROP TABLE Groups
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Employees' AND xtype =
'U')
BEGIN
TRUNCATE TABLE Employees
DROP TABLE Employees
END
GO
CREATE TABLE dbo.Groups
(
GroupID int NOT NULL,
Name varchar(50) NOT NULL
CONSTRAINT PK_Groups PRIMARY KEY NONCLUSTERED
(
GroupID
)
)
GO
CREATE TABLE dbo.Employees
(
EmployeeID int NOT NULL,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL
CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED
(
EmployeeID
)
)
GO
CREATE TABLE dbo.EmployeeGroups
(
EmployeeID int NOT NULL,
GroupID int NOT NULL,
BeginDate datetime NOT NULL,
EndDate datetime NOT NULL,
CONSTRAINT PK_EmployeeGroups PRIMARY KEY NONCLUSTERED
(
EmployeeID,
GroupID
),
CONSTRAINT FK_EmployeeGroups_Employees FOREIGN KEY
(
EmployeeID
) REFERENCES Employees(EmployeeID),
CONSTRAINT FK_EmployeeGroups_Groups FOREIGN KEY
(
GroupID
) REFERENCES Groups(GroupID)
)
GO
INSERT Groups (GroupID, Name)
SELECT 1, 'Group1' UNION ALL
SELECT 2, 'Group2' UNION ALL
SELECT 3, 'Group3' UNION ALL
SELECT 4, 'Group4'
GO
INSERT Employees (EmployeeID, LastName, FirstName)
SELECT 1, 'Davolio', 'Nancy' UNION ALL
SELECT 2, 'Fuller', 'Andrew' UNION ALL
SELECT 3, 'Leverling', 'Janet' UNION ALL
SELECT 4, 'Peacock', 'Margaret' UNION ALL
SELECT 5, 'Buchanan', 'Steven'
GO
INSERT EmployeeGroups (EmployeeID, GroupID, BeginDate, EndDate)
SELECT 1, 3, '1990-01-01', '2004-10-15' UNION ALL
SELECT 1, 4, '2004-10-16', '2004-10-16' UNION ALL
SELECT 1, 1, '2004-10-17', '2099-12-31' UNION ALL
SELECT 3, 2, '1999-11-15', '2002-02-22' UNION ALL
SELECT 3, 4, '2002-02-23', '2099-12-31' UNION ALL
SELECT 4, 3, '2006-05-17', '2099-12-31'
GO
--SELECT * FROM Groups
--SELECT * FROM Employees
--SELECT * FROM EmployeeGroups
DECLARE @.EmployeeID INTEGER
DECLARE @.BeginDate DATETIME
DECLARE @.EndDate DATETIME
PRINT 'First example of querying...'
SET @.EmployeeID = 1
SET @.BeginDate = 'Sep 18 2005 12:00:00:000AM'
SET @.EndDate = 'Sep 24 2006 12:00:00:000AM'
-- This is the code logic being used in the database I am looking at.
SELECT *
FROM EmployeeGroups
WHERE EmployeeGroups.EmployeeID = @.EmployeeID
AND (
(EmployeeGroups.BeginDate <= @.BeginDate AND EmployeeGroups.EndDate
Quote:
Originally Posted by
>= @.EndDate)
OR (EmployeeGroups.BeginDate >= @.BeginDate AND
EmployeeGroups.BeginDate <= @.EndDate)
OR (EmployeeGroups.EndDate >= @.BeginDate AND EmployeeGroups.EndDate <=
@.EndDate)
)
PRINT 'Second example of querying...'
SET @.EmployeeID = 1
SET @.BeginDate = 'Sep 18 2003 12:00:00:000AM'
SET @.EndDate = 'Oct 16 2004 12:00:00:000AM'
-- This is the code logic being used in the database I am looking at.
SELECT *
FROM EmployeeGroups
WHERE EmployeeGroups.EmployeeID = @.EmployeeID
AND (
(EmployeeGroups.BeginDate <= @.BeginDate AND EmployeeGroups.EndDate
Quote:
Originally Posted by
>= @.EndDate)
OR (EmployeeGroups.BeginDate >= @.BeginDate AND
EmployeeGroups.BeginDate <= @.EndDate)
OR (EmployeeGroups.EndDate >= @.BeginDate AND EmployeeGroups.EndDate <=
@.EndDate)
)
-- For me I would swap the parameters and the EmployeeGroups.BeginDate and
-- EmployeeGroups.EndDate like this because it's easier for me to understand
the code
-- this way.
PRINT 'Third example of querying the same parameters of the second
example...'
SELECT *
FROM EmployeeGroups
WHERE EmployeeGroups.EmployeeID = @.EmployeeID
AND (
(@.BeginDate >= EmployeeGroups.BeginDate AND @.EndDate <=
EmployeeGroups.EndDate)
OR (@.BeginDate <= EmployeeGroups.BeginDate AND @.EndDate >=
EmployeeGroups.BeginDate)
OR (@.BeginDate <= EmployeeGroups.EndDate AND @.EndDate >=
EmployeeGroups.EndDate)
)
/*
I was doing some research on the newsgroup and I saw some similar examples
where Celko and David
Portas where using or discussing about using calendar tables:
http://groups.google.com/group/comp...f580af5913efcce
http://groups.google.com/group/comp...439bd3af4345ac1
First I am not sure if those examples can be applied to my scenario and also
I am not
sure how to use and whether or not I should be using a calendar table in
this case?
My question is there a better/simpler way to write these WHERE conditions or
the
whole statement?
I would appreciate any help on this.
Thank you
*/On Sun, 24 Sep 2006 12:48:20 -0400, serge wrote:
Quote:
Originally Posted by
>/*
>Subject: How best to use BETWEEN Begin and End Dates to find out if an
>employee
>was/is member of any group for a certain date range?
(snip)
Quote:
Originally Posted by
>-- This is the code logic being used in the database I am looking at.
>SELECT *
>FROM EmployeeGroups
>WHERE EmployeeGroups.EmployeeID = @.EmployeeID
AND (
(EmployeeGroups.BeginDate <= @.BeginDate AND EmployeeGroups.EndDate
Quote:
Originally Posted by
= @.EndDate)
OR (EmployeeGroups.BeginDate >= @.BeginDate AND
>EmployeeGroups.BeginDate <= @.EndDate)
OR (EmployeeGroups.EndDate >= @.BeginDate AND EmployeeGroups.EndDate <=
>@.EndDate)
)
(snip)
Quote:
Originally Posted by
>My question is there a better/simpler way to write these WHERE conditions or
>the
>whole statement?
Hi Serge,
First of all: thanks for providing CREATE TABLE and INSERT statements
with your question. That made it very easy to understand your question
and to test my reply before posting it.
Second: Yes, this can be made much simpler. I've struggled with the
"overlappping intervals" question myself once, and suddenly found the
best solution after drawing many sets of two intervals on an axis to
explore the different possibilities of overlap or non-overlap. It turned
out that there are many ways in which two intervals can overlap, but
only two ways in which they can not overlap - if interval A ends before
interval B starts, or if interval A starts after interval B ends. So the
intervals do NOT overlap IF A.end < B.start OR A.start B.end (note:
you must change < and to <= and >=, depending on your definition of
"overlap"). Reverse this to find that the condition for overlapping
intervals is simple IF A.end >= B.Start AND A.start <= B.end
Or, in your query:
SELECT *
FROM EmployeeGroups
WHERE EmployeeID = @.EmployeeID
AND BeginDate <= @.EndDate
AND EndDate >= @.BeginDate
--
Hugo Kornelis, SQL Server MVP|||Second: Yes, this can be made much simpler. I've struggled with the
Quote:
Originally Posted by
"overlappping intervals" question myself once, and suddenly found the
best solution after drawing many sets of two intervals on an axis to
explore the different possibilities of overlap or non-overlap.
Thanks Hugo. I actually drew all the possible intervals and I had
16 possibilities.
In any case your example is much easier to grasp. I'll work on
memorizing the logic so that I'm able to write this again without
reading the explanation.
better way to update then select
since i can't update before i declare the cursor, is there a better way so i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * and then write to the text file? (using Oracle Forms)
if I dont include the updatethen a text file can be created. If I include the update, the column is updated but the text file isnt created.
i'm not sure what to do... any suggestions?
a WHEN-BUTTON-PRESSEd trigger calls this procedure:
PROCEDURE SEND_BY_DATE(fromdate in date, todate in date) IS
CURSOR dateProcess IS
SELECT *
FROM SIR
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
AND SIR_REQUEST_SENT = 'N'
rProcess dateProcess%ROWTYPE;
cOut VARCHAR2(2000);
myfile VARCHAR2(255);
mypath VARCHAR2(255);
N_FILE text_io.file_type;
BEGIN
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate;
COMMIT;
OPEN dateProcess;
myfile := sysdate||'_'||'batch'||fromdate||'to'||todate||'_' ||rProcess.SIR_COMPANY;
--myfile := sysdate||'_'||rProcess.SIR_COMPANY;
mypath := 'C:\request';
N_FILE := TEXT_IO.FOPEN(mypath||'\'||myfile||'.TXT', 'W');
LOOP
FETCH dateProcess INTO rProcess;
EXIT WHEN dateProcess%NOTFOUND;
cOut := rProcess.SIR_TRANS_NO || ';'
|| rProcess.SIR_COMPANY || ';'
|| rProcess.SIR_PROJECT || ';'
|| rProcess.SIR_APPL || ';'
|| rProcess.SIR_BUS_FUN || ';'
|| rProcess.SIR_REPORTED_BY || ';'
|| rProcess.SIR_HANDLED_BY || ';'
|| rProcess.SIR_PHASE || ';'
|| rProcess.SIR_TYPE || ';'
|| rProcess.SIR_CAUSE || ';'
|| rProcess.SIR_CLASSIFICATION || ';'
|| rProcess.SIR_DESCRIPTION || ';'
|| rProcess.SIR_REASON || ';'
|| rProcess.SIR_REMARKS || ';'
|| rProcess.SIR_STATUS || ';'
|| rProcess.SIR_REQUEST_DATE || ';'
|| rProcess.SIR_RECEIVED_DATE || ';'
|| rProcess.SIR_START_DATE || ';'
|| rProcess.SIR_CLOSE_DATE || ';'
|| rProcess.SIR_TARGET_DATE || ';'
|| rProcess.SIR_ESTIMATED_MANHRS || ';'
|| rProcess.SIR_ACTUAL_MANHRS || ';'
|| rProcess.SIR_BILLABLE_MANHRS || ';'
|| rProcess.SIR_ATTACHMENT || ';'
|| rProcess.SIR_REQUEST_SENT || ';'
|| rProcess.SIR_CRE_USERID || ';'
|| rProcess.SIR_CRE_DATE || ';'
|| rProcess.SIR_UPD_USERID || ';'
|| rProcess.SIR_UPD_DATE;
TEXT_IO.PUT_LINE(N_FILE, cOut);
END LOOP BeginLoop;
TEXT_IO.FCLOSE(N_FILE);
CLOSE dateProcess;
EXCEPTION
WHEN OTHERS THEN
IF dateProcess%ISOPEN THEN
CLOSE dateProcess;
END IF;
END;I'm no expert on Oracle Forms but have you tried to create file before commit not after. Do the update, select data and create file, commit. You are in the same transaction, so you will be able to see your newly update data.
Hope it helps.
Originally posted by alram
since i can't update before i declare the cursor, is there a better way so i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * and then write to the text file? (using Oracle Forms)
if I dont include the updatethen a text file can be created. If I include the update, the column is updated but the text file isnt created.
i'm not sure what to do... any suggestions?
a WHEN-BUTTON-PRESSEd trigger calls this procedure:
PROCEDURE SEND_BY_DATE(fromdate in date, todate in date) IS
CURSOR dateProcess IS
SELECT *
FROM SIR
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
AND SIR_REQUEST_SENT = 'N'
rProcess dateProcess%ROWTYPE;
cOut VARCHAR2(2000);
myfile VARCHAR2(255);
mypath VARCHAR2(255);
N_FILE text_io.file_type;
BEGIN
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate;
COMMIT;
OPEN dateProcess;
myfile := sysdate||'_'||'batch'||fromdate||'to'||todate||'_' ||rProcess.SIR_COMPANY;
--myfile := sysdate||'_'||rProcess.SIR_COMPANY;
mypath := 'C:\request';
N_FILE := TEXT_IO.FOPEN(mypath||'\'||myfile||'.TXT', 'W');
LOOP
FETCH dateProcess INTO rProcess;
EXIT WHEN dateProcess%NOTFOUND;
cOut := rProcess.SIR_TRANS_NO || ';'
|| rProcess.SIR_COMPANY || ';'
|| rProcess.SIR_PROJECT || ';'
|| rProcess.SIR_APPL || ';'
|| rProcess.SIR_BUS_FUN || ';'
|| rProcess.SIR_REPORTED_BY || ';'
|| rProcess.SIR_HANDLED_BY || ';'
|| rProcess.SIR_PHASE || ';'
|| rProcess.SIR_TYPE || ';'
|| rProcess.SIR_CAUSE || ';'
|| rProcess.SIR_CLASSIFICATION || ';'
|| rProcess.SIR_DESCRIPTION || ';'
|| rProcess.SIR_REASON || ';'
|| rProcess.SIR_REMARKS || ';'
|| rProcess.SIR_STATUS || ';'
|| rProcess.SIR_REQUEST_DATE || ';'
|| rProcess.SIR_RECEIVED_DATE || ';'
|| rProcess.SIR_START_DATE || ';'
|| rProcess.SIR_CLOSE_DATE || ';'
|| rProcess.SIR_TARGET_DATE || ';'
|| rProcess.SIR_ESTIMATED_MANHRS || ';'
|| rProcess.SIR_ACTUAL_MANHRS || ';'
|| rProcess.SIR_BILLABLE_MANHRS || ';'
|| rProcess.SIR_ATTACHMENT || ';'
|| rProcess.SIR_REQUEST_SENT || ';'
|| rProcess.SIR_CRE_USERID || ';'
|| rProcess.SIR_CRE_DATE || ';'
|| rProcess.SIR_UPD_USERID || ';'
|| rProcess.SIR_UPD_DATE;
TEXT_IO.PUT_LINE(N_FILE, cOut);
END LOOP BeginLoop;
TEXT_IO.FCLOSE(N_FILE);
CLOSE dateProcess;
EXCEPTION
WHEN OTHERS THEN
IF dateProcess%ISOPEN THEN
CLOSE dateProcess;
END IF;
END;|||Some alternatives:
1) You could create the file BEFORE you do the update. The problem you have is that if someone else inserts and commits another record into the table between your opening the cursor and doing the update, then you could end up updating some records that you didn't actually put in the file. This can be overcome using "Alter session set isolation_level=serializable" before opening the cursor.
2) You could do the update first as you do now, but in the update also set a new column e.g. batch_id to identify the records you updated. Then use this batch_id in the cursor:
DECLARE
v_batch_id INTEGER;
CURSOR dateProcess( p_batch_id INTEGER ) IS
SELECT *
FROM SIR
WHERE batch_id = p_batch_id;
BEGIN
SELECT batch_seq.NEXTVAL INTO v_batch_id FROM DUAL;
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y',
BATCH_ID = v_batch_id
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate;
COMMIT;
FOR rec IN dateProcess( v_batch_id )
LOOP
-- Output record to file
...
END LOOP;
...
3) You could read and update them at the same time:
PROCEDURE ... IS
CURSOR dateProcess IS
SELECT *
FROM SIR
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
AND SIR_REQUEST_SENT = 'N'
FOR UPDATE OF SIR_REQUEST_SENT;
...
BEGIN
FOR rec IN dateProcess LOOP
-- Output record to file
...
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE CURRENT OF dateProcess;
END LOOP;
...
END;|||Thank you for the replies! Very helpful!
sir andrewst,
I was just wondering, is there a way to update first without creating the batch i.d. column?|||Originally posted by alram
Thank you for the replies! Very helpful!
sir andrewst,
I was just wondering, is there a way to update first without creating the batch i.d. column?
Yes, if you really want to you can do this:
declare
cursor c is select * from t where status='N';
r c%ROWTYPE;
begin
open c;
update t set status='Y';
loop
fetch c into r;
exit when c%NOTFOUND;
dbms_output.put_line('=='||r.id);
end loop;
close c;
end;
The key point is to OPEN the cursor BEFORE doing the UPDATE.
You should still use "Alter session set isolation_level=serializable" before opening the cursor to avoid possibility of updating a different set of records to the set selected.|||sir andrewst, thanks again for your help!!|||Originally posted by andrewst
You should still use "Alter session set isolation_level=serializable" before opening the cursor to avoid possibility of updating a different set of records to the set selected.
sir andrewst, i can update successfully, but i tried to do the alter session and Oracle Forms does not recognize the isolation_level. It only recognizes the ff:
nls_language
nls_territory
nls_date_format
nls_date_language
nls_numeric_characters
nls_iso_currency
nls_iso_currency
nls_sort
nls_calendar
i've been looking for other commands that i can use to ensure serializability but no such luck so far.|||If you put spaces arounf equal sign this command will be recognised:
alter session set isolation_level = serializable;
Originally posted by alram
sir andrewst, i can update successfully, but i tried to do the alter session and Oracle Forms does not recognize the isolation_level. It only recognizes the ff:
nls_language
nls_territory
nls_date_format
nls_date_language
nls_numeric_characters
nls_iso_currency
nls_iso_currency
nls_sort
nls_calendar
i've been looking for other commands that i can use to ensure serializability but no such luck so far.|||Originally posted by sjacek
If you put spaces arounf equal sign this command will be recognised:
alter session set isolation_level = serializable;
i gave that a try too, before opening the cursor but the error that comes out during compilation is this:
Encountered the symbol 'alter' when expecting one of the following:
begin declare end exception exit for goto if....(etc.)|||You cannot use 'alter session' directly in PL/SQL. Use native dynamic sql:
execute immediate 'alter session set isolation_level = serializable';
This should work.
Originally posted by alram
i gave that a try too, before opening the cursor but the error that comes out during compilation is this:
Encountered the symbol 'alter' when expecting one of the following:
begin declare end exception exit for goto if....(etc.)|||Originally posted by sjacek
You cannot use 'alter session' directly in PL/SQL. Use native dynamic sql:
execute immediate 'alter session set isolation_level = serializable';
This should work.
sir, i tried just that. but i still encountered an error:
Encountered the symbol "IMMEDIATE" when expecting one of the ff:
:= . [ @. % ;
The symbol ".= was inserted before "IMMEDIATE" to continue.
what am i doing wrong?|||i have this problem: i need to check if the SIR_REQUEST_SENT flag is 'Y'. if it is, then the text/dat file should not be created anymore.
my question is, is this statement possible and where should i put it?
IF rProcess.SIR_REQUEST_SENT = 'N' THEN
-- retrieve the records
-- create the file
ELSE
CLOSE dateProcess;
here is my code:
-------
PROCEDURE SEND_BY_DATE(fromdate in date, todate in date) IS
CURSOR dateProcess IS
SELECT *
FROM SIR
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
AND SIR_REQUEST_SENT = 'N'
ORDER BY SIR_TRANS_NO;
rProcess dateProcess%ROWTYPE;
cOut LONG;
myfile VARCHAR2(255);
mypath VARCHAR2(255);
N_FILE text_io.file_type;
sir_columns VARCHAR2(2000) := 'SIR_TRANS_NO,SIR_COMPANY,SIR_PROJECT,SIR_APPL,SIR _BUS_FUN,SIR_REPORTED_BY,
SIR_TYPE,SIR_CLASSIFICATION,SIR_DSCRIPTION,SIR_REA SON,
SIR_REQUEST_DATE,SIR_ATTACHMENT,SIR_REQUEST_SENT,
SIR_CRE_USERID,SIR_CRE_DATE';
BEGIN
OPEN dateProcess;
myfile := sysdate||'_'||'from'||'_'||fromdate||'_'||'to'||'_ '||todate||'_'||rProcess.SIR_COMPANY;
mypath := 'C:\request';
N_FILE := TEXT_IO.FOPEN(mypath||'\'||myfile||'.dat', 'W');
LOOP
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate;
:SYSTEM.MESSAGE_LEVEL := 5;
COMMIT;
:SYSTEM.MESSAGE_LEVEL := 0;
FETCH dateProcess INTO rProcess;
EXIT WHEN dateProcess%NOTFOUND;
cOut := rProcess.SIR_TRANS_NO || ';'
|| rProcess.SIR_COMPANY || ';'
|| rProcess.SIR_PROJECT || ';'
|| rProcess.SIR_APPL || ';'
|| rProcess.SIR_BUS_FUN || ';'
|| rProcess.SIR_REPORTED_BY || ';'
|| rProcess.SIR_TYPE || ';'
|| rProcess.SIR_CLASSIFICATION || ';'
|| rProcess.SIR_DESCRIPTION || ';'
|| rProcess.SIR_REASON || ';'
|| rProcess.SIR_REQUEST_DATE || ';'
|| rProcess.SIR_ATTACHMENT || ';'
|| rProcess.SIR_REQUEST_SENT || ';'
|| rProcess.SIR_CRE_USERID || ';'
|| rProcess.SIR_CRE_DATE || ';';
TEXT_IO.PUT_LINE(N_FILE, cOut);
END LOOP BeginLoop;
TEXT_IO.FCLOSE(N_FILE);
CLOSE dateProcess;
-- CREATE A CONTROL FILE
n_file := TEXT_IO.FOPEN(mypath||'\'||myfile||'.ctl','W');
-- INSERT THE TEXT ENTRY TO THE CONTROL FILE
TEXT_IO.PUT_LINE( N_FILE ,'LOAD DATA' );
TEXT_IO.PUT_LINE( N_FILE ,'INFILE '||''''||myfile||'''');
TEXT_IO.PUT_LINE( N_FILE ,'APPEND'||' INTO TABLE '||'SIR');
TEXT_IO.PUT_LINE( N_FILE ,'FIELDS TERMINATED BY '';'' OPTIONALLY ENCLOSED BY ''"''
TRAILING NULLCOLS
('||sir_columns||' )
');
if text_io.is_open( n_file ) then
TEXT_IO.FCLOSE(N_FILE);
end if;
EXCEPTION
WHEN OTHERS THEN
IF dateProcess%ISOPEN THEN
CLOSE dateProcess;
END IF;
END;
------
i have tried putting the IF statement right after i open the cursor, but even if there are records with SIR_REQUEST_SENT = 'N', the file is not created anymore even though it should. i figure that it is only checking the first record.
any help would be appreciated! thank you in advance!|||I'm not following your requirement here. You say "i need to check if the SIR_REQUEST_SENT flag is 'Y'". What - on any record? On one particular record? On ALL records?
Guessing that you probably mean: if ALL records have the flag='Y' then there is no need to create a file. In that case the logic should be:
FOR rProcess IN dateProcess LOOP
IF NOT TEXT_IO.ISOPEN(n_file) THEN
-- Open the file
n_file := TEXT_IO.FOPEN(mypath||'\'||myfile||'.dat', 'W');
END IF;
-- Output record to file
...
END LOOP;
-- If file was opened, then complete process
IF v_file_opened THEN
TEXT_IO.FCLOSE(n_file);
-- Update records
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
AND SIR_REQUEST_SENT = 'N';
-- Create control file
...
--
END IF;|||Originally posted by andrewst
I'm not following your requirement here. You say "i need to check if the SIR_REQUEST_SENT flag is 'Y'". What - on any record? On one particular record? On ALL records?
Guessing that you probably mean: if ALL records have the flag='Y' then there is no need to create a file. In that case the logic should be:
sir andrewst, sorry about that..
to make things clearer, these are my steps:
- Input a from_date and to_date.
- check for records that have SIR_REQUEST_SENT = 'N' that fall between the from_date and to_date. If there are records found, then update the SIR_REQUEST_SENT flag of the found records to 'Y' and create the text file.
- If the SIR_REQUEST_SENT flag is 'Y' for all records between the 2 dates, then I should not create the text file at all.
again, thank you for your help! i'm really appreciate it!|||Originally posted by alram
sir andrewst, sorry about that..
to make things clearer, these are my steps:
- Input a from_date and to_date.
- check for records that have SIR_REQUEST_SENT = 'N' that fall between the from_date and to_date. If there are records found, then update the SIR_REQUEST_SENT flag of the found records to 'Y' and create the text file.
- If the SIR_REQUEST_SENT flag is 'Y' for all records between the 2 dates, then I should not create the text file at all.
again, thank you for your help! i'm really appreciate it!
OK, in that case the logic I showed should work for you.
if I dont include the updatethen a text file can be created. If I include the update, the column is updated but the text file isnt created.
i'm not sure what to do... any suggestions?
a WHEN-BUTTON-PRESSEd trigger calls this procedure:
PROCEDURE SEND_BY_DATE(fromdate in date, todate in date) IS
CURSOR dateProcess IS
SELECT *
FROM SIR
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
AND SIR_REQUEST_SENT = 'N'
rProcess dateProcess%ROWTYPE;
cOut VARCHAR2(2000);
myfile VARCHAR2(255);
mypath VARCHAR2(255);
N_FILE text_io.file_type;
BEGIN
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate;
COMMIT;
OPEN dateProcess;
myfile := sysdate||'_'||'batch'||fromdate||'to'||todate||'_' ||rProcess.SIR_COMPANY;
--myfile := sysdate||'_'||rProcess.SIR_COMPANY;
mypath := 'C:\request';
N_FILE := TEXT_IO.FOPEN(mypath||'\'||myfile||'.TXT', 'W');
LOOP
FETCH dateProcess INTO rProcess;
EXIT WHEN dateProcess%NOTFOUND;
cOut := rProcess.SIR_TRANS_NO || ';'
|| rProcess.SIR_COMPANY || ';'
|| rProcess.SIR_PROJECT || ';'
|| rProcess.SIR_APPL || ';'
|| rProcess.SIR_BUS_FUN || ';'
|| rProcess.SIR_REPORTED_BY || ';'
|| rProcess.SIR_HANDLED_BY || ';'
|| rProcess.SIR_PHASE || ';'
|| rProcess.SIR_TYPE || ';'
|| rProcess.SIR_CAUSE || ';'
|| rProcess.SIR_CLASSIFICATION || ';'
|| rProcess.SIR_DESCRIPTION || ';'
|| rProcess.SIR_REASON || ';'
|| rProcess.SIR_REMARKS || ';'
|| rProcess.SIR_STATUS || ';'
|| rProcess.SIR_REQUEST_DATE || ';'
|| rProcess.SIR_RECEIVED_DATE || ';'
|| rProcess.SIR_START_DATE || ';'
|| rProcess.SIR_CLOSE_DATE || ';'
|| rProcess.SIR_TARGET_DATE || ';'
|| rProcess.SIR_ESTIMATED_MANHRS || ';'
|| rProcess.SIR_ACTUAL_MANHRS || ';'
|| rProcess.SIR_BILLABLE_MANHRS || ';'
|| rProcess.SIR_ATTACHMENT || ';'
|| rProcess.SIR_REQUEST_SENT || ';'
|| rProcess.SIR_CRE_USERID || ';'
|| rProcess.SIR_CRE_DATE || ';'
|| rProcess.SIR_UPD_USERID || ';'
|| rProcess.SIR_UPD_DATE;
TEXT_IO.PUT_LINE(N_FILE, cOut);
END LOOP BeginLoop;
TEXT_IO.FCLOSE(N_FILE);
CLOSE dateProcess;
EXCEPTION
WHEN OTHERS THEN
IF dateProcess%ISOPEN THEN
CLOSE dateProcess;
END IF;
END;I'm no expert on Oracle Forms but have you tried to create file before commit not after. Do the update, select data and create file, commit. You are in the same transaction, so you will be able to see your newly update data.
Hope it helps.
Originally posted by alram
since i can't update before i declare the cursor, is there a better way so i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * and then write to the text file? (using Oracle Forms)
if I dont include the updatethen a text file can be created. If I include the update, the column is updated but the text file isnt created.
i'm not sure what to do... any suggestions?
a WHEN-BUTTON-PRESSEd trigger calls this procedure:
PROCEDURE SEND_BY_DATE(fromdate in date, todate in date) IS
CURSOR dateProcess IS
SELECT *
FROM SIR
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
AND SIR_REQUEST_SENT = 'N'
rProcess dateProcess%ROWTYPE;
cOut VARCHAR2(2000);
myfile VARCHAR2(255);
mypath VARCHAR2(255);
N_FILE text_io.file_type;
BEGIN
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate;
COMMIT;
OPEN dateProcess;
myfile := sysdate||'_'||'batch'||fromdate||'to'||todate||'_' ||rProcess.SIR_COMPANY;
--myfile := sysdate||'_'||rProcess.SIR_COMPANY;
mypath := 'C:\request';
N_FILE := TEXT_IO.FOPEN(mypath||'\'||myfile||'.TXT', 'W');
LOOP
FETCH dateProcess INTO rProcess;
EXIT WHEN dateProcess%NOTFOUND;
cOut := rProcess.SIR_TRANS_NO || ';'
|| rProcess.SIR_COMPANY || ';'
|| rProcess.SIR_PROJECT || ';'
|| rProcess.SIR_APPL || ';'
|| rProcess.SIR_BUS_FUN || ';'
|| rProcess.SIR_REPORTED_BY || ';'
|| rProcess.SIR_HANDLED_BY || ';'
|| rProcess.SIR_PHASE || ';'
|| rProcess.SIR_TYPE || ';'
|| rProcess.SIR_CAUSE || ';'
|| rProcess.SIR_CLASSIFICATION || ';'
|| rProcess.SIR_DESCRIPTION || ';'
|| rProcess.SIR_REASON || ';'
|| rProcess.SIR_REMARKS || ';'
|| rProcess.SIR_STATUS || ';'
|| rProcess.SIR_REQUEST_DATE || ';'
|| rProcess.SIR_RECEIVED_DATE || ';'
|| rProcess.SIR_START_DATE || ';'
|| rProcess.SIR_CLOSE_DATE || ';'
|| rProcess.SIR_TARGET_DATE || ';'
|| rProcess.SIR_ESTIMATED_MANHRS || ';'
|| rProcess.SIR_ACTUAL_MANHRS || ';'
|| rProcess.SIR_BILLABLE_MANHRS || ';'
|| rProcess.SIR_ATTACHMENT || ';'
|| rProcess.SIR_REQUEST_SENT || ';'
|| rProcess.SIR_CRE_USERID || ';'
|| rProcess.SIR_CRE_DATE || ';'
|| rProcess.SIR_UPD_USERID || ';'
|| rProcess.SIR_UPD_DATE;
TEXT_IO.PUT_LINE(N_FILE, cOut);
END LOOP BeginLoop;
TEXT_IO.FCLOSE(N_FILE);
CLOSE dateProcess;
EXCEPTION
WHEN OTHERS THEN
IF dateProcess%ISOPEN THEN
CLOSE dateProcess;
END IF;
END;|||Some alternatives:
1) You could create the file BEFORE you do the update. The problem you have is that if someone else inserts and commits another record into the table between your opening the cursor and doing the update, then you could end up updating some records that you didn't actually put in the file. This can be overcome using "Alter session set isolation_level=serializable" before opening the cursor.
2) You could do the update first as you do now, but in the update also set a new column e.g. batch_id to identify the records you updated. Then use this batch_id in the cursor:
DECLARE
v_batch_id INTEGER;
CURSOR dateProcess( p_batch_id INTEGER ) IS
SELECT *
FROM SIR
WHERE batch_id = p_batch_id;
BEGIN
SELECT batch_seq.NEXTVAL INTO v_batch_id FROM DUAL;
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y',
BATCH_ID = v_batch_id
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate;
COMMIT;
FOR rec IN dateProcess( v_batch_id )
LOOP
-- Output record to file
...
END LOOP;
...
3) You could read and update them at the same time:
PROCEDURE ... IS
CURSOR dateProcess IS
SELECT *
FROM SIR
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
AND SIR_REQUEST_SENT = 'N'
FOR UPDATE OF SIR_REQUEST_SENT;
...
BEGIN
FOR rec IN dateProcess LOOP
-- Output record to file
...
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE CURRENT OF dateProcess;
END LOOP;
...
END;|||Thank you for the replies! Very helpful!
sir andrewst,
I was just wondering, is there a way to update first without creating the batch i.d. column?|||Originally posted by alram
Thank you for the replies! Very helpful!
sir andrewst,
I was just wondering, is there a way to update first without creating the batch i.d. column?
Yes, if you really want to you can do this:
declare
cursor c is select * from t where status='N';
r c%ROWTYPE;
begin
open c;
update t set status='Y';
loop
fetch c into r;
exit when c%NOTFOUND;
dbms_output.put_line('=='||r.id);
end loop;
close c;
end;
The key point is to OPEN the cursor BEFORE doing the UPDATE.
You should still use "Alter session set isolation_level=serializable" before opening the cursor to avoid possibility of updating a different set of records to the set selected.|||sir andrewst, thanks again for your help!!|||Originally posted by andrewst
You should still use "Alter session set isolation_level=serializable" before opening the cursor to avoid possibility of updating a different set of records to the set selected.
sir andrewst, i can update successfully, but i tried to do the alter session and Oracle Forms does not recognize the isolation_level. It only recognizes the ff:
nls_language
nls_territory
nls_date_format
nls_date_language
nls_numeric_characters
nls_iso_currency
nls_iso_currency
nls_sort
nls_calendar
i've been looking for other commands that i can use to ensure serializability but no such luck so far.|||If you put spaces arounf equal sign this command will be recognised:
alter session set isolation_level = serializable;
Originally posted by alram
sir andrewst, i can update successfully, but i tried to do the alter session and Oracle Forms does not recognize the isolation_level. It only recognizes the ff:
nls_language
nls_territory
nls_date_format
nls_date_language
nls_numeric_characters
nls_iso_currency
nls_iso_currency
nls_sort
nls_calendar
i've been looking for other commands that i can use to ensure serializability but no such luck so far.|||Originally posted by sjacek
If you put spaces arounf equal sign this command will be recognised:
alter session set isolation_level = serializable;
i gave that a try too, before opening the cursor but the error that comes out during compilation is this:
Encountered the symbol 'alter' when expecting one of the following:
begin declare end exception exit for goto if....(etc.)|||You cannot use 'alter session' directly in PL/SQL. Use native dynamic sql:
execute immediate 'alter session set isolation_level = serializable';
This should work.
Originally posted by alram
i gave that a try too, before opening the cursor but the error that comes out during compilation is this:
Encountered the symbol 'alter' when expecting one of the following:
begin declare end exception exit for goto if....(etc.)|||Originally posted by sjacek
You cannot use 'alter session' directly in PL/SQL. Use native dynamic sql:
execute immediate 'alter session set isolation_level = serializable';
This should work.
sir, i tried just that. but i still encountered an error:
Encountered the symbol "IMMEDIATE" when expecting one of the ff:
:= . [ @. % ;
The symbol ".= was inserted before "IMMEDIATE" to continue.
what am i doing wrong?|||i have this problem: i need to check if the SIR_REQUEST_SENT flag is 'Y'. if it is, then the text/dat file should not be created anymore.
my question is, is this statement possible and where should i put it?
IF rProcess.SIR_REQUEST_SENT = 'N' THEN
-- retrieve the records
-- create the file
ELSE
CLOSE dateProcess;
here is my code:
-------
PROCEDURE SEND_BY_DATE(fromdate in date, todate in date) IS
CURSOR dateProcess IS
SELECT *
FROM SIR
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
AND SIR_REQUEST_SENT = 'N'
ORDER BY SIR_TRANS_NO;
rProcess dateProcess%ROWTYPE;
cOut LONG;
myfile VARCHAR2(255);
mypath VARCHAR2(255);
N_FILE text_io.file_type;
sir_columns VARCHAR2(2000) := 'SIR_TRANS_NO,SIR_COMPANY,SIR_PROJECT,SIR_APPL,SIR _BUS_FUN,SIR_REPORTED_BY,
SIR_TYPE,SIR_CLASSIFICATION,SIR_DSCRIPTION,SIR_REA SON,
SIR_REQUEST_DATE,SIR_ATTACHMENT,SIR_REQUEST_SENT,
SIR_CRE_USERID,SIR_CRE_DATE';
BEGIN
OPEN dateProcess;
myfile := sysdate||'_'||'from'||'_'||fromdate||'_'||'to'||'_ '||todate||'_'||rProcess.SIR_COMPANY;
mypath := 'C:\request';
N_FILE := TEXT_IO.FOPEN(mypath||'\'||myfile||'.dat', 'W');
LOOP
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate;
:SYSTEM.MESSAGE_LEVEL := 5;
COMMIT;
:SYSTEM.MESSAGE_LEVEL := 0;
FETCH dateProcess INTO rProcess;
EXIT WHEN dateProcess%NOTFOUND;
cOut := rProcess.SIR_TRANS_NO || ';'
|| rProcess.SIR_COMPANY || ';'
|| rProcess.SIR_PROJECT || ';'
|| rProcess.SIR_APPL || ';'
|| rProcess.SIR_BUS_FUN || ';'
|| rProcess.SIR_REPORTED_BY || ';'
|| rProcess.SIR_TYPE || ';'
|| rProcess.SIR_CLASSIFICATION || ';'
|| rProcess.SIR_DESCRIPTION || ';'
|| rProcess.SIR_REASON || ';'
|| rProcess.SIR_REQUEST_DATE || ';'
|| rProcess.SIR_ATTACHMENT || ';'
|| rProcess.SIR_REQUEST_SENT || ';'
|| rProcess.SIR_CRE_USERID || ';'
|| rProcess.SIR_CRE_DATE || ';';
TEXT_IO.PUT_LINE(N_FILE, cOut);
END LOOP BeginLoop;
TEXT_IO.FCLOSE(N_FILE);
CLOSE dateProcess;
-- CREATE A CONTROL FILE
n_file := TEXT_IO.FOPEN(mypath||'\'||myfile||'.ctl','W');
-- INSERT THE TEXT ENTRY TO THE CONTROL FILE
TEXT_IO.PUT_LINE( N_FILE ,'LOAD DATA' );
TEXT_IO.PUT_LINE( N_FILE ,'INFILE '||''''||myfile||'''');
TEXT_IO.PUT_LINE( N_FILE ,'APPEND'||' INTO TABLE '||'SIR');
TEXT_IO.PUT_LINE( N_FILE ,'FIELDS TERMINATED BY '';'' OPTIONALLY ENCLOSED BY ''"''
TRAILING NULLCOLS
('||sir_columns||' )
');
if text_io.is_open( n_file ) then
TEXT_IO.FCLOSE(N_FILE);
end if;
EXCEPTION
WHEN OTHERS THEN
IF dateProcess%ISOPEN THEN
CLOSE dateProcess;
END IF;
END;
------
i have tried putting the IF statement right after i open the cursor, but even if there are records with SIR_REQUEST_SENT = 'N', the file is not created anymore even though it should. i figure that it is only checking the first record.
any help would be appreciated! thank you in advance!|||I'm not following your requirement here. You say "i need to check if the SIR_REQUEST_SENT flag is 'Y'". What - on any record? On one particular record? On ALL records?
Guessing that you probably mean: if ALL records have the flag='Y' then there is no need to create a file. In that case the logic should be:
FOR rProcess IN dateProcess LOOP
IF NOT TEXT_IO.ISOPEN(n_file) THEN
-- Open the file
n_file := TEXT_IO.FOPEN(mypath||'\'||myfile||'.dat', 'W');
END IF;
-- Output record to file
...
END LOOP;
-- If file was opened, then complete process
IF v_file_opened THEN
TEXT_IO.FCLOSE(n_file);
-- Update records
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
AND SIR_REQUEST_SENT = 'N';
-- Create control file
...
--
END IF;|||Originally posted by andrewst
I'm not following your requirement here. You say "i need to check if the SIR_REQUEST_SENT flag is 'Y'". What - on any record? On one particular record? On ALL records?
Guessing that you probably mean: if ALL records have the flag='Y' then there is no need to create a file. In that case the logic should be:
sir andrewst, sorry about that..
to make things clearer, these are my steps:
- Input a from_date and to_date.
- check for records that have SIR_REQUEST_SENT = 'N' that fall between the from_date and to_date. If there are records found, then update the SIR_REQUEST_SENT flag of the found records to 'Y' and create the text file.
- If the SIR_REQUEST_SENT flag is 'Y' for all records between the 2 dates, then I should not create the text file at all.
again, thank you for your help! i'm really appreciate it!|||Originally posted by alram
sir andrewst, sorry about that..
to make things clearer, these are my steps:
- Input a from_date and to_date.
- check for records that have SIR_REQUEST_SENT = 'N' that fall between the from_date and to_date. If there are records found, then update the SIR_REQUEST_SENT flag of the found records to 'Y' and create the text file.
- If the SIR_REQUEST_SENT flag is 'Y' for all records between the 2 dates, then I should not create the text file at all.
again, thank you for your help! i'm really appreciate it!
OK, in that case the logic I showed should work for you.
Better way to solve my RS problem...
Hello all,
I have a sales table with the following columns:
Product
Date
Price
SalesAmount
Quantity
I need to write a report in Rs that has the following information:
Products sold in a month and during the year...
Currently I use a sub report with different date parameters ie date between
1/1/04 and 1/1/05 for The year sold data, and Date = @.Month for the month
sales.
However, sub-reports seem to slow the report down significantly. Is there a
better way to do this using only one report?
Thanks,
ClintTake a look at the [Employee Sales Summary.rdl] sample report that ships
with the product.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AshVsAOD" <.> wrote in message news:en4Pm2NqEHA.340@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> I have a sales table with the following columns:
> Product
> Date
> Price
> SalesAmount
> Quantity
> I need to write a report in Rs that has the following information:
> Products sold in a month and during the year...
> Currently I use a sub report with different date parameters ie date
between
> 1/1/04 and 1/1/05 for The year sold data, and Date = @.Month for the month
> sales.
> However, sub-reports seem to slow the report down significantly. Is there
a
> better way to do this using only one report?
> Thanks,
> Clint
>|||Thanks,
However, I need one table, this solution seems to require multiple
tables/charts.
Any other way?
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:eP2I7cWqEHA.3464@.tk2msftngp13.phx.gbl...
> Take a look at the [Employee Sales Summary.rdl] sample report that ships
> with the product.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "AshVsAOD" <.> wrote in message
news:en4Pm2NqEHA.340@.TK2MSFTNGP10.phx.gbl...
> > Hello all,
> >
> > I have a sales table with the following columns:
> > Product
> > Date
> > Price
> > SalesAmount
> > Quantity
> >
> > I need to write a report in Rs that has the following information:
> > Products sold in a month and during the year...
> >
> > Currently I use a sub report with different date parameters ie date
> between
> > 1/1/04 and 1/1/05 for The year sold data, and Date = @.Month for the
month
> > sales.
> > However, sub-reports seem to slow the report down significantly. Is
there
> a
> > better way to do this using only one report?
> >
> > Thanks,
> > Clint
> >
> >
>|||This looks to me to be a good candidate for drill down. Your query should
summarize by year,month. Then in Report Services you adding a grouping for
year and a grouping for month. You want the month grouping to be hidden and
based on the year. Then if they click on the year it expands and you get the
month. If the group header for the year you put in a sum expression for the
year total shows in the heading. Hope that makes sense you you.
Bruce L-C
MVP Reporting Services
"AshVsAOD" <.> wrote in message
news:eCvnWabqEHA.1300@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> However, I need one table, this solution seems to require multiple
> tables/charts.
> Any other way?
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
> news:eP2I7cWqEHA.3464@.tk2msftngp13.phx.gbl...
>> Take a look at the [Employee Sales Summary.rdl] sample report that ships
>> with the product.
>> --
>> Ravi Mumulla (Microsoft)
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "AshVsAOD" <.> wrote in message
> news:en4Pm2NqEHA.340@.TK2MSFTNGP10.phx.gbl...
>> > Hello all,
>> >
>> > I have a sales table with the following columns:
>> > Product
>> > Date
>> > Price
>> > SalesAmount
>> > Quantity
>> >
>> > I need to write a report in Rs that has the following information:
>> > Products sold in a month and during the year...
>> >
>> > Currently I use a sub report with different date parameters ie date
>> between
>> > 1/1/04 and 1/1/05 for The year sold data, and Date = @.Month for the
> month
>> > sales.
>> > However, sub-reports seem to slow the report down significantly. Is
> there
>> a
>> > better way to do this using only one report?
>> >
>> > Thanks,
>> > Clint
>> >
>> >
>>
>|||The Company Sales.rdl sample report, that shipped with SQL Server 2000
Reporting Services, is good example of using drilldown.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:Oj0OQIiqEHA.2764@.TK2MSFTNGP11.phx.gbl...
> This looks to me to be a good candidate for drill down. Your query should
> summarize by year,month. Then in Report Services you adding a grouping for
> year and a grouping for month. You want the month grouping to be hidden
> and based on the year. Then if they click on the year it expands and you
> get the month. If the group header for the year you put in a sum
> expression for the year total shows in the heading. Hope that makes sense
> you you.
> Bruce L-C
> MVP Reporting Services
> "AshVsAOD" <.> wrote in message
> news:eCvnWabqEHA.1300@.TK2MSFTNGP12.phx.gbl...
>> Thanks,
>> However, I need one table, this solution seems to require multiple
>> tables/charts.
>> Any other way?
>> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
>> news:eP2I7cWqEHA.3464@.tk2msftngp13.phx.gbl...
>> Take a look at the [Employee Sales Summary.rdl] sample report that ships
>> with the product.
>> --
>> Ravi Mumulla (Microsoft)
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "AshVsAOD" <.> wrote in message
>> news:en4Pm2NqEHA.340@.TK2MSFTNGP10.phx.gbl...
>> > Hello all,
>> >
>> > I have a sales table with the following columns:
>> > Product
>> > Date
>> > Price
>> > SalesAmount
>> > Quantity
>> >
>> > I need to write a report in Rs that has the following information:
>> > Products sold in a month and during the year...
>> >
>> > Currently I use a sub report with different date parameters ie date
>> between
>> > 1/1/04 and 1/1/05 for The year sold data, and Date = @.Month for the
>> month
>> > sales.
>> > However, sub-reports seem to slow the report down significantly. Is
>> there
>> a
>> > better way to do this using only one report?
>> >
>> > Thanks,
>> > Clint
>> >
>> >
>>
>>
>
I have a sales table with the following columns:
Product
Date
Price
SalesAmount
Quantity
I need to write a report in Rs that has the following information:
Products sold in a month and during the year...
Currently I use a sub report with different date parameters ie date between
1/1/04 and 1/1/05 for The year sold data, and Date = @.Month for the month
sales.
However, sub-reports seem to slow the report down significantly. Is there a
better way to do this using only one report?
Thanks,
ClintTake a look at the [Employee Sales Summary.rdl] sample report that ships
with the product.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AshVsAOD" <.> wrote in message news:en4Pm2NqEHA.340@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> I have a sales table with the following columns:
> Product
> Date
> Price
> SalesAmount
> Quantity
> I need to write a report in Rs that has the following information:
> Products sold in a month and during the year...
> Currently I use a sub report with different date parameters ie date
between
> 1/1/04 and 1/1/05 for The year sold data, and Date = @.Month for the month
> sales.
> However, sub-reports seem to slow the report down significantly. Is there
a
> better way to do this using only one report?
> Thanks,
> Clint
>|||Thanks,
However, I need one table, this solution seems to require multiple
tables/charts.
Any other way?
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:eP2I7cWqEHA.3464@.tk2msftngp13.phx.gbl...
> Take a look at the [Employee Sales Summary.rdl] sample report that ships
> with the product.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "AshVsAOD" <.> wrote in message
news:en4Pm2NqEHA.340@.TK2MSFTNGP10.phx.gbl...
> > Hello all,
> >
> > I have a sales table with the following columns:
> > Product
> > Date
> > Price
> > SalesAmount
> > Quantity
> >
> > I need to write a report in Rs that has the following information:
> > Products sold in a month and during the year...
> >
> > Currently I use a sub report with different date parameters ie date
> between
> > 1/1/04 and 1/1/05 for The year sold data, and Date = @.Month for the
month
> > sales.
> > However, sub-reports seem to slow the report down significantly. Is
there
> a
> > better way to do this using only one report?
> >
> > Thanks,
> > Clint
> >
> >
>|||This looks to me to be a good candidate for drill down. Your query should
summarize by year,month. Then in Report Services you adding a grouping for
year and a grouping for month. You want the month grouping to be hidden and
based on the year. Then if they click on the year it expands and you get the
month. If the group header for the year you put in a sum expression for the
year total shows in the heading. Hope that makes sense you you.
Bruce L-C
MVP Reporting Services
"AshVsAOD" <.> wrote in message
news:eCvnWabqEHA.1300@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> However, I need one table, this solution seems to require multiple
> tables/charts.
> Any other way?
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
> news:eP2I7cWqEHA.3464@.tk2msftngp13.phx.gbl...
>> Take a look at the [Employee Sales Summary.rdl] sample report that ships
>> with the product.
>> --
>> Ravi Mumulla (Microsoft)
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "AshVsAOD" <.> wrote in message
> news:en4Pm2NqEHA.340@.TK2MSFTNGP10.phx.gbl...
>> > Hello all,
>> >
>> > I have a sales table with the following columns:
>> > Product
>> > Date
>> > Price
>> > SalesAmount
>> > Quantity
>> >
>> > I need to write a report in Rs that has the following information:
>> > Products sold in a month and during the year...
>> >
>> > Currently I use a sub report with different date parameters ie date
>> between
>> > 1/1/04 and 1/1/05 for The year sold data, and Date = @.Month for the
> month
>> > sales.
>> > However, sub-reports seem to slow the report down significantly. Is
> there
>> a
>> > better way to do this using only one report?
>> >
>> > Thanks,
>> > Clint
>> >
>> >
>>
>|||The Company Sales.rdl sample report, that shipped with SQL Server 2000
Reporting Services, is good example of using drilldown.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:Oj0OQIiqEHA.2764@.TK2MSFTNGP11.phx.gbl...
> This looks to me to be a good candidate for drill down. Your query should
> summarize by year,month. Then in Report Services you adding a grouping for
> year and a grouping for month. You want the month grouping to be hidden
> and based on the year. Then if they click on the year it expands and you
> get the month. If the group header for the year you put in a sum
> expression for the year total shows in the heading. Hope that makes sense
> you you.
> Bruce L-C
> MVP Reporting Services
> "AshVsAOD" <.> wrote in message
> news:eCvnWabqEHA.1300@.TK2MSFTNGP12.phx.gbl...
>> Thanks,
>> However, I need one table, this solution seems to require multiple
>> tables/charts.
>> Any other way?
>> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
>> news:eP2I7cWqEHA.3464@.tk2msftngp13.phx.gbl...
>> Take a look at the [Employee Sales Summary.rdl] sample report that ships
>> with the product.
>> --
>> Ravi Mumulla (Microsoft)
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "AshVsAOD" <.> wrote in message
>> news:en4Pm2NqEHA.340@.TK2MSFTNGP10.phx.gbl...
>> > Hello all,
>> >
>> > I have a sales table with the following columns:
>> > Product
>> > Date
>> > Price
>> > SalesAmount
>> > Quantity
>> >
>> > I need to write a report in Rs that has the following information:
>> > Products sold in a month and during the year...
>> >
>> > Currently I use a sub report with different date parameters ie date
>> between
>> > 1/1/04 and 1/1/05 for The year sold data, and Date = @.Month for the
>> month
>> > sales.
>> > However, sub-reports seem to slow the report down significantly. Is
>> there
>> a
>> > better way to do this using only one report?
>> >
>> > Thanks,
>> > Clint
>> >
>> >
>>
>>
>
Better Way To Handle The Code?
Hi experts,
I have a table which stores data in multi-rows basis for a particular
record. The structure of the table is as exhibit:
CREATE TABLE [dbo].[Table_Header] (
[Rec_Id] [numeric](18, 0) NOT NULL ,
[Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
Example data is shown as follows:
Rec_Id Param_Name Param_Value
1 Product TV
1 Category Electrical
2 Product Telephone
2 Category Electrical
3 Product TV
3 Category Electrical
In case I would like to filter out those records (Rec_Id) where Product = TV
and Category = Electrical, one way I can think of using T-SQL is:
Select A.Rec_Id From Table_Header A, Table_Header B
Where A.Rec_Id = B.Rec_Id And
A.Param_Name = 'Product' And
A.Param_Value = 'TV' And
B.Param_Name = 'Category' And
B.Param_Value = 'Electrical'
The Select statement above returns me results: 1 and 3. But it will grow to
be quite complicated if I have many parameters to filter in my select
statement. Any better way to handle this coding?
ThanksLBT
What is a PK on your table?
Do you have any indexes defined on the table?
"LBT" <LBT@.discussions.microsoft.com> wrote in message
news:A1E1B426-7986-4535-BF4B-BDD35532F273@.microsoft.com...
> Hi experts,
> I have a table which stores data in multi-rows basis for a particular
> record. The structure of the table is as exhibit:
> CREATE TABLE [dbo].[Table_Header] (
> [Rec_Id] [numeric](18, 0) NOT NULL ,
> [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> Example data is shown as follows:
> Rec_Id Param_Name Param_Value
> 1 Product TV
> 1 Category Electrical
> 2 Product Telephone
> 2 Category Electrical
> 3 Product TV
> 3 Category Electrical
> In case I would like to filter out those records (Rec_Id) where Product =
TV
> and Category = Electrical, one way I can think of using T-SQL is:
> Select A.Rec_Id From Table_Header A, Table_Header B
> Where A.Rec_Id = B.Rec_Id And
> A.Param_Name = 'Product' And
> A.Param_Value = 'TV' And
> B.Param_Name = 'Category' And
> B.Param_Value = 'Electrical'
> The Select statement above returns me results: 1 and 3. But it will grow
to
> be quite complicated if I have many parameters to filter in my select
> statement. Any better way to handle this coding?
> Thanks
>
>
>
>|||Hi
You can normalize your table.
You can have 2 tables here Product and Category
Product:
RecID | Product_Name
1 | TV
2 | Telephone
3 | TV
Category:
RecID | Category_Name
1 | Electrical
2 | Electrical
3 | Electrical
Based on the req. u can query as
Select Category.Rec_id
FROM Category
INNER JOIN Product ON Product.Rec_id = Category.Rec_id
AND Product_Name IN ('TV')
WHERE Category_Name IN ('Electrical')
This will solve the purpose
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"LBT" wrote:
> Hi experts,
> I have a table which stores data in multi-rows basis for a particular
> record. The structure of the table is as exhibit:
> CREATE TABLE [dbo].[Table_Header] (
> [Rec_Id] [numeric](18, 0) NOT NULL ,
> [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> Example data is shown as follows:
> Rec_Id Param_Name Param_Value
> 1 Product TV
> 1 Category Electrical
> 2 Product Telephone
> 2 Category Electrical
> 3 Product TV
> 3 Category Electrical
> In case I would like to filter out those records (Rec_Id) where Product =
TV
> and Category = Electrical, one way I can think of using T-SQL is:
> Select A.Rec_Id From Table_Header A, Table_Header B
> Where A.Rec_Id = B.Rec_Id And
> A.Param_Name = 'Product' And
> A.Param_Value = 'TV' And
> B.Param_Name = 'Category' And
> B.Param_Value = 'Electrical'
> The Select statement above returns me results: 1 and 3. But it will grow t
o
> be quite complicated if I have many parameters to filter in my select
> statement. Any better way to handle this coding?
> Thanks
>
>
>
>|||> I have a table which stores data in multi-rows basis for a particular
> record
Why? That's usually a really poor design. There are better ways to
represent types and subtypes. Example:
CREATE TABLE Products (sku INTEGER PRIMARY KEY, product_type_code
CHAR(2) NOT NULL CHECK (product_type_code IN ('EL','EN','SP')),
productname VARCHAR(40) NOT NULL UNIQUE, UNIQUE (sku,product_type_code)
/* Columns common to all products */)
CREATE TABLE EntertainmentProducts (sku INTEGER PRIMARY KEY,
product_type_code CHAR(2) DEFAULT 'EN' NOT NULL CHECK
(product_type_code='EN'), medium CHAR(3) NOT NULL CHECK (medium IN
('DVD','VHS','CD')), FOREIGN KEY (sku, product_type_code) REFERENCES
Products (sku, product_type_code) /* Columns specific to this
subtype... */)
CREATE TABLE ElectronicProducts (sku INTEGER PRIMARY KEY,
product_type_code CHAR(2) DEFAULT 'EL' NOT NULL CHECK
(product_type_code='EL'), FOREIGN KEY (sku, product_type_code)
REFERENCES Products (sku, product_type_code) /* Columns specific to
this subtype */)
CREATE TABLE SportsProducts (sku INTEGER PRIMARY KEY, product_type_code
CHAR(2) DEFAULT 'SP' NOT NULL CHECK (product_type_code='SP'), FOREIGN
KEY (sku, product_type_code) REFERENCES Products (sku,
product_type_code) /* Columns specific to this subtype */)
> it will grow to
> be quite complicated if I have many parameters to filter in my select
> statement.
Correct! That's why it's better to go for a more normalized approach.
David Portas
SQL Server MVP
--|||The PKs are Rec_Id and Param_Name
"Uri Dimant" wrote:
> LBT
> What is a PK on your table?
> Do you have any indexes defined on the table?
> "LBT" <LBT@.discussions.microsoft.com> wrote in message
> news:A1E1B426-7986-4535-BF4B-BDD35532F273@.microsoft.com...
> ,
> TV
> to
>
>|||Thanks for the suggestion. In fact, there will be a lot of parameters in my
actual application where I think normalization is not my exact solution
(sorry that not to make my question clear in previous post). And because of
the norm that the parameters list will change from time to time, I need to
store data in the mentioned manner (or I will have to change table structure
everytime parameters change).
"Chandra" wrote:
> Hi
> You can normalize your table.
> You can have 2 tables here Product and Category
> Product:
> RecID | Product_Name
> 1 | TV
> 2 | Telephone
> 3 | TV
> Category:
> RecID | Category_Name
> 1 | Electrical
> 2 | Electrical
> 3 | Electrical
> Based on the req. u can query as
> Select Category.Rec_id
> FROM Category
> INNER JOIN Product ON Product.Rec_id = Category.Rec_id
> AND Product_Name IN ('TV')
> WHERE Category_Name IN ('Electrical')
> This will solve the purpose
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "LBT" wrote:
>|||1. What objects are being stored in this table? It looks like a generic
System table - holding attribute bnames and values, for a database. I would
recommend you get a good book on Relational Data modeling, and take a quick
look at it. You might consider normalizing the data structure a bit..
Instead of having a Param_Name column in a table, you might want to create a
Products Table and a Categorys Table, but I don;t know what exactly you are
doing so it's hard to tell..
"LBT" wrote:
> Hi experts,
> I have a table which stores data in multi-rows basis for a particular
> record. The structure of the table is as exhibit:
> CREATE TABLE [dbo].[Table_Header] (
> [Rec_Id] [numeric](18, 0) NOT NULL ,
> [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> Example data is shown as follows:
> Rec_Id Param_Name Param_Value
> 1 Product TV
> 1 Category Electrical
> 2 Product Telephone
> 2 Category Electrical
> 3 Product TV
> 3 Category Electrical
> In case I would like to filter out those records (Rec_Id) where Product =
TV
> and Category = Electrical, one way I can think of using T-SQL is:
> Select A.Rec_Id From Table_Header A, Table_Header B
> Where A.Rec_Id = B.Rec_Id And
> A.Param_Name = 'Product' And
> A.Param_Value = 'TV' And
> B.Param_Name = 'Category' And
> B.Param_Value = 'Electrical'
> The Select statement above returns me results: 1 and 3. But it will grow t
o
> be quite complicated if I have many parameters to filter in my select
> statement. Any better way to handle this coding?
> Thanks
>
>
>
>|||I think I have provided a confusing example. My mistake :)
Actually the table structure is designed in such a way to store variety of
data structure sent in by user. Say for example, user might send in "Rec_Id
[1], Product [TV], Category [Electrical]", "Rec_Id [2], Product [Telephone],
Category [Electrical]", etc.
At later time, user can send in data structure with new add-in parameter(s)
- "Rec_Id [3], Product [TV], Category [Electrical], Power [1000kW], Color
[Black]". The challenge is I cannot change the table structure (this is the
dynamic solution requested by customer) everytime new parameter is being
introduced. With the table design, I can simply store "Power" into Param_Nam
e
and "1000kW" into Param_Value (no adding of column to table structure is
allowed).
So with this table design, the only way I can perform records filtering (the
filtering parameters however will be fixed which are Product and Category in
this case) is as stated in my post? I just wonder whether there is any bette
r
way to perform the query.
In fact, I have to create and configure few reference/definition tables so
that my application will be dynamic enough to cater for this requirement. Bu
t
it will be lengthy to explain the entire situation.
Thanks
"CBretana" wrote:
> 1. What objects are being stored in this table? It looks like a generic
> System table - holding attribute bnames and values, for a database. I wou
ld
> recommend you get a good book on Relational Data modeling, and take a qui
ck
> look at it. You might consider normalizing the data structure a bit..
> Instead of having a Param_Name column in a table, you might want to create
a
> Products Table and a Categorys Table, but I don;t know what exactly you a
re
> doing so it's hard to tell..
> "LBT" wrote:
>|||In a way, you are in the dilemma which often results from trying to achieve
conflicting objectives...
The entire concept of Relational Databases is, in one sense (I need to be
careful here) designed to allow you to "Structure" your data so that it
closely matches the real world data objects or abstractions which represent
those objects, in order to allow the kind of data "processing" (like
arbitrary filtering, sorting, etc. that you are trying to accomplish. Old
style (pre-Relational) Database systems were comparitively inadeguate at
these sorts of things, *because* the data was not stored in a structured
relational way.
But you are *Trying* to store your data in an unstructured way to allow as
much flexibility in what kind of data is allowed in to the database. These
two objectives are fundamentally inconsistent, and that is one reason why yo
u
are experiencing difficulties
"LBT" wrote:
> I think I have provided a confusing example. My mistake :)
> Actually the table structure is designed in such a way to store variety of
> data structure sent in by user. Say for example, user might send in "Rec_I
d
> [1], Product [TV], Category [Electrical]", "Rec_Id [2], Product [Telephone],
> Category [Electrical]", etc.
> At later time, user can send in data structure with new add-in parameter(s
)
> - "Rec_Id [3], Product [TV], Category [Electrical], Power [1000kW], Color
> [Black]". The challenge is I cannot change the table structure (this is the
> dynamic solution requested by customer) everytime new parameter is being
> introduced. With the table design, I can simply store "Power" into Param_N
ame
> and "1000kW" into Param_Value (no adding of column to table structure is
> allowed).
> So with this table design, the only way I can perform records filtering (t
he
> filtering parameters however will be fixed which are Product and Category
in
> this case) is as stated in my post? I just wonder whether there is any bet
ter
> way to perform the query.
> In fact, I have to create and configure few reference/definition tables so
> that my application will be dynamic enough to cater for this requirement.
But
> it will be lengthy to explain the entire situation.
> Thanks
> "CBretana" wrote:
>|||If the customer DEMANDS a design like this then they should understand
and accept its weaknesses with regard to data integrity, performance
and increased complexity. However, I would always discuss the
alternatives with them first. Change control for adding new attributes
should be their friend not their enemy. If they are s
ing a zero
maintenance solution then tell them to forget it - of course no such
thing exists - or at least when a legacy application reaches that stage
of maturity then it's probably already in need of replacement.
The reason that proper change control is required is that users
generally make very poor database designers. If you allow users to
decide the format of business data you will lose any integrity and
future value therein. Do you really expect users to comprehenend and
analyze functional dependencies and standardise and conform attributes
in the system? If they were capable of that then they must be database
pros in which case they wouldn't need this kind of kludge. Corporate
data is too important to be left to users to manage.
David Portas
SQL Server MVP
--
I have a table which stores data in multi-rows basis for a particular
record. The structure of the table is as exhibit:
CREATE TABLE [dbo].[Table_Header] (
[Rec_Id] [numeric](18, 0) NOT NULL ,
[Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
Example data is shown as follows:
Rec_Id Param_Name Param_Value
1 Product TV
1 Category Electrical
2 Product Telephone
2 Category Electrical
3 Product TV
3 Category Electrical
In case I would like to filter out those records (Rec_Id) where Product = TV
and Category = Electrical, one way I can think of using T-SQL is:
Select A.Rec_Id From Table_Header A, Table_Header B
Where A.Rec_Id = B.Rec_Id And
A.Param_Name = 'Product' And
A.Param_Value = 'TV' And
B.Param_Name = 'Category' And
B.Param_Value = 'Electrical'
The Select statement above returns me results: 1 and 3. But it will grow to
be quite complicated if I have many parameters to filter in my select
statement. Any better way to handle this coding?
ThanksLBT
What is a PK on your table?
Do you have any indexes defined on the table?
"LBT" <LBT@.discussions.microsoft.com> wrote in message
news:A1E1B426-7986-4535-BF4B-BDD35532F273@.microsoft.com...
> Hi experts,
> I have a table which stores data in multi-rows basis for a particular
> record. The structure of the table is as exhibit:
> CREATE TABLE [dbo].[Table_Header] (
> [Rec_Id] [numeric](18, 0) NOT NULL ,
> [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> Example data is shown as follows:
> Rec_Id Param_Name Param_Value
> 1 Product TV
> 1 Category Electrical
> 2 Product Telephone
> 2 Category Electrical
> 3 Product TV
> 3 Category Electrical
> In case I would like to filter out those records (Rec_Id) where Product =
TV
> and Category = Electrical, one way I can think of using T-SQL is:
> Select A.Rec_Id From Table_Header A, Table_Header B
> Where A.Rec_Id = B.Rec_Id And
> A.Param_Name = 'Product' And
> A.Param_Value = 'TV' And
> B.Param_Name = 'Category' And
> B.Param_Value = 'Electrical'
> The Select statement above returns me results: 1 and 3. But it will grow
to
> be quite complicated if I have many parameters to filter in my select
> statement. Any better way to handle this coding?
> Thanks
>
>
>
>|||Hi
You can normalize your table.
You can have 2 tables here Product and Category
Product:
RecID | Product_Name
1 | TV
2 | Telephone
3 | TV
Category:
RecID | Category_Name
1 | Electrical
2 | Electrical
3 | Electrical
Based on the req. u can query as
Select Category.Rec_id
FROM Category
INNER JOIN Product ON Product.Rec_id = Category.Rec_id
AND Product_Name IN ('TV')
WHERE Category_Name IN ('Electrical')
This will solve the purpose
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"LBT" wrote:
> Hi experts,
> I have a table which stores data in multi-rows basis for a particular
> record. The structure of the table is as exhibit:
> CREATE TABLE [dbo].[Table_Header] (
> [Rec_Id] [numeric](18, 0) NOT NULL ,
> [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> Example data is shown as follows:
> Rec_Id Param_Name Param_Value
> 1 Product TV
> 1 Category Electrical
> 2 Product Telephone
> 2 Category Electrical
> 3 Product TV
> 3 Category Electrical
> In case I would like to filter out those records (Rec_Id) where Product =
TV
> and Category = Electrical, one way I can think of using T-SQL is:
> Select A.Rec_Id From Table_Header A, Table_Header B
> Where A.Rec_Id = B.Rec_Id And
> A.Param_Name = 'Product' And
> A.Param_Value = 'TV' And
> B.Param_Name = 'Category' And
> B.Param_Value = 'Electrical'
> The Select statement above returns me results: 1 and 3. But it will grow t
o
> be quite complicated if I have many parameters to filter in my select
> statement. Any better way to handle this coding?
> Thanks
>
>
>
>|||> I have a table which stores data in multi-rows basis for a particular
> record
Why? That's usually a really poor design. There are better ways to
represent types and subtypes. Example:
CREATE TABLE Products (sku INTEGER PRIMARY KEY, product_type_code
CHAR(2) NOT NULL CHECK (product_type_code IN ('EL','EN','SP')),
productname VARCHAR(40) NOT NULL UNIQUE, UNIQUE (sku,product_type_code)
/* Columns common to all products */)
CREATE TABLE EntertainmentProducts (sku INTEGER PRIMARY KEY,
product_type_code CHAR(2) DEFAULT 'EN' NOT NULL CHECK
(product_type_code='EN'), medium CHAR(3) NOT NULL CHECK (medium IN
('DVD','VHS','CD')), FOREIGN KEY (sku, product_type_code) REFERENCES
Products (sku, product_type_code) /* Columns specific to this
subtype... */)
CREATE TABLE ElectronicProducts (sku INTEGER PRIMARY KEY,
product_type_code CHAR(2) DEFAULT 'EL' NOT NULL CHECK
(product_type_code='EL'), FOREIGN KEY (sku, product_type_code)
REFERENCES Products (sku, product_type_code) /* Columns specific to
this subtype */)
CREATE TABLE SportsProducts (sku INTEGER PRIMARY KEY, product_type_code
CHAR(2) DEFAULT 'SP' NOT NULL CHECK (product_type_code='SP'), FOREIGN
KEY (sku, product_type_code) REFERENCES Products (sku,
product_type_code) /* Columns specific to this subtype */)
> it will grow to
> be quite complicated if I have many parameters to filter in my select
> statement.
Correct! That's why it's better to go for a more normalized approach.
David Portas
SQL Server MVP
--|||The PKs are Rec_Id and Param_Name
"Uri Dimant" wrote:
> LBT
> What is a PK on your table?
> Do you have any indexes defined on the table?
> "LBT" <LBT@.discussions.microsoft.com> wrote in message
> news:A1E1B426-7986-4535-BF4B-BDD35532F273@.microsoft.com...
> ,
> TV
> to
>
>|||Thanks for the suggestion. In fact, there will be a lot of parameters in my
actual application where I think normalization is not my exact solution
(sorry that not to make my question clear in previous post). And because of
the norm that the parameters list will change from time to time, I need to
store data in the mentioned manner (or I will have to change table structure
everytime parameters change).
"Chandra" wrote:
> Hi
> You can normalize your table.
> You can have 2 tables here Product and Category
> Product:
> RecID | Product_Name
> 1 | TV
> 2 | Telephone
> 3 | TV
> Category:
> RecID | Category_Name
> 1 | Electrical
> 2 | Electrical
> 3 | Electrical
> Based on the req. u can query as
> Select Category.Rec_id
> FROM Category
> INNER JOIN Product ON Product.Rec_id = Category.Rec_id
> AND Product_Name IN ('TV')
> WHERE Category_Name IN ('Electrical')
> This will solve the purpose
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "LBT" wrote:
>|||1. What objects are being stored in this table? It looks like a generic
System table - holding attribute bnames and values, for a database. I would
recommend you get a good book on Relational Data modeling, and take a quick
look at it. You might consider normalizing the data structure a bit..
Instead of having a Param_Name column in a table, you might want to create a
Products Table and a Categorys Table, but I don;t know what exactly you are
doing so it's hard to tell..
"LBT" wrote:
> Hi experts,
> I have a table which stores data in multi-rows basis for a particular
> record. The structure of the table is as exhibit:
> CREATE TABLE [dbo].[Table_Header] (
> [Rec_Id] [numeric](18, 0) NOT NULL ,
> [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> Example data is shown as follows:
> Rec_Id Param_Name Param_Value
> 1 Product TV
> 1 Category Electrical
> 2 Product Telephone
> 2 Category Electrical
> 3 Product TV
> 3 Category Electrical
> In case I would like to filter out those records (Rec_Id) where Product =
TV
> and Category = Electrical, one way I can think of using T-SQL is:
> Select A.Rec_Id From Table_Header A, Table_Header B
> Where A.Rec_Id = B.Rec_Id And
> A.Param_Name = 'Product' And
> A.Param_Value = 'TV' And
> B.Param_Name = 'Category' And
> B.Param_Value = 'Electrical'
> The Select statement above returns me results: 1 and 3. But it will grow t
o
> be quite complicated if I have many parameters to filter in my select
> statement. Any better way to handle this coding?
> Thanks
>
>
>
>|||I think I have provided a confusing example. My mistake :)
Actually the table structure is designed in such a way to store variety of
data structure sent in by user. Say for example, user might send in "Rec_Id
[1], Product [TV], Category [Electrical]", "Rec_Id [2], Product [Telephone],
Category [Electrical]", etc.
At later time, user can send in data structure with new add-in parameter(s)
- "Rec_Id [3], Product [TV], Category [Electrical], Power [1000kW], Color
[Black]". The challenge is I cannot change the table structure (this is the
dynamic solution requested by customer) everytime new parameter is being
introduced. With the table design, I can simply store "Power" into Param_Nam
e
and "1000kW" into Param_Value (no adding of column to table structure is
allowed).
So with this table design, the only way I can perform records filtering (the
filtering parameters however will be fixed which are Product and Category in
this case) is as stated in my post? I just wonder whether there is any bette
r
way to perform the query.
In fact, I have to create and configure few reference/definition tables so
that my application will be dynamic enough to cater for this requirement. Bu
t
it will be lengthy to explain the entire situation.
Thanks
"CBretana" wrote:
> 1. What objects are being stored in this table? It looks like a generic
> System table - holding attribute bnames and values, for a database. I wou
ld
> recommend you get a good book on Relational Data modeling, and take a qui
ck
> look at it. You might consider normalizing the data structure a bit..
> Instead of having a Param_Name column in a table, you might want to create
a
> Products Table and a Categorys Table, but I don;t know what exactly you a
re
> doing so it's hard to tell..
> "LBT" wrote:
>|||In a way, you are in the dilemma which often results from trying to achieve
conflicting objectives...
The entire concept of Relational Databases is, in one sense (I need to be
careful here) designed to allow you to "Structure" your data so that it
closely matches the real world data objects or abstractions which represent
those objects, in order to allow the kind of data "processing" (like
arbitrary filtering, sorting, etc. that you are trying to accomplish. Old
style (pre-Relational) Database systems were comparitively inadeguate at
these sorts of things, *because* the data was not stored in a structured
relational way.
But you are *Trying* to store your data in an unstructured way to allow as
much flexibility in what kind of data is allowed in to the database. These
two objectives are fundamentally inconsistent, and that is one reason why yo
u
are experiencing difficulties
"LBT" wrote:
> I think I have provided a confusing example. My mistake :)
> Actually the table structure is designed in such a way to store variety of
> data structure sent in by user. Say for example, user might send in "Rec_I
d
> [1], Product [TV], Category [Electrical]", "Rec_Id [2], Product [Telephone],
> Category [Electrical]", etc.
> At later time, user can send in data structure with new add-in parameter(s
)
> - "Rec_Id [3], Product [TV], Category [Electrical], Power [1000kW], Color
> [Black]". The challenge is I cannot change the table structure (this is the
> dynamic solution requested by customer) everytime new parameter is being
> introduced. With the table design, I can simply store "Power" into Param_N
ame
> and "1000kW" into Param_Value (no adding of column to table structure is
> allowed).
> So with this table design, the only way I can perform records filtering (t
he
> filtering parameters however will be fixed which are Product and Category
in
> this case) is as stated in my post? I just wonder whether there is any bet
ter
> way to perform the query.
> In fact, I have to create and configure few reference/definition tables so
> that my application will be dynamic enough to cater for this requirement.
But
> it will be lengthy to explain the entire situation.
> Thanks
> "CBretana" wrote:
>|||If the customer DEMANDS a design like this then they should understand
and accept its weaknesses with regard to data integrity, performance
and increased complexity. However, I would always discuss the
alternatives with them first. Change control for adding new attributes
should be their friend not their enemy. If they are s
ing a zeromaintenance solution then tell them to forget it - of course no such
thing exists - or at least when a legacy application reaches that stage
of maturity then it's probably already in need of replacement.
The reason that proper change control is required is that users
generally make very poor database designers. If you allow users to
decide the format of business data you will lose any integrity and
future value therein. Do you really expect users to comprehenend and
analyze functional dependencies and standardise and conform attributes
in the system? If they were capable of that then they must be database
pros in which case they wouldn't need this kind of kludge. Corporate
data is too important to be left to users to manage.
David Portas
SQL Server MVP
--
Subscribe to:
Posts (Atom)