Posted by Erland Sommarskog on 11/12/06 21:24

Twobridge (Twobridge@gmail.com) writes:
> I apologize for not getting back to you sooner, I do appreciate your
> help. My database tables look like this
>
> Bill Table
> - billNumber
> - fileDate
>
> Transaction Table
> - billNumber
> - payDate
> - amount
>
> Currently I am trying this sql statement
>
> SELECT SUM(Transaction.amount) AS [Amount Paid],
> YEAR(Bill.fileDate) AS [Date Filed], YEAR(Transaction.payDate) AS
> [Year Paid]
> FROM Bill LEFT OUTER JOIN
> Transaction ON Bill.billNumber =
> Transaction.billNumber
> GROUP BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)
> HAVING (YEAR(Bill.fileDate) BETWEEN 1985 AND 1990) AND
> (YEAR(Transaction.payDate) BETWEEN 1985 AND 1990)
> ORDER BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)
>
> You mentioned just simply naming the columns, the problem with that
> solution is that the number of columns are different each
> time...depending on the user query. My user can query by year. In the
> statement I currently just hard coded in a year to test.

If the number of columns or the column names are different depending
on user input, the query must be constructed dynamically. A query in
SQL returns a fixed number of columns with fixed names. This because
SELECT returns a table, and a table is supposed to describe an entity
with a fixed set of attributues.

Since it appears that you want to run a crosstab, you should have a look
at RAC, a third-party which is good for that sort of things. See
http://www.rac4sql.com/.

> Sorry let me clarify....if a bill's file date is 1/1/1985 and is paid
> on 3/3/1985 this causes the datediff function to produce a 3/12 * 12 in
> my problem.....3/ 12 = 0 * 12....so now my column heading has a value
> of 0 when in reality it should be grouped as a payment made in the
> first 12 months. I hope this makes since.

I'm afraid that I did not understand much. It seems that you have not
decided what output your query should give in this case, and this is
nothing we can assist you with in a newsgroup.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]