CODEWITHSUNDEEP
sqlsql-server-2008
AW.
AW.

Reputation: 11

Query Problem in SQL Server

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

Upvotes: 1

Views: 146

Answers (2)

user1003018
user1003018

Reputation: 11

I think you are entering incorrect SQL query. In order to avoid this problem you should write 

insert into table <table_name> select <value>;

In case you are experiencing problem in executing queries then you should check online for the SQL queries. If problem persists then there might be damage in the database of SQL.

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166526

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)

Upvotes: 4

Related Questions