August 6, 2001 at 2:29 pm
I'm having some problems writing a SP to do some reporting on a table. I need to count the number of rows that meet one criteria and count the number of rows that meet another criteria (which is mutually exclusive from the other) and return each as a different column. I apologize for being so confusing. Here is an example of what I've done so far but isn't working.
SELECT
dbo.tblUser.iUserID,
dbo.tblCampaign.iCampaignID,
dbo.tblCall.sBTN,
(SELECT count(dbo.tblCall.sBTN)
FROM
dbo.tblCall INNER JOIN dbo.tblCampaign
ON dbo.tblCall.iCampaignID = dbo.tblCampaign.iCampaignID
INNER JOIN dbo.tblProdToCamp
ON dbo.tblCampaign.iCampaignID = dbo.tblProdToCamp.iCampaignID
INNER JOIN dbo.tblProduct
ON dbo.tblProdToCamp.iProductID = dbo.tblProduct.iProductID
INNER JOIN dbo.tblUser
ON dbo.tblCall.iUserID = dbo.tblUser.iUserID
WHERE (dbo.tblProduct.szProdDesc LIKE '%Long Distance%')
GROUP BY
dbo.tblUser.iUserID,
dbo.tblCall.sBTN,
dbo.tblCampaign.iCampaignID),
(SELECT count(dbo.tblCall.sBTN)
FROM
dbo.tblCall INNER JOIN dbo.tblCampaign
ON dbo.tblCall.iCampaignID = dbo.tblCampaign.iCampaignID
INNER JOIN dbo.tblProdToCamp
ON dbo.tblCampaign.iCampaignID = dbo.tblProdToCamp.iCampaignID
INNER JOIN dbo.tblProduct
ON dbo.tblProdToCamp.iProductID = dbo.tblProduct.iProductID
INNER JOIN dbo.tblUser
ON dbo.tblCall.iUserID = dbo.tblUser.iUserID
WHERE (dbo.tblProduct.szProdDesc LIKE '%Toll Free%')
GROUP BY
dbo.tblUser.iUserID,
dbo.tblCall.sBTN,
dbo.tblCampaign.iCampaignID)
FROM
dbo.tblCall INNER JOIN dbo.tblCampaign
ON dbo.tblCall.iCampaignID = dbo.tblCampaign.iCampaignID
INNER JOIN dbo.tblUser
ON dbo.tblCall.iUserID = dbo.tblUser.iUserID
GROUP BY
dbo.tblCall.sBTN,
dbo.tblCampaign.iCampaignID,
dbo.tblUser.iUserID
I'd appreciate any help.
Thanks,
Jason
August 6, 2001 at 2:42 pm
Which part isnt working? The final group by looks weird but I have not tried it yet. Can you post ddl for the tables involved along with some insert statements to load some minimal test data?
Andy
August 7, 2001 at 12:24 pm
Sorry it took awhile to get back, I was having some problem getting on.
Anyway, I kept working on trying to solve my problem and ended up using temp tables. I broke out my query into 3. I do the summarization on one criteria and use the INTO statement to create a temp table and repeat the same steps with the other criteria. Then I wrote one final SELECT to join the two temp tables to get one result set.
I appreciate your time and help.
Thanks,
Jason
August 9, 2001 at 5:02 pm
There's an easier way to do it. Use a statement like this:
--
SELECT dbo.tblUser.iUserID,
dbo.tblCampaign.iCampaignID,
dbo.tblCall.sBTN,
COUNT(CASE WHEN dbo.tblProduct.szProdDesc LIKE '%Long Distance%' THEN 1 ELSE NULL END) AS LongDistance,
COUNT(CASE WHEN dbo.tblProduct.szProdDesc LIKE '%Toll Free%' THEN 1 ELSE NULL END) AS TollFree
FROM
dbo.tblCall INNER JOIN dbo.tblCampaign
ON dbo.tblCall.iCampaignID = dbo.tblCampaign.iCampaignID
INNER JOIN dbo.tblProdToCamp
ON dbo.tblCampaign.iCampaignID = dbo.tblProdToCamp.iCampaignID
INNER JOIN dbo.tblProduct
ON dbo.tblProdToCamp.iProductID = dbo.tblProduct.iProductID
INNER JOIN dbo.tblUser
ON dbo.tblCall.iUserID = dbo.tblUser.iUserID
GROUP BY
dbo.tblUser.iUserID,
dbo.tblCall.sBTN,
dbo.tblCampaign.iCampaignID
--
One statement, and it avoids temp tables and the like. You can add a WHERE clause, too, to narrow down your data if the table is large; I'd suggest something like:
WHERE dbo.tblProduct.szProdDesc LIKE '%Toll Free%' OR dbo.tblProduct.szProdDesc LIKE '%Long Distance%'
Give it a shot.
Matthew Burr
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply