Actuarial sql statement Need Help.

**Erland Sommarskog** · Nov 8 '06, 10:45 PM

Re: Actuarial sql statement Need Help.

Twobridge (Twobridge@gmai l.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@sommarsk og.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

**Twobridge** · Nov 12 '06, 04:45 PM

Re: Actuarial sql statement Need Help.

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.fileD ate) AS [Date Filed], YEAR(Transactio n.payDate) AS
[Year Paid]
FROM Bill LEFT OUTER JOIN
Transaction ON Bill.billNumber =
Transaction.bil lNumber
GROUP BY YEAR(Bill.fileD ate), YEAR(Transactio n.payDate)
HAVING (YEAR(Bill.file Date) BETWEEN 1985 AND 1990) AND
(YEAR(Transacti on.payDate) BETWEEN 1985 AND 1990)
ORDER BY YEAR(Bill.fileD ate), YEAR(Transactio n.payDate)

You mentioned just simply naming the columns, the problem with that
solution is that the number of columns are different each
time...dependin g 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@gmai l.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@sommarsk og.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/prodinf...ons/books.mspx

**Twobridge** · Nov 12 '06, 06:45 PM

Re: Actuarial sql statement Need Help.

I have found a way to generate the number for the columns by using
datediff(month, bill.fileDate, transaction.pay Date)/ 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.fileD ate) AS [Date Filed], YEAR(Transactio n.payDate) AS
[Year Paid]
FROM Bill LEFT OUTER JOIN
Transaction ON Bill.billNumber =
Transaction.bil lNumber
GROUP BY YEAR(Bill.fileD ate), YEAR(Transactio n.payDate)
HAVING (YEAR(Bill.file Date) BETWEEN 1985 AND 1990) AND
(YEAR(Transacti on.payDate) BETWEEN 1985 AND 1990)
ORDER BY YEAR(Bill.fileD ate), YEAR(Transactio n.payDate)
>
You mentioned just simply naming the columns, the problem with that
solution is that the number of columns are different each
time...dependin g 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@gmai l.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@sommarsk og.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/prodinf...ons/books.mspx

**Twobridge** · Nov 12 '06, 06:55 PM

Re: Actuarial sql statement Need Help.

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.pay Date)/ 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.fileD ate) AS [Date Filed], YEAR(Transactio n.payDate) AS
[Year Paid]
FROM Bill LEFT OUTER JOIN
Transaction ON Bill.billNumber =
Transaction.bil lNumber
GROUP BY YEAR(Bill.fileD ate), YEAR(Transactio n.payDate)
HAVING (YEAR(Bill.file Date) BETWEEN 1985 AND 1990) AND
(YEAR(Transacti on.payDate) BETWEEN 1985 AND 1990)
ORDER BY YEAR(Bill.fileD ate), YEAR(Transactio n.payDate)

You mentioned just simply naming the columns, the problem with that
solution is that the number of columns are different each
time...dependin g 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@gmai l.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@sommarsk og.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/prodinf...ons/books.mspx

**Erland Sommarskog** · Nov 12 '06, 09:05 PM

Re: Actuarial sql statement Need Help.

Twobridge (Twobridge@gmai l.com) writes:

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.fileD ate) AS [Date Filed], YEAR(Transactio n.payDate) AS
[Year Paid]
FROM Bill LEFT OUTER JOIN
Transaction ON Bill.billNumber =
Transaction.bil lNumber
GROUP BY YEAR(Bill.fileD ate), YEAR(Transactio n.payDate)
HAVING (YEAR(Bill.file Date) BETWEEN 1985 AND 1990) AND
(YEAR(Transacti on.payDate) BETWEEN 1985 AND 1990)
ORDER BY YEAR(Bill.fileD ate), YEAR(Transactio n.payDate)
>
You mentioned just simply naming the columns, the problem with that
solution is that the number of columns are different each
time...dependin g 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/.

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@sommarsk og.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

**Ed Murphy** · Nov 15 '06, 04:05 PM

Re: Actuarial sql statement Need Help.

Erland Sommarskog wrote:

Twobridge (Twobridge@gmai l.com) writes:

>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.

Surely "first 12 months" ought to be followed by "second 12 months"
etc., and so
datediff(month, bill.fileDate, transaction.pay Date)/ 12 * 12
should be changed to
(datediff(month , bill.fileDate, transaction.pay Date)+11)/ 12 * 12

Actuarial sql statement Need Help.

Actuarial sql statement Need Help.

Comment

Comment

Comment

Comment

Comment

Comment