0

In sql server I wrote the code below. My table name is Finding and when i finished the query I want to see final situation with "Select * from Findings" code

But its show me the first version of the table (with out calculated fields). Can you help me how can i fixed this , how can ı call the final table with one command

select *,
(
  case
  when C.ACTION_CO -B.CLOSE_ACT_CO > 0 then 'OPEN'
  when C.ACTION_CO -B.CLOSE_ACT_CO <= 0 then 'CLOSED'
  ELSE 'ERROR' end) as FINDING_ACTUAL_STATU

from (
    select *,
      ([REPORT REF] +'-'+ [FINDING NO]) as ID_FINDING
    from Findings) A
    left join (
        select
          sum(case 
              when [RECO STATUS] = 'Closed' THEN 1
              ELSE 0 end) as CLOSE_ACT_CO,
          ([REPORT REF] +'-'+ [FINDING NO]) as ID_FINDINGG
        from Findings
        --where [RECO STATUS] = 'Closed'
        group by
          ([REPORT REF] +'-'+ [FINDING NO])
) B on (A.ID_FINDING = B.ID_FINDINGG)

left join (
    select
      ([REPORT REF] +'-'+ [FINDING NO]) as ID_FINDING,
      count(([REPORT REF] +'-'+ [RECO NO])) as ACTION_CO
    from Findings
    Group by
      ([REPORT REF] +'-'+ [FINDING NO])
) C on (A.ID_FINDING = C.ID_FINDING)
Charlieface
  • 52,284
  • 6
  • 19
  • 43
datamind
  • 1
  • 2
    That is not possible because you are not modifying Finding. You should make a view – nacho Aug 05 '22 at 12:11
  • a [View](https://learn.microsoft.com/en-us/sql/relational-databases/views/views?view=sql-server-ver16) could do this – GuidoG Aug 05 '22 at 12:17
  • Some basic formatting would go a long way. It's unclear what exactly your problem is. When you run this query you get a result, what's the issue? – Charlieface Aug 05 '22 at 14:22
  • A tangent - but when you see the same expression used multiple times, that should prompt you to reconsider the query and the schema. But why do this in all the derived tables? You can group by the two columns directly without the concatenation, leaving that to the outermost column list for the SELECT. You also refer to the Findings table multiple times - which suggest you could use conditional aggregation to achieve your goal. But that is a guess. It is not clear what "final situation" nor "first version" mean. – SMor Aug 05 '22 at 14:41
  • If I had to guess, it may be that you have a "first in group" issue with some aggregation. That first term can be used to find suggestions. – SMor Aug 05 '22 at 14:42

0 Answers0