May 14, 2014 at 11:03 pm
I want to aggregate to monthly values for the reading. I want to display Reading value for Oct 2010, November 2010 likewise My question is simple and I have tried to follow the etiquette.
Currently it is displaying.....
MeterIDReadingdateReading
3969 22/10/2013 0:150
3969 22/10/2013 0:300
3969 22/10/2013 0:450
3969 22/10/2013 1:000
3969 22/10/2013 1:150
3969 22/10/2013 1:300
3969 22/10/2013 1:450
3969 22/10/2013 2:001
3969 22/10/2013 2:150
MeterId int
ReadingDate datetime
Reading real
-===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
meterID INT PRIMARY KEY,
Readingdate DATETIME,
reading real
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
SELECT '4','Oct 17 2013 12:00AM','5.1709' UNION ALL
SELECT '4','Oct 17 2013 12:15AM','5.5319' UNION ALL
SELECT '4','Nov 17 2013 12:00AM','5.5793' UNION ALL
SELECT '4','Nov 17 2013 14:00AM','5.2471' UNION ALL
SELECT '5','Nov 17 2013 12:00AM','5.1177' UNION ALL
SELECT '5','Nov 17 2013 14:00AM','5.5510' UNION ALL
SELECT '5','Dec 17 2013 15:00AM','5.5128', UNION ALL
SELECT '5','Dec 17 2013 16:00AM','5.5758' UNION ALL
Output should display as
MeterId Period Reading
4 Oct 13 10.20
4 Nov 13 10.40
5 Oct 13 10.20
5 Nov 13 10.40
4 Dec 13 11.15
May 14, 2014 at 11:26 pm
Maybe I'm missing something, because my results are different from yours... here's my SQL and results:
insert into Readings(MeterID, ReadingDate, Reading)
SELECT 4 ,'10/17/2013 12:00',5.1709 UNION ALL
SELECT 4,'10/17/2013 12:15',5.5319 UNION ALL
SELECT 4,'11/17/2013 12:00',5.5793 UNION ALL
SELECT 4,'11/17/2013 14:00',5.2471 UNION ALL
SELECT 5,'11/17/2013 12:00',5.1177 UNION ALL
SELECT 5,'11/17/2013 14:00',5.5510 UNION ALL
SELECT 5,'12/17/2013 15:00',5.5128 UNION ALL
SELECT 5,'12/17/2013 16:00',5.5758;
SELECT MeterID, DateOnly, SUM(Reading) AS TotalReading
FROM (
SELECT MeterID, DATEFROMPARTS(YEAR(ReadingDate),MONTH(ReadingDate),DAY(ReadingDate)) AS DateOnly, Reading
FROM Readings) x
GROUP BY MeterID, DateOnly;
are you summing the readings?
May 14, 2014 at 11:37 pm
Got an error ''DATEFROMPARTS' is not a recognized built-in function name.'
Yes I am summing up reading for monthly. I want data to be displayed monthly like for October 2012 November 2012.
I just used any value to show under Reading so your values will not match with what I posted.
May 15, 2014 at 1:25 am
smart_analyst (5/14/2014)
I want to aggregate to monthly values for the reading. I want to display Reading value for Oct 2010, November 2010 likewise My question is simple and I have tried to follow the etiquette.Currently it is displaying.....
MeterIDReadingdateReading
3969 22/10/2013 0:150
3969 22/10/2013 0:300
3969 22/10/2013 0:450
3969 22/10/2013 1:000
3969 22/10/2013 1:150
3969 22/10/2013 1:300
3969 22/10/2013 1:450
3969 22/10/2013 2:001
3969 22/10/2013 2:150
MeterId int
ReadingDate datetime
Reading real
-===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
meterID INT PRIMARY KEY,
Readingdate DATETIME,
reading real
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
SELECT '4','Oct 17 2013 12:00AM','5.1709' UNION ALL
SELECT '4','Oct 17 2013 12:15AM','5.5319' UNION ALL
SELECT '4','Nov 17 2013 12:00AM','5.5793' UNION ALL
SELECT '4','Nov 17 2013 14:00AM','5.2471' UNION ALL
SELECT '5','Nov 17 2013 12:00AM','5.1177' UNION ALL
SELECT '5','Nov 17 2013 14:00AM','5.5510' UNION ALL
SELECT '5','Dec 17 2013 15:00AM','5.5128', UNION ALL
SELECT '5','Dec 17 2013 16:00AM','5.5758' UNION ALL
Output should display as
MeterId Period Reading
4 Oct 13 10.20
4 Nov 13 10.40
5 Oct 13 10.20
5 Nov 13 10.40
4 Dec 13 11.15
MeterId can not be set as primary key if you want to have duplicate values.
Anyway remove PK and give a try to below query.
;WITH TEST
AS
(SELECT MeterID,DATENAME(M,ReadingDate) + ' ' + CAST(YEAR(ReadingDate)AS VARCHAR(10)) AS DateOnly ,Reading
FROM #mytable)
SELECT MeterID,DateOnly,SUM(Reading) AS TotalReading
FROM TEST GROUP BY MeterID,DateOnly ORDER BY MeterID
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
May 15, 2014 at 1:32 am
smart_analyst (5/14/2014)
Got an error ''DATEFROMPARTS' is not a recognized built-in function name.'Yes I am summing up reading for monthly. I want data to be displayed monthly like for October 2012 November 2012.
I just used any value to show under Reading so your values will not match with what I posted.
'DATEFROMPARTS' is introduced in SQL 2012 onwards.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply