sql script for report
-
Thank you for any assistance you can lend in creating a sql statement for my report. It seems simple but I have not been able to get it in a day and a half and I'm not any closer than when I started.
My table has the following fields (among others):
LoanNumber
LoanAmount
ApplicationDate
ClosingDate
I am tring to create a report that is grouped by Month/Year that shows the sum(LoanAmount) and count of loans that closed and the sum(LoanAmount) and count of loan applications.
My column headers would look something like this:
mm/yy
total Loan amount of loans closed
Count of loans closed
Total Loan amount of Applications
Count of applications
Thanks for looking at this. Paul
-
I imagine you have been experimenting with various GROUP BY and HAVING clauses on the Date fields yes? Tricky business. What I would do here is to cheat a bit. I would create a table called ReportingMonths like this:
create table ReportingMonths (
StartDate datetime,
EndDate datetime
)
I would then populate it thus:
insert into Reporting Months ( StartDate )
select '20040101','20040131' union
select '20040201','20040229' union
-- etc etc
Now the report query is easily done using correlated subqueries:
select
cast(month(StartDate) as varchar(2)) + '/' + cast(year(StartDate) as varchar(4)) as mmyy,
(select sum(LoanAmount) from Loan where ClosingDate between StartDate and EndDate) as LoanAmountClosed,
(select count(LoanNumber) from Loan where ClosingDate between StartDate and EndDate) as LoansClosed,
(select sum(LoanAmount) from Loan where ApplicationDate between StartDate and EndDate) as LoanAmountApplied,
(select count(LoanNumber) from Loan where ApplicationDate between StartDate and EndDate) as LoansApplied
from ReportingMonths
where StartDate between -- whatever reporting range you want
order by StartDate asc
-
Try this, I have not tested but nested queries in this situation should work well.
select mm/yy,
Total Loan amount of loans closed,
Count of loans closed,
Total Loan amount of Applications,
Count of applications
from
(
select mm/yy,
sum(Loan amount) as total Loan amount,
count(loans closed) as count of loans closed
from database..table
where cloasing date = ' '
group by mm/yy
) as tI
left outer join
(
select sum(Loan amount) as total loan amount of applications,
count(applications) as count of applications
from database..table
group by mm/yy
) as tII
on tI.mm/yy = tII.mm/yy
order by mm/yy
-
Apologies
, this should work.select tI.mm/yy,
Total Loan amount of loans closed,
Count of loans closed,
Total Loan amount of Applications,
Count of applications
from
(
select mm/yy,
sum(Loan amount) as total loan amount of applications,
count(applications) as count of applications
from database..table
group by mm/yy
) as tI
left outer join
(
select mm/yy,
sum(Loan amount) as total Loan amount,
count(loans closed) as count of loans closed
from database..table
where closing date = ' '
group by mm/yy
) as tII
on tI.mm/yy = tII.mm/yy
order by tI.mm/yy
-
Hello Paul,
1. Referencing the Date Conversion documention from MSDN:
Style Number / Standard / Input/Output
20 or 120 (*) / ODBC canonical / yyyy-mm-dd hh:mi:ss(24h)
(*) The default values (including style 20 or 120) always return the century (yyyy).
2. This statement gives you a useful YYYY-MM character format for reporting:
SUBSTRING(CONVERT(VARCHAR(19),ClosingDate,120),1,7) AS ReportYYYYMM
3. With your table: LoanTable
LoanNumber
LoanAmount
ApplicationDate
ClosingDate
To create a report that is grouped by Month/Year that shows both:
- the sum(LoanAmount) and count of loans that closed
- the sum(LoanAmount) and count of loan applications
you need two Selects on the Loan Table.
4. I suggest you create a view like this:
CREATE VIEW LoanReportDetails AS
SELECT SUBSTRING(CONVERT(VARCHAR(19),ClosingDate,120),1,7) AS ReportYYYYMM,
LoanAmount AS ClosedLoanAmount,
1 AS ClosedLoanCount,
0.00 AS ApplicationLoanAmount,
0 AS ApplicationLoanCount
FROM LoanTable
UNION ALL
SELECT SUBSTRING(CONVERT(VARCHAR(19),ApplicationDate,120),1,7) AS ReportYYYYMM,
0.00 AS ClosedLoanAmount,
0 AS ClosedLoanCount,
LoanAmount AS ApplicationLoanAmount,
1 AS ApplicationLoanCount
FROM LoanTable
5. Then this select statement should give you what you need:
SELECT ReportYYYYMM AS YYYY-MM
SUM(ClosedLoanAmount) AS TotalClosedAmount,
SUM(ClosedLoadCount) AS CountClosed,
SUM(ApplicationLoanAmount) AS TotalApplicationAmount,
SUM(ApplicationLoadCount) AS CountApplications
FROM LoanReportDetails
GROUP BY ReportYYYYMM
6. If you don't want to create the view, you can do it in one big statement:
SELECT ReportYYYYMM AS YYYY-MM
SUM(ClosedLoanAmount) AS TotalClosedAmount,
SUM(ClosedLoadCount) AS CountClosed,
SUM(ApplicationLoanAmount) AS TotalApplicationAmount,
SUM(ApplicationLoadCount) AS CountApplications
FROM
(
SELECT SUBSTRING(CONVERT(VARCHAR(19),ClosingDate,120),1,7) AS ReportYYYYMM,
LoanAmount AS ClosedLoanAmount,
1 AS ClosedLoanCount,
0.00 AS ApplicationLoanAmount,
0 AS ApplicationLoanCount
FROM LoanTable
UNION ALL
SELECT SUBSTRING(CONVERT(VARCHAR(19),ApplicationDate,120),1,7) AS ReportYYYYMM,
0.00 AS ClosedLoanAmount,
0 AS ClosedLoanCount,
LoanAmount AS ApplicationLoanAmount,
1 AS ApplicationLoanCount
FROM LoanTable
)
GROUP BY ReportYYYYMM
Good luck with it.
Regards,Bob Monahon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply