Need help with the SELECT statement. 12-04-09

--SQL 2005

I need to give a total count of LCPI from another table based upon the 
Loanid.  Below show the 
business rule and desire results.  Thank you in advance.

 IF OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL
  DROP TABLE #LoanInfo
GO

CREATE TABLE #LoanInfo
(
  LoanId    VARCHAR(8)   NULL,
  Status    VARCHAR(10)  NULL
)
GO

IF OBJECT_ID('Tempdb.dbo.#LoanComp', 'u') IS NOT NULL
  DROP TABLE #LoanComp 
GO
CREATE TABLE #LoanComp 
(
  LCPI         INT           NULL,
  LoanId       VARCHAR(8)    NULL
)
GO

INSERT INTO #LoanInfo VALUES ('102752', 'PortActive')
INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
INSERT INTO #LoanInfo VALUES ('102983', 'PortActive')
INSERT INTO #LoanInfo VALUES ('104101', 'PortActive')

GO

INSERT INTO #LoanComp VALUES(123079,102922)
INSERT INTO #LoanComp VALUES(123098,102922)
INSERT INTO #LoanComp VALUES(123069,102922)
INSERT INTO #LoanComp VALUES(123034,102922)
INSERT INTO #LoanComp VALUES(146725,102922)
INSERT INTO #LoanComp VALUES(18419, 102922)

INSERT INTO #LoanComp VALUES(122297,102983)
INSERT INTO #LoanComp VALUES(122324,102983)
INSERT INTO #LoanComp VALUES(146774,102983)
INSERT INTO #LoanComp VALUES(122297,102983)
INSERT INTO #LoanComp VALUES(146781,102983)

INSERT INTO #LoanComp VALUES(124626,104101)
INSERT INTO #LoanComp VALUES(124633,104101)
GO

   SELECT *
     FROM #LoanInfo;
     
   SELECT *
     FROM #LoanComp;
     
  Rule: Count number of LCPI from #LoanComp based upon the LoanId associate 
to #LoanInfo table.     
-- Result want:
     
LoanId   Status       TotalCountLCPI
-------- ----------   --------------
102752   PortActive   0
102922   PortActive   6
102922   PortActive   
102983   PortActive   5
104101   PortActive   2
0
Utf
12/4/2009 3:25:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

8 Replies
792 Views

Similar Articles

[PageSpeed] 58

On Dec 4, 9:25=A0am, LN <L...@discussions.microsoft.com> wrote:
> --SQL 2005
>
> I need to give a total count of LCPI from another table based upon the
> Loanid. =A0Below show the
> business rule and desire results. =A0Thank you in advance.
>
> =A0IF OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL
> =A0 DROP TABLE #LoanInfo
> GO
>
> CREATE TABLE #LoanInfo
> (
> =A0 LoanId =A0 =A0VARCHAR(8) =A0 NULL,
> =A0 Status =A0 =A0VARCHAR(10) =A0NULL
> )
> GO
>
> IF OBJECT_ID('Tempdb.dbo.#LoanComp', 'u') IS NOT NULL
> =A0 DROP TABLE #LoanComp
> GO
> CREATE TABLE #LoanComp
> (
> =A0 LCPI =A0 =A0 =A0 =A0 INT =A0 =A0 =A0 =A0 =A0 NULL,
> =A0 LoanId =A0 =A0 =A0 VARCHAR(8) =A0 =A0NULL
> )
> GO
>
> INSERT INTO #LoanInfo VALUES ('102752', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('102983', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('104101', 'PortActive')
>
> GO
>
> INSERT INTO #LoanComp VALUES(123079,102922)
> INSERT INTO #LoanComp VALUES(123098,102922)
> INSERT INTO #LoanComp VALUES(123069,102922)
> INSERT INTO #LoanComp VALUES(123034,102922)
> INSERT INTO #LoanComp VALUES(146725,102922)
> INSERT INTO #LoanComp VALUES(18419, 102922)
>
> INSERT INTO #LoanComp VALUES(122297,102983)
> INSERT INTO #LoanComp VALUES(122324,102983)
> INSERT INTO #LoanComp VALUES(146774,102983)
> INSERT INTO #LoanComp VALUES(122297,102983)
> INSERT INTO #LoanComp VALUES(146781,102983)
>
> INSERT INTO #LoanComp VALUES(124626,104101)
> INSERT INTO #LoanComp VALUES(124633,104101)
> GO
>
> =A0 =A0SELECT *
> =A0 =A0 =A0FROM #LoanInfo;
>
> =A0 =A0SELECT *
> =A0 =A0 =A0FROM #LoanComp;
>
> =A0 Rule: Count number of LCPI from #LoanComp based upon the LoanId assoc=
iate
> to #LoanInfo table. =A0 =A0
> -- Result want:
>
> LoanId =A0 Status =A0 =A0 =A0 TotalCountLCPI
> -------- ---------- =A0 --------------
> 102752 =A0 PortActive =A0 0
> 102922 =A0 PortActive =A0 6
> 102922 =A0 PortActive =A0
> 102983 =A0 PortActive =A0 5
> 104101 =A0 PortActive =A0 2

Try this:


SELECT
	LoanID,
	[Status],
	(SELECT COUNT(*) FROM #LoanComp WHERE LoanID=3D#LoanInfo.LoanID)
[TotalCountLCPI]
		FROM #LoanInfo
0
John
12/4/2009 4:09:15 PM
Not sure if this yeilds what you're looking for..

select       a.loanid
	,a.status
	,Counts = (select count(lcpi) 
		from #loancomp c 
		where c.loanid = a.loanid and lcpi is not null)
from #loaninfo a
group by a.loanid ,a.status


"LN" wrote:

> --SQL 2005
> 
> I need to give a total count of LCPI from another table based upon the 
> Loanid.  Below show the 
> business rule and desire results.  Thank you in advance.
> 
>  IF OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL
>   DROP TABLE #LoanInfo
> GO
> 
> CREATE TABLE #LoanInfo
> (
>   LoanId    VARCHAR(8)   NULL,
>   Status    VARCHAR(10)  NULL
> )
> GO
> 
> IF OBJECT_ID('Tempdb.dbo.#LoanComp', 'u') IS NOT NULL
>   DROP TABLE #LoanComp 
> GO
> CREATE TABLE #LoanComp 
> (
>   LCPI         INT           NULL,
>   LoanId       VARCHAR(8)    NULL
> )
> GO
> 
> INSERT INTO #LoanInfo VALUES ('102752', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('102983', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('104101', 'PortActive')
> 
> GO
> 
> INSERT INTO #LoanComp VALUES(123079,102922)
> INSERT INTO #LoanComp VALUES(123098,102922)
> INSERT INTO #LoanComp VALUES(123069,102922)
> INSERT INTO #LoanComp VALUES(123034,102922)
> INSERT INTO #LoanComp VALUES(146725,102922)
> INSERT INTO #LoanComp VALUES(18419, 102922)
> 
> INSERT INTO #LoanComp VALUES(122297,102983)
> INSERT INTO #LoanComp VALUES(122324,102983)
> INSERT INTO #LoanComp VALUES(146774,102983)
> INSERT INTO #LoanComp VALUES(122297,102983)
> INSERT INTO #LoanComp VALUES(146781,102983)
> 
> INSERT INTO #LoanComp VALUES(124626,104101)
> INSERT INTO #LoanComp VALUES(124633,104101)
> GO
> 
>    SELECT *
>      FROM #LoanInfo;
>      
>    SELECT *
>      FROM #LoanComp;
>      
>   Rule: Count number of LCPI from #LoanComp based upon the LoanId associate 
> to #LoanInfo table.     
> -- Result want:
>      
> LoanId   Status       TotalCountLCPI
> -------- ----------   --------------
> 102752   PortActive   0
> 102922   PortActive   6
> 102922   PortActive   
> 102983   PortActive   5
> 104101   PortActive   2
0
Utf
12/4/2009 4:19:01 PM
The requirement seems a bit strange with the duplicate LoanId from table #LoanInfo not getting any counts. Here is a 
query to accomplish exactly that:

WITH Ranked AS (
SELECT LoanId, Status,
        ROW_NUMBER() OVER(PARTITION BY LoanId ORDER BY (SELECT NULL)) AS rk
FROM #LoanInfo)
SELECT R.LoanId, R.Status,
        CASE WHEN rk = 1
             THEN COALESCE(T.lcpi_cnt, 0)
        END AS TotalCountLCPI
FROM Ranked AS R
LEFT OUTER JOIN (SELECT LoanId, COUNT(*) AS lcpi_cnt
                  FROM #LoanComp
                  GROUP BY LoanId) AS T
   ON R.LoanId = T.LoanId
  AND R.rk = 1;

/*

LoanId   Status     TotalCountLCPI
-------- ---------- --------------
102752   PortActive 0
102922   PortActive 6
102922   PortActive NULL
102983   PortActive 5
104101   PortActive 2

*/

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
12/4/2009 4:21:06 PM
LN wrote:
> --SQL 2005
>
> I need to give a total count of LCPI from another table based upon the
> Loanid.  Below show the
> business rule and desire results.  Thank you in advance.
>
>  IF OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL
>   DROP TABLE #LoanInfo
> GO
>
> CREATE TABLE #LoanInfo
> (
>   LoanId    VARCHAR(8)   NULL,
>   Status    VARCHAR(10)  NULL
> )
> GO
>
> IF OBJECT_ID('Tempdb.dbo.#LoanComp', 'u') IS NOT NULL
>   DROP TABLE #LoanComp
> GO
> CREATE TABLE #LoanComp
> (
>   LCPI         INT           NULL,
>   LoanId       VARCHAR(8)    NULL
> )
> GO
>
> INSERT INTO #LoanInfo VALUES ('102752', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('102983', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('104101', 'PortActive')
>
> GO
>
> INSERT INTO #LoanComp VALUES(123079,102922)
> INSERT INTO #LoanComp VALUES(123098,102922)
> INSERT INTO #LoanComp VALUES(123069,102922)
> INSERT INTO #LoanComp VALUES(123034,102922)
> INSERT INTO #LoanComp VALUES(146725,102922)
> INSERT INTO #LoanComp VALUES(18419, 102922)
>
> INSERT INTO #LoanComp VALUES(122297,102983)
> INSERT INTO #LoanComp VALUES(122324,102983)
> INSERT INTO #LoanComp VALUES(146774,102983)
> INSERT INTO #LoanComp VALUES(122297,102983)
> INSERT INTO #LoanComp VALUES(146781,102983)
>
> INSERT INTO #LoanComp VALUES(124626,104101)
> INSERT INTO #LoanComp VALUES(124633,104101)
> GO
>
>    SELECT *
>      FROM #LoanInfo;
>
>    SELECT *
>      FROM #LoanComp;
>
>   Rule: Count number of LCPI from #LoanComp based upon the LoanId
> associate to #LoanInfo table.
> -- Result want:
>
> LoanId   Status       TotalCountLCPI
> -------- ----------   --------------
> 102752   PortActive   0
> 102922   PortActive   6
> 102922   PortActive
> 102983   PortActive   5
> 104101   PortActive   2

This result is not possible without some way of differentiating the two
rows in #LoanInfo with the same LoanID (102922) from each other.
The table needs a primary key column, and a corresponding column needs
to be added to LoanComp so the records can be properly linked.

The closest I can get is with this query:
SELECT i.LoanID,i.[Status]
,COALESCE(TotalCountLCPI,0) As TotalCountLCPI
FROM #LoanInfo i LEFT JOIN (
Select LoanID, COUNT(LCPI) As TotalCountLCPI
from #LoanComp
GROUP BY LoanID) As q ON i.LoanID=q.LoanID

Which produces two duplicate records for 102922. If we change the tables
as follows, we can get the results you want.
 IF OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL
  DROP TABLE #LoanInfo
GO

CREATE TABLE #LoanInfo
(
  LoanId    VARCHAR(8)   NOT NULL,
  LoanInstance    INT    NOT NULL,
  Status    VARCHAR(10)  NOT NULL
)
GO

IF OBJECT_ID('Tempdb.dbo.#LoanComp', 'u') IS NOT NULL
  DROP TABLE #LoanComp
GO
CREATE TABLE #LoanComp
(
  LCPI         INT          NOT NULL,
  LoanId       VARCHAR(8)   NOT NULL,
  LoanInstance    INT    NOT NULL,
)
GO

INSERT INTO #LoanInfo VALUES ('102752',1, 'PortActive')
INSERT INTO #LoanInfo VALUES ('102922',1, 'PortActive')
INSERT INTO #LoanInfo VALUES ('102922',2, 'PortActive')
INSERT INTO #LoanInfo VALUES ('102983',1, 'PortActive')
INSERT INTO #LoanInfo VALUES ('104101',1, 'PortActive')

GO

INSERT INTO #LoanComp VALUES(123079,102922,1)
INSERT INTO #LoanComp VALUES(123098,102922,1)
INSERT INTO #LoanComp VALUES(123069,102922,1)
INSERT INTO #LoanComp VALUES(123034,102922,1)
INSERT INTO #LoanComp VALUES(146725,102922,1)
INSERT INTO #LoanComp VALUES(18419, 102922,1)

INSERT INTO #LoanComp VALUES(122297,102983,1)
INSERT INTO #LoanComp VALUES(122324,102983,1)
INSERT INTO #LoanComp VALUES(146774,102983,1)
INSERT INTO #LoanComp VALUES(122297,102983,1)
INSERT INTO #LoanComp VALUES(146781,102983,1)

INSERT INTO #LoanComp VALUES(124626,104101,1)
INSERT INTO #LoanComp VALUES(124633,104101,1)
GO
  SELECT *
     FROM #LoanInfo;

   SELECT *
     FROM #LoanComp;

SELECT i.LoanID,i.[Status]
,COALESCE(TotalCountLCPI,0) As TotalCountLCPI
FROM #LoanInfo i LEFT JOIN (
Select LoanID,LoanInstance, COUNT(LCPI) As TotalCountLCPI
from #LoanComp
GROUP BY LoanID,LoanInstance) As q
ON i.LoanID=q.LoanID AND i.LoanInstance=q.LoanInstance
-- 
HTH,
Bob Barrows


