|
|
Posted by Erland Sommarskog on 11/08/06 23:08
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]
|