Link to home
Create AccountLog in
Avatar of dlavar
dlavar

asked on

Stored Procedure not giving correct data.

The query below is resulting in the following data which is not what i want..

vchRecordID       vchManufacturer              vchCertificationType    Number of Brands  Total Amount Owed
T-110057      COMMONWEALTH BRANDS, INC      Initial      6      6000


what it really should be populating is 3 for number of distinct brands and 3000 for total amount owed.

What wrong with my query?  I know its populating the following because in the tblCLBrandFamily the recordid is in there 3 different times which is possible. I've tried adding distinct to the query but it doesn't work. how can i get it to work?
SELECT  t.vchRecordID, t.vchManufacturer, a.vchCertificationType, 
    COUNT(b.vchBrandFamily)as 'Number of Brands', 'Total Amount Owed'=
        (Case
    when vchCertificationType = 'Initial' then '1000'
    when vchCertificationType = '3 Year Renewal' then '1000'
    when vchCertificationType ='Corrective Filing' then '0'
    end) * COUNT(b.vchBrandFamily)
    from tblCLTestLab t
    inner join tblCLApplicants a on t.vchManufacturer = a.vchManufacturer
    Inner join  tblCLBrandFamily b on t.vchRecordID = b.vchRecordID
    where t.vchRecordID = 'T-110057'
    group by t.vchRecordID, t.vchManufacturer,  a.vchCertificationType

Open in new window

Avatar of BourbonKid
BourbonKid
Flag of United States of America image

Just try the query below. It is likely that you got six rows (each duplicated two times) :

SELECT  t.vchRecordID, t.vchManufacturer, a.vchCertificationType, 
    b.vchBrandFamily as 'Number of Brands'
from tblCLTestLab t
inner join tblCLApplicants a on t.vchManufacturer = a.vchManufacturer
Inner join  tblCLBrandFamily b on t.vchRecordID = b.vchRecordID
where t.vchRecordID = 'T-110057'
order by t.vchRecordID, t.vchManufacturer, a.vchCertificationType, b.vchBrandFamily

Open in new window

SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of dlavar
dlavar

ASKER

Sorry it took so long for me to respond.  For some reason it would let me pull up the answers you all were posting.