asked on

Condtional Sorting and Grouping in a SQL Server Stored Procedure

I am fairly new to SQL Server. I'm working in Developer 14.

I've created a stored procedure that will select records from a view based on the passed parameters. One of the parameters passed is TaxTypeID. There are 4 tax type (1, 2, 3, and 4) I want to group the returned record set depending on which tax type was passed.

If the taxtype is 1 I want to
group by CountyID, PayYear, PayMonth

if tax type is 2
group by SchoolDistrictNum, payYear, PayMonth

if taxtype is 3 or 4
group by MuniCode, PayYear, PayMOnth

Is there any way to code the conditional grouping an sorting?

This is the current version of the SP

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spRptJerrysMonthlySummary]    Script Date: 10/17/2017 11:34:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:	Jerrys Report
-- =============================================
ALTER PROCEDURE [dbo].[spRptJerrysMonthlySummary] 
	-- Add the parameters for the stored procedure here
	@FromDate    datetime = null, 
	@ThruDate    datetime = null,
	@MuniCode    int      = Null,
	@SchoolDist  int      = Null, 
	@TaxTypeID   int      = Null 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT SUm(FaceAmt)              as SumFace, 
	       Sum(PenaltyAmt)           as SumPenaltyAmt, 
		   Sum(InterestAmt)          as SumInterestAmt,
		   Sum (SvcChgAmt)           as SumSvcChgAmt, 
		   Sum(AboveAndBelowCostAmt) as SumCostAmt,
		   Sum(LienCostAmt)          as SumLienCostAmt,
		   PayYear, PayMonth, MuniCode,SchoolDistrictNum
	From vRpt_JerrysMonthlySummary
	WHERE 
	      ((@MuniCode   is null)  or (MuniCode           = @MuniCode))	  AND
	      ((@SchoolDist is null)  or (SchoolDistrictNum  = @SchoolDist))  AND
          ((@FromDate   is null)  or ([PaymentDate]     >= @FromDate))	  AND
		  ((@ThruDate   is null)  or ([PaymentDate]     <= @ThruDate))    AND
		  ((@TaxTypeID  is null)  or ([TaxTypeID]        = @TaxTypeID))

	Group By CASE WHEN @SchoolDist IS NULL THEN SchoolDistrictNum ELSE MuniCode END
			,PayYear, PayMonth, MuniCode, SchoolDistrictNum
	Order By CASE WHEN @SchoolDist IS NULL THEN SchoolDistrictNum ELSE MuniCode END
			,PayYear, PayMonth, SchoolDistrictNum, MuniCode



END

Open in new window

Pawan Kumar

Please try like below -

Changed code

GROUP BY
                  CASE WHEN taxtype = 1 THEN CountyID, PayYear, PayMonth
                          WHEN taxtype = 2 THEN SchoolDistrictNum, payYear, PayMonth
                          WHEN taxtype = 3 OR taxtype = 4 THEN MuniCode, PayYear, PayMOnth
                  END

      ORDER BY
                  CASE WHEN taxtype = 1 THEN CountyID, PayYear, PayMonth
                          WHEN taxtype = 2 THEN SchoolDistrictNum, payYear, PayMonth
                          WHEN taxtype = 3 OR taxtype = 4 THEN MuniCode, PayYear, PayMOnth
                  END

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spRptJerrysMonthlySummary]    Script Date: 10/17/2017 11:34:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:	Jerrys Report
-- =============================================
ALTER PROCEDURE [dbo].[spRptJerrysMonthlySummary] 
	-- Add the parameters for the stored procedure here
	@FromDate    datetime = null, 
	@ThruDate    datetime = null,
	@MuniCode    int      = Null,
	@SchoolDist  int      = Null, 
	@TaxTypeID   int      = Null 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT SUm(FaceAmt)              as SumFace, 
	       Sum(PenaltyAmt)           as SumPenaltyAmt, 
		   Sum(InterestAmt)          as SumInterestAmt,
		   Sum (SvcChgAmt)           as SumSvcChgAmt, 
		   Sum(AboveAndBelowCostAmt) as SumCostAmt,
		   Sum(LienCostAmt)          as SumLienCostAmt,
		   PayYear, PayMonth, MuniCode,SchoolDistrictNum
	From vRpt_JerrysMonthlySummary
	WHERE 
	      ((@MuniCode   is null)  or (MuniCode           = @MuniCode))	  AND
	      ((@SchoolDist is null)  or (SchoolDistrictNum  = @SchoolDist))  AND
          ((@FromDate   is null)  or ([PaymentDate]     >= @FromDate))	  AND
		  ((@ThruDate   is null)  or ([PaymentDate]     <= @ThruDate))    AND
		  ((@TaxTypeID  is null)  or ([TaxTypeID]        = @TaxTypeID))

	GROUP BY 
			CASE WHEN taxtype = 1 THEN CountyID, PayYear, PayMonth
				  WHEN taxtype = 2 THEN SchoolDistrictNum, payYear, PayMonth
				  WHEN taxtype = 3 OR taxtype = 4 THEN MuniCode, PayYear, PayMOnth
			END

	ORDER BY 
			CASE WHEN taxtype = 1 THEN CountyID, PayYear, PayMonth
				  WHEN taxtype = 2 THEN SchoolDistrictNum, payYear, PayMonth
				  WHEN taxtype = 3 OR taxtype = 4 THEN MuniCode, PayYear, PayMOnth
			END
