Post reply

help me write the SQL

  • June 9, 2015 at 1:47 pm

    #318765

    Take a look at the picture

    Those records come from a table patient_blood_pressure

    Can you help me write the SQL so that

    I need result set to have the ID appearing once and it should have the blood_pressure value that goes with the latest date.

    I know I need a GROUO by clause there but not sure...

  • June 9, 2015 at 1:59 pm

    #1804117

    The image you post does not help. DDL and some easily consumable sample data would be better.

    The logic you are looking for will look something like this:

    -- SOME SAMPLE DATA TO WORK WITH:

    DECLARE @yourdata TABLE ([pi.patientID] int, fieldWithBloodPressureValue varchar(100), SomeDate date);

    INSERT INTO @yourdata VALUES

    (1, 'somethingElse', '1/1/2010'),

    (1, 'Blood Pressure', '1/1/2010'),

    (1, 'Blood Pressure', '1/20/2010'),

    (2, 'Blood Pressure', '1/1/2010'),

    (2, 'Blood Pressure', '1/1/2010'),

    (3, 'Blood Pressure', '1/1/2010'),

    (3, 'Blood Pressure', '2/1/2010'),

    (3, 'somethingElse', '1/1/2010');

    -- WHAT YOU ARE LOOKING FOR:

    WITH x AS

    (

    SELECT

    xx = ROW_NUMBER() OVER (PARTITION BY [pi.patientID] ORDER BY someDate DESC),

    [pi.patientID],

    fieldWithBloodPressureValue,

    SomeDate

    FROM @yourdata

    WHERE fieldWithBloodPressureValue = 'Blood Pressure'

    )

    SELECT

    [pi.patientID],

    fieldWithBloodPressureValue,

    SomeDate

    FROM x

    WHERE xx = 1;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • June 9, 2015 at 2:09 pm

    #1804118

    Sorry this is too cokplicated.. Lets go back to the picture and I will explain.

    The picture was to show you how the data looks like in the table.

    Please assume that the table in the picture is as follows

    Create table A ( ID uniqueidentifier, NUM_VAL decimal(19,2) , T_stamp datetime )

    Now I need the result set to be like this where I get the ID only once and it picks the blood pressure value for the

    record that has the HIGHEST timestamp. Does this make sense ?

    E1A126F2-D8D1-E311-85AD-00215A9B0A8C, 146

    FB4EAB83-04D2-E411-8773-00215A9B0A8C, 90

  • June 9, 2015 at 2:19 pm

    #1804124

    No I changed my mind..

    That solution worked...:):)

    Thanks...Have a great day and no need to reply to this

    ___CASE CLOSED _______

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply