-2

Hi my table looks like this

1

Combination of Rows with same S id is termed as a family. Output should have a Calculated Tier code based on below derivation

Output put should be Output

First time i am using Stackoverflow. I need a query in db2 and I can use the output as a report.

  • please provide desired output and avoid pasting screenshots – eshirvana Feb 04 '21 at 17:16
  • While asking a question, you need to provide a minimal reproducible example. Please refer to the following link: https://stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) – Yitzhak Khabinsky Feb 04 '21 at 17:17

1 Answers1

0

You need windows function as follows:

Select t.*,
       Concat('Tier ', count(*) over (partition by s_id) ) as tier
  From your_table t
Popeye
  • 35,427
  • 4
  • 10
  • 31
  • Thanks for your Query but its not just Count of number of dependents ,if under a Sid there is a set of Relationship code i.e EE+CH it should give some kind of 'Teir+EE+CH' if its EE+SP the it should give 'Tier+EE+SP' ,if its EE+CH+CH then it should be 'Tier+FAM' like that basically some kind of string for a set of combination of Relationship code. Hope I am clear about my question – Lovely Kolli Feb 05 '21 at 06:32
  • What? I have written query according to expected output. You should make your logic clear in the question itself. – Popeye Feb 05 '21 at 06:41
  • Thanks For you time REally Appreciate it.Is it possible to compare rows with in set of rows in table.Also I am writing query in Db2 – Lovely Kolli Feb 05 '21 at 07:28
  • Yes. It is done using windows function. You should really get your answer if you learn how to use windows function. – Popeye Feb 05 '21 at 08:00