END

Open in new window

Pawan Kumar

For ordering also i have used the same logic. Please let me know if any other logic is to be used.

mlcktmguy

ASKER

Thank you. I commented the old Group by and Order By and entered the revised code but I getting an syntax error
Msg 102, Level 15, State 1, Procedure spRptJerrysMonthlySummary, Line 46
Incorrect syntax near ','.
On this line:
GROUP BY
CASE WHEN @TaxTypeID = 1 THEN CountyID, PayYear, PayMonth

This is what the revised code looks like

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spRptJerrysMonthlySummary]    Script Date: 10/18/2017 8:39:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:	Jerrys Report
-- =============================================
ALTER PROCEDURE [dbo].[spRptJerrysMonthlySummary] 
	-- Add the parameters for the stored procedure here
	@FromDate    datetime = null, 
	@ThruDate    datetime = null,
	@MuniCode    int      = Null,
	@SchoolDist  int      = Null, 
	@TaxTypeID   int      = Null 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT SUm(FaceAmt)              as SumFace, 
	       Sum(PenaltyAmt)           as SumPenaltyAmt, 
		   Sum(InterestAmt)          as SumInterestAmt,
		   Sum (SvcChgAmt)           as SumSvcChgAmt, 
		   Sum(AboveAndBelowCostAmt) as SumCostAmt,
		   Sum(LienCostAmt)          as SumLienCostAmt,
		   PayYear, PayMonth, MuniCode,SchoolDistrictNum
	From vRpt_JerrysMonthlySummary
	WHERE 
	      ((@MuniCode   is null)  or (MuniCode           = @MuniCode))	  AND
	      ((@SchoolDist is null)  or (SchoolDistrictNum  = @SchoolDist))  AND
          ((@FromDate   is null)  or ([PaymentDate]     >= @FromDate))	  AND
		  ((@ThruDate   is null)  or ([PaymentDate]     <= @ThruDate))    AND
		  ((@TaxTypeID  is null)  or ([TaxTypeID]        = @TaxTypeID))

--	Group By CASE WHEN @SchoolDist IS NULL THEN SchoolDistrictNum ELSE MuniCode END
--			,PayYear, PayMonth, MuniCode, SchoolDistrictNum
--	Order By CASE WHEN @SchoolDist IS NULL THEN SchoolDistrictNum ELSE MuniCode END
--			,PayYear, PayMonth, SchoolDistrictNum, MuniCode

	GROUP BY 
            CASE WHEN @TaxTypeID = 1 THEN CountyID, PayYear, PayMonth
                 WHEN @TaxTypeID = 2 THEN SchoolDistrictNum, payYear, PayMonth
                 WHEN @TaxTypeID = 3 OR @TaxTypeID = 4 THEN MuniCode, PayYear, PayMOnth
            END

    ORDER BY 
            CASE WHEN @TaxTypeID = 1 THEN CountyID, PayYear, PayMonth
                 WHEN @TaxTypeID = 2 THEN SchoolDistrictNum, payYear, PayMonth
                 WHEN @TaxTypeID = 3 OR @TaxTypeID = 4 THEN MuniCode, PayYear, PayMOnth
            END


END

Open in new window

Pawan Kumar

