getting linked data from 2 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

getting linked data from 2 tables

Author

Topic

shonar
Starting Member

2 Posts

Posted - 2013-04-19 : 11:23:47

Hi

I have 2 tables
Table: Audit

Application
CacheId
ResponseSource
Surname
Postcode

Table : ResponseCache
CacheId
Data

Source can only have values- Direct or Cache

Different application request this data, everytime any applications requests data an entry is posted in audit table.
Entry is only posted into ResponseCache the first time any application requests the data, where the corresponding audit entry will have responseSource as 'Direct'.

All the subsequent calls for the same data will be from Responsecache and the Audit entry will have responseSource as 'cache'.So that all apps can share data.

I need to find how many enteries where inserted by each application into ResponseCache and then how many were cross used. Something like in example below.

Table : Audit
Application CacheId Source Postcode Surname
Application1 1 Direct XY12AB Name1
Application1 1 Direct XY12AB Name1
Application1 2 Direct AB23YZ Name2
Application2 3 Direct AB12YZ Name3
Application1 1 Cache XY12AB Name1
Application2 1 Cache XY12AB Name1
Application1 3 Cache AB12YZ Name3
Application2 1 Cache XY12AB Name1
Application2 1 Cache XY12AB Name1

CacheID Data
1 XYZ
2 ABC
3 test

Expected result:
Application Direct UsedDataCachedbyOthers
Application1 2 1
Application2 1 1

Any hints how can I query this.

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-19 : 12:25:40

Not 100% clear on the rules you are using, but perhaps this?

select
	Application,
	count(distinct case when Source = 'Direct' then CacheId end) as Direct,
	count(distinct case when Source = 'Cache'  then CacheId end) as UsedDataCachedByOthers 
from
	Audit
group by
	Application;

shonar
Starting Member

2 Posts

Posted - 2013-04-19 : 16:32:32

Thanks for your response, I want to check how many queries were made directly by each application as that inserts an entry into cache and if that entry was used by any other application. But if the same entry is used again and again I need to count it as one.
Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-19 : 17:42:56

Hm.. not quite sure of the rules yet - but did the query I posted earlier give incorrect results for Application 1 (but all else seemed ok)? if so, can you try this?

SELECT a.Application, a.N, b.N FROM 
 ( SELECT Application, COUNT(DISTINCT CacheId) N FROM Audit a WHERE Source = 'direct'  GROUP BY Application ) a
 LEFT JOIN 
 ( SELECT Application, COUNT(DISTINCT CacheId) N FROM Audit a WHERE Source = 'Cache' 
	AND NOT EXISTS (SELECT * FROM Audit b WHERE b.Source = 'direct' 
	AND a.Application = b.Application AND a.cacheid = b.cacheid )GROUP BY Application 
) b ON a.Application = b.Application;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources