asked on

SQL column compare question

I have 2 filelds I am valuing in my application.
- Pain Locaion
- Pain Location Other
Pain Location is a drop down and if Other is selected a free text field (Pain Location Other) is required for input. I need to report the latest charted value. Since this is a multi select combo box I am concatinating Pain Location to Pain Location Other if both are valued To do this I am using...

SELECT StartDT, Abbr, Value, PersonID, AssessmentID 
 FROM (select StartDT, Abbr, Value, PersonID, AssessmentID,
 row_number() over (partition by Abbr order by StartDT desc) rn from ResultsTable with(nolock) 
WHERE PersonID = @intPersonID
 and Abbr IN ('Pain_Location','A_Pain_Other')
and Value IS NOT NULL and Value <> '') as x
where x.rn = 1;

Open in new window

These values can be charted several times a day. The problem I have is when say in the morning someone charts Pain Location from the drop down and in the afternoon someone charts Pain Location Other. The syntax above is pulling both values even though they were not charted at the same time. I need a way to pull the latest charted values but only if they were from the same AssessmentID. Is there a way to compare? Please do let me know if this is not clear.

Sharath S

I think you need to PARTITION BY AssessmentID. Try in that way.

SELECT StartDT, Abbr, Value, PersonID, AssessmentID 
 FROM (select StartDT, Abbr, Value, PersonID, AssessmentID,
 row_number() over (partition by AssessmentID order by StartDT desc) rn from ResultsTable with(nolock) 
WHERE PersonID = @intPersonID
 and Abbr IN ('Pain_Location','A_Pain_Other')
and Value IS NOT NULL and Value <> '') as x
where x.rn = 1;

Open in new window

If you still not getting what you want, post some sample data with expected result.

JRM874

ASKER

Thanks Sharath! I need to partition by start date to pull the latest value. Do you advise doing a second select to partition by assessmentid or sub select?

8080_Diver

JRM874,

I think you can get what you want with the attached:

SELECT StartDT
  ,Abbr
  ,Value
  ,PersonID
  ,AssessmentID
FROM
    (SELECT R1.StartDT
      ,R1.Abbr
      ,R1.Value
      ,R1.PersonID
      ,R1.AssessmentID
      ,row_number() over(partition BY R1.Abbr ORDER BY R1.StartDT DESC) rn
    FROM ResultsTable R1 WITH(NOLOCK)
    INNER JOIN 
	(
		SELECT PersonID, MAX(StartDate)
		FROM   ResultsTable R2
    	WHERE PersonID = @intPersonID
	) R1
	ON R1.PersonID = R2.PersonID
	WHERE   Abbr IN('Pain_Location','A_Pain_Other')
	  AND Value IS NOT NULL
	  AND Value <> ''
    ) AS x
WHERE x.rn = 1;

Open in new window

JRM874

ASKER

I must be doing something wrong When I use this syntax I get an error on the sub select alias R1 when I hover over it it tells me no column was specified for column 2 of R1 and teh join R2.PersonID could not be bound.

JRM874

ASKER

just had to add as StartDT after the MAX in subselect. testing now..

JRM874

ASKER

8080_Diver

Sorry about the StartDT and you need to check the subqueryin the INNER JOIN . . . and I accidentally re-used R1. :-/

Okay, so you need not just the last asseessment but the last assessment by AssessmentID.

Try the attached (not< I corrected the other foo-bars ;-).

SELECT StartDT
  ,Abbr
  ,Value
  ,PersonID
  ,AssessmentID
FROM
    (SELECT R1.StartDT
      ,R1.Abbr
      ,R1.Value
      ,R1.PersonID
      ,R1.AssessmentID
      ,row_number() over(partition BY R1.Abbr ORDER BY R1.StartDT DESC) rn
    FROM ResultsTable R1 WITH(NOLOCK)
    INNER JOIN 
	(
		SELECT  PersonID
                            ,AssessmentID
                            ,MAX(StartDate) StartDate
		FROM   ResultsTable R2
    	WHERE PersonID = @intPersonID
          GROUP BY  PersonID
                   ,AssessmentID
	) R2
	ON R1.PersonID = R2.PersonID
	WHERE   Abbr IN('Pain_Location','A_Pain_Other')
	  AND Value IS NOT NULL
	  AND Value <> ''
    ) AS x
WHERE x.rn = 1;

Open in new window

Sharath S

Can you check this?

SELECT StartDT, Abbr, Value, PersonID, AssessmentID 
 FROM (select StartDT, Abbr, Value, PersonID, AssessmentID,
 row_number() over (partition by AssessmentID,Abbr order by StartDT desc) rn from ResultsTable with(nolock) 
WHERE PersonID = @intPersonID
 and Abbr IN ('Pain_Location','A_Pain_Other')
and Value IS NOT NULL and Value <> '') as x
where x.rn = 1;

Open in new window

JRM874

ASKER

Sharath S

>> I just ran that query on this data.

With my last post, you should get two records for the combination of AssessmentID and Abbr. That means, for every AssessmentID, you should get two records, one for Pain_Location and one for Pain_Other. Is that what you are looking for?

JRM874

ASKER

Not exactly. Does the previous post help explain at all?

Sharath S

Can you post the result of this query?

SELECT StartDT, Abbr, Value, PersonID, AssessmentID 
 FROM (select StartDT, Abbr, Value, PersonID, AssessmentID,
 row_number() over (partition by AssessmentID,Abbr order by StartDT desc) rn from ResultsTable with(nolock) 
WHERE PersonID = @intPersonID
 and Abbr IN ('Pain_Location','A_Pain_Other')
and Value IS NOT NULL and Value <> '') as x
where x.rn = 1 and AssessmentID = 10120712;

Open in new window

8080_Diver

.Abbr               | StartDT                              | Value                          | PersonID   | AssessmentID
Pain_Location | 2011-03-28 13:32:00.000  | Neck                            | 30172895 | 10120712
Pain_Location | 2011-03-24 12:30:00.000  | Left Upper Extremity   | 30172895 | 10120963
Pain_Other      | 2011-03-28 13:29:00.000  | Head                           | 30172895  | 10120712 
Pain_Location | 2011-03-28 12:30:00.000  | Right Upper Extremity   | 30172895 | 10120711

Open in new window

Would you expect to have a result set containing 2 rows from AssessmentID 10120712 and one from AssessmentID 10120711? If So, try SQL_1.

-- SQL_1
SELECT StartDT
  ,Abbr
  ,Value
  ,PersonID
  ,AssessmentID