0
Bob
12/4/2009 4:25:10 PM
You did not explain, why or how the second 102922 should have a NULL count 
instead of 6, but assuming you want the accumulation on the "first" row, you 
could modify John's code as such:

SELECT LoanID, [Status],
   -- Case statement using RowNumber to
   -- ensure that only the 'first' of duplicate
   -- LoanIDs reports a count.
   CASE (RowNumber)
      WHEN 1 THEN TotalCountLCPI
      ELSE NULL
   END AS TotalCountLCPI
FROM
(SELECT
 LoanID,
 [Status],
 (SELECT COUNT(*) FROM #LoanComp WHERE LoanID=#LoanInfo.LoanID) 
[TotalCountLCPI],
 -- Add a RowNumber so that we can tell which row was 'first'
 ROW_NUMBER() OVER (PARTITION BY LoanID ORDER BY [Status]) AS RowNumber
  FROM #LoanInfo) AS Derived

Now, why do I keep saying 'first' instead of just first?  Because the order 
is arbitrary and there is no rule describing why one row would accumulate 
and another would not.  As you can see, the accumulation on both 102922 rows 
is the natural result.  What if the two rows had different Status?  Et 
cetera.    So, while this gives you the result you asked for, what that 
result is supposed to mean is still unclear.

RLF


"LN" <LN@discussions.microsoft.com> wrote in message 
news:9760ADD5-EF3F-4C37-B6FC-8F212078A8B8@microsoft.com...
> --SQL 2005
>
> I need to give a total count of LCPI from another table based upon the
> Loanid.  Below show the
> business rule and desire results.  Thank you in advance.
>
> IF OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL
>  DROP TABLE #LoanInfo
> GO
>
> CREATE TABLE #LoanInfo
> (
>  LoanId    VARCHAR(8)   NULL,
>  Status    VARCHAR(10)  NULL
> )
> GO
>
> IF OBJECT_ID('Tempdb.dbo.#LoanComp', 'u') IS NOT NULL
>  DROP TABLE #LoanComp
> GO
> CREATE TABLE #LoanComp
> (
>  LCPI         INT           NULL,
>  LoanId       VARCHAR(8)    NULL
> )
> GO
>
> INSERT INTO #LoanInfo VALUES ('102752', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('102983', 'PortActive')
> INSERT INTO #LoanInfo VALUES ('104101', 'PortActive')
>
> GO
>
> INSERT INTO #LoanComp VALUES(123079,102922)
> INSERT INTO #LoanComp VALUES(123098,102922)
> INSERT INTO #LoanComp VALUES(123069,102922)
> INSERT INTO #LoanComp VALUES(123034,102922)
> INSERT INTO #LoanComp VALUES(146725,102922)
> INSERT INTO #LoanComp VALUES(18419, 102922)
>
> INSERT INTO #LoanComp VALUES(122297,102983)
> INSERT INTO #LoanComp VALUES(122324,102983)
> INSERT INTO #LoanComp VALUES(146774,102983)
> INSERT INTO #LoanComp VALUES(122297,102983)
> INSERT INTO #LoanComp VALUES(146781,102983)
>
> INSERT INTO #LoanComp VALUES(124626,104101)
> INSERT INTO #LoanComp VALUES(124633,104101)
> GO
>
>   SELECT *
>     FROM #LoanInfo;
>
>   SELECT *
>     FROM #LoanComp;
>
>  Rule: Count number of LCPI from #LoanComp based upon the LoanId associate
> to #LoanInfo table.
> -- Result want:
>
> LoanId   Status       TotalCountLCPI
> -------- ----------   --------------
> 102752   PortActive   0
> 102922   PortActive   6
> 102922   PortActive
> 102983   PortActive   5
> 104101   PortActive   2 

0
Russell
12/4/2009 4:29:48 PM
John, I think LN needs a little "group" action and perhaps my counts col 
should be named... 

,TotalCountLCPI = (select count(lcpi) from #loancomp c where c.loanid = 
a.loanid and lcpi is not null)

....but I could be wrong, we'll await LN's response...

"John" wrote:

> On Dec 4, 9:25 am, LN <L...@discussions.microsoft.com> wrote:
> > --SQL 2005
> >
> > I need to give a total count of LCPI from another table based upon the
> > Loanid.  Below show the
> > business rule and desire results.  Thank you in advance.
> >
> >  IF OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL
> >   DROP TABLE #LoanInfo
> > GO
> >
> > CREATE TABLE #LoanInfo
> > (
> >   LoanId    VARCHAR(8)   NULL,
> >   Status    VARCHAR(10)  NULL
> > )
> > GO
> >
> > IF OBJECT_ID('Tempdb.dbo.#LoanComp', 'u') IS NOT NULL
> >   DROP TABLE #LoanComp
> > GO
> > CREATE TABLE #LoanComp
> > (
> >   LCPI         INT           NULL,
> >   LoanId       VARCHAR(8)    NULL
> > )
> > GO
> >
> > INSERT INTO #LoanInfo VALUES ('102752', 'PortActive')
> > INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
> > INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
> > INSERT INTO #LoanInfo VALUES ('102983', 'PortActive')
> > INSERT INTO #LoanInfo VALUES ('104101', 'PortActive')
> >
> > GO
> >
> > INSERT INTO #LoanComp VALUES(123079,102922)
> > INSERT INTO #LoanComp VALUES(123098,102922)
> > INSERT INTO #LoanComp VALUES(123069,102922)
> > INSERT INTO #LoanComp VALUES(123034,102922)
> > INSERT INTO #LoanComp VALUES(146725,102922)
> > INSERT INTO #LoanComp VALUES(18419, 102922)
> >
> > INSERT INTO #LoanComp VALUES(122297,102983)
> > INSERT INTO #LoanComp VALUES(122324,102983)
> > INSERT INTO #LoanComp VALUES(146774,102983)
> > INSERT INTO #LoanComp VALUES(122297,102983)
> > INSERT INTO #LoanComp VALUES(146781,102983)
> >
> > INSERT INTO #LoanComp VALUES(124626,104101)
> > INSERT INTO #LoanComp VALUES(124633,104101)
> > GO
> >
> >    SELECT *
> >      FROM #LoanInfo;
> >
> >    SELECT *
> >      FROM #LoanComp;
> >
> >   Rule: Count number of LCPI from #LoanComp based upon the LoanId associate
> > to #LoanInfo table.    
> > -- Result want:
> >
> > LoanId   Status       TotalCountLCPI
> > -------- ----------   --------------
> > 102752   PortActive   0
> > 102922   PortActive   6
> > 102922   PortActive  
> > 102983   PortActive   5
> > 104101   PortActive   2
> 
> Try this:
> 
> 
> SELECT
> 	LoanID,
> 	[Status],
> 	(SELECT COUNT(*) FROM #LoanComp WHERE LoanID=#LoanInfo.LoanID)
> [TotalCountLCPI]
> 		FROM #LoanInfo
> .
> 
0
Utf
12/4/2009 4:55:01 PM
I can't do Count and Group by within the same statement because I have 30 
columns returns and 5 other tables need to JOINs.  Thank you all for your 
help.


"JeffP->" wrote:

> John, I think LN needs a little "group" action and perhaps my counts col 
> should be named... 
> 
> ,TotalCountLCPI = (select count(lcpi) from #loancomp c where c.loanid = 
> a.loanid and lcpi is not null)
> 
> ...but I could be wrong, we'll await LN's response...
> 
> "John" wrote:
> 
> > On Dec 4, 9:25 am, LN <L...@discussions.microsoft.com> wrote:
> > > --SQL 2005
> > >
> > > I need to give a total count of LCPI from another table based upon the
> > > Loanid.  Below show the
> > > business rule and desire results.  Thank you in advance.
> > >
> > >  IF OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL
> > >   DROP TABLE #LoanInfo
> > > GO
> > >
> > > CREATE TABLE #LoanInfo
> > > (
> > >   LoanId    VARCHAR(8)   NULL,
> > >   Status    VARCHAR(10)  NULL
> > > )
> > > GO
> > >
> > > IF OBJECT_ID('Tempdb.dbo.#LoanComp', 'u') IS NOT NULL
> > >   DROP TABLE #LoanComp
> > > GO
> > > CREATE TABLE #LoanComp
> > > (
> > >   LCPI         INT           NULL,
> > >   LoanId       VARCHAR(8)    NULL
> > > )
> > > GO
> > >
> > > INSERT INTO #LoanInfo VALUES ('102752', 'PortActive')
> > > INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
> > > INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
> > > INSERT INTO #LoanInfo VALUES ('102983', 'PortActive')
> > > INSERT INTO #LoanInfo VALUES ('104101', 'PortActive')
> > >
> > > GO
> > >
> > > INSERT INTO #LoanComp VALUES(123079,102922)
> > > INSERT INTO #LoanComp VALUES(123098,102922)
> > > INSERT INTO #LoanComp VALUES(123069,102922)
> > > INSERT INTO #LoanComp VALUES(123034,102922)
> > > INSERT INTO #LoanComp VALUES(146725,102922)
> > > INSERT INTO #LoanComp VALUES(18419, 102922)
> > >
> > > INSERT INTO #LoanComp VALUES(122297,102983)
> > > INSERT INTO #LoanComp VALUES(122324,102983)
> > > INSERT INTO #LoanComp VALUES(146774,102983)
> > > INSERT INTO #LoanComp VALUES(122297,102983)
> > > INSERT INTO #LoanComp VALUES(146781,102983)
> > >
> > > INSERT INTO #LoanComp VALUES(124626,104101)
> > > INSERT INTO #LoanComp VALUES(124633,104101)
> > > GO
> > >
> > >    SELECT *
> > >      FROM #LoanInfo;
> > >
> > >    SELECT *
> > >      FROM #LoanComp;
> > >
> > >   Rule: Count number of LCPI from #LoanComp based upon the LoanId associate
> > > to #LoanInfo table.    
> > > -- Result want:
> > >
> > > LoanId   Status       TotalCountLCPI
> > > -------- ----------   --------------
> > > 102752   PortActive   0
> > > 102922   PortActive   6
> > > 102922   PortActive  
> > > 102983   PortActive   5
> > > 104101   PortActive   2
> > 
> > Try this:
> > 
> > 
> > SELECT
> > 	LoanID,
> > 	[Status],
> > 	(SELECT COUNT(*) FROM #LoanComp WHERE LoanID=#LoanInfo.LoanID)
> > [TotalCountLCPI]
> > 		FROM #LoanInfo
> > .
> > 
0
Utf
12/4/2009 6:08:02 PM
On Dec 4, 10:55=A0am, JeffP-> <Je...@discussions.microsoft.com> wrote:
> John, I think LN needs a little "group" action and perhaps my counts col
> should be named...
>
> ,TotalCountLCPI =3D (select count(lcpi) from #loancomp c where c.loanid =
=3D
> a.loanid and lcpi is not null)
>
> ...but I could be wrong, we'll await LN's response...
>
>
>
> "John" wrote:
> > On Dec 4, 9:25 am, LN <L...@discussions.microsoft.com> wrote:
> > > --SQL 2005
>
> > > I need to give a total count of LCPI from another table based upon th=
e
> > > Loanid. =A0Below show the
> > > business rule and desire results. =A0Thank you in advance.
>
> > > =A0IF OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL
> > > =A0 DROP TABLE #LoanInfo
> > > GO
>
> > > CREATE TABLE #LoanInfo
> > > (
> > > =A0 LoanId =A0 =A0VARCHAR(8) =A0 NULL,
> > > =A0 Status =A0 =A0VARCHAR(10) =A0NULL
> > > )
> > > GO
>
> > > IF OBJECT_ID('Tempdb.dbo.#LoanComp', 'u') IS NOT NULL
> > > =A0 DROP TABLE #LoanComp
> > > GO
> > > CREATE TABLE #LoanComp
> > > (
> > > =A0 LCPI =A0 =A0 =A0 =A0 INT =A0 =A0 =A0 =A0 =A0 NULL,
> > > =A0 LoanId =A0 =A0 =A0 VARCHAR(8) =A0 =A0NULL
> > > )
> > > GO
>
> > > INSERT INTO #LoanInfo VALUES ('102752', 'PortActive')
> > > INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
> > > INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
> > > INSERT INTO #LoanInfo VALUES ('102983', 'PortActive')
> > > INSERT INTO #LoanInfo VALUES ('104101', 'PortActive')
>
> > > GO
>
> > > INSERT INTO #LoanComp VALUES(123079,102922)
> > > INSERT INTO #LoanComp VALUES(123098,102922)
> > > INSERT INTO #LoanComp VALUES(123069,102922)
> > > INSERT INTO #LoanComp VALUES(123034,102922)
> > > INSERT INTO #LoanComp VALUES(146725,102922)
> > > INSERT INTO #LoanComp VALUES(18419, 102922)
>
> > > INSERT INTO #LoanComp VALUES(122297,102983)
> > > INSERT INTO #LoanComp VALUES(122324,102983)
> > > INSERT INTO #LoanComp VALUES(146774,102983)
> > > INSERT INTO #LoanComp VALUES(122297,102983)
> > > INSERT INTO #LoanComp VALUES(146781,102983)
>
> > > INSERT INTO #LoanComp VALUES(124626,104101)
> > > INSERT INTO #LoanComp VALUES(124633,104101)
> > > GO
>
> > > =A0 =A0SELECT *
> > > =A0 =A0 =A0FROM #LoanInfo;
>
> > > =A0 =A0SELECT *
> > > =A0 =A0 =A0FROM #LoanComp;
>
> > > =A0 Rule: Count number of LCPI from #LoanComp based upon the LoanId a=
ssociate
> > > to #LoanInfo table. =A0 =A0
> > > -- Result want:
>
> > > LoanId =A0 Status =A0 =A0 =A0 TotalCountLCPI
> > > -------- ---------- =A0 --------------
> > > 102752 =A0 PortActive =A0 0
> > > 102922 =A0 PortActive =A0 6
> > > 102922 =A0 PortActive =A0
> > > 102983 =A0 PortActive =A0 5
> > > 104101 =A0 PortActive =A0 2
>
> > Try this:
>
> > SELECT
> > =A0 =A0LoanID,
> > =A0 =A0[Status],
> > =A0 =A0(SELECT COUNT(*) FROM #LoanComp WHERE LoanID=3D#LoanInfo.LoanID)
> > [TotalCountLCPI]
> > =A0 =A0 =A0 =A0 =A0 =A0FROM #LoanInfo
> > .- Hide quoted text -
>
> - Show quoted text -

Yes, my fault. I posted before I saw the requirement of null or
nothing in case of a duplicate.
0
John
12/4/2009 7:33:44 PM
Reply:

Similar Artilces:

Excel 97 VBA Help File
In the MS Excel Visual Basic Reference help file contents page, I click on Functions and it only offers me functions beginning with the letter S. So, I have a list of Solver and SQL functions. But what about all the other functions in VBA, for example for doing arithmetic and manipulating dates and strings? Why don't they show up? Are they left out because those functions are all part of Visual Basic generally, and the Excel VBA help file is specific to the _extra_ functions in Excel VBA? It's the only explanation I can think of. Am I right, or have I got a corrupted help file (vbaxl...

How to select series in chart?
I know I asked this question before, but (sigh) I cannot find the answer now, when I need it of course. How can I select a series in an Excel chart (XY Scatter) using the keyboard, not the mouse? The issue is: I have overlapping series, so it is difficult for me to select a series by moving the mouse cursor to a point in one series and right-clicking it, as I normally do. Someone once mentioned a ctrl and/or shift key combination (I think) that would allow me to select each series explicit in round-robin fashion. That is what I am looking for again. More generally, how could I have found...

help need with VC 6.0 IDE and mfc
Hello, First let me explain the scenario where i m using this requirement. We are Using CustomAppWizard and designing a wizard .One of the wizard pages will Insert Composite controls as many as the user wants . 1.So i should be able to dynamically insert ATL controls without using Insert Control Dailog. 2. can any one tell me how to dynamically create Template file in TEMPLATE folder of resource view . 3. I want to include many files created by templet files and add them to build by editing newproj.inf Is it possible to do this. 4.I would even like to know if i have 2 ifles in my C drive h...

Help, I cannot Save!
I created a document and locked the worksheet to protect the formulars before creating a template for the document. But now when I open th document and insert a new sheet using the template I created, th document will refuse to save. Once I click on save, office assistant will say "doc not saved". Wha could I have done wrong? PLease help. computerfinema -- computerfinema ----------------------------------------------------------------------- computerfineman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3716 View this thread: http://www.excelforum.c...

Help Required
Hi, Whenever I open Outlook 2003, I am getting a dialog box which displays the following message: Microsoft Office Outlook has encountered a problem and needs to close. We are sorry for the inconvenience When I click Debug it displays a message box with the following error message "The instruction at "0x3007e993" referenced memory at "0x0000000:. The memory could not be read" When I click No it Visual Studio JIT debugger pops up. I uninstalled and installed several times but still the problem persists. Is there any regsitry entry that I've to modify/delete? ...

Need Help with Deleting Empty Paragraphs in Word 2003
I have written the code below to delete all empty paragraphs at the end of a document and then place the cursor at the end of the last paragraph. It works fine as a stand alone sub in a new doc, but fails inside the real document that contains other code that manipulates several documents. The failure is that it will delete the last empty para, but then gets stuck looping inside the While...Wend because subsequent .Delete are not happening. So, the question is why would this work in one document, but then fail in another? n = 0 ...

Windows Server 2008 R2 04-09-10
Windows Server 2008 R2 and Windows 7 share the same code? how is that possible when Windows 7 has both 32 bit and 64 bit versions and windows server 2008 r2 is only 64 bit Hello Charle, As Microsoft is going to use only 64bit versions for servers they don't built the 32bit version. Sharing the same code doesn't mean that the server OS use exaclty the same files, there are a lot more and different ones. But the basic code is the same. Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights. ...

HELP Recovering addresses and email from Outlook 2003
I had some serious driver issues that required re-installing XP from disc. I did use the backup option and have a backup of all the old data. And of course had to reinstall Office 2003. Will third party software restore my old email and addresses or am I out of luck?? Thanks for the help texraid wrote: > I had some serious driver issues that required re-installing XP from > disc. I did use the backup option and have a backup of all the old > data. And of course had to reinstall Office 2003. > > Will third party software restore my old email and addresses or am I > out of lu...

conversion 04-07-04
anything change over the years.....can Filemaker Pro 4.0 be converted to Access ?? please reply on the Newsgroup, thanks Hi Cat, Not much has changed on this front. You have to export the data from FM into a format Access can read (e.g. CSV), import that into Access, massage the data into a relational structure, and finally re-create the functionality of the FM database using Access's native concepts and tools. On Tue, 6 Apr 2004 19:32:19 -0700, "Cat" <anonymous@discussions.microsoft.com> wrote: >anything change over the years.....can Filemaker Pro 4.0 >be con...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

OLK 2k7
Outlook is behaving strangly with the "through the selected account" option. Each time I restart Outlook the rule fails. When I go in to check on the rules I get told that the rule is "invalid". and the "SELECTED" account is no longer selected. Each time the criteria the account needs to be selected by changes. For example with the following data Account Name Email Account mailserver.domain1.com user@domain1.com mailserver.domain2.com user@domain2.com One time I go in and it's asking me to select the account ...

Help me identify my missing permission (Cannot open public folder) -2147217843 (Maybe Authentication Fails?)
The following snippet of code throws an error number -2147217843. When I googled this error code, I see many references to authentication failed. I am assuming my problem is some kind of permission related problem on the "MyNewFolder" public folder. -- start code --- Dim objFolder As New CDO.Folder Dim f As ADODB.Field 'sURL is like: file://./backofficestorage/mydomain.com/Public Folders/MyNewFolder/ objFolder.DataSource.Open sURL, , adModeReadWrite, adFailIfNotExists --- end code -- I have code that runs before this that actually creates the "MyNewFolder" publ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

GP 10 AP Reconciliation Statement
Hi All, I have one query about the reconcile feature that is available in GP 10 to reconcile the AP and AR to GL. It has come to my notice when i take a recon statement for AP it does not match with the AP historical aged trail balance after taking into consideration the unmatched and potentially matched transactions. I have taken the AP smartlist for the given period and compared it with the transaction being displayed in the recon statement. What has come to my notice is the recon statement is not taking few transaction like invoice or payment for some reason. I faced this issue with almo...

Getting rid of selection
How can I get rid of the selection rectangle? It seems that it's always there, with a heavy black rectangle, or there's a light black rectangle marking where it was. I'm trying to get rid of it altogether, so I can capture an image of the sheet for use in a webpage. I can achieve the effect that I want by selecting a cell which is outside the area that I'm trying to capture, but now that I've found that I cannot get rid of it entirely, it is driving me nuts trying to do so. -- Steve Swift http://www.swiftys.org.uk/swifty.html http://www.ringers.org.uk You could al...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Help with Registration
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, I've tried to register my copy of Office for Mac through the Mactopia page. I log in successfully, but then it just keeps on loading and doesn't refresh or change. Any advice? On 6/16/09 6:35 PM, in article 59b76b64.-1@webcrossing.caR9absDaxw, "theconfuzed1@officeformac.com" <theconfuzed1@officeformac.com> wrote: > I've tried to register my copy of Office for Mac through the Mactopia page. I > log in successfully, but then it just keeps on loading and doesn't refresh or > ...

Help about numeric type field. Thanks.
I created a SQL Server 2005 CE DB ( .sdf, version 3.0) with vs2005. And I created a table which has 2 fields: fld1 - int, fld2 - numeric(38,25). But I encounted an error messagebox when I tried to insert a record (4005,9000000). The msgbox said Conversion overflows. The setting for my numeric fld2 is (precision=38,scale=25). So why occur error when to insert 9000000? Thanks in advance. ...

HELP! Outlook POP3 problem(s)
Hello. I am so lost. I have a few e-mail accounts set up on my computer which retrieves my mail from a couple of different providers and deposits the mail into my Outlook Inbox. Up until yesterday, my mail always has worked fine. For some strange reason, my Outlook is now (Again) retrieving my messages from all of my accounts I had set up, which are all duplicates of my messages. There is now nearly 4,000 duplicate messages in my folders. I can't seem to stop the download of these already retrieved messages. To top things off, a couple of my email account login windows keep p...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...

Spam Filtering HELP
I recently started a new job, and discovered after day one, that I had inharited a spam mess. Now the previous admin ad installed a Symantic Spam Server Prox which in my opinion, was a complete waste of money as it does not allow for blocking IP addresses. Now here is the question; I am running Exchange 2003, and am looking at setting up the Conection Filter under Message Delivery to block messages based on IP address. The problem is that when I save the IPs to be blocked, I get a message stating that the Connection filter "has to be enabled manually through the specific SMTP virtual serv...

Help with simple(?) VBA function
I'm trying to selectively BOLD cells by the use of a User-Defined function. No joy. The VBA Help topics suggest something like this: Function Bold() Worksheets("Sheet1").Range("A1:A5").Font.Bold = True End Function When I try to use it the referenced cells are not changed and the function returns "0". Can anyone point this VBA neophyte in the right direction? Thanks, -Dick- Hi Dick, A function can only return a value. Macros and Functions (Macros as Opposed to Functions) http://www.cpearson.com/excel/differen.htm If all...

Need macro help to close excel
I have created a button in Access2000 that opens an Excel Spreadsheet. What I need now is assit in closing excel upon completion. I can get an excel macro to save my file and close the worksheet, but it is not closing excel entirely. I'm on project with this employer and could use a response today to fix this before I leave. Thanks much to any and all. My macro is as follows: Sub SaveClose() ' ' SaveClose Macro ' Macro recorded 9/27/2004 by cdjohnso ' ' Keyboard Shortcut: Ctrl+Shift+C ' ChDir "I:\SchoolsSurvey\Graphs_Reports" ActiveWorkb...

If Then Help!!!!
Hi! I'm stuck. I have a working macro but it needs a small tweek. The macro executes a find statement and performs calculations from the find to the end of the column. The problem is when nothing is found. I need an if statement or suggestion on how to tell it to skip the calculations if there is nothing found. This is what I have so far(with no if's): Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Selection.End(xlToRi...

Macro
Hi, i need an macro to select all filled cells in range C10:K90. Can this be done? Thanks!!! Range("C10:K90").SpecialCells(xlCellTypeConstants, _ xlNumbers + xlTextValues).Select If you want to select xlErrors and xlLogical add those to xlNumbers + xlTextValues -- Jacob (MVP - Excel) "puiuluipui" wrote: > Hi, i need an macro to select all filled cells in range C10:K90. > Can this be done? > Thanks!!! It's perfect! Thanks! "Jacob Skaria" a scris: > Range("C10:K90").SpecialCells(xlCellTypeConstants, _ >...