May 13, 2009 at 7:35 am
Im a newbie in using SQL and I want to extract data in one table, with multiple criteria to be displayed in multiple column. To illustrate, the display would look like this..
Table LOANS
- amount
- DateOpen
- MatDate
* Term = MatDate-DateOpen
-- criteria 1 -- -- criteria 2 -- -- criteria 3 -- -- criteria 4 --
#_1 | amount_1 |#_2 | amount_2 | #_3 | amount_3 | #_4 | amount_4
Criteria 1 - amount<=15,000.00
Criteria 2 - amount<=15,000.00 and term <= 12
Criteria 3 - amount<=15,000.00 and term > 12 and term < 60
Criteria 4 - amount<=15,000.00 and term > 60
How will I translate this to a select statement? I can do for the first criteria with this statement -
SELECT count(*), amount FROM loans WHERE amount<=1500000. But with the other criteria, I dont know how. Your help is much appreciated. Thanks
May 13, 2009 at 7:45 am
I think you want something like this
select case when amount 15000 and terms 15000 and terms between 12 and 60 then amount else null end as criteria3
from loans
May 13, 2009 at 8:32 am
If you are simply wanting counts of loans within the various criteria, try this:
-- produces counts by criteria
select sum(case when amount 15000 and terms 15000 and terms between 12 and 60 then 1 else 0 end) as criteria3,
sum(case when amount > 15000 and terms > 60 then 1 else 0 end) as criteria4
from loans
Do you want to total the loan amounts as well, or do you want to group by each amount?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 13, 2009 at 9:48 am
Thanks for your reply.
I want to total the amount as well as count the number of records that falls to the criteria.
May 13, 2009 at 10:03 am
Piece of cake.
select
sum(case when amount < 15000 then 1 else 0 end) as criteria1_count,
sum(case when amount 15000 and terms 15000 and terms 15000 and terms between 12 and 60 then 1 else 0 end) as criteria3_count,
sum(case when amount > 15000 and terms between 12 and 60 then amount else 0 end) as criteria3_amount,
sum(case when amount > 15000 and terms > 60 then 1 else 0 end) as criteria4_count,
sum(case when amount > 15000 and terms > 60 then amount else 0 end) as criteria4_amount
from loans
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 13, 2009 at 10:29 am
Great! Thanks Bob...
Now, how am I going to export the result to an excel file? 🙂
May 13, 2009 at 10:38 am
Your options include:
1. Cut-and-Paste from Sql Server Management Studio (SSMS)
2. Using the "RESULTS to FILE" option in SSMS
3. Using Sql Server Integration Services
4. Creating this as a view and letting Excel just access the DB
Almost certainly more options exist that haven't occurred to me. What's the mission? Let a user pull this on demand or periodically publish a spreadsheet? Is this a one-time thing?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 14, 2009 at 7:50 am
Bob Hovious (5/13/2009)
Your options include:1. Cut-and-Paste from Sql Server Management Studio (SSMS)
2. Using the "RESULTS to FILE" option in SSMS
3. Using Sql Server Integration Services
4. Creating this as a view and letting Excel just access the DB
Almost certainly more options exist that haven't occurred to me. What's the mission? Let a user pull this on demand or periodically publish a spreadsheet? Is this a one-time thing?
Actually Bob, this is report is generated periodically. This report has already a format in excel and they want me to extract the data from sql database the directly transfer it to the existing excel file...
Thanks for you help Bob...
May 14, 2009 at 9:22 am
SSIS (Integration Services) is probably going to be your best bet. You could create a scheduled job to do it, rather than have to manually cut and paste over and over again. I haven't used SSIS to go to Excel yet, but I'm fairly certain I've seen some posts and even an article on your topic within the last few months. Why don't you try searching here on SSIS and EXCEL?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 14, 2009 at 7:05 pm
Thanks for the info Bob. I will try to search it here in the forum.
May 14, 2009 at 7:11 pm
[font="Verdana"]Why not just create a report in SQL Server Reporting Services? From there, people can subscribe to the report, you can schedule it to run automatically once a month, you can set it to export to Excel, etc.[/font]
May 14, 2009 at 11:08 pm
Bob Hovious (5/13/2009)
Piece of cake.
select
sum(case when amount < 15000 then 1 else 0 end) as criteria1_count,
sum(case when amount 15000 and terms 15000 and terms 15000 and terms between 12 and 60 then 1 else 0 end) as criteria3_count,
sum(case when amount > 15000 and terms between 12 and 60 then amount else 0 end) as criteria3_amount,
sum(case when amount > 15000 and terms > 60 then 1 else 0 end) as criteria4_count,
sum(case when amount > 15000 and terms > 60 then amount else 0 end) as criteria4_amount
from loans
Hi Bob. Just a follow query with regards to the above script. What if there are two or three tables involve? Let say from loans and savings? Will this code work out?
select
sum(case when loan.amount < 15000 then 1 else 0 end) as criteria1_count,
sum(case when loan.amount < 15000 then loan.amount else 0 end) as criteria1_amount,
sum(case when saving.amount < 15000 then 1 else 0 end) as criteria1_sv_count,
sum(case when saving.amount < 15000 then saving.amount else 0 end) as criteria1_sv_amount,
from loans loan, savings
May 14, 2009 at 11:24 pm
TomYum (5/14/2009)
What if there are two or three tables involve? Let say from loans and savings? Will this code work out?select
sum(case when loan.amount < 15000 then 1 else 0 end) as criteria1_count,
sum(case when loan.amount < 15000 then loan.amount else 0 end) as criteria1_amount,
sum(case when saving.amount < 15000 then 1 else 0 end) as criteria1_sv_count,
sum(case when saving.amount < 15000 then saving.amount else 0 end) as criteria1_sv_amount,
from loans loan, savings
[font="Verdana"]If you run your code, you will quickly see what the issue is.
Basically, every line in your loan table will be multiplied by ever line in your saving table, because you haven't given SQL Server any criteria to be able to join the tables. This is known as a "cross join" (or a full cartesian product if you are using set notation.)
You may not have any simple way of joining those two tables though. So as a suggestion, try something like the following:
select[Source],
[Criteria],
count(*) as [Count],
sum(amount) as [Amount]
from(
select'Loan' as [Source],
case when amount < 1500 then '= $1,500' end as [Criteria],
amount
fromloan
union all
select'Saving' as [Source],
case when amount < 1500 then '= $1,500' end as [Criteria],
amount
fromsaving
) sources
group by
[Source],
[Criteria]
order by
[Source],
[Criteria];
So what this is doing is creating a big list of all of the entries in the two table, along with a column (Criteria) that shows what the criteria is for each row. It does that for both loans and savings. It then sums them up across the sources and loans.
Hopefully that makes sense. 😀
[/font]
May 15, 2009 at 12:27 am
select
sum(case when loan.amount < 15000 then 1 else 0 end) as criteria1_count,
sum(case when loan.amount < 15000 then loan.amount else 0 end) as criteria1_amount,
sum(case when saving.amount < 15000 then 1 else 0 end) as criteria1_sv_count,
sum(case when saving.amount < 15000 then saving.amount else 0 end) as criteria1_sv_amount,
from loans loan, savings[/quote]
You may not have any simple way of joining those two tables though. So as a suggestion, try something like the following:
select[Source],
[Criteria],
count(*) as [Count],
sum(amount) as [Amount]
from(
select'Loan' as [Source],
case when amount < 1500 then '= $1,500' end as [Criteria],
amount
fromloan
union all
select'Saving' as [Source],
case when amount < 1500 then '= $1,500' end as [Criteria],
amount
fromsaving
) sources
group by
[Source],
[Criteria]
order by
[Source],
[Criteria];
------
Thanks Bruce. I'm just a little bit confused. Im not so familiar with SQL coding yet. Just want to as what will be my 'Source' and 'Criteria'? What statement will I replace with the 'Source' and 'Criteria'?
May 15, 2009 at 1:29 pm
TomYum,
If you are going to continue to code in SQL, you need to very quickly read up on JOIN in all it's forms. It's absolutely essential.
Yes, the code "can" still work, but as Bruce pointed out, depending on the join, you may get more rows back than you expect, which means your totals will be larger than they should be.
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply