Query Problem in SQL Server

Question

Hi All, I have a problem writing a query. At the moment I can retrieve an hourly recording of percentages for each machine running and I've got a table which shows, on a daily basis, each percentage recorded for each machine per hour. The code looks like this:

.......
WHERE     (tblCheckResult.DateTime >= @DateFrom) 
AND (tblCheckResult.DateTime <= DateTo) AND (tblCheck.CheckID = 69)
.......

CheckID being the ID for the percentages. What I want to do is show an average Monthly recordings and a Yearly recordings e.g. Jan 80%, Feb 95% etc. Can anyone please advise?

Thanks

Answer 1

You can use DatePart to retrieve the Month part/ Year part and group by those.

Something like this

DECLARE @Table TABLE(
     DVal DATETIME,
     Val FLOAT
)


INSERT INTO @Table (DVal,Val) SELECT '01 Jan 2008', 10
INSERT INTO @Table (DVal,Val) SELECT '02 Jan 2008', 20
INSERT INTO @Table (DVal,Val) SELECT '03 Jan 2008', 30
INSERT INTO @Table (DVal,Val) SELECT '04 Jan 2008', 40
INSERT INTO @Table (DVal,Val) SELECT '05 Jan 2008', 50
INSERT INTO @Table (DVal,Val) SELECT '06 Jan 2008', 60

INSERT INTO @Table (DVal,Val) SELECT '01 Feb 2008', 11
INSERT INTO @Table (DVal,Val) SELECT '02 Feb 2008', 22
INSERT INTO @Table (DVal,Val) SELECT '03 Feb 2008', 33
INSERT INTO @Table (DVal,Val) SELECT '04 Feb 2008', 44
INSERT INTO @Table (DVal,Val) SELECT '05 Feb 2008', 55
INSERT INTO @Table (DVal,Val) SELECT '06 Feb 2008', 66


INSERT INTO @Table (DVal,Val) SELECT '01 Jan 2009', 16
INSERT INTO @Table (DVal,Val) SELECT '02 Jan 2009', 26
INSERT INTO @Table (DVal,Val) SELECT '03 Jan 2009', 36
INSERT INTO @Table (DVal,Val) SELECT '04 Jan 2009', 46
INSERT INTO @Table (DVal,Val) SELECT '05 Jan 2009', 56
INSERT INTO @Table (DVal,Val) SELECT '06 Jan 2009', 66

INSERT INTO @Table (DVal,Val) SELECT '01 Feb 2009', 17
INSERT INTO @Table (DVal,Val) SELECT '02 Feb 2009', 27
INSERT INTO @Table (DVal,Val) SELECT '03 Feb 2009', 37
INSERT INTO @Table (DVal,Val) SELECT '04 Feb 2009', 47
INSERT INTO @Table (DVal,Val) SELECT '05 Feb 2009', 57
INSERT INTO @Table (DVal,Val) SELECT '06 Feb 2009', 67


SELECT  DATEPART(yy, DVal) YearPart,
     DATEPART(MM, DVal) MonthPart,
     AVG(Val)
FROM    @Table
GROUP BY DATEPART(yy, DVal),
     DATEPART(MM, DVal)

SELECT  DATEPART(yy, DVal) YearPart,
     AVG(Val)
FROM    @Table
GROUP BY DATEPART(yy, DVal)