FROM
    (SELECT R1.StartDT
      ,R1.Abbr
      ,R1.Value
      ,R1.PersonID
      ,R1.AssessmentID
      ,row_number() over(partition BY R1.Abbr ORDER BY R1.StartDT DESC) rn
    FROM ResultsTable R1 WITH(NOLOCK)
    INNER JOIN 
	(
	 SELECT  PersonID
                  ,AssessmentID
                  ,MAX(CONVERT(DateTime, CONVERT(VarChar(10), StartDate, 120), 120) StartDate
	 FROM   ResultsTable R2
    	 WHERE PersonID = @intPersonID
           GROUP BY  PersonID
                    ,AssessmentID
	) R2
	ON R1.PersonID = R2.PersonID
	WHERE   Abbr IN('Pain_Location','A_Pain_Other')
	  AND Value IS NOT NULL
	  AND Value <> ''
    ) AS x
WHERE x.rn = 1;

Open in new window

8080_Diver

I think I used StartDate instead of your StartDT, see below:

SELECT StartDT
  ,Abbr
  ,Value
  ,PersonID
  ,AssessmentID
FROM
    (SELECT R1.StartDT
      ,R1.Abbr
      ,R1.Value
      ,R1.PersonID
      ,R1.AssessmentID
      ,row_number() over(partition BY R1.Abbr ORDER BY R1.StartDT DESC) rn
    FROM ResultsTable R1 WITH(NOLOCK)
    INNER JOIN 
	(
	 SELECT  PersonID
                  ,AssessmentID
                  ,MAX(CONVERT(DateTime, CONVERT(VarChar(10), StartDT, 120), 120) StartDate
	 FROM   ResultsTable R2
    	 WHERE PersonID = @intPersonID
           GROUP BY  PersonID
                    ,AssessmentID
	) R2
	ON R1.PersonID = R2.PersonID
	WHERE   Abbr IN('Pain_Location','A_Pain_Other')
	  AND Value IS NOT NULL
	  AND Value <> ''
    ) AS x
WHERE x.rn = 1;

Open in new window

JRM874

ASKER

@Sharath_123: Nothing is returned with your query posted 03/28/11 03:14 PM, ID: 35236656

JRM874

ASKER

@8080_Driver: I would expect to have the result set contain ONLY the 2 rows from AssessmentID 10120712 and NOT one from AssessmentID 10120711 as assessment 10120711 was done prior. I am only interested in the latest charted value and if Pain_Location_Other is valued it must be from the same AssessmentID as Pain_Loaction.
.
From this sample data...
Abbr | StartDT | Value | PersonID | AssessmentID
Pain_Location | 2011-03-28 13:32:00.000 | Neck | 30172895 | 10120712
Pain_Location | 2011-03-24 12:30:00.000 | Left Upper Extremity | 30172895 | 10120963
Pain_Other | 2011-03-28 13:29:00.000 | Head | 30172895 | 10120712
Pain_Location | 2011-03-28 12:30:00.000 | Right Upper Extremity | 30172895 | 10120711
.
I would expect the following result set...
Pain_Location | 2011-03-28 13:32:00.000 | Neck | 30172895 | 10120712
Pain_Other | 2011-03-28 13:29:00.000 | Head | 30172895 | 10120712
.
I have not tried your queries yet doing that now but I suspect I was not clear on the requirement.

JRM874

ASKER

8080_DriverI am getting Incorrect syntax near 'StartDate' in column 3 which uses the MAX(CONVERT function in your most recent query.

JRM874

ASKER

Just needed the closing paren after 120)). Unfortunatly I am getting...
Msg 241, Level 16, State 1, Line 186
Conversion failed when converting date and/or time from character string.
Let me see what you are trying to do. I can probobly fix the conversion problem myself.

JRM874

ASKER

Not havnig any luck with the conversion. Actually have no idea why this is needed as StartDT column is already (smalldatetime, not null)

8080_Diver

The problem is that your small datetime still has the time portion and that means that two rows that were entered a minute apart will not be selected if you get the "MAX(StartDT)"; instead you will only get one of the rows. If, on the other hand, you remove the time portion (so that both, in effect, have YYYY-MM-DD 00:00:00) then you can use the MAX(StartDT) to select both of the rows (which is what you appear to want).

See if the attached works better.

From this sample data...
Abbr | StartDT | Value | PersonID | AssessmentID
Pain_Location | 2011-03-28 13:32:00.000 | Neck | 30172895 | 10120712
Pain_Location | 2011-03-24 12:30:00.000 | Left Upper Extremity | 30172895 | 10120963
Pain_Other | 2011-03-28 13:29:00.000 | Head | 30172895 | 10120712
Pain_Location | 2011-03-28 12:30:00.000 | Right Upper Extremity | 30172895 | 10120711
.
I would expect the following result set...
Pain_Location | 2011-03-28 13:32:00.000 | Neck | 30172895 | 10120712
Pain_Other | 2011-03-28 13:29:00.000 | Head | 30172895 | 10120712

So, what happpens with the following row from the proposed data?
Pain_Location | 2011-03-28 12:30:00.000 | Right Upper Extremity | 30172895 | 10120711

It has the same date as the two you say you would expect, so why wouldn't it be included?

SELECT StartDT
  ,Abbr
  ,Value
  ,PersonID
  ,AssessmentID
FROM
    (SELECT R1.StartDT
      ,R1.Abbr
      ,R1.Value
      ,R1.PersonID
      ,R1.AssessmentID
      ,row_number() over(partition BY R1.Abbr ORDER BY R1.StartDT DESC) rn
    FROM ResultsTable R1 WITH(NOLOCK)
    INNER JOIN 
	(
	 SELECT  PersonID
                  ,AssessmentID
                  ,MAX(StartDate) StartDate
	 FROM   
	     (
                 SELECT  PersonID
                        ,AssessmentID
                        ,CONVERT(DateTime, CONVERT(VarChar(10), StartDT, 120), 120) StartDate
	       FROM   ResultsTable R2
    	       WHERE PersonID = @intPersonID
               )
           GROUP BY  PersonID
                    ,AssessmentID
	) R2
	ON R1.PersonID = R2.PersonID
	WHERE   Abbr IN('Pain_Location','A_Pain_Other')
	  AND Value IS NOT NULL
	  AND Value <> ''
    ) AS x
WHERE x.rn = 1;

Open in new window

JRM874

ASKER

Unfortunatly that will not work. I do need the time and I understand this presents a problem with the MAX function. I need the time to distinguish latest charted values if charted on the same day. These values can be added multiple times in the same day. Do you think there is a way to do this if we break it up into 2 queries?

JRM874

ASKER

Maybe select max(StartDT) for each field individually into a temp table then compare the assessmentID?

ASKER CERTIFIED SOLUTION

Sharath S

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

JRM874

ASKER

Sharath_123 this looked promising but unfortunatly we've run into the same error that we had with 8080_Drivers orig method...
Msg 241, Level 16, State 1, Line 186
Conversion failed when converting date and/or time from character string.

JRM874

ASKER

8080_Driver to answer you question: So, what happpens with the following row from the proposed data?
Pain_Location | 2011-03-28 12:30:00.000 | Right Upper Extremity | 30172895 | 10120711

I would not expect that row as the row below
Pain_Location | 2011-03-28 13:32:00.000 | Neck | 30172895 | 10120712

was charted later as denoted by 13:32 as opposed to 12:30. I would expect...
Pain_Location | 2011-03-28 13:32:00.000 | Neck | 30172895 | 10120712
Pain_Other | 2011-03-28 13:29:00.000 | Head | 30172895 | 10120712

JRM874

ASKER

Sharath_123 I corrected the conversion issue was a problem with my table.

JRM874

ASKER

Sharath_123: Working with the following data.
2011-03-28 13:29:00.000      Pain_Other        Haad        30172895 10120963
2011-03-28 13:32:00.000 Pain_Location Right Upper Extremity 30172895 10120712

I ran your query and it appears to be doing the opposite of what I need. It returned...
2011-03-28 13:29:00      Pain_Other      Head      30172895      10120963

Where I would have expected it to return.
2011-03-28 13:32:00.000 Pain_Location Right Upper Extremity      30172895      10120712

I only want it to return Pain_Other if it is valued from the same assessmentID i.e. 10120712

8080_Diver

I do need the time

If you look at the query, it is pulling the DateTime column but using the Date Only value to determine which rows to pull.

Consider the following:

If you try to pull based upon the date and time, you will only get a match on one of the rows.

If you try to pull based upon the date, you will get a match on any rows on that date.

If you know that associated entries will be within x minutes of each other, you might be able to use a DateDiff() function to give you the time between two consecutive rows and, coombining that with the MAX(StartDate), be able to get the desired results most of the time.

On other option might be to pull the MAX(StartDate) AS StartDate, grouped by the PersonID and the AssessmentID, as I have done, and then take the TOP 2 when selecting the data based upon the PersonID, AssessmentID, and the StartDT > StartDate.

Try the attached:

SOLUTION

8080_Diver

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Sharath S

What is the data type of StartDT? If it is varchar, you need to convert it to datetime. try this.

SELECT StartDT, Abbr, Value, PersonID, AssessmentID 
 FROM (select StartDT, Abbr, Value, PersonID, AssessmentID,
 row_number() over (partition by PersonID,Abbr order by convert(datetime,StartDT) desc) rn from ResultsTable with(nolock) 
WHERE PersonID = @intPersonID
 and Abbr IN ('Pain_Location','A_Pain_Other')
and Value IS NOT NULL and Value <> '') as x
where x.rn = 1;

Open in new window

JRM874

ASKER

Datatype of StartDT is smalldatetime, not null. Trying the last two posts now.

JRM874

ASKER

8080_Driver: I cannot get your syntax to work. I have several errors that I cannot get past. I the subselect directly after the inner join I am getting invalid column name for PersonID,AssessmentID and MAX(StartDate) StartDate. I am getting the same message in the where clause of this select on personid. The group by is highlighted with message that states Incorrect syntax near 'GROUP' expecting AS, ID or QUOTED_ID. PersonID,AssessmentID are also highlighted in the group as invalid column names. R2.PersonID in the ON R1.PersonID = R2.PersonID is displaying as an invalid column id. R1. is highlighted just below with statement Incorrect syntax near R1. I noticed the same errors with your post from 03/29/11 08:40 AM, ID: 35242284 where it appears you introduced this technique. Do you have any advice on how I could change the query to be syntactically correct?

8080_Diver

Suggestions:
a) start with the following code (I had copied the subquery and inadvertantly left the R2 alias);

b) Highlight and execute the inner most query (the one that converts the date);

c) move to the next outer query (the one that gets the MAX() of that data;

d) eventually, you should reach the complete query.

Not having the data to play with 9coupled with trying to get work done ;-) sometimes leads me to try to get code to you too quickly. Sorry.

SELECT StartDT
  ,Abbr
  ,Value
  ,PersonID
  ,AssessmentID
FROM
    (SELECT R1.StartDT
      ,R1.Abbr
      ,R1.Value
      ,R1.PersonID
      ,R1.AssessmentID
      ,row_number() over(partition BY R1.Abbr ORDER BY R1.StartDT DESC) rn
    FROM ResultsTable R1 WITH(NOLOCK)
    INNER JOIN 
	(
	 SELECT  PersonID
                  ,AssessmentID
                  ,MAX(StartDate) StartDate
	 FROM   
	     (
                 SELECT  PersonID
                        ,AssessmentID
                        ,CONVERT(DateTime, CONVERT(VarChar(10), StartDT, 120), 120) StartDate
	       FROM   ResultsTable 
    	       WHERE PersonID = @intPersonID
               )
           GROUP BY  PersonID
                    ,AssessmentID
	) R2
	ON R1.PersonID = R2.PersonID
             R1.AssessmentID = R2.AssessmentID
	WHERE   Abbr IN('Pain_Location','A_Pain_Other')
	  AND Value IS NOT NULL
	  AND Value <> ''
    WHERE R1.StartDT > R2.StartDate
    ) AS x
