Select Historical Vehicle Infor 1

[FunkyBunch]

Hello All,

I am currently using SQL 2005. The select statement I am trying to make will pull in the equipment id, a string literal describing the transfer.(Addition, Deletion, Transfer To, Transfer From), and the unit number the vehicle was transferred from/to.

This is the SQL i currently have:
*********************************************************
select access_equip_Fw,
(case
when (date_mounted_Equipment_removed_fw is null) and (access_equip_Fw in (select access_equip_Fw
from user_Defined20_Fw group by access_equip_fw having count(access_equip_fw)>1)) then 'Transfer From'

when Date_mounted_equipment_removed_fw is not null and (access_equip_Fw in (select access_equip_Fw
from user_Defined20_Fw group by access_equip_fw having count(access_equip_fw)>1)) then 'Transfer To'

when Date_Mounted_equipment_removed_fw is null and (access_equip_Fw in (select access_equip_Fw
from user_Defined20_Fw group by access_equip_fw having count(access_equip_fw)=1)) then 'Addition'
When (Archive_status_fw = 'A') then 'Deletion'
Else ''
end) AS "ADD/DELETE/TRANSFER"
from user_Defined20_fw
group by date_mounted_equipment_removed_fw, access_equip_Fw, archive_status_fw
**********************************************************
This is the data it returns:
100 Transfer From
1000 Addition
1002 Addition
1003 Addition
1004 Addition
1005 Addition

What i want to do is display a third column that tells where the piece of was transferred from/to. This is the select statment i have thus far:
******************************************************
(select vehicle_id_Fw
from user_defined20_fw
Where date_mounted_equipment_removed_Fw is not null and date_mounted_equipment_removed_fw <= (Select max(date_mounted_equipment_added_fw)

from user_Defined20_Fw
where access_equip_Fw in (select access_equip_Fw
from user_Defined20_Fw
group by access_equip_fw
having count(access_equip_fw)>1)))
*********************************************************
The data this select returns is the three units that equipment has been moved from:
10
8011
1189

Is there anyway (using the vehicle_id, a record #, equipment id, date removed, or date added.) that i can select the exact vehicle that a specific piece of equipment came from.

The Table i am using is a history table and holds the fields described above and creates a new record for every change. FX:

Equiment ID Vehicle ID Removed Added Record
5 1 09/10/07 01/12/05 1
5 5 NULL 09/10/07 2

I hope i provided enough information to recieve help, thanks everyone!

 

[FunkyBunch]

DOes anyone have any ideas? Or is my post a little bit to confusing? Or Large?

 

[bborissov]

Can you post some simple data and what you want from it?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[FunkyBunch]

Here is some sample data:

In The Database:
Column Order: Vehicle ID, Equip ID, Date Added, Date Removed, Record Number

1 20 1/1/2007 1/25/2007 1
2 20 1/25/2007 NULL 2
1 25 5/16/2006 NULL 3
4 14 9/7/2005 5/20/2006 4
8 14 5/20/2006 NULL 5
7 88 7/25/2007 NULL 6
7 98 7/25/2007 NULL 7
7 44 7/25/2007 8/21/2007 8
4 11 6/22/2003 NULL 9
10 44 8/21/2007 NULL 10


What I want to see:
Column Order: Equip ID, Transfer/Add/Delete, To/From(vehicle ID)

20 Transfer From 1
20 Transfer To 2
25 Addition
11 Addition
44 Transfer From 7
44 Transfer To 10

I hope this helps!!!

 

[bborissov]

[COLOR=blue]DECLARE[/color] @TEMP [COLOR=blue]Table[/color] (Vehicle_ID [COLOR=blue]Int[/color], Equip_ID  [COLOR=blue]Int[/color], Date_Added [COLOR=#FF00FF]datetime[/color], Date_Removed [COLOR=#FF00FF]datetime[/color] NULL,
                     Record_Number [COLOR=blue]int[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](   1, 20,[COLOR=red]'1/1/2007'[/color],[COLOR=red]'1/25/2007'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](   2, 20,[COLOR=red]'1/25/2007'[/color],NULL,2)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](   1, 25,[COLOR=red]'5/16/2006'[/color],NULL,3)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](   4, 14,[COLOR=red]'9/7/2005'[/color],[COLOR=red]'5/20/2006'[/color],4)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](   8, 14,[COLOR=red]'5/20/2006'[/color],NULL,5)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](   7, 88,[COLOR=red]'7/25/2007'[/color],NULL,6)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](   7, 98,[COLOR=red]'7/25/2007'[/color],NULL,7)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](   7, 44,[COLOR=red]'7/25/2007'[/color],[COLOR=red]'8/21/2007'[/color],8)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](   4, 11,[COLOR=red]'6/22/2003'[/color],NULL,9)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](  10, 44,[COLOR=red]'8/21/2007'[/color],NULL,10)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](  34, 12,[COLOR=red]'6/22/2003'[/color],[COLOR=red]'8/21/2007'[/color],0)

  
[COLOR=blue]SELECT[/color] Tmp.Equip_ID,
      [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] (Tmp.Date_Removed [COLOR=blue]IS[/color] NOT NULL AND
                Tmp1.Date_Added [COLOR=blue]IS[/color] NOT NULL)  OR
                Tmp2.Date_Added [COLOR=blue]IS[/color] NOT NULL
                 [COLOR=blue]THEN[/color] [COLOR=red]'Transfer'[/color]

            [COLOR=blue]WHEN[/color] Tmp.Date_Removed [COLOR=blue]IS[/color] NOT NULL AND
                 Tmp1.Date_Added [COLOR=blue]IS[/color] NULL
                 [COLOR=blue]THEN[/color] [COLOR=red]'Delete'[/color]

       [COLOR=blue]ELSE[/color] [COLOR=red]'Addition'[/color] [COLOR=blue]END[/color] [COLOR=blue]AS[/color] [Transfer/[COLOR=blue]Add[/color]/[COLOR=blue]Delete[/color]],

       [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] Tmp.Date_Removed [COLOR=blue]IS[/color] NOT NULL AND
                 Tmp1.Date_Added [COLOR=blue]IS[/color] NOT NULL
                 [COLOR=blue]THEN[/color] [COLOR=red]'From '[/color]+[COLOR=#FF00FF]CAST[/color](Tmp.Record_Number [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](20))

            [COLOR=blue]WHEN[/color] Tmp2.Date_Removed [COLOR=blue]IS[/color] NOT NULL
                 [COLOR=blue]THEN[/color] [COLOR=red]'To '[/color]+[COLOR=#FF00FF]CAST[/color](Tmp.Record_Number [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](20))
      [COLOR=blue]ELSE[/color] [COLOR=red]''[/color] [COLOR=blue]END[/color] [COLOR=blue]AS[/color] [[COLOR=blue]To[/color]/[COLOR=blue]From[/color]]
[COLOR=blue]FROM[/color] @Temp Tmp
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] @Temp Tmp1 [COLOR=blue]ON[/color] Tmp.Equip_ID = Tmp1.Equip_ID AND
                        Tmp.Date_Removed = Tmp1.Date_Added
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] @Temp Tmp2 [COLOR=blue]ON[/color] Tmp.Equip_ID   = Tmp2.Equip_ID AND
                        Tmp.Date_Added = Tmp2.Date_Removed
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] Tmp.Equip_ID

Is that what you want?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[FunkyBunch]

Thank You Borissov, that looks like it will work. I will not have time to test it today but i will keep you posted on my progress.

Thank you so much!!!!