-1

I have the same data on Account, Item, Project, Location, Date, and Version only differs in the Amount

Account Item Project Location Date Version Amount
Acc1 1001 B1 CA 11/27/2022 Actual 100
Acc1 1001 B1 CA 11/27/2022 Actual 230
Acc6 3001 A1 BA 11/28/2022 Actual 70
Acc6 3010 A2 AA 11/29/2022 Actual 400
Acc7 7001 B2 CD 11/30/2022 Actual 620

enter image description here

I just want the data to be fetched from the same date as the Amount 230

How to filter it by query?

Please help thank you

Zhorov
  • 28,486
  • 6
  • 27
  • 52
Arif
  • 27
  • 2
  • 1
    As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Feb 01 '23 at 07:57
  • 1
    Have you tried anything? – Dale K Feb 01 '23 at 07:58
  • 2
    Why have you chosen the amount 230 ? which condition are you based on ? – SelVazi Feb 01 '23 at 08:11
  • Seems to me like a poorly designed table. – Zohar Peled Feb 01 '23 at 08:13
  • 1
    @DaleK, the shown expected result for Acc1 is just picking one row out of two. I don't see where there is a summing. – Vladimir Baranov Feb 01 '23 at 08:21

2 Answers2

0

You can use GROUP BY and an AGGREGATE function to get your data. In example below I assumed you would like to have the highest Amount.

SELECT Account,
       Item,
       Project,
       Location,
       Date,
       Version,
       MAX(Amount) AS Amount 
FROM dbo.NotProvidedTableName
GROUP BY Account,
         Item,
         Project,
         Location,
         Date,
         Version
Peter
  • 475
  • 1
  • 8
-1

Question is not really well defined. The only difference I see is you ignore the lower amount for the rest of that dataset is exactly the same? In such case group by the first 6 columns and take the max of the 7th column.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Alex Vermeulen
  • 51
  • 7