more questions

Lets say on tbDeposits I have a Index that is on both depositdue and deposit paid

why does this statement

Select * from tbDeposits Deposits where Deposits.DateDue IS NOT NULL
AND Deposits.DatePaid IS NULL

have 2 table scans in it? one for 83 percent?

A NULL value is 'unknown' and cannot be efficiently indexed.

Any time you are trying to find the absence of something, you have to look at everything you have to see if it is missing. That may require a table scan or it may require an index scan. (IF it is the clustered index key, then an index scan is still a table scan.)

sql

More Efficient SQL Statement - Select Count(1)

I know that you can write select statement (Select Count(1)) within Oracle
if finds one record it will stop the search critieria.
Please help me modify the SQL statement listed below to use the equivalent
if it finds one records it stops and output is 1 if not the output is 0.
Thank You,
SET @.COUNT_CALLS_REC_1 =
(Select count(Icent_Num)
from TKCalls.dbo.tblCalls
where DATEDIFF(mi, StartedTime, GETDATE()) <=30
AND left(cast(Icent_Num as varchar(20)),6) = ('962472'))Preface the query with a return limitation -ROWCOUNT.
USE Northwind
GO
SET ROWCOUNT 0
SELECT CustomerID
FROM Customers
WHERE Country = 'Mexico'
SET ROWCOUNT 0
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message news:FDDCBA4B-3553-4459-B7DA-2
5436FBEC8C9@.microsoft.com...
>
> I know that you can write select statement (Select Count(1)) within Oracle
> if finds one record it will stop the search critieria.
>
> Please help me modify the SQL statement listed below to use the equivalent
> if it finds one records it stops and output is 1 if not the output is 0.
>
> Thank You,
>
>
> SET @.COUNT_CALLS_REC_1 =
> (Select count(Icent_Num)
> from TKCalls.dbo.tblCalls
> where DATEDIFF(mi, StartedTime, GETDATE()) <=30
> AND left(cast(Icent_Num as varchar(20)),6) = ('962472'))|||something like this?
if exists(
Select 1 from TKCalls.dbo.tblCalls
where DATEDIFF(mi, StartedTime, GETDATE()) <=30
AND left(cast(Icent_Num as varchar(20)),6) = ('962472')
)
SET @.COUNT_CALLS_REC_1 = 1
else
SET @.COUNT_CALLS_REC_1 = 0
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Sorry, the first SET ROWCOUNT should be 1 -NOT 0.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23ojjUjHnGHA.2264@.TK
2MSFTNGP04.phx.gbl...
Preface the query with a return limitation -ROWCOUNT.
USE Northwind
GO
SET ROWCOUNT 0
SELECT CustomerID
FROM Customers
WHERE Country = 'Mexico'
SET ROWCOUNT 0
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message news:FDDCBA4B-3553-4459-B7DA-2
5436FBEC8C9@.microsoft.com...
>
> I know that you can write select statement (Select Count(1)) within Oracle
> if finds one record it will stop the search critieria.
>
> Please help me modify the SQL statement listed below to use the equivalent
> if it finds one records it stops and output is 1 if not the output is 0.
>
> Thank You,
>
>
> SET @.COUNT_CALLS_REC_1 =
> (Select count(Icent_Num)
> from TKCalls.dbo.tblCalls
> where DATEDIFF(mi, StartedTime, GETDATE()) <=30
> AND left(cast(Icent_Num as varchar(20)),6) = ('962472'))|||Joe k.,
Use the operator "exists" and do not manipulate the columns in the "where"
clause as possible, to let SQL Server to use the indexes optimally in case
they exists.
if exists (
select *
from TKCalls.dbo.tblCalls
where
StartedTime between dateadd(minute, -30, GETDATE()) and GETDATE()
and cast(Icent_Num as varchar(20)) like '962472%'
)
set ...
else
set ...
go
AMB
"Joe K." wrote:

