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

Kinetic ERP
1

Hi
I tried both the above functions in the below BAQ in the both the cases I get the error message
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 <> ‘FG’s’
and Part.ClassID <> ‘FOC’
and Part.ClassID <> ‘’
and Part.TypeCode = ‘P’
and Part.NonStock = 0 )
group by
[PartTran].[PartNum],
[PartTran].[TranType],
(Year(PartTran.TranDate))
Instead of Datefromparts(year(dateadd(year,-2,getdate())),12,31) if I use
Datefromparts(2023,12,31) or Convert(date,‘20231231’,112) I get the correct result.
Could somebody please help me to know where the mistake is happening.

Thanks for your suggestions

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