Entering data in date ranges in SQL Server 2005

Question

I have a table with ranges of weeks (week number, start date, end date) and a table with training dates (for writing tutors (tutor ID, tutorial_date, type of tutorial (A or B).

I want to create two queries that show the week ranges (week 1, week 2) above, with the names of teachers on the side with textbook counters (type “A”) in this range of week dates in each block this week.

The result should look like this:

Counting Type A Training Aids

Tutor|Week One|Week Two|Week Three|Week Four|Total
Joe  |   3    |   5    |    7     |    8    | 23        
Sam  |   2    |   4    |    3     |    8    | 17        

Having said that, Joe completed 3 textbooks in the first week, five in two weeks, 7 in the third week and 8 in week 4.

The second query should display the totals for the textbook type "A" and type "B"

Tutor|Week One|Week Two|Week Three|Week Four|Total   |
Joe  |  3/1   |   5/3  |   7/2    |   8/2   | 23/8   |     
Sam  |  2/3   |   4/4  |   3/2    |   8/3   | 17/12  |      

, , 3 A 1 B.

( )

Tutor | Tutorial_ID | Tutorial Date |Type|
------------------------------------------
Joe   |    1        | 2011-01-01    | A  |
Joe   |    2        | 2011-01-02    | A  |
Joe   |    3        | 2011-01-03    | A  |
Joe   |    4        | 2011-01-03    | B  |
Sam   |    5        | 2011-01-01    | A  |
Sam   |    6        | 2011-01-02    | A  |
Sam   |    7        | 2011-01-03    | B  |

:

weekNumber |startDate |endDate
1          |2011-01-01|2011-01-15

SQL Server 2005

Answer 1

.

, PIVOT "A", PIVOT

select *
from 
(
  select w1.tutor
    , w1.type
    , wk.weeknumber
  from w1
  inner join wk
    on w1.tutorialdate between wk.startdate and wk.enddate
  where w1.type = 'a'
) x
pivot
(
  count(type)
  for weeknumber in ([1])
)p 

. SQL Fiddle

Count() CASE.

select w1.tutor
  , COUNT(CASE WHEN w1.type = 'A' THEN 1 ELSE null END) [Week One]
from w1
inner join wk
  on w1.tutorialdate between wk.startdate and wk.enddate
group by w1.tutor

. SQL Fiddle with Demo

Count() CASE

select w1.tutor
  , Cast(COUNT(CASE WHEN w1.type = 'A' AND wk.weeknumber = 1 THEN 1 ELSE null END) as varchar(10))
     + ' / '
     + Cast(COUNT(CASE WHEN w1.type = 'B' AND wk.weeknumber = 1 THEN 1 ELSE null END) as varchar(10)) [Week One]
   , Cast(COUNT(CASE WHEN w1.type = 'A' AND wk.weeknumber = 2 THEN 1 ELSE null END) as varchar(10))
     + ' / '
     + Cast(COUNT(CASE WHEN w1.type = 'B' AND wk.weeknumber = 2 THEN 1 ELSE null END) as varchar(10)) [Week Two]
from w1
inner join wk
  on w1.tutorialdate between wk.startdate and wk.enddate
group by w1.tutor

SQL Fiddle with Demo

, AndriyM , PIVOT, :

SELECT *
FROM
(
  select distinct w1.tutor
    , wk.weeknumber
    , left(total, len(total)-1) Totals
  FROM w1
  inner join wk
      on w1.tutorialdate between wk.startdate and wk.enddate
  CROSS APPLY
  (
    SELECT cast(count(w2.type) as varchar(max)) + ' / '
    from w1 w2
    inner join wk wk2
      on w2.tutorialdate between wk2.startdate and wk2.enddate
    WHERE w2.tutor = w1.tutor
      AND wk2.weeknumber = wk.weeknumber
    group by w2.tutor, wk2.weeknumber, w2.type
    FOR XML PATH('')
  )  D ( total )
)  x
PIVOT
(
  min(totals)
  for weeknumber in ([1], [2])
) p

SQL Fiddle with Demo