|
1
|
SELECT N'3 - 5 năm' USE_DATE_KT,ROUND((ISNULL(SUM(VL.BUY_PRICE),0) -ISNULL(SUM(AM.AMORTIZED_AMT),0))/1000000000,2) BUY_PRICE,'2023' AS [YEAR] FROM ASS_MASTER A
|
|
2
|
LEFT JOIN
|
|
3
|
(
|
|
4
|
SELECT X.ASSET_ID,ISNULL(SUM(CASE X.CRDR WHEN 'C' THEN X.ASSET_AMT WHEN 'D' THEN -X.ASSET_AMT END),0) AS BUY_PRICE
|
|
5
|
FROM ASS_VALUES X
|
|
6
|
INNER JOIN ASS_MASTER Y ON X.ASSET_ID = Y.ASSET_ID
|
|
7
|
WHERE --X.CREATE_DT >= Y.CREATE_DT
|
|
8
|
(X.CREATE_DT <= CONVERT(DATE,'31/03/2023',103) OR '31/03/2023' IS NULL OR '31/03/2023' = '')
|
|
9
|
GROUP BY X.ASSET_ID
|
|
10
|
) VL ON A.ASSET_ID = VL.ASSET_ID
|
|
11
|
LEFT JOIN
|
|
12
|
(
|
|
13
|
SELECT X.ASSET_ID,ISNULL(SUM(CASE X.CRDR WHEN 'C' THEN X.AMORT_AMT WHEN 'D' THEN -X.AMORT_AMT END),0) AS AMORTIZED_AMT
|
|
14
|
FROM ASS_AMORT_DT X
|
|
15
|
INNER JOIN ASS_MASTER Y ON X.ASSET_ID = Y.ASSET_ID
|
|
16
|
WHERE --X.CREATE_DT >= Y.CREATE_DT
|
|
17
|
(X.AMORT_DT <= CONVERT(DATE,'31/03/2023',103) OR '31/03/2023' IS NULL OR '31/03/2023' = '')
|
|
18
|
GROUP BY X.ASSET_ID
|
|
19
|
) AM ON A.ASSET_ID = AM.ASSET_ID
|
|
20
|
WHERE 1=1 AND A.RECORD_STATUS = '1' AND A.AUTH_STATUS = 'A' AND A.ENTRY_BOOKED = 'Y'
|
|
21
|
--AND (CONVERT(date,A.USE_DATE_KT,103) >= CONVERT(date,@p_FROM_DATE,103) OR @p_FROM_DATE IS NULL OR @p_FROM_DATE ='')
|
|
22
|
AND ((A.[TYPE_ID] = 'TSCD' AND CONVERT(date,A.LIQUIDATION_DT,103) >= CONVERT(date,'31/03/2023',103) OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT ='')
|
|
23
|
OR (A.[TYPE_ID] = 'CCLD' AND A.AMORT_STATUS <> 'VNM' AND CONVERT(date,A.LIQUIDATION_DT,103) > CONVERT(date,'31/03/2023',103) OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT =''))
|
|
24
|
AND (CONVERT(date,A.USE_DATE_KT,103) <= CONVERT(date,'31/03/2023',103) OR '31/03/2023' IS NULL OR '31/03/2023' ='')
|
|
25
|
AND (2023-YEAR(USE_DATE_KT) > 3)
|
|
26
|
AND (2023-YEAR(USE_DATE_KT) <= 5)
|
|
27
|
AND (A.[TYPE_ID]='CCLD')
|