Michael Michael - 1 month ago 4
SQL Question

How to group only by month and year

I'm rather vaguely familiar with SQL. I use SQL Server 2012.

I have this table:

|Id | SiteId| SiteDesc |IsNormal| DateReview |FrequencyId|
|3379| 5| colon | 1 | 2016-09-10 00:00:00.000| 1 |
|3381| 5| colon | 0 | 2016-09-15 00:00:00.000| 1 |
|3382| 5| colon | 1 | 2016-09-21 00:00:00.000| 1 |

|3489| 5| colon | 0 | 2016-08-10 00:00:00.000| 1 |
|3851| 5| colon | 1 | 2016-08-16 00:00:00.000| 1 |

|3537| 2| dogon | 1 | 2016-05-05 00:00:00.000| 1 |
|3863| 2| dogon | 1 | 2016-05-20 00:00:00.000| 1 |


IsNormal
column is of
BIT
data type.

I need to group the table by
SiteId
and
DateReview
(only month and year). If in
IsNormal
column at least one row has property false, in grouped table it has to be False.

Here is the desired grouped table:

| SiteId| SiteDesc |IsNormal| DateReview |FrequencyId|
| 5| colon | 0 | 2016-09-10 00:00:00.000| 1 |
| 5| colon | 0 | 2016-08-10 00:00:00.000| 1 |
| 2| dogon | 1 | 2016-05-05 00:00:00.000| 1 |


Thank you in advance.

Answer

SQL Server can't aggregate bit columns as is, so cast it to int first, then use MIN and then cast it back to bit.

To group by month I use the following formula:

DATEADD(month, DATEDIFF(month, '20010101', DateReview), '20010101')

You can pick any anchor date instead of '20010101', it can be any first day of the month. The idea is simple. DATEDIFF(month,...) calculates the number of months between the anchor date and the value from the table. This is integer number - how many times the boundary of the month was crossed. Then this number is added back to the anchor date. Result of this expression is the value of DateReview truncated to the first day of the month.

Query

SELECT
    SiteId
    ,MIN(SiteDesc) AS SiteDesc
    ,CAST(MIN(CAST(IsNormal AS int)) AS bit) AS IsNormal
    ,MIN(DateReview) AS DateReview
    ,MIN(FrequencyId) AS FrequencyId
FROM T
GROUP BY
    SiteId
    ,DATEADD(month, DATEDIFF(month, '20010101', DateReview), '20010101')
Comments