Re: Need Help With Pivot/Cross-Tab Query Please/ **Dates Are Not Fixed**

From: jj (jjj.nosspam_at_mindspring.com)
Date: 06/06/04 

Date: Sun, 06 Jun 2004 15:37:59 GMT

Tom Moreau wrote:
> 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!
>

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)
=======

Relevant Pages

  • RE: Array Help
    ... Dim strSQL As String ... the Access "INSERT INTO" SQL query is: ... one field is changed for each query, I was thinking that I could loop though ... I am not sure how to do a loop through the array or write any code for it as ...
    (microsoft.public.access.modulesdaovba)
  • Re: Save report letters to file
    ... I was beginning to think i would have to resort to VBA. ... ...save report for individual record ... first of all how do i loop through the records of a query ...
    (microsoft.public.access.macros)
  • Re: How do I use an array for a report recordsource in Access?
    ... Create a Query based on the selection. ... start on what you need to get the data for your report... ... > populates an array with the customer's request. ... >> Toews site for the example of creating a temporary database to contain ...
    (microsoft.public.access.reports)
  • RE: Sending Email to multiple recipients
    ... I am using a query to gather the recipients info ... with the account number and email addresses of my report recipients. ... Then I loop through the recordset, ...
    (microsoft.public.access.formscoding)
  • Re: Need Help With Pivot/Cross-Tab Query Please/ **Dates Are Not Fixed**
    ... SQL Server MVP ... and loop in the QUERY's SELECT statement that returns the "AS Day1, ... Used another CF array and loop to get the column headers to print the ... variables when outputting the query results to a report, ...
    (microsoft.public.sqlserver.programming)
Loading