CODEWITHSUNDEEP
sqlsql-serverselectcount
cjmcc5150
cjmcc5150

Reputation: 11

Count daily records and compare across multiple years

Hoping someone might be able to point me in the right direction as I not great with SQL and not been able to find what I need online. I have tblRegister in which multiple records a day are created and now need to compare each day across multiple years.

date id
2010-01-01 1
2010-01-01 2
2010-01-01 3
2010-01-02 4
2010-01-03 5
2010-01-03 6
... and so on up to today #

I am trying to work out how to query the data so that I can compare each day of the year across multiple years (so can then review for spikes / troughs trends when graphed)

date 2010 2011 2012 ...2022
01 Jan 3 count(id) count(id) count(id)
02 Jan 1 count(id) count(id) count(id)
03 Jan 2 count(id) count(id) count(id)
...for each dd MMM of the calendar year count(id) count(id) count(id) count(id)

Hope someone might be able to assist. Thanks.

Upvotes: 1

Views: 148

Answers (1)

Stuck at 1337
Stuck at 1337

Reputation: 2084

You could build a dynamic PIVOT that takes into account all the years present in the table and builds out a column for each one.

-- pick an arbitrary leap year, like 2020
DECLARE @leapyear char(4)       = '2020', 
        @cols     nvarchar(max) = N'';

SELECT @cols = STUFF
  ((SELECT N',' + QUOTENAME(YEAR(date))
    FROM dbo.[a table] 
    GROUP BY YEAR(date)
    ORDER BY YEAR(date)
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 
           1, 1, N'');

DECLARE @sql nvarchar(max) = N'SELECT date = CONVERT(char(6), 
    CONVERT(date, @ly + d), 106), ' + @cols + N' FROM 
(
  SELECT y = YEAR([date]), 
         d = RIGHT(CONVERT(char(8), date, 112), 4), 
         c = COUNT(id)
  FROM dbo.[a table]
  GROUP BY YEAR([date]), RIGHT(CONVERT(char(8), date, 112), 4)
) AS x PIVOT (MAX(c) FOR y IN (' + @cols + N')) p 
ORDER BY d;';

EXEC sys.sp_executesql @sql, N'@ly char(4)', @leapyear;

Working example in this fiddle - makes an assumption of how you want to handle February 29th, which will only have values for certain years.

Upvotes: 1

Related Questions