SQLTeam.com | Weblogs | Forums

Query needs accounting entires created from it


#1

This is my query shown below:

WITH VALUE (ASSETNUM, VALUE) AS
(SELECT ASSETNUM,
CASE
WHEN a.cost1 = '0' THEN a.cost2
ELSE a.cost1
END AS value
from asset a),
FIRSTDATE(FIRSTMOVEDATE, ASSETNUM) AS
(select min(datemoved), assetnum
from assetmove
WHERE ASSETMOVE.TRANSTYPE = 'MOVED'
GROUP BY ASSETNUM)

select
CASE WHEN T.DATEMOVED = F.FIRSTMOVEDATE THEN '1' ELSE '2' END MOVECOUNT,
v.value AS VALUE,
asset.acct 'DEBITACCT', asset.reclaim 'CREDITACCT', '100' 'DIFFACCT',

   CONVERT(VARCHAR,DATEMOVED, 101) AS 'ACCT DATE',
   CASE WHEN T.DATEMOVED = F.FIRSTMOVEDATE THEN CONVERT(INT,v.VALUE*.75) ELSE CONVERT(INT,v.VALUE*.75) END AS DEBIT,
   CASE WHEN T.DATEMOVED = F.FIRSTMOVEDATE THEN CONVERT(INT,v.VALUE*.75) ELSE CONVERT(INT,v.VALUE*.65) END AS CREDIT,
   CASE WHEN T.DATEMOVED = F.FIRSTMOVEDATE THEN '0' ELSE CONVERT(INT,V.VALUE*.10) END AS DIFFAMT,  

FROM assetmove t
left join value v on t.assetnum = v.assetnum
LEFT JOIN FIRSTDATE F ON T.ASSETNUM = F.ASSETNUM
LEFT JOIN dbo.asset ON t.assetnum = dbo.asset.assetnum

WHERE t.assetnum = '145827 ' AND t.transtype = 'moved'

The data set results of running the query are shown below:

MOVECOUNT	DEBITACCT	CREDITACCT	DIFFACCT	ACCT DATE	DEBIT	CREDIT	DIFFAMT
1		100		200		300		07/01/15	35000	35000	0
2		100	        200		300		07/01/15	35000	30333	4667

Therefore, I would like for dataset to become the outcome as shown below

Acct	Acct Date	Debit	Credit
200	07/01/15	0	35000
100	07/01/15	35000	0
200	07/01/15	0	30,333
100	07/01/15	35000	0
300	07/01/15	0	4667

Any help would be greatly appreciated as I cannot figure out what to do in order to get those type of entries created.
Thank you


#2

It seems to have not posted my query correctly, so hopefully this is easier to read.

WITH VALUE (ASSETNUM, VALUE) AS
(SELECT ASSETNUM,
CASE
WHEN a.cost1 = '0' THEN a.cost2
ELSE a.cost1
END AS value
from asset a),
FIRSTDATE(FIRSTMOVEDATE, ASSETNUM) AS
(select min(datemoved), assetnum
from assetmove
WHERE ASSETMOVE.TRANSTYPE = 'MOVED'
GROUP BY ASSETNUM)

select
CASE WHEN T.DATEMOVED = F.FIRSTMOVEDATE THEN '1' ELSE '2' END MOVECOUNT,
v.value AS VALUE,
asset.acct 'DEBITACCT', asset.reclaim 'CREDITACCT', '100' 'DIFFACCT',

CONVERT(VARCHAR,DATEMOVED, 101) AS 'ACCT DATE',
CASE WHEN T.DATEMOVED = F.FIRSTMOVEDATE THEN CONVERT(INT,v.ASSETVALUE*.75) ELSE CONVERT(INT,v.ASSETVALUE*.75) END AS DEBIT,
CASE WHEN T.DATEMOVED = F.FIRSTMOVEDATE THEN CONVERT(INT,v.ASSETVALUE*.75) ELSE CONVERT(INT,v.ASSETVALUE*.65) END AS CREDIT,
CASE WHEN T.DATEMOVED = F.FIRSTMOVEDATE THEN '0' ELSE CONVERT(INT,V.ASSETVALUE*.10) END AS DIFFAMT,

FROM assetmove t
left join value v on t.assetnum = v.assetnum
LEFT JOIN FIRSTDATE F ON T.ASSETNUM = F.ASSETNUM
LEFT JOIN dbo.asset ON t.assetnum = dbo.asset.assetnum

WHERE t.assetnum = '145827 ' AND t.transtype = 'moved'


#3

Please provide table descriptions (of both tables - asset and assetmove), sample data (from both tables) and expected output (from your provided sample data)