Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

UPDATE

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.

share|improve this question
    
Are you sure RIGHT outer join is the correct thing to use ? –  Erwin Smout May 19 at 20:43

3 Answers 3

up vote 3 down vote accepted

This will give you the result you wanted but I'm not sure if it's the most flexible piece of code.

SELECT p.PersonId,
    SUM(CASE 
            WHEN a.ActionTime 
              BETWEEN '2014-01-15 00:00:00' 
          AND '2014-02-14 23:59:59'
            THEN 1 ELSE 0 END) AS GroupA_Actions_Made,
    SUM(CASE 
            WHEN a.ActionTime 
              BETWEEN '2014-02-15 00:00:00' 
              AND '2014-03-14 23:59:59'
            THEN 1 ELSE 0 END) AS GroupB_Actions_Made
FROM
    Person p
JOIN
    Action a on p.PersonId = a.PersonId
GROUP BY p.PersonId
share|improve this answer
2  
BETWEEN can be problematic. It would be more concise to use DateField >= '2014-01-01' AND DateField < '2014-01-16' since that captures ALL possible values, including 2015-01-15 23:59:59.999 –  Max Vernon May 19 at 21:35
    
I agree. If I wasn't on my phone and in bed I would edit it. –  James Anderson May 19 at 21:46
    
@MaxVernon - Datetime is rounded to "Accuracy - Rounded to increments of .000, .003, or .007 seconds" so James' answer is sufficient. –  Michael Green May 20 at 2:22
    
@michael - so every value between 23:59:59.000 and 23:59:59.999 is covered by his original query then? I suggest you look at the link provided by Sqlfox regarding the vagaries of using BETWEEN and datetime values in general. –  Max Vernon May 20 at 2:25
    
See this answer for more detail: dba.stackexchange.com/a/36095/10832 –  Max Vernon May 20 at 2:33

A slightly different approach:

select
    PersonId
    ,GroupA_Actions_Made
    ,GroupB_Actions_Made
    from (
        select
            p.PersonId
            ,[groupName] = 'Group' + case
                when a.ActionTime >= '2014-01-15' and a.ActionTime < '2014-02-15'
                    then 'A'
                when a.ActionTime >= '2014-02-15' and a.ActionTime < '2014-03-15'
                    then 'B'
                end + '_Actions_Made'
            from dbo.Person p
            inner join dbo.Action a
                on p.PersonId = a.PersonId
        ) pdata
    pivot(count([groupName]) for [groupName] in ([GroupA_Actions_Made],[GroupB_Actions_Made]))pvt

Also, beware of between.

share|improve this answer

This should be somewhat more flexible.

SELECT PersonId, [2014.02] AS GroupA_Actions_Made, [2014.03] AS GroupB_Actions_Made
FROM (
    SELECT ActionId, PersonId, 
        CONVERT(char(7), DATEADD(month,1,DATEADD(dd,-14,ActionTime)), 102) Grouping
    FROM Action
    ) p
PIVOT (COUNT(ActionId) 
    FOR Grouping IN ([2014.02],[2014.03])
    ) AS pvt
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.