Background:
I am developing a website using MS Visual Studio 2005 and SQL 2005 Express.
I'm using SQL queries (not stored procedures yet).
Problem:
I have a query that I can't make work. I found the COALESCE function and it
solves part of the problem but not all.
I have a variable '@.filter_by_date' which is bound to a dropdownlist on the
webpage. The dropdownlist's values are NULL, -7, -14, -30 which represent th
e
number of days to subtract from today's date.
If a user selects the NULL option the query should return all rows which is
how my query is:
SELECT * FROM t_jobs WHERE (created_date >= COALESCE(@.filter_by_date,
created_date))
The problem arises when the user selects any of the other options. If the
user selects -7, for example, then this value should be subtracted from the
current date and used in the query to only show jobs created after that date
.
The query looks like this:
SELECT * FROM t_jobs WHERE (created_date >= GETDATE() -@.filter_by_date)
Is it possible to merge the two queries? I thought of trying to use an 'IF'
in the 'WHERE' clause but I can't get it to work.
Thank you for your time. Hope you can help.You'll probably get better performance with two separate queries, each in
its own stored proc. However, you can try:
SELECT
*
FROM
t_jobs
WHERE
@.filter_by_date IS NULL
OR (@.filter_by_date IS NOT NULL
AND created_date >= GETDATE() -@.filter_by_date))
(assumes @.filter_by_date is a positive number.)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Matthew Hill" <MatthewHill@.discussions.microsoft.com> wrote in message
news:6AA25C85-C8B9-48FB-8AE6-8850E0A03161@.microsoft.com...
> Background:
> I am developing a website using MS Visual Studio 2005 and SQL 2005
> Express.
> I'm using SQL queries (not stored procedures yet).
> Problem:
> I have a query that I can't make work. I found the COALESCE function and
> it
> solves part of the problem but not all.
> I have a variable '@.filter_by_date' which is bound to a dropdownlist on
> the
> webpage. The dropdownlist's values are NULL, -7, -14, -30 which represent
> the
> number of days to subtract from today's date.
> If a user selects the NULL option the query should return all rows which
> is
> how my query is:
> SELECT * FROM t_jobs WHERE (created_date >= COALESCE(@.filter_by_date,
> created_date))
> The problem arises when the user selects any of the other options. If the
> user selects -7, for example, then this value should be subtracted from
> the
> current date and used in the query to only show jobs created after that
> date.
> The query looks like this:
> SELECT * FROM t_jobs WHERE (created_date >= GETDATE() -@.filter_by_date)
> Is it possible to merge the two queries? I thought of trying to use an
> 'IF'
> in the 'WHERE' clause but I can't get it to work.
> Thank you for your time. Hope you can help.
>|||Thanks for the help. I implemented your querry into mine but when I go out o
f
and back into the Query Builder the query has changed. My other comparisons
in the WHERE clause have been duplicated for each side of the OR operator. I
s
this normal?
"Tom Moreau" wrote:
> You'll probably get better performance with two separate queries, each in
> its own stored proc. However, you can try:
> SELECT
> *
> FROM
> t_jobs
> WHERE
> @.filter_by_date IS NULL
> OR (@.filter_by_date IS NOT NULL
> AND created_date >= GETDATE() -@.filter_by_date))
> (assumes @.filter_by_date is a positive number.)
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Matthew Hill" <MatthewHill@.discussions.microsoft.com> wrote in message
> news:6AA25C85-C8B9-48FB-8AE6-8850E0A03161@.microsoft.com...
>
>|||Don't use the query builder. Use Query Analyzer (QA).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Matthew Hill" <MatthewHill@.discussions.microsoft.com> wrote in message
news:A944E1F3-C855-441C-8F90-F4ED600E33F1@.microsoft.com...
> Thanks for the help. I implemented your querry into mine but when I go out
> of
> and back into the Query Builder the query has changed. My other
> comparisons
> in the WHERE clause have been duplicated for each side of the OR operator.
> Is
> this normal?
> "Tom Moreau" wrote:
>|||Thank you for your prompt replies, much appreciated.
Ok, stupid question but here goes. Is QA in SQL Server? I'm not using SQL
Server yet, waiting until my Visual Studio 2005 Pro turns up (any day now)
before I dable into yet another app.
Mat
"Tom Moreau" wrote:
> Don't use the query builder. Use Query Analyzer (QA).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Matthew Hill" <MatthewHill@.discussions.microsoft.com> wrote in message
> news:A944E1F3-C855-441C-8F90-F4ED600E33F1@.microsoft.com...
>
>|||If you have SQL Server 2000, that's part of the tools that ship with it. If
you have SQL Server 2005, then you use SQL Server Management Studio (SSMS),
that ships with SQL Server 2005.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Matthew Hill" <MatthewHill@.discussions.microsoft.com> wrote in message
news:476B52A3-1188-46DE-AF3F-B905475B1A1D@.microsoft.com...
Thank you for your prompt replies, much appreciated.
Ok, stupid question but here goes. Is QA in SQL Server? I'm not using SQL
Server yet, waiting until my Visual Studio 2005 Pro turns up (any day now)
before I dable into yet another app.
Mat
"Tom Moreau" wrote:
> Don't use the query builder. Use Query Analyzer (QA).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Matthew Hill" <MatthewHill@.discussions.microsoft.com> wrote in message
> news:A944E1F3-C855-441C-8F90-F4ED600E33F1@.microsoft.com...
>
>
