Not able to get the result with the function Convert() and Datefromparts()

Kinetic ERP
2

Welcome to Epiusers @Sreejith_J

I wanted to point you to a couple of posts in the FAQ category that will help you get assistance and contribute

This one is about formatting code:

Searching:

Now to look into your issue. I managed to do a bit of refactoring to get it to work.


SELECT
    [partTran].[partNum] AS [partTran_partNum],
    [partTran].[tranType] AS [partTran_tranType],
    YEAR(partTran.tranDate) AS [calculated_year],
    SUM(partTran.tranQty) AS [calculated_transQty],
    CASE
        WHEN YEAR(partTran.tranDate) = 2024 THEN SUM(tranQty) / 12.0
        ELSE SUM(tranQty) / ROUND(DATEDIFF(DAY, '2025-1-1', GETDATE()) / 30.0, 2)
    END AS [calculated_avg_tranQty]
FROM
    ERP.part AS [part]
INNER JOIN
    ERP.partTran AS [partTran] ON part.company = partTran.company
    AND part.partNum = partTran.partNum
WHERE
    partTran.tranDate > DATEFROMPARTS(YEAR(DATEADD(YEAR, -2, GETDATE())), 12, 31)
    AND partTran.tranClass = 'I'
    AND part.classId NOT IN ('FG''S', 'FOC', '')
    AND part.typeCode = 'P'
    AND part.nonStock = 0
GROUP BY
    [partTran].[partNum],
    [partTran].[tranType],
    YEAR(partTran.tranDate)

At least it now runs…

Oh and one more useful post to help you on your way

1 Like