Thought this would be more simple but don’t get this to work…
This is the issue :
There are 2 SO’s in this example. You see for the first SO all lines have an “A” in Status field.
The second SO has two lines without an “A”.
What I now want is 1 field that counts all lines in an SO and a second field that count all lines with an “A” in an SO.
Somehow all my counts just return not the correct numbers in both cases.
What would be the correct way to count this table?
Ok lot of trial and error later I found that I had to use the addition ‘All’ in the count function… 
Sigh…too soon
I have a change indicator in the report that was set to Yes.
It will always be at yes anyway but I want it also to work when Yes and No are visible but it doesn’t:
Was trying with this:
=Count([ChangeIndicator];All) In ([SO]) Where ([ChangeIndicator] = “Yes”)
But where it should count 15 it only counts 9…
Brought it into excel and found :
Excel counts 15 and says the Average is 9…I get 9 in Webi…don’t think that is a coincidence?
Why would the count formula give me the average?
Did you want to have a line under each SO to summarise this?
The key thing to realise (if you weren’t already aware) is that the formula would be different.
For example, in the totals line you could have: =COUNT([SO Item]) Where ([Status manual price change]=“A”)
What are your formulas for Rows, RowsA, and ChangeIndicator?
Ok I will list the formulas I already have (where ChangeIndicator and RowsA already work fine)
ChangeIndicator:
=If Not (IsNull(Count([Status manual price change];All) In ([Key]) Where ([Status manual price change] = “A”))) Then “Yes” Else “No”
RowsA:
=Count([Status manual price change];All) Where ([Status manual price change] = “A”) In ([SO])
Rows:
=Count([ChangeIndicator];All) In ([SO]) Where ([ChangeIndicator] = “Yes”)
Ps: No I want no line under each SO to summarize.
Also RowA (the count of A’s) is working.
Its Rows…which I would think would be the most standard of the three 
*Edit: listing them I saw RowA and Row are slightly different (where they actually should do the same) in where i place the IN and WHERE clause.
However changing Rows to:
=Count([ChangeIndicator];All) Where ([ChangeIndicator] = “Yes”) In ([SO])
Did not change a thing unfortunately
Can you provide a screenshot in Excel of what you want it to look like? There is a lot to sift through to figure out what you want the final result to be.
Ok let met try to explain with this Excel screen:
Now I made change indicator to identify these 5 SO items that are in scope. At the end I will filter this report to show only with changeindicator “Yes” because the rest is out of scope.
As you see SO item 30 has 3 lines and only 2 “A’s”.
I made changeindicator in such a way that also a “Yes” comes in on the line within SO item 30 that has no “A” so that is good (all lines from SO item need to get in if there was somewhere a change in the SO item)
The “yes”/“no” works just fine.
Now you see that when you count all “yes” there are 15.
When you count all “A” there are 13 (you see SO item 30 and 40 both have a line without “A”)
These numbers are correct.
Now when I count all “A” in RowsA field it gives 13, so that is correct.
However when I count all rows with a “Yes” in field Rows I get 5…
It should give 15 because as you can see there are 15 “Yes” in the SO.
5 is the number of SO Items with an “A”, but the formula is counting lines with “Yes”…is it not??
The challenge in getting help in a forum like this is sharing reports and data. Obviously, you cannot (and should not) just upload your report or share proprietary data.
However, with free-hand SQL I think we can get to something workable. Hear me out.
Take this simple spreadsheet for example…
I can copy the sample data from my spreadsheet and drop it in this website…
There are a bunch of options here. All you need to do is…
Now you have some SQL that you can use as free-hand SQL. Even better, you can share it here so that we can be working with the same data you are. Ideally, you would build out your example with this free-hand SQL query.
You could even go a step further and drop that SQL into dbfiddle and verify it actually runs and is accurately reflecting your situation and share that link.
We can then take that SQL drop it in our own free-hand SQL query and if you provide us the formulas for any variables we theoretically now have a report the exhibits the issue you are having.
I fully realize that I am not answering your question. I am trying to help you help me so that I can help you. Short of data to work with there isn’t much I can do.
Ok let me try this.
I think I did everything like you said but when I paste this SQL in dbfiddle it gives an error…
So I will paste the SQL here hoping you can still look at it or tell which step I did not get right.
SELECT 9200 AS Plant,1000230845 AS Customer,701218112310 AS Key,‘ZSOR’ AS SO_Type,7012181123 AS SO,‘P097061000462H’ AS Material,0077471206 AS DocCondition,0077471206 AS First_line,0077657663 AS Last_change,NULL AS Price_Status,5 AS Rows,13 AS RowsA,‘No’ AS ChangeIndicator,‘Old’ AS TimeLine,5 AS Qnty,39 AS Pricing
UNION ALL
SELECT 9200,1000230845,701218112310,‘ZSOR’,7012181123,‘P097061000462H’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,5,NULL
UNION ALL
SELECT 9200,1000230845,701218112310,‘ZSOR’,7012181123,‘P097061000462H’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,5,39
UNION ALL
SELECT 9200,1000230845,7012181123100,‘ZSOR’,7012181123,‘P097091009660J’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,5,36
UNION ALL
SELECT 9200,1000230845,7012181123100,‘ZSOR’,7012181123,‘P097091009660J’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,5,NULL
UNION ALL
SELECT 9200,1000230845,7012181123100,‘ZSOR’,7012181123,‘P097091009660J’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,5,36
UNION ALL
SELECT 9200,1000230845,7012181123110,‘ZSOR’,7012181123,‘P097111010480-H’,0077471206,0077471206,0077521376,NULL,5,13,‘No’,‘Old’,190,36
UNION ALL
SELECT 9200,1000230845,7012181123110,‘ZSOR’,7012181123,‘P097111010480-H’,0077521376,0077471206,0077521376,NULL,5,13,‘No’,‘New’,190,36
UNION ALL
SELECT 9200,1000230845,7012181123120,‘ZSOR’,7012181123,‘P097111010480-HJ’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,20,36
UNION ALL
SELECT 9200,1000230845,7012181123120,‘ZSOR’,7012181123,‘P097111010480-HJ’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,20,NULL
UNION ALL
SELECT 9200,1000230845,7012181123120,‘ZSOR’,7012181123,‘P097111010480-HJ’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,20,36
UNION ALL
SELECT 9200,1000230845,7012181123130,‘ZSOR’,7012181123,‘P097111010481-H’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,10,36
UNION ALL
SELECT 9200,1000230845,7012181123130,‘ZSOR’,7012181123,‘P097111010481-H’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,10,NULL
UNION ALL
SELECT 9200,1000230845,7012181123130,‘ZSOR’,7012181123,‘P097111010481-H’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,10,36
UNION ALL
SELECT 9200,1000230845,7012181123140,‘ZSOR’,7012181123,‘P097111010482-H’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,10,36
UNION ALL
SELECT 9200,1000230845,7012181123140,‘ZSOR’,7012181123,‘P097111010482-H’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,10,NULL
UNION ALL
SELECT 9200,1000230845,7012181123140,‘ZSOR’,7012181123,‘P097111010482-H’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,10,36
UNION ALL
SELECT 9200,1000230845,7012181123150,‘ZSOR’,7012181123,‘P097111010485-H’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,10,36
UNION ALL
SELECT 9200,1000230845,7012181123150,‘ZSOR’,7012181123,‘P097111010485-H’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,10,NULL
UNION ALL
SELECT 9200,1000230845,7012181123150,‘ZSOR’,7012181123,‘P097111010485-H’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,10,36
UNION ALL
SELECT 9200,1000230845,7012181123160,‘ZSOR’,7012181123,‘P097091011834H’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,10,39
UNION ALL
SELECT 9200,1000230845,7012181123160,‘ZSOR’,7012181123,‘P097091011834H’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,10,NULL
UNION ALL
SELECT 9200,1000230845,7012181123160,‘ZSOR’,7012181123,‘P097091011834H’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,10,39
UNION ALL
SELECT 9200,1000230845,7012181123170,‘ZSOR’,7012181123,‘P001071011923-040’,0077471206,0077471206,0077521376,NULL,5,13,‘No’,‘Old’,2,314
UNION ALL
SELECT 9200,1000230845,7012181123170,‘ZSOR’,7012181123,‘P001071011923-040’,0077521376,0077471206,0077521376,NULL,5,13,‘No’,‘New’,2,314
UNION ALL
SELECT 9200,1000230845,7012181123180,‘ZSOR’,7012181123,‘P099041500225-18’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,3,300
UNION ALL
SELECT 9200,1000230845,7012181123180,‘ZSOR’,7012181123,‘P099041500225-18’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,3,NULL
UNION ALL
SELECT 9200,1000230845,7012181123180,‘ZSOR’,7012181123,‘P099041500225-18’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,3,300
UNION ALL
SELECT 9200,1000230845,7012181123190,‘ZSOR’,7012181123,‘P099041500225-23’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,3,300
UNION ALL
SELECT 9200,1000230845,7012181123190,‘ZSOR’,7012181123,‘P099041500225-23’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,3,NULL
UNION ALL
SELECT 9200,1000230845,7012181123190,‘ZSOR’,7012181123,‘P099041500225-23’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,3,300
UNION ALL
SELECT 9200,1000230845,701218112320,‘ZSOR’,7012181123,‘P001051001309’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,5,39
UNION ALL
SELECT 9200,1000230845,701218112320,‘ZSOR’,7012181123,‘P001051001309’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,5,NULL
UNION ALL
SELECT 9200,1000230845,701218112320,‘ZSOR’,7012181123,‘P001051001309’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,5,39
UNION ALL
SELECT 9200,1000230845,7012181123200,‘ZSOR’,7012181123,‘P099041500225-28’,0077471206,0077471206,0077521376,NULL,5,13,‘No’,‘Old’,3,300
UNION ALL
SELECT 9200,1000230845,7012181123200,‘ZSOR’,7012181123,‘P099041500225-28’,0077521376,0077471206,0077521376,NULL,5,13,‘No’,‘New’,3,300
UNION ALL
SELECT 9200,1000230845,7012181123210,‘ZSOR’,7012181123,‘P099041500250-15’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,1,300
UNION ALL
SELECT 9200,1000230845,7012181123210,‘ZSOR’,7012181123,‘P099041500250-15’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,1,NULL
UNION ALL
SELECT 9200,1000230845,7012181123210,‘ZSOR’,7012181123,‘P099041500250-15’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,1,300
UNION ALL
SELECT 9200,1000230845,7012181123220,‘ZSOR’,7012181123,‘P099041500275-12’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,2,300
UNION ALL
SELECT 9200,1000230845,7012181123220,‘ZSOR’,7012181123,‘P099041500275-12’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,2,NULL
UNION ALL
SELECT 9200,1000230845,7012181123220,‘ZSOR’,7012181123,‘P099041500275-12’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,2,300
UNION ALL
SELECT 9200,1000230845,7012181123230,‘ZSOR’,7012181123,‘P099041500300-15’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,2,300
UNION ALL
SELECT 9200,1000230845,7012181123230,‘ZSOR’,7012181123,‘P099041500300-15’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,2,NULL
UNION ALL
SELECT 9200,1000230845,7012181123230,‘ZSOR’,7012181123,‘P099041500300-15’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,2,300
UNION ALL
SELECT 9200,1000230845,7012181123240,‘ZSOR’,7012181123,‘P099041500300-23’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,2,300
UNION ALL
SELECT 9200,1000230845,7012181123240,‘ZSOR’,7012181123,‘P099041500300-23’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,2,NULL
UNION ALL
SELECT 9200,1000230845,7012181123240,‘ZSOR’,7012181123,‘P099041500300-23’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,2,300
UNION ALL
SELECT 9200,1000230845,7012181123250,‘ZSOR’,7012181123,‘P099041500300-38’,0077471206,0077471206,0077657663,‘A’,5,13,‘Yes’,‘Old’,1,300
UNION ALL
SELECT 9200,1000230845,7012181123250,‘ZSOR’,7012181123,‘P099041500300-38’,0077521376,0077471206,0077657663,‘A’,5,13,‘Yes’,‘Middle’,1,NULL
UNION ALL
SELECT 9200,1000230845,7012181123250,‘ZSOR’,7012181123,‘P099041500300-38’,0077657663,0077471206,0077657663,‘A’,5,13,‘Yes’,‘New’,1,300
UNION ALL
SELECT 9200,1000230845,7012181123260,‘ZSOR’,7012181123,‘P099041500350-15’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,2,300
UNION ALL
SELECT 9200,1000230845,7012181123260,‘ZSOR’,7012181123,‘P099041500350-15’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,2,NULL
UNION ALL
SELECT 9200,1000230845,7012181123260,‘ZSOR’,7012181123,‘P099041500350-15’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,2,300
UNION ALL
SELECT 9200,1000230845,7012181123270,‘ZSOR’,7012181123,‘P099041500350-18’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,1,300
UNION ALL
SELECT 9200,1000230845,7012181123270,‘ZSOR’,7012181123,‘P099041500350-18’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,1,NULL
UNION ALL
SELECT 9200,1000230845,7012181123270,‘ZSOR’,7012181123,‘P099041500350-18’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,1,300
UNION ALL
SELECT 9200,1000230845,7012181123280,‘ZSOR’,7012181123,‘P0010522225M’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,5,39
UNION ALL
SELECT 9200,1000230845,7012181123280,‘ZSOR’,7012181123,‘P0010522225M’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,5,NULL
UNION ALL
SELECT 9200,1000230845,7012181123280,‘ZSOR’,7012181123,‘P0010522225M’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,5,39
UNION ALL
SELECT 9200,1000230845,7012181123290,‘ZSOR’,7012181123,‘P0010522299M-W2’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,5,39
UNION ALL
SELECT 9200,1000230845,7012181123290,‘ZSOR’,7012181123,‘P0010522299M-W2’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,5,NULL
UNION ALL
SELECT 9200,1000230845,7012181123290,‘ZSOR’,7012181123,‘P0010522299M-W2’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,5,39
UNION ALL
SELECT 9200,1000230845,701218112330,‘ZSOR’,7012181123,‘P097081001780-HC’,0077471206,0077471206,0077657663,NULL,5,13,‘Yes’,‘Old’,700,38
UNION ALL
SELECT 9200,1000230845,701218112330,‘ZSOR’,7012181123,‘P097081001780-HC’,0077521376,0077471206,0077657663,‘A’,5,13,‘Yes’,‘Middle’,700,NULL
UNION ALL
SELECT 9200,1000230845,701218112330,‘ZSOR’,7012181123,‘P097081001780-HC’,0077657663,0077471206,0077657663,‘A’,5,13,‘Yes’,‘New’,700,36
UNION ALL
SELECT 9200,1000230845,7012181123300,‘ZSOR’,7012181123,‘P001051002703-02’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,20,46
UNION ALL
SELECT 9200,1000230845,7012181123300,‘ZSOR’,7012181123,‘P001051002703-02’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,20,NULL
UNION ALL
SELECT 9200,1000230845,7012181123300,‘ZSOR’,7012181123,‘P001051002703-02’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,20,46
UNION ALL
SELECT 9200,1000230845,7012181123310,‘ZSOR’,7012181123,‘P001051003282’,0077471206,0077471206,0077657663,‘A’,5,13,‘Yes’,‘Old’,5,35
UNION ALL
SELECT 9200,1000230845,7012181123310,‘ZSOR’,7012181123,‘P001051003282’,0077521376,0077471206,0077657663,‘A’,5,13,‘Yes’,‘Middle’,5,NULL
UNION ALL
SELECT 9200,1000230845,7012181123310,‘ZSOR’,7012181123,‘P001051003282’,0077657663,0077471206,0077657663,‘A’,5,13,‘Yes’,‘New’,5,35
UNION ALL
SELECT 9200,1000230845,7012181123320,‘ZSOR’,7012181123,‘P001061013785’,0077471206,0077471206,0077521376,NULL,5,13,‘No’,‘Old’,50,47
UNION ALL
SELECT 9200,1000230845,7012181123320,‘ZSOR’,7012181123,‘P001061013785’,0077521376,0077471206,0077521376,NULL,5,13,‘No’,‘New’,50,47
UNION ALL
SELECT 9200,1000230845,7012181123330,‘ZSOR’,7012181123,‘P099041014264-080’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,2,89
UNION ALL
SELECT 9200,1000230845,7012181123330,‘ZSOR’,7012181123,‘P099041014264-080’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,2,NULL
UNION ALL
SELECT 9200,1000230845,7012181123330,‘ZSOR’,7012181123,‘P099041014264-080’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,2,89
UNION ALL
SELECT 9200,1000230845,7012181123340,‘ZSOR’,7012181123,‘P0990811003-59’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,1,223
UNION ALL
SELECT 9200,1000230845,7012181123340,‘ZSOR’,7012181123,‘P0990811003-59’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,1,NULL
UNION ALL
SELECT 9200,1000230845,7012181123340,‘ZSOR’,7012181123,‘P0990811003-59’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,1,223
UNION ALL
SELECT 9200,1000230845,7012181123350,‘ZSOR’,7012181123,‘P0990811004-39’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,5,223
UNION ALL
SELECT 9200,1000230845,7012181123350,‘ZSOR’,7012181123,‘P0990811004-39’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,5,NULL
UNION ALL
SELECT 9200,1000230845,7012181123350,‘ZSOR’,7012181123,‘P0990811004-39’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,5,223
UNION ALL
SELECT 9200,1000230845,7012181123360,‘ZSOR’,7012181123,‘P0990811010-59’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,4,223
UNION ALL
SELECT 9200,1000230845,7012181123360,‘ZSOR’,7012181123,‘P0990811010-59’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,4,NULL
UNION ALL
SELECT 9200,1000230845,7012181123360,‘ZSOR’,7012181123,‘P0990811010-59’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,4,223
UNION ALL
SELECT 9200,1000230845,7012181123370,‘ZSOR’,7012181123,‘P0990912673-05’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,200,92
UNION ALL
SELECT 9200,1000230845,7012181123370,‘ZSOR’,7012181123,‘P0990912673-05’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,200,NULL
UNION ALL
SELECT 9200,1000230845,7012181123370,‘ZSOR’,7012181123,‘P0990912673-05’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,200,92
UNION ALL
SELECT 9200,1000230845,7012181123380,‘ZSOR’,7012181123,‘P0991412773-02’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,10,100
UNION ALL
SELECT 9200,1000230845,7012181123380,‘ZSOR’,7012181123,‘P0991412773-02’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,10,NULL
UNION ALL
SELECT 9200,1000230845,7012181123380,‘ZSOR’,7012181123,‘P0991412773-02’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,10,100
UNION ALL
SELECT 9200,1000230845,7012181123390,‘ZSOR’,7012181123,‘P0990814679-02’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,30,91
UNION ALL
SELECT 9200,1000230845,7012181123390,‘ZSOR’,7012181123,‘P0990814679-02’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,30,NULL
UNION ALL
SELECT 9200,1000230845,7012181123390,‘ZSOR’,7012181123,‘P0990814679-02’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,30,91
UNION ALL
SELECT 9200,1000230845,701218112340,‘ZSOR’,7012181123,‘P097081001780J-HC’,0077471206,0077471206,0077657663,NULL,5,13,‘Yes’,‘Old’,20,38
UNION ALL
SELECT 9200,1000230845,701218112340,‘ZSOR’,7012181123,‘P097081001780J-HC’,0077521376,0077471206,0077657663,‘A’,5,13,‘Yes’,‘Middle’,20,NULL
UNION ALL
SELECT 9200,1000230845,701218112340,‘ZSOR’,7012181123,‘P097081001780J-HC’,0077657663,0077471206,0077657663,‘A’,5,13,‘Yes’,‘New’,20,36
UNION ALL
SELECT 9200,1000230845,7012181123400,‘ZSOR’,7012181123,‘P0011022443-19’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,5,455
UNION ALL
SELECT 9200,1000230845,7012181123400,‘ZSOR’,7012181123,‘P0011022443-19’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,5,NULL
UNION ALL
SELECT 9200,1000230845,7012181123400,‘ZSOR’,7012181123,‘P0011022443-19’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,5,455
UNION ALL
SELECT 9200,1000230845,7012181123410,‘ZSOR’,7012181123,‘P0011342045060-080’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,1,354
UNION ALL
SELECT 9200,1000230845,7012181123410,‘ZSOR’,7012181123,‘P0011342045060-080’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,1,NULL
UNION ALL
SELECT 9200,1000230845,7012181123410,‘ZSOR’,7012181123,‘P0011342045060-080’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,1,354
UNION ALL
SELECT 9200,1000230845,7012181123420,‘ZSOR’,7012181123,‘P0011342055200-080’,0077471206,0077471206,0077657663,‘A’,5,13,‘Yes’,‘Old’,2,354
UNION ALL
SELECT 9200,1000230845,7012181123420,‘ZSOR’,7012181123,‘P0011342055200-080’,0077521376,0077471206,0077657663,‘A’,5,13,‘Yes’,‘Middle’,2,NULL
UNION ALL
SELECT 9200,1000230845,7012181123420,‘ZSOR’,7012181123,‘P0011342055200-080’,0077657663,0077471206,0077657663,‘A’,5,13,‘Yes’,‘New’,2,354
UNION ALL
SELECT 9200,1000230845,7012181123430,‘ZSOR’,7012181123,‘P0B812B1050-100’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,2,66
UNION ALL
SELECT 9200,1000230845,7012181123430,‘ZSOR’,7012181123,‘P0B812B1050-100’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,2,NULL
UNION ALL
SELECT 9200,1000230845,7012181123430,‘ZSOR’,7012181123,‘P0B812B1050-100’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,2,66
UNION ALL
SELECT 9200,1000230845,7012181123440,‘ZSOR’,7012181123,‘P0B812B1060-200’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,2,66
UNION ALL
SELECT 9200,1000230845,7012181123440,‘ZSOR’,7012181123,‘P0B812B1060-200’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,2,NULL
UNION ALL
SELECT 9200,1000230845,7012181123440,‘ZSOR’,7012181123,‘P0B812B1060-200’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,2,66
UNION ALL
SELECT 9200,1000230845,7012181123450,‘ZSOR’,7012181123,‘P0B812B1070-040’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,4,66
UNION ALL
SELECT 9200,1000230845,7012181123450,‘ZSOR’,7012181123,‘P0B812B1070-040’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,4,NULL
UNION ALL
SELECT 9200,1000230845,7012181123450,‘ZSOR’,7012181123,‘P0B812B1070-040’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,4,66
UNION ALL
SELECT 9200,1000230845,7012181123460,‘ZSOR’,7012181123,‘P0B812B2050-040’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,2,66
UNION ALL
SELECT 9200,1000230845,7012181123460,‘ZSOR’,7012181123,‘P0B812B2050-040’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,2,NULL
UNION ALL
SELECT 9200,1000230845,7012181123460,‘ZSOR’,7012181123,‘P0B812B2050-040’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,2,66
UNION ALL
SELECT 9200,1000230845,7012181123470,‘ZSOR’,7012181123,‘P0B812B2060-150’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,2,66
UNION ALL
SELECT 9200,1000230845,7012181123470,‘ZSOR’,7012181123,‘P0B812B2060-150’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,2,NULL
UNION ALL
SELECT 9200,1000230845,7012181123470,‘ZSOR’,7012181123,‘P0B812B2060-150’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,2,66
UNION ALL
SELECT 9200,1000230845,7012181123480,‘ZSOR’,7012181123,‘P0B812B2070-080’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,2,66
UNION ALL
SELECT 9200,1000230845,7012181123480,‘ZSOR’,7012181123,‘P0B812B2070-080’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,2,NULL
UNION ALL
SELECT 9200,1000230845,7012181123480,‘ZSOR’,7012181123,‘P0B812B2070-080’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,2,66
UNION ALL
SELECT 9200,1000230845,7012181123490,‘ZSOR’,7012181123,‘P0B812B2090-040’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,1,66
UNION ALL
SELECT 9200,1000230845,7012181123490,‘ZSOR’,7012181123,‘P0B812B2090-040’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,1,NULL
UNION ALL
SELECT 9200,1000230845,7012181123490,‘ZSOR’,7012181123,‘P0B812B2090-040’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,1,66
UNION ALL
SELECT 9200,1000230845,701218112350,‘ZSOR’,7012181123,‘P097091005351H’,0077471206,0077471206,0077521376,NULL,5,13,‘No’,‘Old’,30,39
UNION ALL
SELECT 9200,1000230845,701218112350,‘ZSOR’,7012181123,‘P097091005351H’,0077521376,0077471206,0077521376,NULL,5,13,‘No’,‘New’,30,39
UNION ALL
SELECT 9200,1000230845,701218112360,‘ZSOR’,7012181123,‘P097091005351HJ’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,20,39
UNION ALL
SELECT 9200,1000230845,701218112360,‘ZSOR’,7012181123,‘P097091005351HJ’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,20,NULL
UNION ALL
SELECT 9200,1000230845,701218112360,‘ZSOR’,7012181123,‘P097091005351HJ’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,20,39
UNION ALL
SELECT 9200,1000230845,701218112370,‘ZSOR’,7012181123,‘P097091005357H’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,50,39
UNION ALL
SELECT 9200,1000230845,701218112370,‘ZSOR’,7012181123,‘P097091005357H’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,50,NULL
UNION ALL
SELECT 9200,1000230845,701218112370,‘ZSOR’,7012181123,‘P097091005357H’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,50,39
UNION ALL
SELECT 9200,1000230845,701218112380,‘ZSOR’,7012181123,‘P097091005357HJ’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,135,39
UNION ALL
SELECT 9200,1000230845,701218112380,‘ZSOR’,7012181123,‘P097091005357HJ’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,135,NULL
UNION ALL
SELECT 9200,1000230845,701218112380,‘ZSOR’,7012181123,‘P097091005357HJ’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,135,39
UNION ALL
SELECT 9200,1000230845,701218112390,‘ZSOR’,7012181123,‘P097091009660’,0077471206,0077471206,0077657663,NULL,5,13,‘No’,‘Old’,100,36
UNION ALL
SELECT 9200,1000230845,701218112390,‘ZSOR’,7012181123,‘P097091009660’,0077521376,0077471206,0077657663,NULL,5,13,‘No’,‘Middle’,100,NULL
UNION ALL
SELECT 9200,1000230845,701218112390,‘ZSOR’,7012181123,‘P097091009660’,0077657663,0077471206,0077657663,NULL,5,13,‘No’,‘New’,100,36;
In the mean time I gave it another shot with a workaround.
So where I first counted all lines in an SO where the ChangeIndicator was “Yes” I now made a counter.
1 if ChangeIndicator is “Yes” and 0 if it is “No”.
Surprisingly the sum of this did give me the correct amounts per SO:
Now…immediately ran into the next strange behaviour in my data set…
Field Change status compares the 2 Row fields. If they are equal it should show Y and else X.
A simple if (rows = rowsa) then will not work…
Apparently this way 4 <> 4 ?? (If say rows > rowsa then it does work…so somehow the first 4 is more than the second 4…)
Also this I could tackle with a workaround by using if (rows - rowsa = 0) then Y else X (field Test)
That does work but I do not get why direct comparing the outcome of rows and rowsa does not work…
Lets hope you can get my data set sql to work.
Nice work! I think it didn’t like your use of Key as a column since that is a SQL keyword. To resolve that I enclosed it in square brackets…
701218112310 AS [Key]
Also, the your quotes were curly (‘) rather than straight ('). Picky, I know. Anyway, now we have a some working SQL to work with.
I will investigate further when I can find some time.
Your example data keeps changing. For example, the SQL you provided doesn’t have SO Create Date, but your latest screen shot does.
Also, the data in the SQL you provided has just one SO. However, your original stated objects says…
If that is still your objective, then your example data should contain cases that will show that. For example, have more than one SO in order to see the different count value.
I apologize if I have confused matters here, but this is beyond what I can assist with in this setting.
Ok let me try again.
Yes the list can have hundreds of SO’s but per SO the logic would be the same.
I will make an example with 2 SO’s (otherwise would get to long).
Bottomline:
A SO has SO Items. When something changes in such an Item it will get an A (and new doc condition number)
First thing I did was isolate the combi SO+SOItem (old field Key which I changed to Combi because of SQL)
Field ‘ChangeIndicator’ now gives yes if an SOItem has an A somewhere in one of its lines.
Example: combi 701218112330 has 3 lines and 2 have an “A” so all 3 lines have ChangeIndicator “Yes”.
Next step is the one I have problems with.
Because you will have 2 situations.
A) All the lines with “Yes” changeindicator for a combi have and “A”
B) Lines with “Yes” have also blank lines so there are less lines with an “A” then total lines in a combi
In the example I named those “Status A” and “Status B”
So these two states I want to identify for the lines with “Yes” in changeindicator.
To keep it all a bit readable I will paste the SQL in a next post.
*Edit:
Think I got it to work in dbfiddle:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d79bdc60b8fe638f3b17f95917272235
