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.
ALTER PROCEDURE [dbo].[df_Analysis_Production_by_Well_Facility] @DSID integer, @FieldID integer, @EntityID integer, @StartDate DateTime, @EndDate DateTime AS BEGIN -- 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) SELECT WF.DS_ID , DS.DS_Description AS Data_Source , WF.FieldID , WF.FieldName , P.Entity_ID , WF.Entity_Name AS Well_Fac_Name , WF.Bolo_ID , WF.Entity_Type_Desc AS Well_Fac_Type , P.docDate , P.Oil , P.Gas , P.Water 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 END
From novice to tech pro — start learning today.