SQL Group Distinct Count

I have the following table

Use开发者_JS百科r ID    Start Date   End Date
-------------------------------------
John Doe   Mar 11 2011  May 28 2011
Robret S   Mar 21 2011  Jun 29 2011
Tina T     Feb 01 2011  August 20 2011

I want to show how many people I have available for the past 6 months, even if the month has no people. How can this be possible. I know I have to do grouping and use distinct count.

Expected Output:

February  = 1 Resource
March     = 3 Resources
April     = 3 Resources
May       = 3 Resources
June      = 2 Resources
July      = 1 Resource
August    = 1 Resource

With Calendar As
    (
    Select Cast('20110501' As DateTime) As [Date]
    Union All
    Select DateAdd(m,-1,[Date])
    From Calendar
    Where [Date] > DateAdd(m,-5,'20110501')
    )
Select DateName(m, C.Date) + ' ' + Cast(Year(C.Date) As char(4))
    , Case Count(*)
        When 1 Then Cast(Count(*) As varchar(10)) + ' Resource'
        Else Cast(Count(*) As varchar(10)) + ' Resources'
        End
From Calendar As C
    Left Join MyTable As T
        On C.Date Between T.StartDate And T.EndDate
Group By C.Date

Results:

December 2010   | 1 Resource
January 2011    | 1 Resource
February 2011   | 1 Resource
March 2011      | 1 Resource
April 2011      | 3 Resources
May 2011        | 3 Resources

You will need the existing data records for the last 6 months, so that you can merge the two sets of data. You can generate the last 6 months in a CTE and do a left join with your data. That will allow you to show the last 6 months even if you have no data.

I don't think you can do what you want using a "simple" select statement (even using GROUPing etc.) The following is off the top of my head, so you'll have to experiment with it a little, and preferably read Joe Celko's excellent SQL for Smarties book.

You need to create a second table that contains all of your months (start/end dates). You only need one table for all types of similar queries, and it must contain all the months in the date ranges your interested in querying:

CREATE TABLE months (id, start DATE, end DATE);

INSERT INTO months (id, start, end) 
          values ( (1, 2011-01-01, 2011-01-31), 
                   (2, 2011-02-01, 2011-02-28), ...);

You then LEFT OUTER JOIN from your user table to this month table. That will give you a row for each user for each month they were available, which you can GROUP as required:

SELECT months.id, COUNT(user.id) 
  FROM months LEFT OUTER JOIN users 
         ON user.start_date < months.end 
        AND user.end_date > months.start 
GROUP BY months.id;

Hope that helps.

WITH resources AS (
  SELECT
    Date = DATEADD(month, v.number, [Start Date])
  FROM atable t
    INNER JOIN master.dbo.spt_values v ON v.type = 'P'
      AND v.number BETWEEN 0 AND DATEDIFF(month, t.[Start Date], t.[End Date])
)
SELECT
  Month = DATENAME(month, Date),
  ResourceCount = CAST(COUNT(*) AS varchar(30)) +
                  CASE COUNT(*) WHEN 1 THEN ' Resource' ELSE ' Resources' END
FROM resources
WHERE Date > DATEADD(month, -6, DATEADD(day, -DAY(GETDATE()), GETDATE()))
GROUP BY YEAR(Date), MONTH(Date), DATENAME(month, Date)
ORDER BY YEAR(Date), MONTH(Date)