-2

I have a small app that is linked to a SQL table. That particular table has the following fields:

  • key1(P. key)
  • shortchar05
  • number03
  • number04
  • number05

This app is to keep the stock updated.

So for key1=1, I enter item1 in shortchar05, number05 (total) I'll have 5 for example. number04 (this is used for how many item1 I have issued) and number03 should be updated stock (difference between number05 and number04).

I know how to make the app work if values of shortchar05 are distinct (e.g. item1, item2, item3 etc.), but that's not what I am interested in.

What if I have the following situation:

key1 shortchar05 number05 number04 number03
===========================================
1    item1          5        3        2
2    item2          7        4        3
3    item3          8        2        6

Now if I am creating the next key1 with value 4 but want to select item1 and now the total would be 2 which would go in number05 and I want to issue 1 piece of item1.

key1 shortchar05 number05 number04 number03
===========================================
4    item1          2        1        1

For key1=5 I want to select item1 again, so what I would like to have selected is the item1 with the lowest value from column number03 (key=4 and not key1=1)

key1 shortchar05 number05 number04 number03
===========================================
5    item1          1        0        1

This is what I would like to have, for n number of items, if I select itemX, I'd like to display the duplicate itemX with lowest value in number03 (that would be the updated total)

I hope I have been clear enough
Thank you!

This is simply done when creating a new key1 and entering a new distinct item

foreach (var ttUD04_R in ttUD04)
{
    var ttUD04_Recss = (from ttUD04_Row in Db.UD04 where ttUD04_Row.Company == Session.CompanyID orderby ttUD04_Row.Number03 ascending select ttUD04_Row).FirstOrDefault();
    if (ttUD04_Recss != null)
    {           
        ttUD04_R.Number05 = ttUD04_Recss.Number03;
        ttUD04_R.Number03 = (Convert.ToInt32(ttUD04_R.Number05) - Convert.ToInt32(ttUD04_R.Number04));
    }
}
René Vogt
  • 43,056
  • 14
  • 77
  • 99
John
  • 11
  • 1
  • 6
  • You talk about a lot of stuff that does not make sense and then you don't ask a question ... do you have a question? – Hogan Mar 04 '16 at 17:59
  • how to select itemX(even if duplicate) with lowest value in number03? How does that not make sense? I have explained, not talked about a lot of stuff! If you don't want to help fine, don't do it, but don't tell me that it is non sense. – John Mar 04 '16 at 18:05

2 Answers2

1

This query will find ONLY duplicate values and select only the smallest value:

var duplicateMin = (yourTable.GroupBy(x=>x).Where(g=>g.Count()>1).Select(y=>y.DuplicateValue)).Min();

The variable naming in your code looks terrible, it's just about close to impossible to read after you. Please consider renaming your variables. I mean just look at your names: ttUD04_R, ttUD04, ttUD04_Recss, Number05, Number03. This makes no sense at all and I can guarantee you that anyone else that will try to read after you will have absolutely no idea what you're doing. And it seems like you don't either.

I'd suggest renaming them to something more sensible like stockItems, updatedStock, itemStock.

Peroxy
  • 646
  • 8
  • 18
  • Thanks Peroxy, yes you're right I should have renamed them, instead of using as they are in my table. That's how those are defined in my Epicor table, so that's why I kept them like that. For others it might be confusing, but for me it's quite good. I appreciate! – John Mar 04 '16 at 18:24
  • Peroxy I have tried your solution but it does not contain a definition for DuplicateValue is there a special reference? – John Mar 04 '16 at 18:53
  • Well yeah, `DuplicateValue` is something I came up with, I have no idea what duplicate value you're looking for. It should be named like: `y => y.YourValueName`. – Peroxy Mar 04 '16 at 19:03
  • well, a random value from column shortchar05 – John Mar 04 '16 at 19:17
0

how to select itemX(even if duplicate) with lowest value in number03?

It does matter how you state it, for example if I state it like this:

Select lowest value for a field (in this case number03) from all items that match a criteria (in this case shortchar05 = 'item1')

Then the translation to linq is simple

All items that match the criteria:

var result = sourceList.Where(x => x.shortchar05 == 'item1');

Lowest value of number03 field -- order by that field and take the first one:

var result = sourceList.Where(x => x.shortchar05 == 'item1')
                       .OrderByDescending(x => x.number03)
                       .FirstOrDefault();
Hogan
  • 69,564
  • 10
  • 76
  • 117
  • I understood what you have said, but the thing is (perhaps I did not make myself clear enough, sorry for that) Let's say I have 4000 rows in my table, and multiple "item1", or multiple "item2" and so on. For example, now I open my window and write into my text box(shortchar05) -->"item239" What I would like to see is that it loads "item239" with lowest value stored in number03 column, even if it's 0. – John Mar 07 '16 at 20:49
  • @John -- that is what my code does. Just change the where to the value or variable you need `x.shortchar05 == 'item1'` – Hogan Mar 07 '16 at 22:11
  • no because it selects the highest value, and I need the lowest one. If I select FirstOrDefault and ordering ascending, it already assumes the value from number03 as 0, once a new row is created in the table. – John Mar 07 '16 at 22:27
  • @John - there is no table. If you want lowest value do `OrderBy`, if you max value do `OrderByDescending` -- what is the issue here? – Hogan Mar 08 '16 at 02:14
  • Hogan, yes there is, if you checked what I have stated in my description, you would have seen what I mean. When I create a new key1(primary key) I may enter a new item or enter a previously stored item. I know what OrderBy and OrderByDescending do, and I would need the min value stored in column number03 to be passed into shortchar05 in case an existing item is selected (when a new key1 is created -->new row in my table) Now, with the new row created, with OrderBy, the min value would be 0, and I do not want that, but min value of number03 from current key1-1(first key1 before my current one). – John Mar 08 '16 at 13:45
  • @John - you continue to make no sense. "when an existing item is selected" and "when new key1 is created" don't really mean anything. – Hogan Mar 08 '16 at 17:21
  • Hogan, not at all, it does make sense, perhaps you are not understanding what I mean. It's all right, I appreciate your help anyway. – John Mar 08 '16 at 18:03
  • @John -- well typically a question on SO is answered in 10 mins -- yours has been here for almost a week. I expect you question hasn't been answered because no one understands your question. I would review the "How to ask" and "what makes a good question" sections of the help and FAQ if you want this resource to be of use to you. Telling me (and everyone else) we are wrong won't get you far. – Hogan Mar 10 '16 at 15:19
  • Hogan, I never said you are wrong and I appreciate you help! Your answer helped me a lot in fact, I made it work yesterday. Thanks! – John Mar 10 '16 at 16:30