Out of a sample of 100 demands what else was asked for?

Question

I am doing some work on an inbound call demand capture system where each call could have one or more than one demands linked to it.

There is a CaptureHeader table with CallDate, CallReference and CaptureID and a CaptureDemand table with CaptureID and DemandID.

EDIT:
I have added some representative data to show what would be expected in each table.

CaptureHeader

CaptureID | CallReference | CallDate
-----------------------------------------------
1         | 1             | 2009-11-02 20:37:00
2         | 3             | 2009-11-02 20:37:05
3         | 2             | 2009-11-02 20:37:10
4         | 4             | 2009-11-02 20:38:00
5         | 5             | 2009-11-02 20:38:30

CaptureDemand

DemandID | CaptureID | DemandText
------------------------------------
1        | 1         | Fund value
2        | 2         | Password reset
3        | 2         | Fund value
4        | 3         | Change address
5        | 3         | Fund value
6        | 3         | Rate change
7        | 3         | Fund value
8        | 4         | Variable to fixed
9        | 4         | Change address
10       | 5         | Fund value
11       | 5         | Address change

Using the tables above a filter on 'Fund value' would bring back call references of 1, 2, 3, 3, 5 because 3 has two fund values.

If I did a DISTINCT on this because I have ordered by date it would ask me to show that which would also give me two lines for 3.

To get the full set of data I would do the following query:

SELECT * FROM CaptureHeader AS ch
JOIN CaptureDemand AS cd ON ch.CaptureID = cd.CaptureID
JOIN DemandDetails AS dd ON cd.DemandID = dd.DemandID

What I would like though is to get the last 100 headers by date for a particular demand. Where it gets tricky is when there is more than one of the same demand on a header for a particular reference which is possible.

I would like 100 unique call references because I then need to get back all the demands for those call references and then count how many of each other demand was also recorded in the same call.

EDIT:
I would like to be able to say 'WHERE DemandID = SomeValue' to select my 100 references.

In other words out of 100 "value requested" demands what else was asked for. If this doesn't make sense let me know and I will try and modify the question to be clearer.

I would like to get a table like this:

Demands          | Count
------------------------
Demand asked for | 100
Another demand   |  36
Third demand     |  12
Fourth demand    |   6

Cheers, Ian.

Answer 1

Now that the sample data made your requirement more explicit, I believe the following will generally server your needs. It is essentially the same as previous submission, with an added condition on the JOIN; this condition essentially excludes any CaptureDemand row for which we readily have the same DemandText (within the same Capture), only retaining the one with the lowest DemandId.

WITH myCTE (CaptId, NbOfDemands)
AS (
  SELECT CaptureID, COUNT(*)  -- Can use COUNT(DISTINCT DemandText)
  FROM CaptureDemand
  WHERE CaptureID IN 
    (SELECT TOP 100 C.CaptureID 
     FROM CaptureHeader C
     JOIN CaptureDemand D ON C.CaptureID = D.CaptureID
        AND NOT EXISTS (
           SELECT * FROM CaptureDemand X
           WHERE X.CaptureId = D.CaptureId AND X.DemandText = D.DemandText
              AND X.DemandId < D.DemandId
        )
     WHERE D.DemandText= 'Fund Value'
     ORDER BY CallDate DESC)
)

SELECT NbOfDemands, COUNT(*)
FROM myCTE
GROUP BY NbOfDemands
ORDER BY NbOfDemands

What this query provides: The number of Captures which had exactly one demand The number of Captures which had exactly two demands .. The number of Captures which had exactly n demands

For the 100 MOST RECENT Captures which included a Demand of a particular value 'someValue' (and, this time, giving indeed 100, i.e. not counting the same CaptureID twice in case of dups on the Demand Type).

A few points:

• You may want to use COUNT(DISTINCT DemandText) rather than COUNT(*) in the select list of the CTE. (We do include 100 distinct CaptureIDs, i.e. that the Capture #3 in your sample doesn't come twice and hence hiding another capture at the end of the list, but we need to know if this #3 Capture should be counted as 3 Demands or a 4 Demands capture).
• Oops, not quite what you required because each line show the number of Capture instances that have exactly this amount of demands...
• use a CASE on NbOfDemands to display the text as in the question (trivial)
• This may show Capture instances with more than 4 demands, but that's probably a plus (if any), but that is probably a plus
• This would not show 0 if for example there were no Capture instances with the given number of demands.

Answer 2

It sounds like you are trying to solve a Many to Many problem with just two tables and you really need three tables. For example:

TABLE Calls

CallId | CallDate
----------------------------
1      | 2009-11-02 20:37:00
2      | 2009-11-02 20:37:05
3      | 2009-11-02 20:37:10
4      | 2009-11-02 20:38:00
5      | 2009-11-02 20:38:30

TABLE Requests

RequestId | RequestType
----------------------------
1         | Fund value
2         | Password reset
3         | Change address
4         | Rate change
5         | Variable to fixed

TABLE CallRequests (resolves the many to many)

CallId |RequestId
-----------------
1      |1        
2      |2      
2      |1      
3      |3      
3      |1      
3      |4
3      |1
4      |5
4      |3
5      |1
5      |3    

This data structure will let you query from the Call side of things and from the Request side of things.