Finding previous record based on a single common field.

WayneS

Thank you for the review of you forum's practices. I apologize for not lurking longer before posting. I had gone back and forth on whether to post in this forum or the Newbie one and seems I chose wrongly<G> To be honest with you I had expected some suggestions on direction to take which I would research and try to use, not a tested solution.

Thom

mister.magoo

Thank you for the reply. Let me review the links in WayneS' sig to ensure I'm posting the test data correctly and will then put some up.

thom

Thom,

No, you selected the correct forum. It just helps us help you if you provide information in a way that we can just cut-n-paste to try out different things. No need to apologize... unless it happens again;-):w00t:

OK, let's try this again<G>

Hello,

I work with emergency response vehicles and we track what they are doing by unit status codes. These records are in a single table in a SQL Server 2005 DB. We track between 300 to 400 units on any given day. I have a need to find what unit status they where in prior to being placed in USDP status. I will also need to be able to do counts of the previous statuses.

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#ThomT','U') IS NOT NULL

DROP TABLE ThomT

--===== Create the test table with

CREATE TABLE ThomT

(

Manpower_PK INT IDENTITY(1,1) PRIMARY KEY CLUSTERED

,Manpower_Record_TimeDATETIME

,Unit_IDVarChar(8)

,CodeVarChar(4)

,AddressVarChar(30)

)

--===== Setup any special required conditions especially where dates are concerned

--Insert Data into Test Table ThomT

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT ThomT ON

--===== Insert the test data into the test table

INSERT INTO ThomT

(Manpower_PK

,Manpower_Record_Time

,Unit_ID

,Code

,Address)

SELECT '6276765','Apr 22 2010 6:11AM','M01','USDP','7 LAKES' UNION ALL

SELECT '6276767','Apr 22 2010 6:12AM','M01','USER','7 LAKES' UNION ALL

SELECT '6276781','Apr 22 2010 6:18AM','M01','USAR','7 LAKES' UNION ALL

SELECT '6276811','Apr 22 2010 6:38AM','M01','OM','' UNION ALL

SELECT '6276812','Apr 22 2010 6:38AM','M01','USTR','D7 P1 1P2 P3 XA2' UNION ALL

SELECT '6276852','Apr 22 2010 6:49AM','M01','OM','' UNION ALL

SELECT '6276853','Apr 22 2010 6:49AM','M01','USTA','D7' UNION ALL

SELECT '6276861','Apr 22 2010 6:52AM','M12','USCP','' UNION ALL

SELECT '6276862','Apr 22 2010 6:52AM','M12','CM','' UNION ALL

SELECT '6276863','Apr 22 2010 6:52AM','M12','O1','' UNION ALL

SELECT '6276864','Apr 22 2010 6:52AM','M12','O2','' UNION ALL

SELECT '6276872','Apr 22 2010 6:54AM','M12','CM','' UNION ALL

SELECT '6276884','Apr 22 2010 6:56AM','M01','USOL','D7' UNION ALL

SELECT '6276895','Apr 22 2010 6:58AM','M01','USAV','' UNION ALL

SELECT '6276898','Apr 22 2010 6:58AM','M01','USRQ','D7>>>M01' UNION ALL

SELECT '6276932','Apr 22 2010 7:06AM','M02','CM','' UNION ALL

SELECT '6276935','Apr 22 2010 7:07AM','M02','O1','' UNION ALL

SELECT '6276936','Apr 22 2010 7:07AM','M02','O2','' UNION ALL

SELECT '6276937','Apr 22 2010 7:07AM','M02','CM','' UNION ALL

SELECT '6276958','Apr 22 2010 7:12AM','M12','USAQ','IN STATION' UNION ALL

SELECT '6277002','Apr 22 2010 7:21AM','M01','CM','' UNION ALL

SELECT '6277005','Apr 22 2010 7:21AM','M01','USAQ','IN STATION' UNION ALL

SELECT '6277006','Apr 22 2010 7:21AM','M01','O1','' UNION ALL

SELECT '6277007','Apr 22 2010 7:21AM','M01','O2','' UNION ALL

SELECT '6277008','Apr 22 2010 7:21AM','M01','CM','' UNION ALL

SELECT '6277035','Apr 22 2010 7:27AM','M01','USDP','COMPASS PT' UNION ALL

SELECT '6277036','Apr 22 2010 7:28AM','M01','USER','COMPASS PT' UNION ALL

SELECT '6277068','Apr 22 2010 7:34AM','M01','USAR','COMPASS PT' UNION ALL

SELECT '6277121','Apr 22 2010 7:49AM','M01','OM','' UNION ALL