> I know that you can write select statement (Select Count(1)) within Oracle
> if finds one record it will stop the search critieria.
> Please help me modify the SQL statement listed below to use the equivalent
> if it finds one records it stops and output is 1 if not the output is 0.
> Thank You,
>
> SET @.COUNT_CALLS_REC_1 =
> (Select count(Icent_Num)
> from TKCalls.dbo.tblCalls
> where DATEDIFF(mi, StartedTime, GETDATE()) <=30
> AND left(cast(Icent_Num as varchar(20)),6) = ('962472'))|||Are you sure that on Oracle, the statement SELECT COUNT(1) will never
return a value > 1? I would be very surprised if this is the Oracle
behavior, because that is not what the SQL statement is specifying...
Gert-Jan
Joe K. wrote:
> I know that you can write select statement (Select Count(1)) within Oracle
> if finds one record it will stop the search critieria.
> Please help me modify the SQL statement listed below to use the equivalent
> if it finds one records it stops and output is 1 if not the output is 0.
> Thank You,
> SET @.COUNT_CALLS_REC_1 =
> (Select count(Icent_Num)
> from TKCalls.dbo.tblCalls
> where DATEDIFF(mi, StartedTime, GETDATE()) <=30
> AND left(cast(Icent_Num as varchar(20)),6) = ('962472'))|||doesn't "select top 1 * from ..." work for you?
Jos=E9.
Gert-Jan Strik wrote:
> Are you sure that on Oracle, the statement SELECT COUNT(1) will never
> return a value > 1? I would be very surprised if this is the Oracle
> behavior, because that is not what the SQL statement is specifying...
> Gert-Jan
>
> Joe K. wrote:
cle
ent|||This should work as well...
scott0100
---
scott0100's Profile: http://www.dbtalk.net/m491
View this thread: http://www.dbtalk.net/t316762|||As I recall, one of the issues with TOP 1 is that it has to find all records
(or at least indexes) first in order to determine which one is the TOP 1. So
that may not be 'efficient'.
;-)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<josearaujof@.gmail.com> wrote in message
news:1151700301.773682.267740@.75g2000cwc.googlegroups.com...
doesn't "select top 1 * from ..." work for you?
Jos.
Gert-Jan Strik wrote:
> Are you sure that on Oracle, the statement SELECT COUNT(1) will never
> return a value > 1? I would be very surprised if this is the Oracle
> behavior, because that is not what the SQL statement is specifying...
> Gert-Jan
>
> Joe K. wrote:|||> As I recall, one of the issues with TOP 1 is that it has to find all records (or at least
indexes)
> first in order to determine which one is the TOP 1. So that may not be 'efficient'
.
Only with ORDER BY. Without ORDER BY, you ask for any one row, so the optimi
zer and engine know it
can stop after the first row it encounters.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23G0c2mInGHA.3388@.TK2MSFTNGP05.phx.
gbl...
> As I recall, one of the issues with TOP 1 is that it has to find all recor
ds (or at least indexes)
> first in order to determine which one is the TOP 1. So that may not be 'ef
ficient'.
> ;-)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> <josearaujof@.gmail.com> wrote in message
> news:1151700301.773682.267740@.75g2000cwc.googlegroups.com...
> doesn't "select top 1 * from ..." work for you?
> Jos.
> Gert-Jan Strik wrote:
>

Monthly Reports - Date Format

