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