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
TABLE 1
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
(3) Debit
(4) Credit
(5) Store #
(6) Description
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.
To write a query one would need to know how the source data is stored. Perhaps you have a assets table, an asset_movement table and so on. It would be useful if you posted the DDL (scripts to create those tables) and scripts to populate some sample data into those tables.
Here is an updated version of my original post.
I have provided more information as I have been out of the country for several months.
Here is the query that I have produced. As you can see I will focus on only records that will have a type = moved. This ensures that the records that are moved from store to store are being accounted for only. There are 2 tables involved: asset and assetmove.
SELECT
dbo.asset.assetid,
dbo.asset.status,
dbo.asset.condition,
dbo.asset.itemid,
dbo.assetmove.fromglaccount AS FromGLAcct,
dbo.assetmove.toglaccount AS ToGLAcct,
dbo.assetmove.acctdate,
dbo.asset.cost,
dbo.assetmove.fromstore,
dbo.assetmove.tostore,
dbo.asset.description AS assetdescription,
dbo.assetmove.type
FROM dbo.assetmove INNER JOIN
dbo.asset ON dbo.assetmove.assetid = dbo.asset.assetid
where type = 'MOVED
This is the data results of running the above query:
Within the data results, you will see there is no debit or credit columns available. I could have created those, but chose not too. I am not sure how much of difference that makes since they will need to be present in the end results
Data Results
assetid status condition itemid fromGLacct ToGLacct AcctDate cost fromstore tostore description type
1 active A 24 NULL 500.800.250 06/10/15 45000 NULL 100 scanning equip created
1 active B 24 500.800.250 500.800.200 06/30/15 45000 100 101 scanning equip moved
1 active C 24 500.800.200 500.800.300 06/30/15 45000 101 102 condition value adj moved
And I need the output to look like this which would be the journal entry itself: