Calculated Fields in the report

shamen

I'm supposed to create a report like shown below.

Credit Tier	Jan-07	Feb-07	Mar-07
AA	0.00%	0.00%	0.00%
A	0.00%	0.00%	0.00%
B	0.00%	0.00%	0.00%
C	0.00%	0.00%	0.00%

Time columns go up to Dec 07. But I did not show all the columns.

The values are calculated as follows.

Field value for Jan07 = (No of Loans pass due in Jan07 / Total No of loans disbursed in Jan07)

Repayment due date is 5th of every month. if smbody does not pay on 5th, its considedred as pass due.

Source data tables look like this.

LoanTable(Disbursed date, userID, Amount, Status)

CreditTier table (Credit Tier, Rate) - Seems like no relationship with Loan table

we can identify pass due loans from status field in loan table. Status appears as 'Deliquency'.

Please Can any one help me to create this report

Lisa Nicholls

I will assume you know how to create the formula, which is basically something like:

Code Block

(SUM(CASE WHEN Status = 'Delinquent' THEN 1.00 ELSE 0.00 END) / COUNT(*) )
* 100 AS PercentDelinquent

The relationship between the CreditTier table and the result of the formula is on the value. In other words, your Credit Tier table has to show that CreditTier C is used for individuals whose percentage is below <some value> and Credit Tier B is for individuals whose percent is between <that value> and <some other value>.

That is the relationship. Where has your CreditTier schema hidden the appropriate data <s>

>L<

shamen

Hi Lisa,

Still I cant figure it out SELECT part. Because I have to apply two conditions at the same time. Forget about the table names I mentioned above. Following names are the real names.

1. For Total no of loans disbursed in each month , I need to write smthing like this.

SELECT Rate.Tier AS CreditTier, COUNT(Loan.AccountId) AS TotalNoOfLoans, DATEPART(month, Loan.CommencedOn) AS Month
FROM Loan INNER JOIN
Rate ON Loan.Id = Rate.Id

WHERE CommencedOn BETWEEN '01/01/07' AND '12'/01/07'

GROUP BY DATEPART(month, Loan.CommencedOn), CreditTier

2. For Total no of deliquent loans disbursed in each month, I may need to write smthing like this.

SELECT Rate.Tier AS CreditTier, COUNT(Loan.AccountId) AS TotalNoOfDeliquentLoans, DATEPART(month, Loan.CommencedOn) AS Month
FROM Loan INNER JOIN
Rate ON Loan.Id = Rate.Id

WHERE CommencedOn BETWEEN '01/01/07' AND '12'/01/07' AND State = 'Deliquent'

GROUP BY DATEPART(month, Loan.CommencedOn), CreditTier

Now I want to calculate values for each record.

For example, COLUMN Jan and ROW CreaditTier AA,

0.00 % (This is not necessarily zero) = ((TotalNoOfDeliquentLoans in Jan with CreditTier AA )/ (TotalNoOfLoans in Jan with creaditTier AA ))*100

But I cant figure it out how to combine these. Coz If i write like this, I'm making two different data sets.

Hope now you have a better idea of what i'm trying to do..

Also I think I have to use matrix instead of a table for this

shamen

Well I wrote smthing like this.

SELECT Rate.Tier AS CreditTier, COUNT(Loan.AccountId) AS NoOfAcounts, DATEPART(month, Loan.CommencedOn) AS Month,
SUM(CASE WHEN State = 'Delinquent' THEN 1 ELSE 0.00 END) / COUNT(*) * 100 AS PercentDelinquent
FROM Loan INNER JOIN
Rate ON Loan.Id = Rate.Id
WHERE (Loan.CommencedOn BETWEEN '01/01/07' AND '12/01/07')
GROUP BY Rate.Tier, DATEPART(month, Loan.CommencedOn)

But Im not sure I'm using the right query.

Does CASE Statement itself identify total no of deliquent loans disbursed in each month seperately

As a example, For January Total no of deliqent loans mean total no of loans which are deliquent and disbursed in January.

The I selected Matrix option and added CreditTier field to ROWS and Month field to COLUMNS and PercentDeliquent field to DETAILS.

I could create the report. Have I done this right way

Lisa Nicholls

Why are you trying to substitute COUNT(*) for 1

The point of SUM(CASE ... 1 ELSE 0 END) is to do a conditional count *for* you. You are summing 1 (not an expression) for each row that fits your condition. See

Nothing to substitute and no reason to use COUNT() <s>.

>L<

shamen

Yes actually I got your point and now it works...Can you please see my previous post again I want to make sure I did the right thing..

Lisa Nicholls

I am not sure which of your previous posts you want me to look at and where you are stuck, but I'll try.

* -- to answer your question about whether you need to use a matrix or not, actually you have to make another decision first. Here is what it is: do you ever have more than 12 months to look at If you do, you need to revise this bit: DATEPART(month, Loan.CommencedOn). You should be looking at DateDiff(), not DatePart, as I said earlier, or you are going to mix up the data for the month of April (for example) in two different years.

If you use DateDiff(month...) with a literal start date as one of the values (for example, store GETDATE() to a variable and use that) then you will have a number of months' difference between that date and any month which will be unique for a month+year combination. For example, DateDiff(month... between last April and today might be 5 but it would be 17 between April 2006 and today. Does that make sense

Alternatively, you could group on DATEPART(year...) and then by DATEPART(month... . This would also ensure uniqueness.

The reason I say that you should do this first before worrying about whether you should use a matrix or not is that, if you always have only one set of months, you really don't have to use a matrix. You can just set up one column for Jan, one for Feb, etc. I hope this makes sense.

* -- for the second part of your problem where you say "I'm trying to use two data sets", as I have been trying to explain to you, you actually *can* combine this data into one SELECT. I *think* you understand this part now, but I am not sure. When you fix your GROUP BY as discussed above, can you show me what SELECT you're currently working with and whether you are still stuck or unsure about something

>L<

shamen

Well for this report I need to show PercentDeliquent values only for year 2007. So I use WHERE clause to restrict data only 2007 year.

Sorry actually I changed DATEPART to DATENAME.

The code I use for this report is as shown below.

Code Block

SELECT Rate.Tier AS CreditTier, COUNT(Loan.AccountId) AS DATENAME (month, Loan.CommencedOn) AS Month, SUM(CASE WHEN State = 'Delinquent' THEN 1 ELSE 0.00 END) / COUNT(*) * 100 AS PercentDelinquent

FROM Loan INNER JOIN
Rate ON Loan.Id = Rate.Id

WHERE (Loan.CommencedOn BETWEEN '01/01/07' AND '12/01/07')

GROUP BY Rate.Tier, DATENAME(month, Loan.CommencedOn)

Still Do I need to use DATEDIFF

Lisa Nicholls

No, as I said before, if you are restricting the range to < 13 months, then you don't need to use DATEDIFF and it doesn't make any difference whether you use DATENAME or DATEPART, FWIW.

And if you are restricting the range to a certain date range that is always < 13 months, also, I don't think you have to use a matrix if you don't want to, because you can explicitly name the columns (one for each month).

So... are you still stuck on something here, or are you okay now

>L<

shamen

All clear...I just cant test it coz data base does not have data yet... But I guess I did what u said...

Thanks lot