I have a report that charts our weekly sales and monthly sales. I use the
following SQL statement to reflect 7 days or 30 days, which works but not
properly. I'd like for my report to run from Sunday to Sunday for the 7-day
report, and from the 1st of the month to the end of the month, regardless of
the number of days in the month. How do I modfify my statement to do this?
Here's my SQL statement for the 30-day or monthly report:
SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
(*) AS TOT
FROM Sysadm.receipt
WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
-30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
101), 101))
GROUP BY receipt_date, receipt_no, stamp
ORDER BY receipt_date
--
EnchantnetI used the following statement to run a report for the previous week, from
Saturday to Friday:
CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
101), 101) AS StartDt,
CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
101), 101) AS EndDt
You could probably use a variation of this for your monthly report. I hope
this helps!
"Enchantnet" wrote:
> I have a report that charts our weekly sales and monthly sales. I use the
> following SQL statement to reflect 7 days or 30 days, which works but not
> properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> report, and from the 1st of the month to the end of the month, regardless of
> the number of days in the month. How do I modfify my statement to do this?
> Here's my SQL statement for the 30-day or monthly report:
> SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> (*) AS TOT
> FROM Sysadm.receipt
> WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> 101), 101))
> GROUP BY receipt_date, receipt_no, stamp
> ORDER BY receipt_date
> --
> Enchantnet|||Hey DAW,
Thanks for the code. However, I'm a newbie at this sort of stuff and I get
the following error when I entered your code: "ADO error: Incorrect syntax
near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
could not be completed." Any idea? Do I need to include or define the
StartDt and EndDt elsewhere?
--
Enchantnet
"daw" wrote:
> I used the following statement to run a report for the previous week, from
> Saturday to Friday:
> CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> 101), 101) AS StartDt,
> CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> 101), 101) AS EndDt
> You could probably use a variation of this for your monthly report. I hope
> this helps!
> "Enchantnet" wrote:
> > I have a report that charts our weekly sales and monthly sales. I use the
> > following SQL statement to reflect 7 days or 30 days, which works but not
> > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > report, and from the 1st of the month to the end of the month, regardless of
> > the number of days in the month. How do I modfify my statement to do this?
> > Here's my SQL statement for the 30-day or monthly report:
> >
> > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > (*) AS TOT
> > FROM Sysadm.receipt
> > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > 101), 101))
> > GROUP BY receipt_date, receipt_no, stamp
> > ORDER BY receipt_date
> > --
> > Enchantnet|||I'm not sure what that particular error means, but your statement should look
something like:
select CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw,
GETDATE()), 101), 101) AS StartDt, CONVERT(datetime, CONVERT(nvarchar,
GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101) AS EndDt
from ....
"Enchantnet" wrote:
> Hey DAW,
> Thanks for the code. However, I'm a newbie at this sort of stuff and I get
> the following error when I entered your code: "ADO error: Incorrect syntax
> near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
> could not be completed." Any idea? Do I need to include or define the
> StartDt and EndDt elsewhere?
> --
> Enchantnet
>
> "daw" wrote:
> > I used the following statement to run a report for the previous week, from
> > Saturday to Friday:
> > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> > 101), 101) AS StartDt,
> > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> > 101), 101) AS EndDt
> >
> > You could probably use a variation of this for your monthly report. I hope
> > this helps!
> >
> > "Enchantnet" wrote:
> >
> > > I have a report that charts our weekly sales and monthly sales. I use the
> > > following SQL statement to reflect 7 days or 30 days, which works but not
> > > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > > report, and from the 1st of the month to the end of the month, regardless of
> > > the number of days in the month. How do I modfify my statement to do this?
> > > Here's my SQL statement for the 30-day or monthly report:
> > >
> > > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > > (*) AS TOT
> > > FROM Sysadm.receipt
> > > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > > 101), 101))
> > > GROUP BY receipt_date, receipt_no, stamp
> > > ORDER BY receipt_date
> > > --
> > > Enchantnet|||Sorry, I had that wrong as far as what you need. Try this:
select ....
from...
where date between CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 -
DATEPART(dw, GETDATE()), 101), 101) AND CONVERT(datetime, CONVERT(nvarchar,
GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101)
"daw" wrote:
> I'm not sure what that particular error means, but your statement should look
> something like:
> select CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw,
> GETDATE()), 101), 101) AS StartDt, CONVERT(datetime, CONVERT(nvarchar,
> GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101) AS EndDt
> from ....
> "Enchantnet" wrote:
> > Hey DAW,
> >
> > Thanks for the code. However, I'm a newbie at this sort of stuff and I get
> > the following error when I entered your code: "ADO error: Incorrect syntax
> > near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
> > could not be completed." Any idea? Do I need to include or define the
> > StartDt and EndDt elsewhere?
> > --
> > Enchantnet
> >
> >
> > "daw" wrote:
> >
> > > I used the following statement to run a report for the previous week, from
> > > Saturday to Friday:
> > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> > > 101), 101) AS StartDt,
> > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> > > 101), 101) AS EndDt
> > >
> > > You could probably use a variation of this for your monthly report. I hope
> > > this helps!
> > >
> > > "Enchantnet" wrote:
> > >
> > > > I have a report that charts our weekly sales and monthly sales. I use the
> > > > following SQL statement to reflect 7 days or 30 days, which works but not
> > > > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > > > report, and from the 1st of the month to the end of the month, regardless of
> > > > the number of days in the month. How do I modfify my statement to do this?
> > > > Here's my SQL statement for the 30-day or monthly report:
> > > >
> > > > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > > > (*) AS TOT
> > > > FROM Sysadm.receipt
> > > > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > > > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > > > 101), 101))
> > > > GROUP BY receipt_date, receipt_no, stamp
> > > > ORDER BY receipt_date
> > > > --
> > > > Enchantnet|||Perfect! Thank u, Thank u, Thank u!!!!!
--
Enchantnet
"daw" wrote:
> Sorry, I had that wrong as far as what you need. Try this:
> select ....
> from...
> where date between CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 -
> DATEPART(dw, GETDATE()), 101), 101) AND CONVERT(datetime, CONVERT(nvarchar,
> GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101)
> "daw" wrote:
> > I'm not sure what that particular error means, but your statement should look
> > something like:
> >
> > select CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw,
> > GETDATE()), 101), 101) AS StartDt, CONVERT(datetime, CONVERT(nvarchar,
> > GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101) AS EndDt
> > from ....
> >
> > "Enchantnet" wrote:
> >
> > > Hey DAW,
> > >
> > > Thanks for the code. However, I'm a newbie at this sort of stuff and I get
> > > the following error when I entered your code: "ADO error: Incorrect syntax
> > > near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
> > > could not be completed." Any idea? Do I need to include or define the
> > > StartDt and EndDt elsewhere?
> > > --
> > > Enchantnet
> > >
> > >
> > > "daw" wrote:
> > >
> > > > I used the following statement to run a report for the previous week, from
> > > > Saturday to Friday:
> > > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> > > > 101), 101) AS StartDt,
> > > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> > > > 101), 101) AS EndDt
> > > >
> > > > You could probably use a variation of this for your monthly report. I hope
> > > > this helps!
> > > >
> > > > "Enchantnet" wrote:
> > > >
> > > > > I have a report that charts our weekly sales and monthly sales. I use the
> > > > > following SQL statement to reflect 7 days or 30 days, which works but not
> > > > > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > > > > report, and from the 1st of the month to the end of the month, regardless of
> > > > > the number of days in the month. How do I modfify my statement to do this?
> > > > > Here's my SQL statement for the 30-day or monthly report:
> > > > >
> > > > > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > > > > (*) AS TOT
> > > > > FROM Sysadm.receipt
> > > > > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > > > > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > > > > 101), 101))
> > > > > GROUP BY receipt_date, receipt_no, stamp
> > > > > ORDER BY receipt_date
> > > > > --
> > > > > Enchantnet

