In my web application I have a query with a control parameter that enables switching between two different reports: one that groups by 'Section' and the other that groups by 'Section' and 'Date'
I'm doing this trick:
declare @switch nvarchar(1)
set @switch = CASE WHEN @checkbox = 1 THEN '' ELSE NULL END
SELECT SUM(minutes) as Time, Section, COALESCE(@switch,Date) as Date
FROM Table
GROUP by Section, COALESCE(@switch,Date)
That works, but has the ugly side effect that the empty date is then displayed as '1900-01-01' (which I will hide on the webside). I'm puzzeling over a function that returns a column or Null based on the value (1/0) of the control parameter.
Someone has a better solution?
Martin
Try to integrate the check/switch in your query like this:
DECLARE @TABLE AS TABLE
(
[minutes] INT NOT NULL
,[Section] INT NOT NULL
,[Date] DATETIME NOT NULL
)
INSERT INTO @TABLE(minutes,Section,Date)
VALUES (10,1,'20161011')
,(12,1,'20161012')
,(5,2,'20161011')
declare @switch nvarchar(1)
,@checkbox tinyint = 0
SELECT
SUM(minutes) as Time
, Section
, CASE WHEN @checkbox = 1 THEN NULL ELSE Date END as Date
FROM
@TABLE
GROUP by
Section
, CASE WHEN @checkbox = 1 THEN NULL ELSE Date END
In your place , I will create 2 stored procedures:
-
first - call Total_MinutesSections - that is a total without dates , with the body like this:
SELECT
SUM(minutes) as Time
, Section
FROM
TABLE
GROUP by
Section
-
second - call Total_MinutesSectionsDates - that is a total with dates , with the body like this:
SELECT
SUM(minutes) as Time
, Section
, Date
FROM
TABLE
GROUP by
Section
, Date
and base on your checkbox you;re calling the right one.
+1 Absolutely this. Definitely avoid using a User Defined Function for this (if that was your intention) as the performance will be dreadful. You won't notice how dreadful until the APP scales up - by which time you'll probably have the function all over your code and a Month-of-Sundays' work to replace it 
Not needed in @stepson solution, but if you encounter this issue you can work around it with:
SELECT
...
NullIf(COALESCE(@switch, Date), '19000101') as Date
...
which will give you NULL instead of the CompareValue matched by the NullIf(Value, CompareValue) function. If you want to force a different value, instead of the CompareValue, you can do
COALESCE(NullIf(MyColumn, @CompareValue), @ReplaceValue)
to force a different @ReplaceValue value when MyColumn matches @CompareValue
Bit unwieldy, but runs very efficiently.
1 Like
SELECT SUM(minutes) as Time, Section,
NULLIF(COALESCE(@switch,Date),'19000101') as Date
FROM Table
GROUP BY Section, NULLIF(COALESCE(@switch,Date),'19000101')
1 Like
Thank you, I will use the solution proposed by Kristen and ScottPletcher as it can adapt to different column types (INT, VARCHAR).
@stepson: Actually I'm using this trick quite often and that's why I don't like the idea of writing a stored procedure each time.
Years ago I read an article that explained that one should try to avoid the CASE-WHEN expression whenever it is possible to substitute it with the SQL functions Coalesce, Nullif, IsNull. Performance will improve - hope that this isn't a legend.
@Kristen: I didn't looked for a user defined function but for a smart combination of SQL functions like the one you posted.
Martin
SELECT CHOOSE(@checkbox, Date) As [Date]
Assuming @checkbox will be either 0 or 1 - when the value is 1 it will return the value from the column and when the value is 0 it will return a NULL.
1 Like
@jeffw8713: Very handy! One should spend more time to explore all new features of the newer SQL versions; this one came up with 2012 as I just read.