|
|
Posted by Twobridge on 11/12/06 19:16
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.
Twobridge wrote:
> 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]
|