|
|
Posted by Twobridge on 11/12/06 17:08
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.
Erland Sommarskog wrote:
> Twobridge (Twobridge@gmail.com) writes:
> > I hope someone can help me out with my problem.
> > I have found a sql statement that basically pulls all bills filed
> > within a
> > certain time period and the payments made on those bills with in the
> > same time period. I group the payments by payment year and filed year
> > which gives me a matrix with the filed year as the row and the pay
> > year
> > as the column....and this appears fine. My problem is that my employer
> >
> > does not want to see the payment year as the column header. He would
> > like to see the number of months from the bill year in sequence of 12
> > months periods.
>
> The column names in a query are fixed, so you would just name the columns
> 12, 24, 36 etc.
>
> Sorry, that is not a very good answer, but not knowing your query or
> anything it's the best I can say.
>
> > Also I have run into a problem that if I narrow down
> > my search to display only bills from a certain department. We may have
> >
> > a instance where that department will not have any payments for that
> > time period.
> > My employer would like to see a zero in that spot on the matrix. My
> > sql statement
> > would simply skip that year and display the next years value. Any
> > suggestions? Someone else suggested the ISNULL but since there are no
> > payments for that year there would be no null to replace. This is a
> > sample of how they want it displayed
> >
> > 12 24 36
> > 1985 $50 $100 $200
> > 1986 $0 $120 $40
> > 1987 $0 $0 $500
>
> Set up a one-column table with the years (or just get them from the
> base table as (SELECT DISTINCT year FROM tbl) in a derived table.
> The left join that table with the rest.
>
> Here is a query from Northwind to illustrate:
>
> SELECT m.month, nooforders = coalesce(o.cnt, 0)
> FROM (SELECT DISTINCT month = convert(char(6), OrderDate, 112)
> FROM Orders) AS m
> LEFT JOIN (SELECT month = convert(char(6), OrderDate, 112),
> cnt = COUNT(*)
> FROM Orders
> WHERE CustomerID = 'BERGS'
> GROUP BY convert(char(6), OrderDate, 112)) AS o
> ON m.month = o.month
>
> --
> 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
Navigation:
[Reply to this message]
|