The objective is to create a SQL query that will result in what is shown in Table 1 (attached).
In other words, I need to have a query created to get those results and also to take into consideration the 3rd, 4th, etc moves each time an asset is rotated between stores.
Scenario: when a B condition asset is moved to store A and later transferred to store B, the accounting for that 2nd move should be a credit at 65% of $45,000 to store A and a charge to store B at 75% of the same FMV. The 10% differential will need to be coded to 500.800.300. This cycle continues on and on as assets are moved from store to store.
Note: 1st 2 line entries are just the value based on 100% and I wouldn't consider it the "first move"
table used: dbo.assetmove
GLAcct AcctDate Debit Credit Store Asset Description 500.800.250 06/01/15 45000 0 A scanning equip 500.800.200 06/01/15 0 45000 500.800.250 06/10/15 0 33750 B scanning equip 500.800.250 06/10/15 33750 0 500.800.250 06/18/15 0 29250 C scanning equip 500.800.200 06/18/15 33750 0 500.800.300 06/18/15 0 4500 Condition value adjustment
The columns to be included are
(1) GL Acct
(2) Acct Date
(5) Store #
I think seeing an example will allow me to understand what I am doing wrong. I didn't post my original query because it would confuse people as to what I wrote.