July 11, 2006 at 11:23 pm
I got following masters
Customer
Contact_type (All contacts, which will give was it sale or not)
and also
Contact_result (which will store no. of contacts made and its details which is related to Contact_type master)
---------
Now, I have to get
by postcode
No. of sales else 0
No. contacts made else 0
Is this possible to have it in a single SQL? Else if any other way is also appreciated.
Please help me
July 12, 2006 at 12:29 am
Can you post your table structure, some sample data and expected results please? That would make it much easier for us to help you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 12, 2006 at 12:44 am
TABLE STRUCTURE
Customer table
sfid int
vcTitle varchar
vcFName varchar
vcState varchar
vcPostcode varchar
------------------------
Contact_type table
iCALL_RESULT_ID int
Description .....
------------------------
Contact_Result table
SFID int
iCALL_RESULT_ID int
description varchar
-------------------------
Relation between tables is as below,
Customer.SFID = Contact_Result.SFID
and
Contact_Result.iCALL_RESULT_ID = Contact_type.iCALL_RESULT_ID
-------------------------
I need resut like below
Post Code, Total Contacts made, Total Sales
3056, 15, 2
3011, 9, 1
Here Postcode I can get from CUSTOMER table
Total number of records for a given SFID (Contact_Result table) is total contacts made
Total sales is total no. of records in Contact_Result table which will match 'SALE' in Contact_type table
July 12, 2006 at 3:57 am
Not tested...
SELECT c.vcPostcode, COUNT(r.SFID), COUNT(t.iCALL_RESULT_ID)
FROM Customer c LEFT JOIN Contact_Result r
ON c.SFID = r.SFID
LEFT JOIN Contact_type t
ON r.iCALL_RESULT_ID = t.iCALL_RESULT_ID
AND t.Description = 'SALE'
John
July 12, 2006 at 4:07 am
I think you left out the group by
SELECT c.vcPostcode, COUNT(r.SFID), COUNT(t.iCALL_RESULT_ID)
FROM Customer c LEFT JOIN Contact_Result r
ON c.SFID = r.SFID
LEFT JOIN Contact_type t
ON r.iCALL_RESULT_ID = t.iCALL_RESULT_ID
AND t.Description = 'SALE'
GROUP BY c.vcPostcode
Again, not tested...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 12, 2006 at 7:12 pm
Thanks a lot. It works!!!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply