SQL - How can I combine 3 similar queries into one?-CodePudding

The following 3 queries differ only in the WHERE clauses with different date ranges.

I'd like to know if I can get the same data with one query, or some other way to improve performance.

left join (
    select
        mi.acIdent,
        m.acissuer,
        sum(mi.anQty) q
    from the_moveitem mi
        left join the_move m on mi.acKey = m.acKey
    where m.adDate between getdate() - (365 * 1) and getdate() - (365 * 1 - @days)  -- <--- THIS PART
    group by
        mi.acident,
        m.acIssuer
) soldNextDaysOneYearAgo
    on soldNextDaysOneYearAgo.acIdent = s.acIdent and soldNextDaysOneYearAgo.acIssuer = s.acWarehouse

left join (
    select
        mi.acIdent,
        m.acissuer,
        sum(mi.anQty) q
    from the_moveitem mi
        left join the_move m on mi.acKey = m.acKey
    where m.adDate between getdate() - (365 * 2) and getdate() - (365 * 2 - @days)  -- <--- THIS PART
    group by
        mi.acident,
        m.acIssuer
) soldNextDaysTwoYearsAgo
    on soldNextDaysTwoYearsAgo.acIdent = s.acIdent and soldNextDaysTwoYearsAgo.acIssuer = s.acWarehouse

left join (
    select
        mi.acIdent,
        m.acissuer,
        sum(mi.anQty) q
    from the_moveitem mi
        left join the_move m on mi.acKey = m.acKey
    where m.adDate between getdate() - (365 * 3) and getdate() - (365 * 3 - @days)  -- <--- THIS PART
    group by
        mi.acident,
        m.acIssuer
) soldNextDaysThreeYearsAgo
    on soldNextDaysThreeYearsAgo.acIdent = s.acIdent and soldNextDaysThreeYearsAgo.acIssuer = s.acWarehouse

CodePudding user response：

You can make a conditional Sum

SELECT *
FROM tab1
left join (
    select
        mi.acIdent,
        m.acissuer,
        sum(CASE WHEN  m.adDate between getdate() - (365 * 1) and getdate() - (365 * 1 - @days) THEN mi.anQty ELSE 0 END) q1
        ,sum(CASE WHEN  m.adDate between getdate() - (365 * 2) and getdate() - (365 * 2 - @days) THEN mi.anQty ELSE 0 END) q2
        ,sum(CASE WHEN  m.adDate between getdate() - (365 * 3) and getdate() - (365 * 3 - @days) THEN mi.anQty ELSE 0 END) q3
    from the_moveitem mi
        left join the_move m on mi.acKey = m.acKey

    group by
        mi.acident,
        m.acIssuer
) soldNextDaysOneYearAgo
    on soldNextDaysOneYearAgo.acIdent = s.acIdent and soldNextDaysOneYearAgo.acIssuer = s.acWarehouse