Userid | Date
 |
   101       |   15 Aug ,2011
   102       |   15 Aug ,2011
   103       |   16 Aug ,2011
   104       |   16 Aug ,2011
   105       |   17 Aug ,2011

Userid(f.k) |  sts
   101       |   x
   102       |   y
   101       |   z
   103       |   x
   101       |   y

Date | Total | Sts (if=x) |Sts (if=y) |Avg (Total/2)

15 Aug| 20   |  15        |5          |10
16aug | 30   |  22        |8          |15

select Date, select case when [Tasble-B].Sts='x' Then 0 else 1 END as StsX,select case when [Tasble-B].Sts='Y' Then 0 else 1 END as StsY,Total/2
from
   Table-A
     inner join  Table-B on Table-A.UserID = Table-B.UserID
     b group by RegDate

select Date,
       select case when [Tasble-B].Sts='x' Then 0
                   else 1
              END as StsX,
       select case when [Tasble-B].Sts='Y' Then 0
                   else 1
              END as StsY,
       Total/2
from Table-A
  inner join Table-B
    on Table-A.UserID = Table-B.UserID
b group by RegDate

select [Date],
       (SUM(case when B.Sts='x' Then 1
                   else 0
              END) +
       SUM( case when B.Sts='Y' Then 1
                   else 0
              END)) AS Total,
       SUM( case when B.Sts='x' Then 1
                   else 0
              END) AS StsX,
       SUM( case when B.Sts='Y' Then 1
                   else 0
              END) as StsY,
       (SUM(case when B.Sts='x' Then 1
                   else 0
              END) +
       SUM( case when B.Sts='Y' Then 1
                   else 0
              END))
        /2.0 AS Average
from A
  inner join B
    on A.UserID = B.UserID
group by [Date]

WITH A (UserId, [Date]) AS (
 SELECT 101, '2011-08-15'
 UNION
 SELECT 102, '2011-08-15'
 UNION
 SELECT 103, '2011-08-16'
 UNION
 SELECT 104, '2011-08-16'
 UNION
 SELECT 105, '2011-08-17'
 ),
 B (UserId, sts) AS
 (
   SELECT 101, 'x'
   UNION
   SELECT 102, 'y'
   UNION
   SELECT 101, 'z'
   UNION
   SELECT 103, 'x'
   UNION
   SELECT 101, 'y'
)
select [Date],
       (SUM(case when B.Sts='x' Then 1
                   else 0
              END) +
       SUM( case when B.Sts='Y' Then 1
                   else 0
              END)) AS Total,
       SUM( case when B.Sts='x' Then 1
                   else 0
              END) AS StsX,
       SUM( case when B.Sts='Y' Then 1
                   else 0
              END) as StsY,
       (SUM(case when B.Sts='x' Then 1
                   else 0
              END) +
       SUM( case when B.Sts='Y' Then 1
                   else 0
              END))
        /2.0 AS Average
from A
  inner join B
    on A.UserID = B.UserID
group by [Date]