Dynamic WHERE Clause

Hi
I have a query which has a few different Time Period columns:
Half_Year (H1,H2)
Quarters (Q1,Q2,Q3,Q4)
Months (M1,M2,M3,... M12)
These periods are held in three difference columns.
I need to run this query with 2 params. One will be the year and other will
be one of the above three:
i.e
sp_Rating 2005, 'H1'
This is all transactions in months 1=6 for the year 2005.
or sp_Rating 2005, 'Q3' or sp_Rating 2005, 'M7'
How can I dynamically interrogate the correct column, based on the param
supplied (H, Q, M) ?
Kind Regards
Ricky
(WIN2K,SQL2K-SP4)Ricky (ricky@.msn.com) writes:
> I have a query which has a few different Time Period columns:
> Half_Year (H1,H2)
> Quarters (Q1,Q2,Q3,Q4)
> Months (M1,M2,M3,... M12)
> These periods are held in three difference columns.
> I need to run this query with 2 params. One will be the year and other
> will be one of the above three:
> i.e
> sp_Rating 2005, 'H1'
> This is all transactions in months 1=6 for the year 2005.
> or sp_Rating 2005, 'Q3' or sp_Rating 2005, 'M7'
> How can I dynamically interrogate the correct column, based on the param
> supplied (H, Q, M) ?
First of all, don't call your procedures sp_something. That prefix is
reserved for system procedures, and SQL Server first looks for a
procedure with such a name in the master database.
As for the question, I'm afraid that I dno't really understand. Does
this table has three columns? May then I ask the stupid question if
H1 can appear together with M7 to M12? I realise that if periodisation
takes place, this can happen. Then again, could a row really have
H1, Q3 and M10?
If you need all three columns try this:
SELECT ...
FROM tbl
WHERE (Halt_year = @.period AND @.period LIKE 'H%')
OR (Quarter = @.period AND @.period LIKE 'Q%')
OR (Months = @.period AND @.period LIKE 'M%')
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|||>> I have a query which has a few different Time Period columns: <<
Really? Mind showing us? Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential Integrity,
data types, etc. in your schema are. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it.
Why? Are they LOGICALLY DIFFERENT? There is usually only one kind of
time in the universe. Try a proper design:
CREATE TABLE PeriodCalendar
(period_name CHAR(15) NOT NULL PRIMARY KEY
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL);
INSERT INTO PeriodCalendar ('Q1-2006', '2006-01-01', '2006-03-341');
etc. for all the possible fiscal, marketing and calendaral periods you
use.
Again, not a good design; you seem to think that a year should be an
integer, while the ANSI standard say it is a CHAR(4); get a copy of the
8601 standards, too. Now life a JOIN and a BETWEEN predicate.
And you have already been told about not using "sp_" prefixes because
they refer to where something is phycially located as well as having a
special meaning in SQL Server.|||Thanks guys for the reply, I'm ashamed to say that I have been using the
'sp_' prefix, I did no know that this was the case, I shall rename mine to
something else today/tomorrow.
Kind Regards
Ricky
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1149463964.082284.170110@.i39g2000cwa.googlegroups.com...
> Really? Mind showing us? Please post DDL, so that people do not have
> to guess what the keys, constraints, Declarative Referential Integrity,
> data types, etc. in your schema are. Sample data is also a good idea,
> along with clear specifications. It is very hard to debug code when
> you do not let us see it.
>
> Why? Are they LOGICALLY DIFFERENT? There is usually only one kind of
> time in the universe. Try a proper design:
> CREATE TABLE PeriodCalendar
> (period_name CHAR(15) NOT NULL PRIMARY KEY
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL);
> INSERT INTO PeriodCalendar ('Q1-2006', '2006-01-01', '2006-03-341');
> etc. for all the possible fiscal, marketing and calendaral periods you
> use.
>
other will be one of the above three: sp_Rating 2005, 'H1' <<
> Again, not a good design; you seem to think that a year should be an
> integer, while the ANSI standard say it is a CHAR(4); get a copy of the
> 8601 standards, too. Now life a JOIN and a BETWEEN predicate.
> And you have already been told about not using "sp_" prefixes because
> they refer to where something is phycially located as well as having a
> special meaning in SQL Server.
>|||That's a mistake that we all make, until someone points out to us the
dangers.
In addition to what --CELKO-- posted, check out this article on calendar
tables, which goes into much more detail.
http://www.aspfaq.com/show.asp?id=2519
"ricky" <ricky@.ricky.com> wrote in message
news:O5wFxWHiGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Thanks guys for the reply, I'm ashamed to say that I have been using the
> 'sp_' prefix, I did no know that this was the case, I shall rename mine to
> something else today/tomorrow.
> Kind Regards
> Ricky
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1149463964.082284.170110@.i39g2000cwa.googlegroups.com...
> other will be one of the above three: sp_Rating 2005, 'H1' <<
>|||Joe,
Just curious, what is the reason for making year a varchar instead of an
integer (in the standard)?
I have always thought of it as an integer that one might add and subtract
from for various date functions (of course leap year can complicate it).
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1149463964.082284.170110@.i39g2000cwa.googlegroups.com...
> Again, not a good design; you seem to think that a year should be an
> integer, while the ANSI standard say it is a CHAR(4); get a copy of the
> 8601 standards, too. Now life a JOIN and a BETWEEN predicate.|||
--CELKO-- wrote:

