Setting variables within a SELECT in TSQL

Question

I have a test MySQL statement as follows:

SELECT 
    id, CCVisit, Admit, Discharge, 
    CASE
        WHEN @prev_value = a.HospID THEN @rank_count:=@rank_count + 1
        ELSE @rank_count:=1
    END AS Rank,
    @prev_value:=a.HospID as CurrentHospID,
    CASE
        WHEN @rank_count = 1 THEN @prev_date := Discharge
        ELSE @prev_date
    END AS PreviousDischarge,
    @prev_date:=Discharge AS DischargeHolder
FROM
    fusion.sqltest
        INNER JOIN
    (SELECT 
        HospID, Count(*) Frequency
    FROM
        fusion.sqltest
    GROUP BY HospID
    ) AS a ON sqltest.HospID = a.HospID

It is running against a very simple test table of four columns:

+----+---------+------------+------------+--------+
| id | CCVisit | Admit      | Discharge  | HospID |
+----+---------+------------+------------+--------+
|  5 |       1 | 2014-01-01 | 2014-01-03 |      1 |
|  6 |       2 | 2014-01-05 | 2014-01-06 |      1 |
|  7 |       3 | 2014-01-07 | 2014-01-08 |      1 |
|  8 |       4 | 2014-01-07 | 2014-01-10 |      2 |
+----+---------+------------+------------+--------+

The ideas to find the time between discharged and then admitted to a subsequent visit with the same HospID. For example the time delay between CCVisti 1 and 2, 2 & 3 but NOT 3 to 4 (as this is a different HospID. To do this I decided to rank the admissions (for a HospID), save the previous Discharge date and ultimately (not included here) calculate the date difference for each record between the previous records discharge date (saved in @prev_date) the the Admission time. I guess I could do this as well without the rank variable?

The code seems to work.

I now need to convert this to work with MS SQL 2005. I am having all sorts of issues, including not being able to set a variable in a select statement. Here is what I have so far

DECLARE @prev_value AS int
DECLARE @rank_count AS int
DECLARE @prev_date AS smalldatetime
DECLARE @rank AS int

SELECT 
    *,
    CASE
        WHEN @prev_value = a.HospID THEN @rank + 1
        ELSE 1
    END AS Rank,
    @prev_value=a.HospID ,
    CASE
        WHEN @rank_count = 1 THEN @prev_date = Discharge
        ELSE @prev_date
    END AS PreviousDischarge,
    @prev_date:=Discharge AS DischargeHolder
FROM
    dbo.sqltest
        INNER JOIN
    (SELECT 
        HospID , Count(*) Frequency
    FROM
        dbo.sqltest
    GROUP BY HospID 
    HAVING COUNT(*) >=2
    ) AS a ON dbo.sqltest.HospID =a.HospID

I guess my first question is - how do I deal with the "@prev_value=a.HospID" type statements? MS SQL is giving me a "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations" error. Any guidance appreciated!

HoneyBadger · Accepted Answer · 2014-10-31 07:51:25Z

1

Far as I know, this is not possible. Because queries are set-based (all rows are processed simultaneously) you cannot assign variables in your query (I'm not familiar with mysql, but I'm surprised it can handle variable assignment in a query. But mysql has many functions which surprise me.).

You can, of course, make a RBAR query where you can set a variable (using a cursor or while loop for example). This will not improve performance, however. You could also look into the LAG function to retrieve a previous date on a set-level (doubt if its available in mssql 2005 though), or use a CTE to store the previous values.

Hope this helps.

answered Oct 31, 2014 at 7:51

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Setting variables within a SELECT in TSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related