Monthly report query

Someone please shed some light on how to write a select statement that will only pull out a bunch of records belongs only to a certain month. The field(sys_date) that keeps track of each record is a datatime field. Let's say that I need to select all the records starting from 03/01/2007 to 03/31/2007 at the end of March. I can't hardcode the dates because this report is scheduled to run at the end of every month via a DTS job in Sql 2000. Please help out. Thanks.

blumonde

Found the solution. Just in case anyone needs it:

Where (DATEPART(Month, sys_date) =
DATEPART(Month, GETDATE())) And (DATEPART(Year, sys_date) = DATEPART(Year,
GETDATE()))

Hope that helps.

Month value in SQL

Hi all

Does anyone know how to get the month field from a date in a SQL statement? I am using Oracle 8.0

There is a field called "birth_date" in my table and I have to print the number of records group by month as follows:

Month Count
--- ---
1 45
2 187
3 18
. ..
. ..
. ..
etc

I need the month value in 1-12 format. Please help..

-Chinnaselect month(birth_date),count(*)
from yourtable
group by month(birth_date)

rudy|||There is not "month" function in Oracle!
that shoulde be:
select to_char(brith_day,'mm'),count(*) from yourtable group by to_char(brith_day,'mm')|||select to_char(sysdate,'MON') from dual;

