I need to make a case statement.
Depending on what the variables value is, it needs to select the correct column from the table
StartDate and EndDate are different variables.
There is a variable i created called Region which should determine what column the query selects.
EDIT: Region can either be 'EW' for England and Wales, 'SC' for Scotland or 'NI' for Northern Ireland. If it is EW it should select column 1, SC for column 2, NI for column 3
SELECT COUNT(COLUMN1) FROM bankholidays WHERE COLUMN1 BETWEEN @StartDate AND @EndDate)Answer1:
WHEN @Region = 'EW' THEN columnName
WHEN @Region = 'SC' THEN columnName
WHEN @Region = 'NI' THEN columnName
END AS newColumnName FROM bankholidaysT
Try something like thisAnswer2:
Assuming you want to count the number of records for which
Column1 is in between the start and end date, then the following should work:
SELECT SUM(CASE WHEN COLUMN1 BETWEEN @StartDate AND @EndDate THEN 1 ELSE 0 END) AS tally FROM bankholidays
This approach is called conditional aggregation, and often you will also use a
GROUP BY clause.
To handle your
@Region variable, a query might look like this:
SELECT CASE WHEN @Region = 'val1' THEN COLUMN1 WHEN @Region = 'val2' THEN COLUMN2 WHEN @Region = 'val3' THEN COLUMN3 END AS new_column FROM bankholidays
Note that I did not attempt to combine this with the first query because it would mix aggregate functions with non aggregate terms, and it might not make sense.Answer3:
1) If you're not concerned about excluding nulls from your count, you don't need to specify a column name in your COUNT statement. i.e.
Will give the same result as
So long as column1 has no null values. If column1 does contain nulls, those aren't counted (so if there are 10 records, 3 of which have null values in column1, you'd get a result of 7 using
count(column1) or a count of 10 using the other methods.
I mention this first as if you care about nulls then changing which column is used here makes sense; if you don't, go with the simpler logic of just
All that said, here's how to change that column:
select count( case @Region when 1 then Column1 when 2 then Column2 else Column3 end )
2) If you want to change the column used in your WHERE statement, there are a couple of approaches:
SELECT COUNT(1) FROM bankholidays WHERE case @Region when 1 then Column1 when 2 then Column2 else Column3 end BETWEEN @StartDate AND @EndDate
SELECT COUNT(1) FROM bankholidays WHERE (@Region = 1 and Column1 BETWEEN @StartDate AND @EndDate) or (@Region = 2 and Column2 BETWEEN @StartDate AND @EndDate or (@Region not in (1,2) and Column3 BETWEEN @StartDate AND @EndDate
Personally I prefer the first style above, since it involves less repetition; however the second style offers the option to use different start & end dates for the different columns, or to add in other logic too, so is still worth being aware of.Answer4:
If you want to dynamically count records where a given column value is between two dates, use a dynamic query:
DECLARE @column NVARCHAR(MAX) DECLARE @sql NVARCHAR(MAX) SET @column = 'COLUMN1' --Set your desired column here SET @sql = 'SELECT COUNT(*) FROM bankholidays WHERE ' + @column + ' BETWEEN ''' + CONVERT(NVARCHAR,@StartDate,121) + ''' AND ''' + CONVERT(NVARCHAR,@EndDate,121) + '''' EXEC @sqlAnswer5:
<strong>TRY THIS</strong> IF you want to
count and check the column dynamically
DECLARE @sql VARCHAR(500), @Region VARCHAR(100) = 'COLUMN2', --It can be Column1, Column2..... @StartDate DATETIME = '2016-04-10', @EndDate DATETIME = '2016-04-15' SET @sql = ' SELECT COUNT(' + @Region + ') FROM bankholidays WHERE ' + @Region + ' BETWEEN ''' + CAST(@StartDate AS VARCHAR) + ''' AND ''' + CAST(@EndDate AS VARCHAR) + '''' EXEC (@SQL)
--Query executed as below
SELECT COUNT(COLUMN2)--It can be Column1, Column2..... FROM bankholidays WHERE COLUMN2 BETWEEN 'Apr 10 2016 12:00AM' AND 'Apr 15 2016 12:00AM'Answer6: