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)

Verified answer

Martin Dráb 236,320 Most Valuable Professional on at

Like (2)

Report

RE: group by records based on a date field

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

RE: group by records based on a date field

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

Was this reply helpful? Yes No
deepak31 45 on at

Like (0)

Report
RE: group by records based on a date field

[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

RE: group by records based on a date field

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 236,320 Most Valuable Professional on at

Like (0)

Report

RE: group by records based on a date field

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

Join the conversation

Community site session details

group by records based on a date field

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

group by records based on a date field

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates