The select statement for this is
SELECT dbo.TEST1.[OFFICE NAME],COUNT(dbo.TEST1.[ACCOUNT ID])AS AccountCountFROM dbo.Test2INNERJOIN dbo.test3INNERJOIN dbo.Test4ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code]INNERJOIN dbo.TEST1ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID]ON dbo.Test2.[Model ID] = dbo.test3.IDINNERJOIN dbo.[Inquiry Details]ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryIDWHERE (dbo.Test2.InquiryDateBETWEENCONVERT(DATETIME, @.startDate, 102)ANDCONVERT(DATETIME, @.endDate, 102))AND dbo.Test1.[Account ID]IN(SELECT [account id]FROM test5WHERE [Contact ID] = @.contactId)GROUP BY dbo.TEST1.[OFFICE NAME]ORDER BYCOUNT(dbo.TEST1.[ACCOUNT ID])DESC
name id count
case1 226 320
case2 219 288
case3 203 163
case4 223 90
case5 224 73
i have another select stnat which returns like this
The select statement is
Group by test1.[Office Name]
order by count(test1.[office name]) DESC
name count
case6 10
case2 56
case4 66
case1 74
case3 88
case7 100
case5 177
How can i combine this select stament with the SP, so that, i get a fourth column with
case1 226 320 74
case2 219 288 56
....................
.....................
Hope i am not confusing you all
Please help me, if someone knows how to combine this?
Thanks
Use an alias for the Office Name column for both statements and add the id column to your first select( you need to add this column to your the GROUP BY list). Then you can use an INNER JOIN on this name column and retrieve all three columns.
Something like:
SELECT t1.name, t1.id, t1.AccountCount, t2.AccountCount2 FROM (SELECT dbo.TEST1.[OFFICE NAME] as name, [ACCOUNT ID] as id,COUNT(dbo.TEST1.[ACCOUNT ID])AS AccountCount
FROM dbo.Test2INNERJOIN
dbo.test3INNERJOIN
dbo.Test4ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code]INNERJOIN
dbo.TEST1ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID]ON dbo.Test2.[Model ID] = dbo.test3.IDINNERJOIN
dbo.[Inquiry Details]ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID
WHERE (dbo.Test2.InquiryDateBETWEENCONVERT(DATETIME, @.startDate, 102)ANDCONVERT(DATETIME, @.endDate, 102))AND dbo.Test1.[Account ID]IN(SELECT [account id]FROM test5WHERE [Contact ID] = @.contactId)
GROUP BY name, id ) t1 INNER JOIN (Select test1.[office name] as name, count(test1.[office name]) as AccountCount2 From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code]
Group by test1.[Office Name] ) t2 ON t1.name=t2.name
ORDER BY t1.AccountCount DESC
|||
I think you've forgotten a column in your first select statement. Your first select statement selects only two columns while the output shows three columns, name, id and count. Please check and repost.
