求帮做sql的一个题

Question

 

Answer 1

yearmonth 那一列的所有值改成标准日期格式为'yyyy-mm-dd'这样的格式(例如2020-04-01)

SELECT c.dueDate
    , CASE 
        WHEN c.baseAmount - d.baseAmount IS NULL THEN c.baseAmount
        ELSE c.baseAmount - d.baseAmount
    END AS 同期增长值
    , CASE 
        WHEN (c.baseAmount - d.baseAmount) / c.baseAmount IS NULL THEN 1
        ELSE (c.baseAmount - d.baseAmount) / c.baseAmount
    END AS 同比
FROM CumulativeCase c
    LEFT JOIN (
        SELECT a.baseAmount AS baseAmount, b.dueDate AS dueDate
        FROM CumulativeCase a
            JOIN CumulativeCase b ON LEFT(convert(nvarchar(25), a.dueDate, 120), 7) = LEFT(convert(nvarchar(25), dateadd(year, -1, b.dueDate), 120), 7)
    ) d
    ON LEFT(convert(nvarchar(25), c.dueDate, 120), 7) = LEFT(convert(nvarchar(25), d.dueDate, 120), 7)
ORDER BY c.dueDate
    
--问题2(环比)
SELECT c.dueDate, c.baseAmount
    , CASE 
        WHEN (c.baseAmount - d.baseAmount) / c.baseAmount IS NULL THEN 1
        ELSE (c.baseAmount - d.baseAmount) / c.baseAmount
    END AS 环比
FROM CumulativeCase c
    LEFT JOIN (
        SELECT a.baseAmount AS baseAmount, b.dueDate AS dueDate
        FROM CumulativeCase a
            JOIN CumulativeCase b ON LEFT(convert(nvarchar(25), a.dueDate, 120), 7) = LEFT(convert(nvarchar(25), dateadd(month, -1, b.dueDate), 120), 7)
    ) d
    ON LEFT(convert(nvarchar(25), c.dueDate, 120), 7) = LEFT(convert(nvarchar(25), d.dueDate, 120), 7)
ORDER BY c.dueDate
    

回答不易 望采纳