SELECT '6277122','Apr 22 2010 7:49AM','M01','USTR','D7 P1 1P2 P3 XA2A' UNION ALL

SELECT '6277198','Apr 22 2010 8:02AM','M01','OM','' UNION ALL

SELECT '6277199','Apr 22 2010 8:02AM','M01','USTA','D7' UNION ALL

SELECT '6277245','Apr 22 2010 8:10AM','M01','USOL','D7' UNION ALL

SELECT '6277246','Apr 22 2010 8:11AM','M01','USEC','' UNION ALL

SELECT '6277299','Apr 22 2010 8:22AM','M12','USDP','WATERWAY ESTATES' UNION ALL

SELECT '6277304','Apr 22 2010 8:23AM','M12','USER','WATERWAY ESTATES' UNION ALL

SELECT '6277331','Apr 22 2010 8:29AM','M01','USAV','' UNION ALL

SELECT '6277333','Apr 22 2010 8:30AM','M01','USRQ','D7>>M01' UNION ALL

SELECT '6277354','Apr 22 2010 8:36AM','M01','USLC','D7>>LOGISTICS' UNION ALL

SELECT '6277369','Apr 22 2010 8:42AM','M12','OM','' UNION ALL

SELECT '6277370','Apr 22 2010 8:42AM','M12','USTR','D4 P1 1P2 P3 XA2A' UNION ALL

SELECT '6277399','Apr 22 2010 8:51AM','M12','OM','' UNION ALL

SELECT '6277400','Apr 22 2010 8:51AM','M12','USTA','D4' UNION ALL

SELECT '6277435','Apr 22 2010 9:00AM','M01','USLC','LOGISTICS' UNION ALL

SELECT '6277489','Apr 22 2010 9:11AM','M12','USOL','D4' UNION ALL

SELECT '6277533','Apr 22 2010 9:19AM','M12','USAV','' UNION ALL

SELECT '6277542','Apr 22 2010 9:21AM','M01','USDP','BEACH VIEW' UNION ALL

SELECT '6277547','Apr 22 2010 9:22AM','M01','USAV','' UNION ALL

SELECT '6277564','Apr 22 2010 9:25AM','M01','USRQ','LOGISTICS>>M01' UNION ALL

SELECT '6277585','Apr 22 2010 9:30AM','M12','USRQ','D4 >> EMS12' UNION ALL

SELECT '6277621','Apr 22 2010 9:35AM','M12','USAQ','IN STATION' UNION ALL

SELECT '6277624','Apr 22 2010 9:36AM','M12','USAQ','IN STATION' UNION ALL

SELECT '6277633','Apr 22 2010 9:39AM','M01','USAQ','IN STATION' UNION ALL

SELECT '6277640','Apr 22 2010 9:42AM','M12','USDP','2316 VISCAYA PKWY' UNION ALL

SELECT '6277646','Apr 22 2010 9:43AM','M12','USER','2316 VISCAYA PKWY' UNION ALL

SELECT '6277659','Apr 22 2010 9:47AM','M12','USAR','2316 VISCAYA PKWY' UNION ALL

SELECT '6277665','Apr 22 2010 9:49AM','M12','USAV','' UNION ALL

SELECT '6277666','Apr 22 2010 9:49AM','M12','D ','' UNION ALL

SELECT '6277668','Apr 22 2010 9:50AM','M12','USRQ','VISCAYA/23RD >> EMS1' UNION ALL

SELECT '6277694','Apr 22 2010 9:55AM','M12','USAQ','IN STATION' UNION ALL

SELECT '6277716','Apr 22 2010 10:02AM','M01','USDP','2337 LA SALLE AVE' UNION ALL

SELECT '6277723','Apr 22 2010 10:03AM','M01','USER','2337 LA SALLE AVE' UNION ALL

SELECT '6277746','Apr 22 2010 10:09AM','M01','USAV','' UNION ALL

SELECT '6277748','Apr 22 2010 10:09AM','M01','USRQ','RTN TO STN' UNION ALL

SELECT '6277753','Apr 22 2010 10:10AM','M01','USRQ','JASPER>>M01' UNION ALL

SELECT '6277759','Apr 22 2010 10:13AM','M01','USAQ','IN STATION' UNION ALL

SELECT '6277824','Apr 22 2010 10:29AM','M12','USSB','FMFD3' UNION ALL

SELECT '6277827','Apr 22 2010 10:31AM','M12','USES','FMFD 3' UNION ALL

SELECT '6277835','Apr 22 2010 10:38AM','M02','USDP','FL CANCER SPECIALIST' UNION ALL