will return today's month|||MONTH() is ANSI/ISO standard sql

my apologies, Chinna, i overlooked the fact that you said oracle 8

oracle corp, in its infinite wisdom, apparently did not begin supporting standard sql until release 9

:(

Month Name

The SQL statement below works to provide the number of help desk requests submitted per month for help_company_id = 1.

The problem is that the months are return as numbers (1 for January). I tried using the DATENAME function but all the months were return as January. This is how I tried to use it: SELECT DATENAME(month, MONTH(help_date_submitted)) AS Month, COUNT(help_request_id) AS Requests)

How do I convert the month numbers to month names?

SELECT MONTH(help_date_submitted) AS Month, COUNT(help_request_id) AS Requests
FROM help_requests
WHERE (help_company_id = 1)
GROUP BY MONTH(help_date_submitted)
ORDER BY Month

Hi,

You should pass the current date to the DATENAME function as follows:

SELECT DATENAME(mm, GETDATE())

|||

Assuming that this application does not need to be localized, and the SQL Server is running in the language you want it output then:

SELECT DATENAME(month, help_date_submitted) AS Month, COUNT(help_request_id) AS Requests

will work for you.

|||

When I try SELECT DATENAME(month, help_date_submitted) AS Month, COUNT(help_request_id) AS Requests the following error is returned:

Column help_requests.help_date_submited is invalid in the list because it is not contained in either an aggregate function or the group by clause.

|||

SELECT MONTH(help_date_submitted) AS MonthNo,MIN(DATENAME(month,help_date_submitted)) AS Month, COUNT(help_request_id) AS Requests
FROM help_requests
WHERE (help_company_id = 1)
GROUP BY MONTH(help_date_submitted)
ORDER BY MonthNo

Not exactly straight forward, but it'll work. Problem is that SQL Server doesn't see the month number (returned by the month function), and the month name being related. So you have to play some games with it, especially since you want it ordered by the month number rather than the month name.

|||

Actually, as long as you focus on the grouped values you can do it. Here is a sample query to demonstrate:

1select2datename(3month4 ,dateadd(5month6 ,month( crdate ) - 17 , 0 ) ) [Month]8 ,count(*) CountOfMonth9from10sysobjects11group by12 month( crdate )13order by14 month( crdate )

So, using this logic for the user's query you would have

1select2datename(3month4 ,dateadd(5month6 ,month( hr.help_date_submitted ) - 17 , 0 ) ) [Month]8 ,count( hr.help_request_id ) Requests9from10 help_requests hr11where12 hr.help_company_id = 113group by14 month( hr.help_date_submitted )15order by16 month( hr.help_date_submitted )

A couple of other points - I am sure you know, but this does group multiple years into each month. Is that the desired behavior? ( Just asking ).

Monster select statement revisited

