How to link the following 2 tables to get the result which is shown below in SQL Server? (continuation)

Visit

LoginID, StaffName, qno, trackno, tmstamp,    Service
1,       James,     1001, 1,      01-01-2011, No1     (transfer to No2)
2,       John,      1002, 2,      01-01-2011, No1
2,       John,      1003, 3,      01-01-2011, No1
1,       James,     1001, 1,      01-01-2011, No2     (transfered from No1)
2,       James,     1003, 3,      01-01-2011, No1     (recall the queue no)

Matter

content, trackno, tmstamp
001,      1,       01-01-2011
002,      1,       01-01-2011
001,      2,       01-01-2011
002,      2,       01-01-2011
003,      2,       01-01-2011
001,      3,       01-01-2011
001,      1,       01-01-2011
001,      3,       01-01-2011
002,      3,       01-01-2011

Result

StaffName, tmstamp,   noOfQno, noOfContent
James,     01-01-2011, 3,       5
John,      01-01-2011, 2,       4

I already asked this question. (similar to this question.)

how to link the following 2 tables to get the result which is shown below in SQL Server?`

And the correct answer is already there.

SELECT StaffName, tmstamp, noOfQno, noOfContent
  FROM (
                SELECT StaffName, tmstamp, trackno, COUNT(1) noOfQno
                    FROM Visit
                GROUP BY StaffName, tmstamp, trackno
             ) a LEFT JOIN
             (
                SELECT trackno, COUNT(1) noOfContent
                    FROM Matter 
                GROUP开发者_运维问答 BY    trackno
             ) b
    ON  b.trackno = a.trackno

But according to that answer, the answer is a bit wrong.

The result I will get according to that answer is..

Result
StaffName, tmstamp,   noOfQno, noOfContent
James,     01-01-2011, 3,       6
John,      01-01-2011, 2,       6

That is because

For line 1 of the Visit table, the noOfContenct should be2 and for line 4 of Visit, noOfContent should be 1. So, altogether 3.

But according to that answer: for line 1, noOfContent is 3, for line 4 also 3. Then the total value become 6.

So I am asking how to update that statement.

P.S. the statement will not give the total like the result. Instead it will give line by line values.

You can not do what you want with the tables you have. If I understand you correctly, you mean that row 1 in Visit is linked to row 1 and 2 in Matter and that row 4 in Visit is linked to row 7 in Matter and that will give noOfContent equals 3.

The only field you have to link between the tables is trackno and using that you will have three rows from Matter for row 1 in Visit and three rows for row 7 so noOfContent is 6.

It might be possible to link on field tmstamp if that is the same in Visit and Matter for the rows that should be linked. You have to check your actual data to see if that is the case.

The correct answer to this question is that you need to redesign your table structure and identify a primary key in Visit and add that as a foreign key in Matter.

Edit 1 The answer given by Crimsonland gives the correct result because of an assumption that one value in trackno column can not be reused by another StaffName. It looks like that in your data, trackno 1 belongs to John and trackno 2 and 3 belongs to James. If that is the case, that query will work for you.

Edit 2 Here is a version where I use the tmstamp to figure out what Matter is related to what Visit. I assume that Matter.tmstamp is greater than Visit.tmstamp for the linked rows, and I assume that the last inserted row in Matter for one row in Visit has a tmstamp value less than the next tmstamp value in Visit.

declare @Visit table
(
  LoginID int,
  StaffName varchar(50),
  qno int,
  trackno int,
  tmstamp datetime,
  [Service] char(3)
)

declare @Matter table
(
  content int,
  trackno int,
  tmstamp datetime
)

insert into @Visit values (1, 'James', 1001, 1, '2011-01-01 00:00:00', 'No1')
insert into @Visit values (2, 'John',  1002, 2, '2011-01-01 00:00:10', 'No1')
insert into @Visit values (2, 'John',  1003, 3, '2011-01-01 00:00:20', 'No1')
insert into @Visit values (1, 'James', 1001, 1, '2011-01-01 00:00:30', 'No2')
insert into @Visit values (2, 'James', 1003, 3, '2011-01-01 00:00:40', 'No1') 

insert into @Matter values (001, 1, '2011-01-01 00:00:01')
insert into @Matter values (002, 1, '2011-01-01 00:00:02')
insert into @Matter values (001, 2, '2011-01-01 00:00:11')
insert into @Matter values (002, 2, '2011-01-01 00:00:12')
insert into @Matter values (003, 2, '2011-01-01 00:00:13')
insert into @Matter values (001, 3, '2011-01-01 00:00:21')
insert into @Matter values (001, 1, '2011-01-01 00:00:31')
insert into @Matter values (001, 3, '2011-01-01 00:00:41')
insert into @Matter values (002, 3, '2011-01-01 00:00:42')

;with cteVisit
as
(
  select
    StaffName,
    qno,
    trackno,
    tmstamp as VisitStart,
    (select coalesce(min(tmstamp), GetDate())
     from @Visit as V2
     where V2.tmstamp > V1.tmstamp) as VisitStop
  from @Visit as V1
)
select
  V.StaffName,
  max(VisitStart) as tmstamp,
  (select count(*)
   from cteVisit as V2
   where V2.StaffName = V.StaffName) as noOfQno,
  (select count(*)
   from @Matter as M
     inner join cteVisit V3
      on M.tmstamp >= V3.VisitStart and
         M.tmstamp < V3.VisitStop
   where V3.StaffName = V.StaffName) as noOfQno
from cteVisit as V
group by StaffName

Result

StaffName tmstamp                 noOfQno noOfQno
James     2011-01-01 00:00:40.000 3       5
John      2011-01-01 00:00:20.000 2       4

Try This:

I create sample table with exact data from your example. Based on that i test your query and came up with this.

1st Query: SELECT StaffName, tmstamp, trackno, COUNT(1) noOfQno FROM Visit GROUP BY StaffName, tmstamp, trackno

StaffName   tmstamp trackno noOfQno
James   01-01-2011  1   2
John    01-01-2011  2   1
John    01-01-2011  3   2

2nd Query: SELECT trackno, COUNT(1) noOfContent FROM Matter GROUP BY trackno

trackno noOfContent
1   3
2   3
3   3

Join :

SELECT StaffName, tmstamp, SUM(noOfQno) asnoOfQno ,SUM(noOfContent) as noOfContent
  FROM (
                SELECT StaffName, tmstamp, trackno, COUNT(1) noOfQno
                    FROM Visit
                GROUP BY StaffName, tmstamp, trackno
             ) a LEFT JOIN
             (
                SELECT trackno, COUNT(1) noOfContent
                    FROM Matter 
                GROUP BY    trackno
             ) b
    ON  b.trackno = a.trackno
    group by StaffName, tmstamp

Result:

StaffName   tmstamp noofQno noofContent
James   01-01-2011  2   3
John    01-01-2011  3   6

Regards

I'm not entirely sure about joining on the timestamp.

You can left join visit to Matter on track Nº and use the CASE WHEN...ELSE.... structure. Something like this (this is not correct SQL code)

SELECT StaffName, tmstamp, 
    SUM(CASE WHEN qno IS NOT NULL THEN 1 ELSE 0) AS noOfQno,
    SUM(CASE WHEN content IS NOT NULL THEN 1 ELSE 0) AS noOfContent
  FROM Visit LEFT JOIN Matter
    ON Visit.trackno = Matter.trackno
 GROUP BY StaffName, tmstamp

Again, not perfect code! But that's the essential idea.

Hope it helps!