SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 getting linked data from 2 tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shonar
Starting Member

2 Posts

Posted - 04/19/2013 :  11:23:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 04/19/2013 :  12:25:40  Show Profile  Reply with Quote
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;
Go to Top of Page

shonar
Starting Member

2 Posts

Posted - 04/19/2013 :  16:32:32  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 04/19/2013 :  17:42:56  Show Profile  Reply with Quote
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;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000