Like this

My method

declare @patients table

( PatientID INT)

declare @Tests table

( PatientID INT, DataofTest DATETIME , TestValue VARCHAR(20) )

insert into @patients (PatientID)

...

What you want to do is to utilize ROW_NUMBER() on table2 , join with table1 and pickk up only the values whre ROW_NUMBER() = 1

This?

declare @date date = '02/29/12'

select dateadd(DD, -1, dateadd(YY,datediff(yy,0,@date),0))

Something like this?

; WITH Numbered AS

(

SELECT MB.id

,MB.MCDate

...

Sean Lange (5/16/2012)

Thanks for the ddl and sample data. Based on your sample data what should...

The table to the left of the keyword LEFT OUTER JOIN will always be kept in the result set irrespective of any matches found or not in the other table....

You can also use RAISERROR with a severity greater than/equal to 10. But RAISERROR should be used with TRY..CATCH to be used effectively as a exit mechanism.

Sample piece

Use TempDB

GO

Create ...

As 90% of the data is unwanted, you can move the 10% of the valid data to a temp table, drop the table , create a new table with the...

OP's request is to create a WHERE clause for these conditions...........

Selection of documents based on these OR conditions:

1. Grade = 'D' and PeriodFinishDate within last 3 years

OR

2. ReportReceivedDate > ReportDueDate...

Yep, that would work (though the CASE syntax is incorrect there). Its defnitely do-able.

Great Job posting all the necessary information. Third post today i really wanted to have a go at it 🙂 Keep it up.

Here are few solutions:

Method 1:

SELECT IdNum

...

GOOD JOB posting the required tables and sample data. It could have even better if you had given a "clear" pictoral representation of your expected result (though i see that...

Will there always be only 3 rows for each COMMID?

If Yes, then what order do those 3 rows be pivoted? I dont see any unique sort order for the table,...

Can you post the Actual Execution Plan ?

If you look at the sample data in @Table, AttrVal1 column has a single word entry for all rows while the second column has multiple words for every row.

I...