Please check this ..

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spRptJerrysMonthlySummary]    Script Date: 10/18/2017 8:39:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:	Jerrys Report
-- =============================================
ALTER PROCEDURE [dbo].[spRptJerrysMonthlySummary] 
	-- Add the parameters for the stored procedure here
	@FromDate    datetime = null, 
	@ThruDate    datetime = null,
	@MuniCode    int      = Null,
	@SchoolDist  int      = Null, 
	@TaxTypeID   int      = Null 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @SQL AS VARCHAR(MAX) = '

    -- Insert statements for procedure here
	SELECT SUm(FaceAmt)              as SumFace, 
	       Sum(PenaltyAmt)           as SumPenaltyAmt, 
		   Sum(InterestAmt)          as SumInterestAmt,
		   Sum (SvcChgAmt)           as SumSvcChgAmt, 
		   Sum(AboveAndBelowCostAmt) as SumCostAmt,
		   Sum(LienCostAmt)          as SumLienCostAmt,
		   PayYear, PayMonth, MuniCode,SchoolDistrictNum
	From vRpt_JerrysMonthlySummary
	WHERE 
	      ((' + @MuniCode + '   is null)  or (MuniCode           =  ' + @MuniCode + ' ))	  AND
	      (( ' + @SchoolDist  + ' is null)  or (SchoolDistrictNum  = ' + @SchoolDist + ' ))  AND
          (( ' + @FromDate  + '  is null)  or ([PaymentDate]     >=  ' + @FromDate + ' ))	  AND
		  (( ' + @ThruDate  + '  is null)  or ([PaymentDate]     <=  ' + @ThruDate + ' ))    AND
		  (( ' + @TaxTypeID  + ' is null)  or ([TaxTypeID]  =  ' + @TaxTypeID + ' )) '  +

	'
	GROUP BY 
            CASE WHEN ' + @TaxTypeID + ' = 1 THEN CountyID, PayYear, PayMonth
                 WHEN ' + @TaxTypeID + ' = 2 THEN SchoolDistrictNum, payYear, PayMonth
                 WHEN ' + @TaxTypeID + ' = 3 OR ' + @TaxTypeID + ' = 4 THEN MuniCode, PayYear, PayMOnth
            END

    ORDER BY 
            CASE WHEN ' + @TaxTypeID + ' = 1 THEN CountyID, PayYear, PayMonth
                 WHEN ' + @TaxTypeID + ' = 2 THEN SchoolDistrictNum, payYear, PayMonth
                 WHEN ' + @TaxTypeID + ' = 3 OR ' + @TaxTypeID + ' = 4 THEN MuniCode, PayYear, PayMOnth
            END '

	EXEC(@SQL)

END

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mlcktmguy

ASKER

I added CountyID to the Select.

I don't doubt your Case construct could be made to work but as a SQL novice It very confusing to me. I'm concerned that I might have to modify it some day and wouldn't know how.

Would an 'If' construct work also and possibly be more clear, at least to me. I don't know the exact syntax but something like

if @TaxTypeID = 1
group by CountyID, PayYear, PayMonth
elseif @TaxTypeID = 2
group by group by SchoolDistrictNum, payYear, PayMonth
elseif @TaxTypeID = 3 or @TaxTypeID = 4
group by group by MuniCode, PayYear, PayMOnth

Pawan Kumar

in this case you have to use the method i gave. That method is called dynamic sql. Pls try.

Pawan Kumar

@Author - could you pls send the final query you are using for my reference.

mlcktmguy

ASKER

	SELECT SUm(FaceAmt)              as SumFace, 
	       Sum(PenaltyAmt)           as SumPenaltyAmt, 
		   Sum(InterestAmt)          as SumInterestAmt,
		   Sum (SvcChgAmt)           as SumSvcChgAmt, 
		   Sum(AboveAndBelowCostAmt) as SumCostAmt,
		   Sum(LienCostAmt)          as SumLienCostAmt,
		   PayYear, PayMonth, MuniCode,SchoolDistrictNum
	From vRpt_JerrysMonthlySummary
	WHERE 
	      ((@MuniCode   is null)  or (MuniCode           = @MuniCode))	  AND
	      ((@SchoolDist is null)  or (SchoolDistrictNum  = @SchoolDist))  AND
          ((@FromDate   is null)  or ([PaymentDate]     >= @FromDate))	  AND
		  ((@ThruDate   is null)  or ([PaymentDate]     <= @ThruDate))    AND
		  ((@TaxTypeID  is null)  or ([TaxTypeID]        = @TaxTypeID))


--
     Group By CASE @TaxTypeID WHEN 1 THEN CountyID WHEN 2 THEN SchoolDistrictNum ELSE MuniCode END                  
                  ,PayYear, PayMonth,MuniCode,SchoolDistrictNum,
                        CASE @TaxTypeID WHEN 1 THEN SchoolDistrictNum ELSE CountyID END,
                        CASE WHEN @TaxTypEID < 3 THEN MuniCode ELSE SchoolDistrictNum END
     Order By CASE @TaxTypeID WHEN 1 THEN CountyID WHEN 2 THEN SchoolDistrictNum ELSE MuniCode END                  
                  ,PayYear, PayMonth, MuniCode,SchoolDistrictNum,
                        CASE @TaxTypeID WHEN 1 THEN SchoolDistrictNum ELSE CountyID END,
                        CASE WHEN @TaxTypEID < 3 THEN MuniCode ELSE SchoolDistrictNum END 

END

Open in new window