Developer Forum

Thanks to all for your help. I apologize but I failed to mention that I
will not know the dates in advance. If I did, it wouldn't be too
difficult to create the query as you all have indicated.

However, the dates will be selected by the user in an HTML form, and
passed to query from the HTML form. The dates will be coded in the
WHERE statement as:
WHERE sales_date BETWEEN '#date1#' and '#date2#'

And, this is where I need the help. Not sure if creating a loop, or
array, or some way to make this "dynamic." I can't find any SQL
examples anywhere, that show how to do this with dynamic dates (or
dynamic SQL).

Any advice, tips, URLs, etc., would be greatly appreciated. Again,
sorry for not making this more clear. I assumed the answer would work
for fixed or dynamic dates. Many thanks again.

jj
=======

f you use my version of the query, you can use parameters for start date
and end date. The DATEDIFF () looks only at the start date and the WHERE
clause limits the range to between the start and end dates. The column
names would be constant. Your ASP code should be able to translate the
Day1 - Day7 column names to the appropriate values for you.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


"jj" < XXXX@XXXXX.COM > wrote in message
news:9apvc.9330$ XXXX@XXXXX.COM ...
Thanks to all for your help. I apologize but I failed to mention that I
will not know the dates in advance. If I did, it wouldn't be too
difficult to create the query as you all have indicated.

However, the dates will be selected by the user in an HTML form, and
passed to query from the HTML form. The dates will be coded in the
WHERE statement as:
WHERE sales_date BETWEEN '#date1#' and '#date2#'

And, this is where I need the help. Not sure if creating a loop, or
array, or some way to make this "dynamic." I can't find any SQL
examples anywhere, that show how to do this with dynamic dates (or
dynamic SQL).

Any advice, tips, URLs, etc., would be greatly appreciated. Again,
sorry for not making this more clear. I assumed the answer would work
for fixed or dynamic dates. Many thanks again.

jj
=======
Adam Machanic wrote:

you'll

Thank you Tom for the tips. Somehow I thought it would be more
complicated than that (the old "forest for the trees" thing). I'll give
it a try. What would be great in future versions of T-SQL would be
allowing for something like this in the GROUP BY statement:

Example: GROUP BY store_num * sales_date

The "*" <asterisk> tells the query to do a cross tab grouping, with the
item to the left of the asterisk as the "row" field, and the item to the
right of the asterisk as the "column" field. Maybe one day, T-SQL will
have something this simple to code.

Again, many thanks for your help. FYI, I think I told you I bought one
of your books (Advanced T-SQL for SQL Server 2K). Excellent resource.
Sometimes it doesn't have the examples I'm looking for (as in the
question posed on this thread). However, I use it frequently and it has
been a great resource. Everyone has their own method of learning that
works best for them. In my case, looking at code, and code examples
with explanations of what's going on, works best for me. That's why
your book is so helpful.

jj
============

Thank you, kind sir! In the next release of SQL Server - SQL Server 2005,
code-named Yukon - you will have the new PIVOT feature, which could help in
situations like this. Stay tuned!

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql




Thank you Tom for the tips. Somehow I thought it would be more
complicated than that (the old "forest for the trees" thing). I'll give
it a try. What would be great in future versions of T-SQL would be
allowing for something like this in the GROUP BY statement:

Example: GROUP BY store_num * sales_date

The "*" <asterisk> tells the query to do a cross tab grouping, with the
item to the left of the asterisk as the "row" field, and the item to the
right of the asterisk as the "column" field. Maybe one day, T-SQL will
have something this simple to code.

Again, many thanks for your help. FYI, I think I told you I bought one
of your books (Advanced T-SQL for SQL Server 2K). Excellent resource.
Sometimes it doesn't have the examples I'm looking for (as in the
question posed on this thread). However, I use it frequently and it has
been a great resource. Everyone has their own method of learning that
works best for them. In my case, looking at code, and code examples
with explanations of what's going on, works best for me. That's why
your book is so helpful.

jj
============

Sounds great. I just hope my company decides to buy it. They tend to
wait a few years after a new product is introduced before they start
deploying it.

By the way, I was able to get the pivot-like query to work. At first, I
could only get it to work for a 7-day (or any XX-day period, but it had
to be for a specific number of days.

Then, I got it to work for any date range, as long as it didn't exceed
whatever number of days I decided to limit the program to. I was able
to do this, by putting a CF (ColdFusion....I use that versus ASP) array
and loop in the QUERY's SELECT statement that returns the "AS Day1, AS
Day2, AS Day3, etc..) up to the number of days in the datediff()
function which takes the user's start/end dates as parameters.

