• Home   /  
  • Archive by category "1"

T Sql Variables Multiple Value Assignment

Hi, I want to get this query right ( please consider the declare statement for the 3 months I want to achieve) so I could use pivot to obtain a report. For now this is giving me error message. Please what am I missing in the syntax ?
The result is right if I declare for one month but error shows up when I try to declare for 3 months.
Thanks.

DECLARE
@StartDate DATE =’01 Jun 2017′,
@EndDate DATE = ’30 June 2017′
@StartDate DATE = ’01 Jul 2017′,
@EndDate DATE =’31 Jul 2017′
@StartDate DATE = ’01 Aug 2017′,
@EndDate DATE = ’31 Aug 2017′

SELECT
AccountNumber,
UnitNumber,
ArrivalDateTime,
DepartureDateTime,
BreachReason,
HospitalName,
ReasonForVisit,
IPAccountNumber,
DOB,
DATEDIFF(YEAR,DOB,ArrivalDateTime) AS Age,
DATEDIFF(HOUR,ArrivalDateTime,DepartureDateTime) AS [Hour],
CASE WHEN DATEDIFF(HOUR,ArrivalDateTime,DepartureDateTime) <=4 THEN 'Not Breach'
ELSE 'Breach' END AS BreachStatus,
CASE
WHEN DATEDIFF(YEAR,DOB,ArrivalDateTime) BETWEEN 0 AND 10 THEN '[0-10]'
WHEN DATEDIFF(YEAR,DOB,ArrivalDateTime) BETWEEN 11 AND 20 THEN '[11-20]'
WHEN DATEDIFF(YEAR,DOB,ArrivalDateTime) BETWEEN 21 AND 30 THEN '[21-30]'
WHEN DATEDIFF(YEAR,DOB,ArrivalDateTime) BETWEEN 31 AND 40 THEN '[31-40]'
WHEN DATEDIFF(YEAR,DOB,ArrivalDateTime) BETWEEN 41 AND 50 THEN '[41-50]'
WHEN DATEDIFF(YEAR,DOB,ArrivalDateTime) BETWEEN 51 AND 60 THEN '[51-60]'
WHEN DATEDIFF(YEAR,DOB,ArrivalDateTime) BETWEEN 61 AND 70 THEN '[61-70]'
ELSE '70+'END AS AgeGroup
FROM DigitalInsightHealthTrustDataWareHouse.Activity.AandE
WHERE CAST(ArrivalDateTime AS DATE) BETWEEN @StartDate AND @EndDate

SELECT (Transact-SQL)

Sets a local variable to the value of an expression.

For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable.

Transact-SQL Syntax Conventions

Syntax

Arguments

@local_variable
Is a declared variable for which a value is to be assigned.

{= | += | -= | *= | /= | %= | &= | ^= | |= }
Assign the value on the right to the variable on the left.

Compound assignment operator:

operatoraction
=Assigns the expression that follows, to the variable.
+=Add and assign
-=Subtract and assign
*=Multiply and assign
/=Divide and assign
%=Modulo and assign
&=Bitwise AND and assign
^=Bitwise XOR and assign
|=Bitwise OR and assign

expression
Is any valid expression. This includes a scalar subquery.

SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.

If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.

One SELECT statement can initialize multiple local variables.

Note

A SELECT statement that contains a variable assignment cannot be used to also perform typical result set retrieval operations.

Examples

A. Use SELECT @local_variable to return a single value

In the following example, the variable is assigned as its value. The query against the table returns no rows because the value specified for does not exist in the table. The variable retains the value.

Here is the result set.

B. Use SELECT @local_variable to return null

In the following example, a subquery is used to assign a value to . Because the value requested for does not exist, the subquery returns no value and the variable is set to .

Here is the result set.

See Also

DECLARE @local_variable (Transact-SQL)
Expressions (Transact-SQL)
Compound Operators (Transact-SQL)
SELECT (Transact-SQL)

One thought on “T Sql Variables Multiple Value Assignment

Leave a comment

L'indirizzo email non verrĂ  pubblicato. I campi obbligatori sono contrassegnati *