|
|
Posted by Twobridge on 11/12/06 19:06
I have found a way to generate the number for the columns by using
datediff(month, bill.fileDate, transaction.payDate)/ 12 * 12 and I
simply do a order by.....my problem now is when a payment is made
before a 12 month marker. This causes the datediff to return a 0/12 *
12 causing my report to produce a "0" column. I will be working on it
some more ...i will keep to up on any progress that i make.
Twobridge wrote:
> 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]
|