>Again, not a good design; you seem to think that a year should be an
>integer, while the ANSI standard say it is a CHAR(4); get a copy of the
>8601 standards, too. Now life a JOIN and a BETWEEN predicate.
>
ANSI 8601 is a standard for the "representation" of dates and times.
It's not a standard for how to store them. If you think the ANSI standard
says that a year *is* a char(4), please quote the relevant section of the
standard.
My copy of the standard says only that "In *expressions* of calendar
dates, year is generally *represented* by four digits..." [emphasis mine]
Steve Kass
Drew University
http://www.stevekass.com|||>> Just curious, what is the reason for making year a varchar instead of an
integer (in the standard)? <<
Mostly history. We based the SQL Standard on the pre-existing the
ISO-8601 Standards which is for display; it says nothing about internal
storage. We wanted to avoid anything to do with internal storage, like
specifying the use of numbers for dates.
You can see this with the specs for "EXTRACT(<temporal unit> FROM
<temporal exp)>" and the fact you have to use strings with "INTERVAL
<exp> <temporal unit>"; that is "INTERVAL '12' YEAR" works and
"INTERVAL 12 YEAR" is an error. What newbies will do is use integers
and force a casting in certain places.
Of course, by the time we got something into SQL, each vendor had a
proprietary library and storage method which was exposed to the users.
Rats!!|||
--CELKO-- wrote:

>Mostly history. We based the SQL Standard on the pre-existing the
>ISO-8601 Standards which is for display; it says nothing about internal
>storage. We wanted to avoid anything to do with internal storage, like
>specifying the use of numbers for dates.
>You can see this with the specs for "EXTRACT(<temporal unit> FROM
><temporal exp)>" and the fact you have to use strings with "INTERVAL
><exp> <temporal unit>"; that is "INTERVAL '12' YEAR" works and
>"INTERVAL 12 YEAR" is an error. What newbies will do is use integers
>and force a casting in certain places.
>
The reason the <interval string> is a string is that its domain is not
<integer>. The domain includes '1-4', for example.
This has nothing whatsoever to do with the issue of whether ANSI 8601
says that
a "year is a char(4)" (it does not).
SK

>Of course, by the time we got something into SQL, each vendor had a
>proprietary library and storage method which was exposed to the users.
> Rats!!
>
>|||I apologise if I have caused a disagreement, but is there an issue, if I do
format YEAR as an INT?
"Steve Kass" <skass@.drew.edu> wrote in message
news:uZknxeMiGHA.4080@.TK2MSFTNGP03.phx.gbl...
>
> --CELKO-- wrote:
>
an integer (in the standard)? <<
> The reason the <interval string> is a string is that its domain is not
> <integer>. The domain includes '1-4', for example.
> This has nothing whatsoever to do with the issue of whether ANSI 8601
> says that
> a "year is a char(4)" (it does not).
> SK
>