SQL Server - group records, as well as monthly archives

Question

I have a Fixture table with three fields.

ID | Fixture | Date
-------------------------
1  | 123456  |  20110515
2  | 123446  |  20110512
3  | 123476  |  20110411
4  | 123486  |  20110310

... etc.

I need to group records by date and want to get instrument counts.

Results will be displayed, as in the following example. How can I get SQL Query results?

Archives

February 2011 (3)
January 2011 (6)
December 2010 (10)
November 2010 (7)
October 2010 (5)

I need community help to solve this problem, kindly help me.

Answer 1

Try it, you have monthly numbers, you can update it in your code or in sql

 select COUNT(id), DATEPART(year, dtCreated) as y, DATEPART(MONTH,dtCreated) as mo 
    from TaxesSteps group by DATEPART(year, dtCreated), DATEPART(MONTH,dtCreated)
    order by 2 desc, 3 desc

Answer 2

How about something like

DECLARE @Table TABLE(
        ID INT,
        Fixture INT,
        Date DATETIME
)
INSERT INTO @Table SELECT 1,123456,'20110515' 
INSERT INTO @Table SELECT 2,123446,'20110512' 
INSERT INTO @Table SELECT 3,123476,'20110411' 
INSERT INTO @Table SELECT 4,123486,'20110310'

;WITH Vals AS (
        SELECT  DATENAME(month,Date) + ' ' + CAST(DATEPART(year,Date) AS VARCHAR(4)) DateValue,
                ID,
                CONVERT(VARCHAR(6), Date, 112) OrderValue 
        FROM    @Table
)
SELECT  DateValue,
        COUNT(ID) Cnt
FROM    Vals
GROUP BY    DateValue,
            OrderValue
ORDER BY    OrderValue

Answer 3

, , :

SELECT
    DATENAME(m, [Date]) + ' ' + CAST(YEAR([Date]) AS VARCHAR(4)) AS ArchiveMonth
    ,COUNT(ID) AS Items
FROM
    Fixture
GROUP BY
    DATENAME(m, [Date]) + ' ' + CAST(YEAR([Date]) AS VARCHAR(4))

(dang it... )

Answer 4

Declare @table table (ID bigint identity(1,1), Fixture nvarchar(100), [Date] nvarchar(100))

INSERT INTO @table values ('123456','20110515')
INSERT INTO @table values ('123256','20110410')
INSERT INTO @table values ('123356','20110511')
INSERT INTO @table values ('122456','20110503')

--select DATEPART(month,0, (cast([date],datetime) from @table
SELECT DATENAME(month, CAST([Date] as datetime))+ ' ' + DATENAME(Year,CAST([Date] as datetime)) + ' (' + CAST(COUNT(Fixture) as varchar(100)) + ') '
from @table
group by DATENAME(month, CAST([Date] as datetime))+ ' ' + DATENAME(Year,CAST([Date] as datetime))

Answer 5

, 3 ,

№1

select   convert(char(6), Date, 112) MonthYear, count(*) CountFixtures
from     Fixture
group by convert(char(6), Date, 112)
order by convert(char(6), Date, 112)

№1 - . :

MonthYear CountFixtures
--------- -------------
201103    1
201104    1
201105    2

---

№ 2

select   datename(month, convert(datetime,convert(char(6), Date, 112)+'01'))
         + ' '
         + left(convert(char(6), Date, 112),4) MonthYear,
         count(*) CountFixtures
from     Fixture
group by convert(char(6), Date, 112)
order by convert(char(6), Date, 112)

Result No. 2 - Recommended. Counting and dates are separate fields.

MonthYear                           CountFixtures
----------------------------------- -------------
March 2011                          1
April 2011                          1
May 2011                            2

---

Option number 3

select   datename(month, convert(datetime,convert(char(6), Date, 112)+'01'))
         + ' '
         + left(convert(char(6), Date, 112),4)
         + ' ('
         + convert(varchar,count(*))
         + ')' FixturesByMonth
from     Fixture
group by convert(char(6), Date, 112)
order by convert(char(6), Date, 112)

Conclusion number 3 - exactly , as in your question, using brackets. However, I strongly believe that formatting (brackets et al) is a foreground task, not a SQL Server side.

FixturesByMonth
----------------
March 2011 (1)
April 2011 (1)
May 2011 (2)