BAQ sum orders added by week

Kinetic ERP
1

I am attempting to create a simple BAQ that returns the sum of all orders by week and year. Currently it returns two columns with the week and order was added and it’s respective total value, see below. I was unable to find the DocOrderAmt field and had to create it as a calculated field. How do I group by week number and year?

image
image244×719 46.4 KB

select
(DATEPART( wk, OrderHed.OrderDate)) as [Calculated_WeekNum],
((OrderHed.DocOrderAmt)) as [Calculated_OrderTotal]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
and OrderHed.OrderNum = OrderDtl.OrderNum
group by (DATEPART( wk, OrderHed.OrderDate)),
((OrderHed.DocOrderAmt))

2

You need to SUM your OrderHed.DocOrderAmt (i.e. SUM(OrderHed.DocOrderAmt)). Grouping only works with aggregate functions.

SELECT
	DATEPART(wk, OrderHed.OrderDate) as [Calculated_WeekNum],
	SUM(OrderHed.DocOrderAmt) as [Calculated_OrderTotal]
FROM
	Erp.OrderHed as OrderHed 
	INNER JOIN Erp.OrderDtl as OrderDtl ON
		OrderHed.Company = OrderDtl.Company AND OrderHed.OrderNum = OrderDtl.OrderNum
GROUP BY 
	DATEPART( wk, OrderHed.OrderDate)
3

If you want all those periods in one query you will probably have to use SUM OVER

4

@abill9 , @abill9 's query is correct, but I will add that you probably want to add the year in there are well and group by the week num as well as the year. Otherwise you will get all of week# 1 regardless of which year it is.

Secondly, Blurring out your all of your results while trying to ask for help makes it pretty hard for people to help you. It’s pretty hard for someone to tell what results you are getting other than it’s 2 columns.

5

This worked! Thanks

6

Got it, thanks