counting records

Hi all,
I have a DTS package that gets information out of a Pervasive db and
drops it into SQL.
I need a count on how many distinct loan number are associated with
specific actions (ie Closing Date, Funded Date etc).
I have 2 tables: 1 contains a reference number and description (ie 170
= Closing Cost, 210 = Funded Date etc) and another table that contains
the reference number and loan number.
So I need a result set that says 20 loan have a closing date, 400 loans
have a funded date etc. There are over 590 reference numbers and over
1.5 million records.
Any help/advice would be immensely apprieciated!!
Thanks,
TonyTry,
select r.ref_desc, count(*) as cnt
from reference as r inner join loan as l
on r.ref_id = l.ref_id
group by r.ref_desc
go
AMB
"Tony" wrote:

> Hi all,
> I have a DTS package that gets information out of a Pervasive db and
> drops it into SQL.
> I need a count on how many distinct loan number are associated with
> specific actions (ie Closing Date, Funded Date etc).
> I have 2 tables: 1 contains a reference number and description (ie 170
> = Closing Cost, 210 = Funded Date etc) and another table that contains
> the reference number and loan number.
> So I need a result set that says 20 loan have a closing date, 400 loans
> have a funded date etc. There are over 590 reference numbers and over
> 1.5 million records.
> Any help/advice would be immensely apprieciated!!
> Thanks,
> Tony
>|||Hi Tony
You don't post ddl but you can do something like (untested):
SELECT [loan number], SUM(CASE WHEN [Closing Date] IS NOT NULL THEN 1 ELSE 0
END) AS NumClosed
SUM(CASE WHEN [Funded Date]IS NOT NULL THEN 1 ELSE 0 END) AS NumFunded
FROM #MyTable
GROUP BY [loan number]
John
"Tony" wrote:

> Hi all,
> I have a DTS package that gets information out of a Pervasive db and
> drops it into SQL.
> I need a count on how many distinct loan number are associated with
> specific actions (ie Closing Date, Funded Date etc).
> I have 2 tables: 1 contains a reference number and description (ie 170
> = Closing Cost, 210 = Funded Date etc) and another table that contains
> the reference number and loan number.
> So I need a result set that says 20 loan have a closing date, 400 loans
> have a funded date etc. There are over 590 reference numbers and over
> 1.5 million records.
> Any help/advice would be immensely apprieciated!!
> Thanks,
> Tony
>|||Hi John,
I thought of this but I have over 590 reference fields...don't feel
like writing a sum/case for each one|||Hi John,
I thought of this but I have over 590 reference fields...don't feel
like writing a sum/case for each one|||On 26 Oct 2005 12:23:47 -0700, Tony wrote:

>Hi John,
>I thought of this but I have over 590 reference fields...don't feel
>like writing a sum/case for each one
Hi Tony,
Did you try Allejandro's suggestion?
If that doesn't help you, then please refer to www.aspfaq.com/5006 to
find out what extra information you need to post in order to help us
help you.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
590 columns in a table seems a large number. It is already causing you
problems by creating unwillingness try a solution!
You can use the information_schema.columns to create the sql for you.
e.g.
USE NORTHWIND
SELECT 'CASE WHEN ' + QUOTENAME (COLUMN_NAME) + ' IS NULL THEN 0 ELSE 1
END AS [SUM_' + COLUMN_NAME + '],'
from informatioN_schema.columns
where table_name = 'orders'
ORDER BY ORDINAL_POSITION
John
Tony wrote:
> Hi John,
> I thought of this but I have over 590 reference fields...don't feel
> like writing a sum/case for each one