Used another CF array and loop to get the column headers to print the
exact/correct date above each column.

But had a little problem with printing the output results...in getting
the query variables: Day1, Day2, etc.. to resolve. Was finally able to
do it, but the code is not as efficient as I'd like. I would still
prefer to allow the program to determine the # of days to process,
without any limits (although for screen-space sake, I would put a limit
of probably 31 days on this particular report).

I was hoping I could use an SQLServer array versus a ColdFusion array in
the QUERY. And use a DECLARE statement to create a temp variable for
the start of the array index, and use a WHILE loop to process the "As
Day1, As Day2, etc." values.

But, I couldn't find anything about arrays in your book, or in the
book's TOC or index. SQL Server Books Online didn't seem to have much
on arrays, other than in user-defined functions. I think if I could get
my "As Day1, As Day2, As Day3, etc.." query variables stored in an array
(e.g., As Day[i]), I might have an easier time of resolving the
variables when outputting the query results to a report, using a LOOP.

Oh well. Will keep at it. At least I was able to get it working well
enough to soon make this a production-level report for the company. I
can use this technique to create many new production-level reports in a
variety of areas that I didn't previously have the knowledge to do. Am
looking forward to it.

Many thanks again. With your help, and the help of others on this
newsgroup, my "customers" will be able to analyze data in a new and
helpful way. You all may not realize the "end results" of your
newsgroup postings, but the end result is often (and in my case) that
thousands of people are going to be pleased with their new reports. I
just felt you should know.

jj (a.k.a. Gary)
=======

hanx, again. You may be interested in fellow MVP Erland Sommarskog's
website:

http://www.sommarskog.se/

My book covers pivot tables (Ch 4, pp 116 - 118), but if you need to have
variable numbers of columns, and if you are committed to doing this entirely
within T-SQL, then you're looking at dynamic SQL. SQL Server doesn't
support arrays - you should be thinking tables, not arrays. As long as the
number of columns is fixed, the pivot table approach I gave you will be
sufficient. Just change the start and end dates. You will, as you noted,
get the same column names every time. However, a good client tool, such as
Reporting Services, or any front-end programming language like VB can
translate the column headers to anything you like. (ASP can do that.)
Indeed, this is part of the role of the front end.

Another idea that came to mind - if you need more than 7 days - is to
include the week number in the output and simply limit yourself to 7 days
*per row*. This way, if you're covering a 2-week period, you'll get two
rows per store - one for each week. Just something to keep in mind.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"jj" < XXXX@XXXXX.COM > wrote in message
news:rTGwc.624$ XXXX@XXXXX.COM ...
Tom Moreau wrote:
in

Sounds great. I just hope my company decides to buy it. They tend to
wait a few years after a new product is introduced before they start
deploying it.

By the way, I was able to get the pivot-like query to work. At first, I
could only get it to work for a 7-day (or any XX-day period, but it had
to be for a specific number of days.

Then, I got it to work for any date range, as long as it didn't exceed
whatever number of days I decided to limit the program to. I was able
to do this, by putting a CF (ColdFusion....I use that versus ASP) array
and loop in the QUERY's SELECT statement that returns the "AS Day1, AS
Day2, AS Day3, etc..) up to the number of days in the datediff()
function which takes the user's start/end dates as parameters.

Used another CF array and loop to get the column headers to print the
exact/correct date above each column.

But had a little problem with printing the output results...in getting
the query variables: Day1, Day2, etc.. to resolve. Was finally able to
do it, but the code is not as efficient as I'd like. I would still
prefer to allow the program to determine the # of days to process,
without any limits (although for screen-space sake, I would put a limit
of probably 31 days on this particular report).

I was hoping I could use an SQLServer array versus a ColdFusion array in
the QUERY. And use a DECLARE statement to create a temp variable for
the start of the array index, and use a WHILE loop to process the "As
Day1, As Day2, etc." values.

But, I couldn't find anything about arrays in your book, or in the
book's TOC or index. SQL Server Books Online didn't seem to have much
on arrays, other than in user-defined functions. I think if I could get
my "As Day1, As Day2, As Day3, etc.." query variables stored in an array
(e.g., As Day[i]), I might have an easier time of resolving the
variables when outputting the query results to a report, using a LOOP.

Oh well. Will keep at it. At least I was able to get it working well
enough to soo

You can lead a horse to water to but you can't make him drink :)

For those willing to take a drink, you can solve this problem
very easily in RAC using the @rank parameter.No messy
coding necessary.

www.rac4sql.net