SQL Server Get values of top three records and dis

Question

I am trying to get the values of the top three rows for a person to all display in one row.

My data looks like this:

id       co_number  client_no  Client_name  taken_date    taken_value
--------------------------------------------------------------------------
270103   12         1111       John Doe     6/7/11 8:45 AM    108
270100   12         1111       John Doe     5/3/11 10:49 AM   109
270097   12         1111       John Doe     4/4/11 1:58 PM    109
270094   12         1111       John Doe     3/1/11 9:04 AM    106
270091   12         1111       John Doe     2/1/11 8:47 AM    105
270088   12         1111       John Doe     1/4/11 9:10 AM    106
270120   12       2222       Jane Smith    6/7/11 9:06 AM     215
270117   12       2222       Jane Smith    5/3/11 2:01 PM     216
270114   12       2222       Jane Smith   4/4/11 2:08 PM      214
270111   12       2222       Jane Smith    3/1/11 9:27 AM     209
270159   12       3333       John Adams    6/7/11 9:45 AM     205
270156   12       3333       John Adams   5/3/11 2:12 PM      203
270153   12       3333       John Adams    4/4/11 1:42 PM     202
270150   12       3333       John Adams   3/1/11 10:32 AM     198

I want the data to display like this (Date1 being the most recent, then Date2, then Date3):

co#  Name      Date1             Value1 Date2             Value2 Date3             Value3
-------------------------------------------------------------------------------------------
12   John Doe  2011-06-07 08:45  108.0  2011-05-03 10:49  109.0  2011-04-04 13:58  109.0

Here is what I have so far. It works but it's slow (takes 30 secs to return one co_number) so I'm wondering if there is a better more efficient way of doing this.

select 
vmain.co_nmber, vmain.Client_name, vmain.Taken_date, vmain.Taken_value
, (select top 1 Taken_date from vital vdate where vdate.co_nmber=vmain.co_nmber and vdate.Medical_Record_Number=vmain.Medical_Record_Number and vdate.Taken_date < vmain.Taken_date order by vdate.Taken_date desc) as date2
, (select top 1 Taken_value from vital v_value where v_value.co_nmber=vmain.co_nmber and v_value.Medical_Record_Number=vmain.Medical_Record_Number and v_value.Taken_date < vmain.Taken_date order by v_value.Taken_date desc) as value2
, (select top 1 Taken_date from vital vdate where vdate.co_nmber=vmain.co_nmber and vdate.Medical_Record_Number=vmain.Medical_Record_Number and vdate.Taken_date < (select top 1 Taken_date from vital vdate where vdate.co_nmber=vmain.co_nmber and vdate.Medical_Record_Number=vmain.Medical_Record_Number and vdate.Taken_date < vmain.Taken_date order by vdate.Taken_date desc) order by vdate.Taken_date desc) as date3
, (select top 1 Taken_value from vital vvalue where vvalue.co_nmber=vmain.co_nmber and vvalue.Medical_Record_Number=vmain.Medical_Record_Number and vvalue.Taken_date < (select top 1 Taken_date from vital vdate where vdate.co_nmber=vmain.co_nmber and vdate.Medical_Record_Number=vmain.Medical_Record_Number and vdate.Taken_date < vmain.Taken_date  order by vdate.Taken_date desc)  order by vvalue.Taken_date desc) as value3
from vital as vmain 
inner join(
SELECT v.co_nmber, v.Medical_Record_Number, max(v.Taken_date) as Taken_date
FROM Vital v
and v.co_nmber = 12
GROUP BY v.co_nmber, v.Medical_Record_Number 
) as vsub on vsub.co_nmber=vmain.co_nmber and vsub.Medical_Record_Number=vmain.Medical_Record_Number and vsub.Taken_date = vmain.Taken_date
and vmain.co_nmber = 12
order by vmain.co_nmber, vmain.Medical_Record_Number, vmain.Taken_date

Help appreciated.

Answer 1

You could number your records per co and client with row_number. After this you can select the first ones and left join the second and third ones. Should be faster.

