CODEWITHSUNDEEP
sqlsql-serverdategroup-by
user3127554
user3127554

Reputation: 577

SQL: Group by specific time instead of year

I have the following query: 

SELECT DATEPART(yyyy, ap.Date) AS 'Year', COUNT(p.Name2) AS 'Times entered', p.Name2 AS 'Name'       
FROM Person p  JOIN Price ap ON ap.PersonId = p.ID   
GROUP BY p.Name2, DATEPART(yyyy, ap.Date) 
ORDER BY DATEPART(yyyy, ap.Date) DESC , p.Name2 ASC

As result I have the following Data: 

2018    50  Bob
2018    40  Fred
2017    10  Bob
2017    5   Fred

What I actually want is to not group by year, but to group by the period of between july 2017 and june 2018.

I want to group by period of July 2017 to June 2018.

How are we possible to set a timeframe as a group by function in SQL?

Upvotes: 0

Views: 44

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Something like this should work:

SELECT g.my AS 'Month-Year', 
       COUNT(p.Name2) AS 'Times entered', 
       p.Name2 AS 'Name'       
FROM Person p  
JOIN Price ap ON ap.PersonId = p.ID   
CROSS APPLY 
(
   SELECT CONCAT(DATEPART(mm, ap.Date), '-', DATEPART(yyyy, ap.Date)) AS my 
) AS g
WHERE ap.Date BETWEEN '2017-07-01' AND '2018-06-30'
GROUP BY p.Name2, g.my
ORDER BY g.my DESC , p.Name2 ASC

Upvotes: 2

Related Questions