I have the following schema:
CREATE TABLE Person ( PersonId int PRIMARY KEY ) CREATE TABLE Action ( ActionId int PRIMARY KEY, PersonId int NOT NULL FOREIGN KEY REFERENCES Person(PersonId), ActionTime datetime NOT NULL )
and the following data:
INSERT INTO Person (PersonId) VALUES (1), (2), (3), (4) INSERT INTO Action (ActionId, PersonId, ActionTime) VALUES (1, 1, '2014-02-01'), (2, 1, '2014-02-02'), (3, 2, '2014-02-02'), (4, 3, '2014-03-05')
I would like to run a query which shows the number of actions each person performs between the 15th of each month. For example, I am trying the following:
SELECT Person.PersonId, COALESCE(GroupA_Actions_Made, 0) AS GroupA_Actions_Made, COALESCE(GroupB_Actions_Made, 0) AS GroupB_Actions_Made FROM Person RIGHT OUTER JOIN ( SELECT PersonId, COUNT(*) AS GroupA_Actions_Made FROM Action WHERE ActionTime BETWEEN '2014-01-15 00:00:00' AND '2014-02-14 23:59:59' GROUP BY PersonId ) GroupA ON GroupA.PersonId = Person.PersonId RIGHT OUTER JOIN ( SELECT PersonId, COUNT(*) AS GroupB_Actions_Made FROM Action WHERE ActionTime BETWEEN '2014-02-15 00:00:00' AND '2014-03-14 23:59:59' GROUP BY PersonId ) GroupB ON GroupB.PersonId = Person.PersonId
However, the query I am trying is returning the following:
PersonId | GroupA_Actions_Made | GroupB_Actions_Made (null) 0 1
but I would like
PersonId | GroupA_Actions_Made | GroupB_Actions_Made 1 2 0 2 1 0 3 0 1
(I do not want the results to return anything for people who have not made actions.)
How can I obtain the results in the desired format?
Each of the answers works except that I had to wrap them as follows:
SELECT PersonId, GroupA_Actions_Made, GroupB_Actions_Made FROM ( -- (answer) ) t WHERE GroupA_Actions_Made > 0 OR GroupB_Actions_Made > 0
Using SQL Server Profiler, the accepted answer seems to have the fastest query time on large data sets.