T-SQL Dynamic Query and pivot

Question

I have data where people have changed role mid month and want to count the activity after their new start date. Can I use the results of a table as a dynamic Query, I have a query which returns the following resultset:-

Firstname    Surname    StartDate
----------------------------------
Jon          Smith      2015-01-01
Paul         Jones      2014-07-23
...

So the query would look something like:

SELECT Firstname +' '+ surname, month,  count(1) FROM dataTable
WHERE (Firstname='John' AND Surname='Smith' AND date >=2015-01-01)
OR (Firstname='Paul' AND Surname='Jones' AND date >=2014-07-23)
OR ...

but the number of 'ORs' would depend on the number of rows in the first table

Name        Month   Count
----------------------------------
Jon Smith   1       15
Paul Jones  1       16
Jon Smith   2       30
Paul Jones  2       25
Charlie Gu  1       52 

Which I can then pivot to get

Name          1      2
--------------------------
Jon Smith     15     30
Paul Jones    16     25
Charlie Gu    52     NULL

Thanks in advance

Answer 1

Please refer this - it will give you complete idea how you can get dynamic column query.Dynamic Column Generation - Pivot [SQL]

Answer 2

It seems to me that Ako is right and a simple join should do the trick rather than a dynamic query.

declare @NewStartDates table
    (
     Firstname nvarchar(100),
     Surname nvarchar(100),
     StartDate date
    );

insert  into @NewStartDates
        (Firstname, Surname, StartDate)
values  (N'Jon', N'Smith', '20150101'),
        (N'Paul', N'Jones', '20140723');

select  d.Firstname,
        d.Surname,
        year(d.Date) * 100 + month(d.Date) as Period,
        count(*) as ActivityCount
from    dataTable as d
inner join @NewStartDates as n
        on d.Firstname = n.Firstname
           and d.Surname = n.Surname
           and d.Date >= n.StartDate
group by d.Firstname,
        d.Surname,
        year(d.Date) * 100 + month(d.Date);