I have a database with a date of birth. From this, I need to select records that include a large number of variables including records where an individual is older than a selected age and/or younger than a selected age, and even worse, of a given astrological sign if one is selected.

I'm currently using a strongly typed dataset. Is this something that's do-able with a dataset, or should I build the select string on the form programmatically? I have an ASP site that I'm re-writing in ASP.NET 2.0. In the ASP site the select string includes the code below. I just don't see how I can do this in a dataset.

if (strLowerAge <> 18) or (strUpperAge <> 99) then
If (strLowerAge <> "") Then
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') >= " & strLowerAge & ")"
End If
If (strUpperAge <> "") Then
If (strSQL <> "") Then
strSQL = strSQL & " AND (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")"
End If
end if
End If
If (strAstSign <> "") Then
Select Case strAstSign
Case "Aries 3/21 - 4/19"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 3 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 4 AND DATEPART(d,[DATE OF BIRTH]) < 20 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m, [DATE OF BIRTH]) = 3 AND DATEPART(d, [DATE OF BIRTH]) > 20 ) OR (DATEPART(m, [DATE OF BIRTH]) = 4 AND DATEPART(d, [DATE OF BIRTH]) < 20 )"
End If
Case "Taurus 4/20 - 5/20"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 4 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) < 21 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 4 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) < 21 )"
End If
Case "Gemini 5/21 - 6/20"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) < 21 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) < 21 )"
End If
Case "Cancer 6/21 - 7/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Leo 7/23 - 8/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Virgo 8/23 - 9/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Libra 9/23 - 10/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Scorpio 10/23 - 11/21"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) < 22 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) < 22 )"
End If
Case "Sagittarius 11/22 - 12/21"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) < 22 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) < 22 )"
End If
Case "Capricorn 12/22 - 1/19"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) < 20 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) < 20 )"
End If
Case "Aquarius 1/20 - 2/18"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) < 19 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) < 19 )"
End If
Case "Pisces 2/19 - 3/20"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) > 18 ) OR (DATEPART(m,[DATE OF BIRTH]) = 3 AND DATEPART(d,[DATE OF BIRTH]) < 21 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) > 18 ) OR (DATEPART(m,[DATE OF BIRTH]) = 3 AND DATEPART(d,[DATE OF BIRTH]) < 21 )"
End If
End Select
END IF

Diane

I have made sql statements like this, and they are a pain in the butt. I cannot comment on how to do this in a dataset in ASP.NET 2.0, but this is how I would do it:

Firstly, I would use a stringBuilder to create this string. It is more effiecient that a string.

Secondly, I see that you have a lot of If (strSQL <> "") Then ... Else ... End If statements. To combat this, I make sure that the strSQL is not empty in the first place. This way you do not need to check if it is empty or not. In your case, I would put the following at the top:

Dim strSQL as new StringBuilder("SELECT .... FROM members WHERE NOT [FIRST NAME] IN NULL ")

Then all you have to do is append to this every time e.g:

Dim strSQL as new StringBuilder("SELECT " & _" [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE]" & _" FROM members " & _" WHERE NOT [FIRST NAME] IS NULL" )If (strLowerAge <> 18) or (strUpperAge <> 99) then If (strLowerAge <> "") Then strSQL.append(" AND (DateDiff(yyyy, [DATE OF BIRTH],'" & date & "') >= " & strLowerAge & ")")End If If (strUpperAge <>"")Then strSQL.append(" AND (DateDiff(yyyy, [DATE OF BIRTH],'" & date & "') <= " & strUpperAge & ")")End ifEnd If

That should make you code a little shorter and easier to handle. To get the finall string, just use Dim mySQL as String = strSQL.toString().

If you really want to make it effiecient, the I would do all of this in the database as a stored procedure. If you are using SQL Server, you can build SQL statements as string and then execute them within a stored procedure. e.g.

