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. 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 $500Twobridge (Twobridge@.gmail.com) writes:
Quote:
Originally Posted by
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.
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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:
Quote:
Originally Posted by
Twobridge (Twobridge@.gmail.com) writes:
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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:
Quote:
Originally Posted by
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:
Quote:
Originally Posted by
Twobridge (Twobridge@.gmail.com) writes:
Quote:
Originally Posted by
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.
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
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:
Quote:
Originally Posted by
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:
Quote:
Originally Posted by
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:
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Quote:
Originally Posted by
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/.
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
Quote:
Originally Posted by
Twobridge (Twobridge@.gmail.com) writes:
Quote:
Originally Posted by
Quote:
Originally Posted by
>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.
Quote:
Originally Posted by
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.
Surely "first 12 months" ought to be followed by "second 12 months"
etc., and so
datediff(month, bill.fileDate, transaction.payDate)/ 12 * 12
should be changed to
(datediff(month, bill.fileDate, transaction.payDate)+11)/ 12 * 12
No comments:
Post a Comment