with cVital as (

select  v.co_nmber, v.Medical_Record_Number, v.Client_name,
        v.taken_date, v.taken_value,
        n = row_number() over (partition by v.co_nmber, v.Medical_Record_Number order by v.taken_date desc)
from    Vital v

)
select  [co#]=v1.co_nmber, [Name]=v1.Client_name,
        Date1 = v1.taken_date, Value1 = v1.taken_value,
        Date2 = v3.taken_date, Value2 = v2.taken_value,
        Date3 = v2.taken_date, Value3 = v3.taken_value
from    cVital v1
left join cVital v2
    on  v2.co_nmber = v1.co_nmber
    and v2.Medical_Record_Number = v1.Medical_Record_Number
    and v2.n = 2
left join cVital v3
    on  v3.co_nmber = v1.co_nmber
    and v3.Medical_Record_Number = v1.Medical_Record_Number
    and v3.n = 3
where   v1.n = 1
order by v1.co_nmber, v1.Medical_Record_Number;

Answer 2

The slowness may be to using correlated sub-queries in the Select statement. Remember that sub-queries will execute for every record in the table

If you are only going to have a fixed number of values returned using a Temp table (or Table variable) and the Row_Number() Over method will work:

SELECT ROW_NUMBER() OVER (PARTITION BY TheKey ORDER BY DateValue DESC) RowNum, TheKey, DateValue, OtherValue
INTO tmp
FROM SomeTable
WHERE .....

SELECT x1.TheKey, x1.DateValue date1, x1.OtherValue Value1, x2.DateValue Date2, x2.OtherValue Value2, x3.CreateDate Date3, x3.OtherValue Value3
FROM (SELECT * FROM #tmp WHERE rownum=1) x1
left JOIN (SELECT * FROM #tmp WHERE rownum=2) x2 ON x1.TheKey = x2.TheKey
left JOIN (SELECT * FROM #tmp WHERE rownum=3) x3 ON x1.TheKey = x3.TheKey

Answer 3

Ok, I updated my answer. Here is a solution that will work based on the query I made earlier:

    DECLARE @Name VARCHAR(100)
    DECLARE @TmpName VARCHAR(100)
    DECLARE @ID INT
    DECLARE @CoNum INT
    DECLARE @Date1 DATETIME
    DECLARE @Date2 DATETIME
    DECLARE @Date3 DATETIME
    DECLARE @Value1 INT
    DECLARE @Value2 INT
    DECLARE @Value3 INT
    DECLARE @IndexValue INT
    DECLARE @SetValue INT
    DECLARE @SetDate DATETIME

    CREATE TABLE #OutputTable (
        co_number INT,
        Client_Name VARCHAR(200),
        Date1 DATETIME,
        Value1 INT,
        Date2 DATETIME,
        Value2 INT,
        Date3 DATETIME,
        Value3 INT
    )

    SELECT DISTINCT Client_name INTO #TempVitalNames FROM vital ORDER BY Client_name

    WHILE (SELECT COUNT(*) FROM #TempVitalNames) > 0
    BEGIN
        SELECT @IndexValue = 0
        SELECT TOP(1) @TmpName = Client_name FROM #TempVitalNames

        SELECT TOP(3) * INTO #TempVital FROM vital WHERE Client_name = @TmpName ORDER BY taken_date DESC

        WHILE (@IndexValue < 3)
        BEGIN       
            SET @Name = (SELECT TOP 1 Client_name FROM #TempVital)
            SET @CoNum = (SELECT TOP 1 co_number FROM #TempVital)
            SELECT TOP 1 @ID = id FROM #TempVital
            SET @SetDate = (SELECT TOP 1 taken_date FROM #TempVital)
            SET @SetValue = (SELECT TOP 1 taken_value FROM #TempVital)
            DELETE FROM #TempVital WHERE id = @ID
            SET @Date1 = CASE WHEN @IndexValue = 0 THEN @SetDate ELSE @Date1 END
            SET @Date2 = CASE WHEN @IndexValue = 1 THEN @SetDate ELSE @Date2 END
            SET @Date3 = CASE WHEN @IndexValue = 2 THEN @SetDate ELSE @Date3 END
            SET @Value1 = CASE WHEN @IndexValue = 0 THEN @SetValue ELSE @Value1 END
            SET @Value2 = CASE WHEN @IndexValue = 1 THEN @SetValue ELSE @Value2 END
            SET @Value3 = CASE WHEN @IndexValue = 2 THEN @SetValue ELSE @Value3 END

            SELECT @IndexValue = @IndexValue + 1                
        END

        INSERT INTO #OutputTable (co_number, Client_Name, Date1, Value1, Date2, Value2, Date3, Value3)
        ( SELECT @CoNum, @Name, @Date1, @Value1, @Date2, @Value2, @Date3, @Value3 )     

        DELETE #TempVitalNames WHERE Client_name = @TmpName
        DROP TABlE #TempVital
    END

    DROP TABLE #TempVitalNames

    SELECT * FROM #OutputTable

    DROP TABLE #OutputTable

Ok Claudia. This will first pull all the unique names in the table into a temporary table. It will then iterate through each name. Inside the iteration loop, it will do what my last program will do and pull the needed dates from the top 3 names and insert them into another temporary table. After all the names have been processed, it will then dump the contents of the temporary table, which should be the output you are looking for. I ran this query on my system with the test data you provided and it executed in under 1 second. I know you probably have a lot more rows then I had to work with, but it seems to work pretty well. If you need anything else, let me know and I will modify it accordingly. You are free to add any kind of additional where clauses to the SELECT statements, if needed. You may also need to change some of the data types. I wasn't sure if your taken_value was integer or decimal or whatever. I went with INT, but you can change it to match your needs.

Answer 4

The big question is "What is causing the query to run slowly?"

Is your table indexed appropriately?

Perhaps you could remove one of your computed columns at a time and see how that effects performance.