CREATE PROCEDURE mySQLBuilder (@.sdtDOBsmalldatetime@.otherVariableint@.vSortColumn@.vSortDirection@.vWhereStr)ASDECLARE @.sqlvarchar(1000)SET @.sql ='SELECT ... FROM ...' + @.vWhereStr +' ORDER BY ' + @.vSortColumn +' ' + @.vSortDirectionEXEC (@.sql)GO

Just treat the stored procedure as you would a select statement.

HTH

Jag

|||

Thank you Jag, you gave me a lot of useful information.

I have never used a stored procedure. It sounds like I would have the same problem though that I would have with the dataset - the astrology sign which is not in the database and needs to be calculated. Unless I have 12 different procedures, 1 per sign, I need to some how include an 'if then' in the SELECT statement. Is that doable?

It gets even better. Currently, in order to give the members better exposure, I have the order clause dependent on the day of the week. One day O oder by last name, another by first name, another by date of birth, another by state, etc. So I would need 84 different procedures. Right?

Diane

|||

As you haven't used stored procedures before, I would leave that for now and go the way you are going. You can do 'if then' statements in stored procedures and use them to build a select statement (like you did in VB), but you should start off with easier things than this.

To learn about stored procedures, just do a google search for 'tutorial stored procedures'. You should get some good ones. They are mostly used for database input, but you can use them for data retrieval as well. They are more efficient that adhoc SQL statements as they are compiled in the SQL database.

Jag

Monitoring Transaction Time

How do I monitor how long a transaction/SQL Statement
takes in SQL Profiler '. What is the unit of the
Duration? sec or ms ?
Thanks.The unit of duration in SQL Profile is milliseconds.
The duration is the total duration of the transaction, form the moment the
request from the client came in until the moment that the receipt of the
last data has been acknowledged by the client. If you just want the
processor time, look at the CPU column in your profiler trace.
--
Jacco Schalkwijk
SQL Server MVP
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
> How do I monitor how long a transaction/SQL Statement
> takes in SQL Profiler '. What is the unit of the
> Duration? sec or ms ?
> Thanks.|||No. I just want to see how long each transaction/SQL
statement takes.
Thanks.
>--Original Message--
>The unit of duration in SQL Profile is milliseconds.
>The duration is the total duration of the transaction,
form the moment the
>request from the client came in until the moment that the
receipt of the
>last data has been acknowledged by the client. If you
just want the
>processor time, look at the CPU column in your profiler
trace.
>--
>Jacco Schalkwijk
>SQL Server MVP
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
>> How do I monitor how long a transaction/SQL Statement
>> takes in SQL Profiler '. What is the unit of the
>> Duration? sec or ms ?
>> Thanks.
>
>.
>|||As a standard I always put start time and end time as the first two columns in my traces, this helps me do other things too
Hope this helpssql

Monitoring sql statement ran against a table

I need to know if there is a way to either record all queries ran again
a particular table or to fire off a trigger if a particualr row is
selected from that table in sql server 2005. The main issue I am
having is "select" statements. The reason I am attempting to do this is
its full fill part of some requirements for a project. I was hoping to
do this with sql server and not a third party solution if possible.
Thanks for any help,
Marty
--[vbcol=seagreen]
A free community forum for resolving all of your high tech issues.maznblue@.gmail.com wrote:

