Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sum on fields between 2 dates Expand / Collapse
Author
Message
Posted Monday, December 10, 2012 9:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:49 PM
Points: 287, Visits: 407
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?
Post #1394672
Posted Monday, December 10, 2012 10:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 1,889, Visits: 2,040
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

Post #1394704
Posted Tuesday, December 11, 2012 1:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:49 PM
Points: 287, Visits: 407
Thanks Chris, I must admit I tried everything except that yesterday.. that worked a treat
Post #1394928
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse