Grouping By Day Of month:
In order to group the data by day you can use sql server in built DAY() funtion.
Grouping By Day Of week:
For this you can use DATEPART(weekday,[date column])
Grouping By week:
For this you can use DATEPART(week,[date column])
Grouping By DAY of Year:
For this you can use DATEPART(dayofyear,[date column])
Örnek:
CREATE TABLE #Sales
(
Name VARCHAR (100),
SalesDateTime DATETIME
)
GO
INSERT INTO #Sales
SELECT 'Product1',
'2012-04-01 00:00:00.000'
UNION ALL
SELECT 'Product2',
'2012-04-02 00:00:00.000'
UNION ALL
SELECT 'Product3',
'2012-04-02 00:00:00.000'
UNION ALL
SELECT 'Product4',
'2012-04-03 00:00:00.000'
UNION ALL
SELECT 'Product5',
'2012-04-03 00:00:00.000'
UNION ALL
SELECT 'Product1',
'2012-04-30 00:00:00.000'
UNION ALL
SELECT 'Product1',
'2012-04-30 00:00:00.000'
UNION ALL
SELECT 'Product1',
'2012-05-02 00:00:00.000'
UNION ALL
SELECT 'Product5',
'2012-05-02 00:00:00.000'
UNION ALL
SELECT 'Product5',
'2012-05-02 00:00:00.000'
GO
--GROUP BY DAY of Month
SELECT count(*) AS SalesCount, DAY(SalesDateTime) Day
FROM #Sales
GROUP BY DAY(SalesDateTime)
--GROUP BY DAY of Week
SELECT count(*) AS SalesCount, DATEPART(weekday,SalesDateTime) Day
FROM #Sales
GROUP BY DATEPART(weekday,SalesDateTime)
--GROUP BY Week
SELECT count(*) AS SalesCount, DATEPART(week,SalesDateTime) Day
FROM #Sales
GROUP BY DATEPART(week,SalesDateTime)
--GROUP BY DAY of Year
SELECT count(*) AS SalesCount, DATEPART(dayofyear,SalesDateTime) Day
FROM #Sales
GROUP BY DATEPART(dayofyear,SalesDateTime)
GO
DROP TABLE #Sales
