group by records based on a date field

(0) Share

Report

Posted on by deepak31

I need to create a excel report from inventtrans table,, i nee to get the record as month wise group by using datePhysical field,againt each item

SELECT ITEMID, DATEPART(Year,DATEPHYSICAL) as year,DATEPART(MONTH,DATEPHYSICAL) as month,SUM(QTY)  FROM INVENTTRANS
				WHERE INVENTTRANS.ITEMID ='101'
				group by INVENTTRANS.ITEMID, DATEPART(Year,DATEPHYSICAL),DATEPART(MONTH,DATEPHYSICAL)

How can i achieve this in x (D365FO)

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Verified answer

Martin Dráb 240,312 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!

Create a view with computed columns, where you'll extract the year and month from DatePhysical.

Then use a select statement to get data from the view and use those computed columns in 'group by' clause.

Was this reply helpful? Yes No
Suggested answer

Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report
Copy link

Link copied!

You can create Static or dynamic query which will solve this.

Was this reply helpful? Yes No
deepak31 45 on at

Like (0)

Report
Copy link

Link copied!
[quote
Create a view with computed columns, where you'll extract the year and month from DatePhysical.

Then use a select statement to get data from the view and use those computed columns in 'group by' clause.
[/quote
Here i need to find the sum of Quantity and sum of costvalue,i tried with this select statemnt ,but those values are in correct,can you pls help me to correct this
while select ItemId,DatePhysical,sum(Qty),sum(CostAmountPosted),sum(CostAmountAdjustment) from inventTrans group by inventTrans.ItemId,inventview.MonthInDate,inventview.YearInDate order by inventview.YearInDate desc join inventview where inventTrans.ItemId == inventview.ItemId

Was this reply helpful? Yes No
Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report
Copy link

Link copied!

Can you use the join in SSMS also? Because if you use join sometimes the returning data will change. So try giving same query in both places and check.

Was this reply helpful? Yes No
Martin Dráb 240,312 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You didn't tell us how you designed your view and what you mean by "incorrect values", but the join looks wrong to me.

I would expect that your view contains year and month values for inventory transactions and than you'll join InventTrans with the view over InventTransId. Your query returns the same year and month for all transactions with the same item. regardless of the value of DatePhysical field of the given transaction.

Was this reply helpful? Yes No