I currently have a stored procedure that returns 12 fields from a combination of tables in my SQL Server database. This table contains data for every day of production for over 1000 wells/facilities, over about 8 years. I currently pass the stored procedure values for DSID, FieldID, Well_ID, StartDate and EndDate, and it returns individual records for each day for each well in list of wells that meet the criteria.
I want to modify this query, so that it will return the same fields, but so that the Oil, Gas, and Water fields are summed over a particular period (either by month, quarter, or year), still within the data range determined by StartDate and EndDate. The stored procedure currently looks like:
ALTER PROCEDURE [dbo].[df_Analysis_Production_by_Well_Facility]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @DSID = ISNULL(@DSID, 0)
SET @FieldID = IsNull(@FieldID, 0)
SET @EntityID = ISNULL(@EntityID, 0)
, DS.DS_Description AS Data_Source
, WF.Entity_Name AS Well_Fac_Name
, WF.Entity_Type_Desc AS Well_Fac_Type
FROM vw_Wells_and_Facilities_All as WF
RIGHT JOIN tbl_sysProduction as P ON WF.Entity_ID = P.Entity_ID
LEFT JOIN lst_Data_Sources as DS ON WF.DS_ID = DS.DS_ID
WHERE ((@DSID = 0) OR (WF.DS_ID = @DSID))
AND ((@FieldID = 0) OR (WF.FieldID = @FieldID))
AND ((@EntityID = 0) OR (P.Entity_ID = @EntityID))
AND ((@StartDate IS NULL) OR (P.docDate >= @StartDate))
AND ((@EndDate IS NULL) OR (P.docDate < DATEADD(day, 1, @EndDate)))
ORDER BY WF.FieldName, WF.Entity_Name, P.docDate
How can I modify this so that I can pass in an additional parameter and have it group accordingly. I assume I'm going to have to modify the query so that it builds a dynamic SQL string and then execute that from within the SP. I know how I would do this in Access, using the Format( ) function with the docDate and a format string that either looks like "yyyy", "yy-q", or "yy-mm", but I'm not sure how to go about that in SQL Server.