SELECT '6277844','Apr 22 2010 10:39AM','M02','USER','FL CANCER SPECIALIST' UNION ALL

SELECT '6277883','Apr 22 2010 10:46AM','M02','USAR','FL CANCER SPECIALIST' UNION ALL

SELECT '6277894','Apr 22 2010 10:48AM','M12','USAS','FMFD3' UNION ALL

SELECT '6277918','Apr 22 2010 10:57AM','M02','OM','' UNION ALL

SELECT '6277919','Apr 22 2010 10:57AM','M02','USTR','D7 P1 1P2 P3 XA2' UNION ALL

SELECT '6277938','Apr 22 2010 11:03AM','M02','LO','D7 1P2 A2' UNION ALL

SELECT '6277940','Apr 22 2010 11:03AM','M02','CM','' UNION ALL

SELECT '6277963','Apr 22 2010 11:11AM','M01','USLC','10-8 FOR A9' UNION ALL

SELECT '6277964','Apr 22 2010 11:11AM','M02','OM','' UNION ALL

SELECT '6277965','Apr 22 2010 11:11AM','M02','USTA','D7' UNION ALL

SELECT '6277973','Apr 22 2010 11:13AM','M01','USLC','WENDYS' UNION ALL

SELECT '6277990','Apr 22 2010 11:14AM','M12','USRQ','RTN TO STN' UNION ALL

SELECT '6277991','Apr 22 2010 11:15AM','M12','USAV','' UNION ALL

SELECT '6277993','Apr 22 2010 11:16AM','M12','USSB','FMFD3' UNION ALL

SELECT '6277994','Apr 22 2010 11:16AM','M12','USES','FMFD3' UNION ALL

SELECT '6277995','Apr 22 2010 11:16AM','M12','USAS','FMFD3' UNION ALL

SELECT '6278059','Apr 22 2010 11:35AM','M01','USRQ','WENDYS>>M01' UNION ALL

SELECT '6278063','Apr 22 2010 11:36AM','M01','USAQ','IN STATION' UNION ALL

SELECT '6278078','Apr 22 2010 11:41AM','M02','USOL','D7' UNION ALL

SELECT '6278079','Apr 22 2010 11:41AM','M02','USAV','' UNION ALL

SELECT '6278080','Apr 22 2010 11:41AM','M02','USLC','D7' UNION ALL

SELECT '6278083','Apr 22 2010 11:44AM','M12','USDP','WALMART/GROCERY ENTR' UNION ALL

SELECT '6278085','Apr 22 2010 11:45AM','M01','USDP','WALMART/GROCERY ENTR' UNION ALL

SELECT '6278086','Apr 22 2010 11:45AM','M01','USAV','' UNION ALL

SELECT '6278093','Apr 22 2010 11:45AM','M12','USER','WALMART/GROCERY ENTR' UNION ALL

SELECT '6278095','Apr 22 2010 11:45AM','M01','USAQ','IN STATION' UNION ALL

SELECT '6278120','Apr 22 2010 11:51AM','M02','USSB','FMFD3' UNION ALL

SELECT '6278136','Apr 22 2010 11:53AM','M02','USAS','D7>>FMFD3' UNION ALL

SELECT '6278147','Apr 22 2010 11:53AM','M12','USAR','WALMART/GROCERY ENTR' UNION ALL

SELECT '6278157','Apr 22 2010 11:54AM','M12','USAV','' UNION ALL

SELECT '6278172','Apr 22 2010 11:56AM','M12','USRQ','6MILE/COLONIAL >> EM'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT ThomT ON

What I am looking for is something like this:

I didn't mention in the first post I would only be looking at Prior_Codes that started with 'US', figuring that I could apply filters after getting the logic down

Thank you

Thom

edit: still trying to get the hang of the tags in this forum

Thomw (4/23/2010)

Does this do what you want?

select [Datetime] = Manpower_Record_Time,

Unit_ID,

Code_DP = t.Code,

PriorCode = l.Code,

PriorAddress = l.Address

from ThomT as t

cross apply (

select top 1 t2.CODE, t2.Address

from ThomT as t2

where t2.UNIT_ID=t.UNIT_ID

and t2.Manpower_PK<t.Manpower_PK

and t2.Code LIKE 'US%'

order by t2.Manpower_PK

) AS l

where t.CODE = 'USDP'

This is what I get when I run the query against the test data

2010-04-22 07:27:00.000M01USDPUSDP7 LAKES

2010-04-22 08:22:00.000M12USDPUSCP

2010-04-22 09:21:00.000M01USDPUSDP7 LAKES

