CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012group-by
Michael
Michael

Reputation: 13616

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      |

Upvotes: 3

Views: 10090

Answers (3)

Biddut
Biddut

Reputation: 457

I have used this code you can try also.

 select  CAST(MONTH(SalesDate) AS VARCHAR(2)) + '-' + CAST(YEAR(SalesDate) AS VARCHAR(4)),  sum(TSalesAmt) as 'GrandTotal' from CreditSales group by  CAST(MONTH(SalesDate) AS VARCHAR(2)) + '-' + CAST(YEAR(SalesDate) AS VARCHAR(4))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Use year() and month():

select SiteId, min(SiteDesc) as SiteDesc,
       min(case when IsNormal = 0 then 0 else 1 end) as IsNormal,
       min(DateReview) as DateReview,
       min(FrequencyId) as FrequencyId
from t
group by SiteId, year(DateReview), month(DateReview)
order by min(DateReview) desc;

This simply uses the year and month for the aggregation. It then pulls the column values using an aggregation function. For DateReview, the appropriate function appears to be min().

Upvotes: 3

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

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')

Upvotes: 6

Related Questions