current community

your communities

more stack exchange communities

Stack Exchange
tour help
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.
up vote 5 down vote favorite

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 yesterday
add comment

3 Answers

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 yesterday
    
I agree. If I wasn't on my phone and in bed I would edit it. –  James Anderson yesterday
    
@MaxVernon - Datetime is rounded to "Accuracy - Rounded to increments of .000, .003, or .007 seconds" so James' answer is sufficient. –  Michael Green yesterday
    
@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 yesterday
    
See this answer for more detail: dba.stackexchange.com/a/36095/10832 –  Max Vernon yesterday
show 1 more comment
up vote 1 down vote

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
add comment
up vote 1 down vote

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
add comment

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.