2010-04-22 09:42:00.000M12USDPUSCP

2010-04-22 10:02:00.000M01USDPUSDP7 LAKES

2010-04-22 11:44:00.000M12USDPUSCP

2010-04-22 11:45:00.000M01USDPUSDP7 LAKES

It seems to be only referencing back to a single record on every other line (PK=6276861)

I am trying to get both the USDP and Prior Code on the same line along with the Address field of the Prior_Code record which would allow me to do counts on both. More like this:

Datetime Unit_ID Code_DP Prior_Code Prior_Address

Apr 22 2010 6:11AMM01 USDP NULL Null

Apr 22 2010 7:27AMM01 USDP USAQ IN STATION

Apr 22 2010 8:22AMM12 USDP USAQ IN STATION

Apr 22 2010 9:21AMM01 USDP USLC LOGSTICS

Apr 22 2010 9:42AMM12 USDP USAQ IN STATION

Apr 22 2010 10:02AMM01 USDP USAQ IN STATION

Apr 22 2010 10:38AMM02 USDP NULL NULL

Apr 22 2010 11:44AMM12 USDP USAS FMFD3

Apr 22 2010 11:45AMM01 USDP USAQ IN STATION

I hand collated this from the test data as the desired result. I could do with out the USDP code dislay as I only put that in there for my own paranoia on data.

Hopefully I'll be able to take a break shortly and do some reading up on Cross Apply. I am not familiar with that at all.

The reason for this is we just put a new process in place for positioning ambulances to decrease our response times. I need to see if we are sending units (USDP) on calls more often from the stand by position then from station. The only way I can determine if a unit is on stand by is by it's status code.

Thom

Well didn't really progress any closer to the solution then what you have already given me, but I did learn some about Apply and table Variables today. This is the latest version of what I've been working on:

-- User Variable Table to hold USDP records (9)

Declare @ThomTest Table

(

fn_IDint

,fn_TimeDatetime

,fn_UnitVarchar(8)

,fn_CodeVarChar(4)

,fn_AddressVarchar(30)

)

Insert Into @ThomTest (fn_ID,fn_time,fn_unit,fn_code,fn_address)

select

Manpower_PK

,Manpower_Record_Time

,Unit_ID

,Code

,Address

from

ThomT

where Code='USDP'

-- End of User Variable Table

select

t3.Unit_ID

,t1.fn_code

,t3.Code

,t3.Address

,t1.fn_id

,t3.Manpower_pk

from

@ThomTest as t1

Cross Apply

(

select

top 1 code

,unit_id

,address

,manpower_PK

from ThomT as t2

where

t1.fn_unit=t2.unit_id

and

t1.fn_id>t2.manpower_pk

and

t2.Code like 'US%'

) as t3

My thought for using the Table Variable was to only bring over the rows I wanted results on. It finds the nine rows ok.

Where I ended up was with this return.

Unit_ID fn_code Code Address fn_id Manpower_pk

-------- ------- ---- ------------------------------ ----------- -----------

M01 USDP USDP 7 LAKES 6277035 6276765

M12 USDP USCP 6277299 6276861

M01 USDP USDP 7 LAKES 6277542 6276765

M12 USDP USCP 6277640 6276861

M01 USDP USDP 7 LAKES 6277716 6276765

M12 USDP USCP 6278083 6276861

M01 USDP USDP 7 LAKES 6278085 6276765

(7 row(s) affected)

Not what I was looking for (see previous post)

Thank you for everyone's help. I'll keep plugging away at this.

Thom

Here you go...Wayne had it just about right, but missed the sort order reversal required...

select [Datetime] = Manpower_Record_Time,

Unit_ID,

Code_DP = t.Code,

PriorCode = l.Code,

PriorAddress = l.Address

from ThomT as t

outer apply (

select top 1 t2.CODE, t2.Address

from ThomT as t2

where t2.UNIT_ID=t.Unit_ID

and t2.Manpower_PK<t.Manpower_PK

and t2.Code LIKE 'US%'

order by t2.Manpower_PK desc

) AS l

where t.CODE = 'USDP'

Thank you Mr Magoo & Wayne Works like a charm.

I didn't realize that the sort order when pulling from 'two' tables made a difference. Is this because of the apply or an over all thing?

Is there a rep or ++ system for this forum? I want to make sure I credit you for the help.

Thom

The reason it makes a difference in this code is because we are looking for the first record prior to the USDP record, so we use a TOP 1 and ORDER BY the PK DESCENDING.

Without the DESCENDING, we were SELECTing the set of previous records and then filtering (TOP 1) down to the first record in that set rather than the last.