SQLTeam.com | Weblogs | Forums

Create journal entries for moving assets


#1

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.

Thank you


#2

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.


#3

JamesK, great point and I appreciate that advice.
Table 1 was created from scratch per the discussion with users who create journal entries.

I'll see if I can conjure up a query of fields that could be used to ultimately create a journal entry.


#4

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:

GLAcct	        AcctDate	Debit	Credit	 Store	 Description
500.800.250	06/10/15	0	33750	 101	 scanning equip
500.800.250	06/10/15	33750	0		
					
500.800.250	06/30/15	0	29250	 102	 scanning equip
500.800.200	06/30/15	33750	0		
500.800.300	06/30/15	0	4500		 Condition value adjustment

.

I really hope this makes sense. This is a pretty complicated task that I am trying to carry out.