> I need to know if there is a way to either record all queries ran again
> a particular table or to fire off a trigger if a particualr row is
> selected from that table in sql server 2005. The main issue I am
> having is "select" statements. The reason I am attempting to do this is
> its full fill part of some requirements for a project. I was hoping to
> do this with sql server and not a third party solution if possible.
> Thanks for any help,
> Marty
> --
> A free community forum for resolving all of your high tech issues.
Use profiler
You can filter data as per your requirement.
use sp:stmtcompleted , sp:completed events and objecttype , objectname
column, which will show you details about triggers when event will be
triggered
Regards
Amish Shah|||Marty,
Try using SQL Profiler and put a filter on TextData column like
'SELECT%XXXX%' where XXXXis the table which you are looking for..
Jayesh
<maznblue@.gmail.com> wrote in message
news:1149817748.504429.74260@.i40g2000cwc.googlegroups.com...
>I need to know if there is a way to either record all queries ran again
> a particular table or to fire off a trigger if a particualr row is
> selected from that table in sql server 2005. The main issue I am
> having is "select" statements. The reason I am attempting to do this is
> its full fill part of some requirements for a project. I was hoping to
> do this with sql server and not a third party solution if possible.
> Thanks for any help,
> Marty
> --
> A free community forum for resolving all of your high tech issues.
>|||Jayesh -
Thanks for the info, it worked like a champ.
Marty
Jayesh Antony Jose wrote:[vbcol=seagreen]
> Marty,
> Try using SQL Profiler and put a filter on TextData column like
> 'SELECT%XXXX%' where XXXXis the table which you are looking for..
> Jayesh
>
> <maznblue@.gmail.com> wrote in message
> news:1149817748.504429.74260@.i40g2000cwc.googlegroups.com...

Monitoring sql statement ran against a table

I need to know if there is a way to either record all queries ran again
a particular table or to fire off a trigger if a particualr row is
selected from that table in sql server 2005. The main issue I am
having is "select" statements. The reason I am attempting to do this is
its full fill part of some requirements for a project. I was hoping to
do this with sql server and not a third party solution if possible.
Thanks for any help,
Marty
--
>> www.techmentors.net <<<<<
A free community forum for resolving all of your high tech issues.maznblue@.gmail.com wrote:
> I need to know if there is a way to either record all queries ran again
> a particular table or to fire off a trigger if a particualr row is
> selected from that table in sql server 2005. The main issue I am
> having is "select" statements. The reason I am attempting to do this is
> its full fill part of some requirements for a project. I was hoping to
> do this with sql server and not a third party solution if possible.
> Thanks for any help,
> Marty
> --
> >> www.techmentors.net <<<<<
> A free community forum for resolving all of your high tech issues.
Use profiler
You can filter data as per your requirement.
use sp:stmtcompleted , sp:completed events and objecttype , objectname
column, which will show you details about triggers when event will be
triggered
Regards
Amish Shah|||Marty,
Try using SQL Profiler and put a filter on TextData column like
'SELECT%XXXX%' where XXXXis the table which you are looking for..
Jayesh
<maznblue@.gmail.com> wrote in message
news:1149817748.504429.74260@.i40g2000cwc.googlegroups.com...
>I need to know if there is a way to either record all queries ran again
> a particular table or to fire off a trigger if a particualr row is
> selected from that table in sql server 2005. The main issue I am
> having is "select" statements. The reason I am attempting to do this is
> its full fill part of some requirements for a project. I was hoping to
> do this with sql server and not a third party solution if possible.
> Thanks for any help,
> Marty
> --
>> www.techmentors.net <<<<<
> A free community forum for resolving all of your high tech issues.
>|||Jayesh -
Thanks for the info, it worked like a champ.
Marty
Jayesh Antony Jose wrote:
> Marty,
> Try using SQL Profiler and put a filter on TextData column like
> 'SELECT%XXXX%' where XXXXis the table which you are looking for..
> Jayesh
>
> <maznblue@.gmail.com> wrote in message
> news:1149817748.504429.74260@.i40g2000cwc.googlegroups.com...
> >I need to know if there is a way to either record all queries ran again
> > a particular table or to fire off a trigger if a particualr row is
> > selected from that table in sql server 2005. The main issue I am
> > having is "select" statements. The reason I am attempting to do this is
> > its full fill part of some requirements for a project. I was hoping to
> > do this with sql server and not a third party solution if possible.
> >
> > Thanks for any help,
> >
> > Marty
> >
> > --
> >> www.techmentors.net <<<<<
> > A free community forum for resolving all of your high tech issues.
> >