WHERE x.rn < 2;

Open in new window

8080_Diver

Also, a comma is needed after the R2.PersonID in the ON clause of the INNER JOIN

JRM874

ASKER

Sharath_123: ran the query in your post 03/29/11 06:08 PM, ID: 35246062 and it returned the unwanted row with AssessmentID 10120963

2011-03-28 13:29:00 Pain_Other Head 30172895 10120963
2011-03-28 13:32:00 Pain_Location Right Upper Extremity 30172895 10120712

Where I would like it to return only the row below as this was the latest charted value and there is no Pain_Other charted with the same assessmentID.
2011-03-28 13:32:00.000 Pain_Location Right Upper Extremity 30172895 10120712

I would only like it to return Pain_Other if it is valued from the same assessmentID i.e. 10120712

JRM874

ASKER

I tried to split this up into 2 queries using temp tables i.e.

DECLARE @tblPainLoc table (StartDT smalldatetime, Abbr varchar (64), Value varchar(3000), PersonID int, AssessmentID int )

INSERT INTO @tblPainLoc
SELECT StartDT, Abbr, Value, PersonID, AssessmentID 
 FROM (select StartDT, Abbr, Value, PersonID, AssessmentID,
 row_number() over (partition by PersonID, Abbr order by convert(datetime,StartDT) desc) rn from ResultTable with(nolock) 
WHERE PersonID = @intPersonID
 and Abbr = 'A_Pain1 Location'
and Value IS NOT NULL and Value <> '') as x
where x.rn = 1;

DECLARE @tblPainOth table (StartDT smalldatetime, Abbr varchar (64), Value varchar(3000), PersonID int, AssessmentID int )

INSERT INTO @tblPainOth
SELECT StartDT, FindingAbbr, Value, PersonID, AssessmentID 
 FROM (select StartDT, FindingAbbr, Value, PersonID, AssessmentID,
 row_number() over (partition by PersonID, Abbr order by convert(datetime,StartDT) desc) rn from ResultTable with(nolock) 
WHERE PersonID = @intPersonID
 and Abbr = 'A_Pain_Other'
and Value IS NOT NULL and Value <> '') as x
where x.rn = 1
and AssessmentID = (Select AssessmentID from @tblPainLoc);

select * from @tblPainLoc
Union
select * from @tblPainOth

Open in new window

This appears to do what I need. Do you see any flaw in this approach?

JRM874

ASKER

8080_Diver: very much appreciate you time and guidance. Also respect the need to get work done . Thank you! I started from the inner most query (the one that converts) this works fine. As soon as I move to the next query the errors present. It does not appear to like the From (Select... and I cannot figure out why as I do that all the time. None of the columns are recognized in the select after Inner Join and I keep getting Incorrect syntax near the keyword 'GROUP'. when attempting to run.

Any thoughts on my post 03/30/11 09:46 AM, ID: 35260701

8080_Diver

Try adding Z after the close-parenthesis before the GROUP BY./ Sometimes, the query analyzer gets ticked if there isn't an alias on the subquery.

8080_Diver

I tried to split this up into 2 queries using temp tables

Other than the fact that it is hitting the database twice when we should be able to get there with one query and the fact that it is possible that you could, potentially, end up with different AssessmentID's in the two tables, it should work.

However, I would recommend pursuing the single query model. ;-)

Sharath S

Its hard to guess your possible scenarios. better provide some sample data in a spreadsheet with expected result. In my attached code, I have 4 records with expected records as 3. Is that what you are looking for?

declare @Table table(Abbr varchar(20),StartDt datetime, PersonID int,AssessmentID int)
insert @Table values 
('Pain_Location','2011-03-28 13:32:00.000',30172895,10120712),
('Pain_Location','2011-03-24 12:30:00.000',30172895,10120963),
('Pain_Other','2011-03-28 13:29:00.000',30172895,10120712),
('Pain_Location','2011-03-28 12:30:00.000',30172895,10120711)
select * from @Table
/*
Abbr	StartDt	PersonID	AssessmentID
Pain_Location	2011-03-28 13:32:00.000	30172895	10120712
Pain_Location	2011-03-24 12:30:00.000	30172895	10120963
Pain_Other	2011-03-28 13:29:00.000	30172895	10120712
Pain_Location	2011-03-28 12:30:00.000	30172895	10120711
*/
;with CTE1 as (select *,COUNT(*) over (partition by PersonID,AssessmentID) cnt from @Table),
      CTE2 as (select *,ROW_NUMBER() over (partition by PersonID,Abbr order by StartDt desc) rn from CTE1 where cnt = 1),
      CTE3 as (select *,ROW_NUMBER() over (partition by PersonID,Abbr order by StartDt desc) rn from CTE1 where cnt <> 1)
select * from CTE2 where rn = 1 union 
select * from CTE3 where rn = 1
/*
Abbr	StartDt	PersonID	AssessmentID	cnt	rn
Pain_Location	2011-03-28 12:30:00.000	30172895	10120711	1	1
Pain_Location	2011-03-28 13:32:00.000	30172895	10120712	2	1
Pain_Other	2011-03-28 13:29:00.000	30172895	10120712	2	1
*/

Open in new window