Yes, today I will problem solve and compare contrast the 3 queries offered here.

Drew's is the shortest and looks very elegant. Dwains was a break through and...

Drew, I appreciate your help. Your query does almost everythinig but the numbers appear to represent the number of days a given ticket is in any status and putting...

Within the CIVIL category the results are all correct.

Padawan. . .

WA_Parent_Relocation Propoosed should be 30-60 days. Yours is <30 days.

WA_Parent_Relocation Active is Correct.

WA_Burglary Proposed should be >60 days. Yours...

I appreciate it. studying it.

SQL Padawan (4/19/2012)

---

Based on the explanation,testdata and your desired results...why would WA_TENANT be in the column for 30-60 days for "Active"? Look at your test data:

UNION ALL SELECT 220, 'Civil',...

I am going to explain better what is needed. It's all very close but I finally realize what I haven't been saying. Note the asterisked words.

I need to capture...

SQL Padawan, I like! it is very simple. But the numbers are wrong.

drew, when I ran your code I got error:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'CTE'.

BUT you are correct. Proposed time for WA_Traffic should be 30-60

(1) dwain the code does almost exactly what it is supposed to. BUT (edit). WA_Traffic Proposed time should actually be 30-60 days and your code places it at <30...

**correction***I was asking dwain if he'd elaborate the 'certain circumstances'?, but I see that I didn't read his whole post following.

I shall venture to disagree with that statement under certain...

My statement is based on the assumption that your department is fully staffed with reasonably adequate people. Of course, there will always be situations where you are either...

Looking at now. U N BELIEVABLE. Will be studying it.

drew allen, I appreciate your point very much. Also, thanks for that query and illustration of what Excel is capable of and how it simplifies matters.

We have not determined which...

Sean Lange, thanks for showing me how to apply Lyn's advice. now I know.

Here is the desired result as a #temp table.

create table #Result

(

Col1 varchar(50),

Col2 varchar(50),

Col3 varchar(50),

Col4 varchar(50)

)

insert #Result

select...

The numbers in the first row are for all Civil law matters. Civial is a parent to WA_Tenant type, WA_Traffic and WA_Consumer.

The number...