Query Problem in SQL Server

Question

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

Adriaan Stander · Accepted Answer · 2009-11-17 12:35:21Z

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)

Martijn Pieters · Accepted Answer · 2018-11-15 12:43:25Z

1

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.

edited Nov 15, 2018 at 12:43

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

answered Oct 19, 2011 at 11:32

user1003018

111 bronze badge

Collectives™ on Stack Overflow

Query Problem in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related