Solved

sql, classic asp, asp.net

Posted on 2012-03-30
4
231 Views
Last Modified: 2012-04-12
I have the following sql statement working fine with the attached result.
However, when the  CountOfTmpRefID = "0", the the whole line will be gone.
How can make it like "ABC" still have 0 showing ? meaning when CountofTmpRefId return null or 0, it still be able to keep all of the list of the agency name?

Thanks

SELECT        
COUNT(tblWSBondQuotes.TmpRefID) AS CountOfTmpRefID,
SUM(tblWSBondQuotes.BondAmount) AS SumOfBondAmount,
SUM(tblWSBondQuotes.GrossPrem) AS SumOfGrossPrem,
SUM(tblWSBondQuotes.Commission) AS SumOfCommission,
SUM(tblWSBondQuotes.NetPrem) AS SumOfNetPrem,
Agency.DBA,
tblWSBondQuotes.sAgencyBroker
FROM            tblWSBondQuotes INNER JOIN
                         Agency_Master ON tblWSBondQuotes.sAgencyBroker = Agency_Master.Agency_ID INNER JOIN
                         Agency ON tblWSBondQuotes.sAgencyBroker = Agency.Agency_ID
WHERE        (tblWSBondQuotes.StatusID = @StatusID) AND (Agency_Master.StatusId = 1)
GROUP BY tblWSBondQuotes.sAgencyBroker, Agency.DBA, Agency_Master.AgencyMasterId
HAVING        (Agency_Master.AgencyMasterId = @AgencyMasterId)
3-30-2012-6-49-58-PM.jpg
0
Comment
Question by:Webboy2008
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
ID: 37789943
SELECT        
	COUNT(tblWSBondQuotes.TmpRefID) AS CountOfTmpRefID, 
	SUM(tblWSBondQuotes.BondAmount) AS SumOfBondAmount, 
	SUM(tblWSBondQuotes.GrossPrem) AS SumOfGrossPrem, 
	SUM(tblWSBondQuotes.Commission) AS SumOfCommission, 
	SUM(tblWSBondQuotes.NetPrem) AS SumOfNetPrem, 
	Agency.DBA, 
	tblWSBondQuotes.sAgencyBroker
FROM
        Agency_Master LEFT JOIN
	tblWSBondQuotes ON tblWSBondQuotes.sAgencyBroker = Agency_Master.Agency_ID AND tblWSBondQuotes.StatusID = @StatusID LEFT JOIN
	Agency ON tblWSBondQuotes.sAgencyBroker = Agency.Agency_ID
WHERE
	Agency_Master.StatusId = 1
GROUP BY
	tblWSBondQuotes.sAgencyBroker,
	Agency.DBA,
	Agency_Master.AgencyMasterId
HAVING
	Agency_Master.AgencyMasterId = @AgencyMasterId

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37793490
Something like this perhaps:
SELECT  a.DBA,
        qm.CountOfTmpRefID,
        qm.SumOfBondAmount,
        qm.SumOfGrossPrem,
        qm.SumOfCommission,
        qm.SumOfNetPrem
FROM    Agency a
        LEFT JOIN (SELECT   q.sAgencyBroker,
                            COUNT(q.TmpRefID) CountOfTmpRefID,
                            SUM(q.BondAmount) SumOfBondAmount,
                            SUM(q.GrossPrem) SumOfGrossPrem,
                            SUM(q.Commission) SumOfCommission,
                            SUM(q.NetPrem) SumOfNetPrem
                   FROM     tblWSBondQuotes q
                            INNER JOIN Agency_Master m ON q.sAgencyBroker = m.Agency_ID
                   WHERE    q.StatusID = @StatusID
                            AND m.StatusId = 1
                            AND m.AgencyMasterId = @AgencyMasterId
                   GROUP BY q.sAgencyBroker
                  ) qm ON a.Agency_ID = qm.sAgencyBroker

Open in new window

0
 

Author Comment

by:Webboy2008
ID: 37797505
Both guys's codes do not work...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37802800
Both guys's codes do not work...
Unfortuantely this tells me nothing...

When you can spend the time to explain to me in English what you mean by "codes do not work"  I will be happy to help.  As it is, the feedback is useless.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question