December 10, 2012 at 9:12 am
I want to be able to add together values from fields that come between 2 dates, I've created a table script below
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE mytable
(
DateValue DATETIME,
sName varchar(50),
sinterventions INT,
sareas INT )
-- Inserting Data into Table
INSERT INTO mytable(DateValue,sName,sinterventions,sareas) VALUES ('23/Dec/2012','Mike',99,23)
INSERT INTO mytable(DateValue,sName,sinterventions,sareas) VALUES ('22/Dec/2012','Joe',43,3)
INSERT INTO mytable(DateValue,sName,sinterventions,sareas) VALUES ('20/Dec/2012','Tony',5,66)
INSERT INTO mytable(DateValue,sName,sinterventions,sareas) VALUES ('21/Dec/2012','Mike',11,44)
INSERT INTO mytable(DateValue,sName,sinterventions,sareas) VALUES ('20/Dec/2012','sally',1,440)
INSERT INTO mytable(DateValue,sName,sinterventions,sareas) VALUES ('21/Dec/2012','sally',11,20)
INSERT INTO mytable(DateValue,sName,sinterventions,sareas) VALUES ('20/Nov/2012','Joy',121,70)
What I'd like to return is if I query the data between 20/dec/2012 and 23/Dec/2012,
Mike 110 67
Joe 43 3
Tony 5 66
Sally 12 460
I've tried GroupBy but that just splits it over dates, so I'll get 2 Sally's etc. How could this be achieved?
December 10, 2012 at 10:22 am
seems simple enough, your dates should be in the WHERE clause, not GROUP BY clause, like this:
SELECT sName, SUM(sinterventions) AS sinterventions, SUM(sareas) AS sareas
FROM mytable
WHERE DateValue BETWEEN '2012-12-20' AND '2012-12-23'
GROUP BY sName
December 11, 2012 at 1:19 am
Thanks Chris, I must admit I tried everything except that yesterday.. that worked a treat
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply